Results for “per_categories”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

APPS.HRI_CL_EDU_QUAL_CAT_HX_V is a read-only Oracle EBS view that exposes the seeded "Person Categories" (lookup type PER_CATEGORIES) as a flattened, reporting-friendly set of ID/VALUE pairs scoped to the current effective date. The object belongs to the HRI (Human Resources Intelligence) family of extraction and reporting views, and its presence in the ETRM repository under 12.1.1 and 12.2.2 confirms it is a supported component of the HRMS analytics and data-warehouse layer. Its primary role is to decouple downstream reporting — particularly Oracle HRMS Intelligence, EBS Discoverer workbooks, and third-party ETL that extracts HR lookup data — from the underlying HR_LOOKUPS structure. By wrapping the lookup table with date-effective filtering and a sentinel row, the view guarantees that any consumer receives a clean, stable dimension of person categories without needing to understand flexfield, date-tracking, or null-handling logic in the base table.

Underlying Base Objects

Per the documented metadata, the view is defined over three referenced objects and one synonym:

• HR_LOOKUPS (VIEW) — the primary source. The view filters this object on LOOKUP_TYPE = 'PER_CATEGORIES' and applies date-effective logic against START_DATE_ACTIVE and END_DATE_ACTIVE using TRUNC(SYSDATE) as the evaluation point. The lookup code supplies the ID column and the meaning supplies the VALUE column.
• HR_GENERAL (PACKAGE) — supplies the START_OF_TIME and END_OF_TIME constants used by NVL to replace null start/end dates, ensuring every row carries a bounded effective range. The unioned sentinel row also draws its date range from this package.
• HR_API (PACKAGE) — referenced by the underlying HRMS lookup infrastructure; it supports the API-based maintenance of lookup values that the view reflects.
• DUAL (SYNONYM) — used to fabricate the 'NA_EDW' placeholder row that is unioned into the result set.

Because the view is marked WITH READ ONLY, no DML may be issued against it; all maintenance must be performed on the base lookup records through standard EBS lookup maintenance or the HR_API interface.

Key Columns

• ID — the lookup code from HR_LOOKUPS for the person category, or the literal 'NA_EDW' for the sentinel row. This is the value typically joined against the person category column on PER_ALL_PEOPLE_F and similar HR tables.
• VALUE — the descriptive meaning of the lookup code. For the sentinel row this column is returned as an empty string.
• START_DATE — the lookup's START_DATE_ACTIVE, or HR_GENERAL.START_OF_TIME when null. Defines the lower bound of the category's effective period.
• END_DATE — the lookup's END_DATE_ACTIVE, or HR_GENERAL.END_OF_TIME when null. Defines the upper bound of the effective period.
• ENABLED_FLAG — the lookup's enabled indicator; the sentinel row is hard-coded to 'Y'.

The columns collectively form a date-effective, optionally enabled dimension suitable for joining to person records on the ID column and constraining by the START_DATE/END_DATE boundaries.

Common Use Cases and Queries

The view is most often used to resolve the person category code on HR records into a readable label, or to populate a lookup dimension in an HR data warehouse. A typical query joining the category to employees follows:

• SELECT p.employee_number, v.value person_category FROM per_all_people_f p, hri_cl_edu_qual_cat_hx_v v WHERE p.person_category = v.id AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date AND v.id <> 'NA_EDW';
• Standalone validation of available categories: SELECT id, value, start_date, end_date FROM hri_cl_edu_qual_cat_hx_v WHERE enabled_flag = 'Y';
• Use as a Discoverer or BI Publisher lookup source for a parameter list of person categories, relying on the built-in effective-date filtering rather than re-implementing it against HR_LOOKUPS.

Because the view already restricts rows to those active on the current date, no additional SYSDATE predicate is required on the lookup itself; consumers only need to apply the person-level date-effective join conditions on the calling HR tables.