Search Results hri_cl_cmpben_obj_x_v




Overview
Explore in AI Chat

Overview

The view APPS.HRI_CL_CMPBEN_OBJ_X_V is a component of the Oracle Human Resources Intelligence (HRI) product family, which supplies the extract and reporting logic used by Oracle E-Business Suite HR analytics and data warehousing integrations. Its role is narrow and well-defined: it exposes the set of valid lookup values associated with the lookup type IRC_VARIABLE_COMP_ELEMENT, together with a synthetic "not applicable" row used by the ETL layer. The suffix conventions (_X_V and the NA_EDW identifier) indicate an interface view intended for Enterprise Data Warehouse (EDW) extraction rather than for interactive forms or concurrent processing.

The view is owned by the APPS schema and is reported as VALID. Because it is defined with the WITH READ ONLY clause, it cannot be used for DML; it functions strictly as a query surface over Oracle HRMS lookup configuration.

Underlying Base Objects

The documented dependencies of the view are HR_LOOKUPS (view), HR_GENERAL (package), HR_API (package), and DUAL (synonym). In practice, the view text selects from HR_LOOKUPS filtered on LOOKUP_TYPE = 'IRC_VARIABLE_COMP_ELEMENT', and references HR_GENERAL for the sentinel date constants START_OF_TIME and END_OF_TIME. This construction lets the view return only currently effective lookup codes: the filter TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE, HR_GENERAL.START_OF_TIME) AND NVL(END_DATE_ACTIVE, HR_GENERAL.END_OF_TIME) resolves null date bounds to open-ended ranges. The second branch of the UNION ALL fabricates a single row with ID = 'NA_EDW' and an empty VALUE, ensuring downstream extracts always have a placeholder for "no variable compensation element."

The HR_API and HR_GENERAL package references reflect the standard HRMS utility layer used across HRI extract views for date handling and security context.

Key Columns

  • ID — the LOOKUP_CODE value, uniquely identifying the variable compensation element within the lookup type. Consumers join or map on this key.
  • VALUE — the human-readable MEANING of the lookup code, used for presentation and label mapping. For the synthetic row this is blank.
  • DESCRIPTION — the longer descriptive text carried on the lookup definition. The synthetic row returns TO_CHAR(NULL).
  • ENABLED_FLAG — indicates whether the lookup is enabled; the synthetic NA_EDW row is forced to 'Y'.
  • START_DATE and END_DATE — the effective date range of the lookup, sourced from START_DATE_ACTIVE and END_DATE_ACTIVE, with the synthetic row bounded by HR_GENERAL.START_OF_TIME and END_OF_TIME.

Common Use Cases and Queries

Typical consumers use this view to resolve the meaning and description of a stored variable compensation element code during compensation analytics loads, or to populate a dimension of valid elements in an EDW target schema. A representative query retrieves the active lookup set excluding the sentinel row:

  • SELECT ID, VALUE, DESCRIPTION FROM APPS.HRI_CL_CMPBEN_OBJ_X_V WHERE ID <> 'NA_EDW';
  • SELECT ID, VALUE FROM APPS.HRI_CL_CMPBEN_OBJ_X_V WHERE ENABLED_FLAG = 'Y';
  • SELECT v.ID, v.VALUE, c.employee_number FROM APPS.HRI_CL_CMPBEN_OBJ_X_V v, <compensation fact> c WHERE v.ID = c.variable_comp_element;

Because the view already applies the effective-date filter against SYSDATE, callers need not repeat date logic. Where historical or future-dated lookups are required, the underlying HR_LOOKUPS view must be queried directly, since this interface view exposes only currently effective values plus the mandatory NA_EDW placeholder. The view should be treated as read-only in all integration code paths.