Search Results current_employee_flag




Overview
Explore in AI Chat

Overview

HRI_CL_PER_SUP_HX_V is an APPS-owned database view within the Oracle EBS Human Resources Intelligence (HRI) product family. In ETRM 12.2.2 the object is documented with STATUS VALID, though the HRI product line itself is flagged as obsolete. The view was designed to expose a supervisor hierarchy listing of current employees in a flattened, denormalized form suitable for downstream reporting, extract-transform-load staging, and historical ("_HX") snapshot consumption by Oracle HR Intelligence and related analytical schemas.

Rather than returning an arbitrary list of persons, the view restricts its result set to individuals whose CURRENT_EMPLOYEE_FLAG equals 'Y'. It further constrains the population through a correlated EXISTS subquery that requires each returned supervisor to actually supervise at least one other current employee. A UNION ALL block appends a single synthetic "NA_EDW" sentinel row sourced from DUAL, providing a placeholder (ID = -1) used by the enterprise data warehouse to represent null or unknown supervisor assignments. The view is declared WITH READ ONLY, making it safe for concurrent reporting consumption.

Underlying Base Objects

The view text selects exclusively from PER_PEOPLE_X, joined implicitly through the subquery to PER_ASSIGNMENTS_X. PER_PEOPLE_X is the date-effective (datetracked) slice of PER_PEOPLE_F and supplies person attributes including CURRENT_EMPLOYEE_FLAG; PER_ASSIGNMENTS_X is the date-effective slice of PER_ASSIGNMENTS_F and supplies the SUPERVISOR_ID linkage used to build the hierarchy. The remaining referenced objects are utility dependencies: HR_GENERAL (package) provides the START_OF_TIME and END_OF_TIME constants used to bound the sentinel row; HR_PERSON_NAME and HR_SECURITY are documented package dependencies, the latter enforcing row-level security so that the view returns only persons the querying responsibility is permitted to see; and DUAL (synonym) supplies the sentinel row.

Key Columns

  • ID — person identifier, sourced from PERSON_ID (or -1 for the NA_EDW sentinel row).
  • VALUE — the display value, populated from FULL_NAME; blank for the sentinel row.
  • DATE_FROM / DATE_TO — effective start and end dates of the person record; the sentinel uses HR_GENERAL.START_OF_TIME and END_OF_TIME.
  • BUSINESS_GROUP_ID — the business group to which the person belongs (-1 for the sentinel).
  • CURRENT_EMPLOYEE_FLAG — the filter column of interest; always 'Y' for real rows and 'NA_EDW' for the sentinel.
  • CURRENT_APPLICANT_FLAG — indicates whether the person is currently an applicant.
  • CURRENT_EMP_OR_APL_FLAG — indicates the person is currently either an employee or an applicant.
  • PERSON_TYPE_ID — the person type classification identifier (-1 for the sentinel).
  • COMMENT_ID — the associated comment/note identifier (-1 for the sentinel).

Common Use Cases and Queries

The view is typically used to populate supervisor lookups and hierarchy dimensions in HR Intelligence reporting, and to provide the "unknown supervisor" sentinel row required by the EDW. A basic query is:

SELECT ID, VALUE, DATE_FROM, DATE_TO, BUSINESS_GROUP_ID FROM APPS.HRI_CL_PER_SUP_HX_V WHERE ID <> -1 ORDER BY VALUE;

To include every valid supervisor together with the NA_EDW placeholder for outer-join scenarios:

SELECT ID, VALUE FROM APPS.HRI_CL_PER_SUP_HX_V ORDER BY ID;

Because the view is READ ONLY and enforces HR_SECURITY, it should be queried as the APPS schema (or via a synonym) within a responsibility whose security profile grants access to the underlying persons. Given the obsolete status of HRI, new development should favor current PER_PEOPLE_X / PER_ASSIGNMENTS_X based alternatives, but the view remains valid for existing 12.1.1 and 12.2.2 integrations that depend on its exact column contract, including the CURRENT_EMPLOYEE_FLAG column that appears both as a projected attribute and as the primary filtering predicate.