Search Results valid_code




Overview
Explore in AI Chat

Overview

The view APPS.HRI_CL_JOB_CAT_X_V is a Human Resources Intelligence (HRI) extraction view shipped with Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a normalized, effective-dated listing of job category lookup values, together with a synthetic placeholder row, in a column layout tailored for downstream reporting and integration consumers such as Oracle Business Intelligence and the E-Business Suite data warehouse (EDW). The view carries a VALID status and is owned by the APPS schema.

Its documented purpose is to expose lookup codes defined against the JOB_CATEGORIES lookup type as a code/description pair with date-effective boundaries and a validity indicator. The view is read-only (declared WITH READ ONLY), so it is intended purely for query access and carries no DML semantics.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over the following base objects:

  • HR_LOOKUPS (VIEW) — the primary source. The view selects LOOKUP_CODE, MEANING, START_DATE_ACTIVE, END_DATE_ACTIVE and ENABLED_FLAG from HR_LOOKUPS, filtered to LOOKUP_TYPE = 'JOB_CATEGORIES' and ENABLED_FLAG = 'Y', and further restricted so that the current system date falls within the lookup's effective date range.
  • HR_GENERAL (PACKAGE) — supplies the sentinel date constants START_OF_TIME and END_OF_TIME, used through NVL to substitute open-ended boundaries when the underlying lookup has no start or end date active.
  • HR_API (PACKAGE) — referenced as an HRI base object; it provides the HR security and lookup access utilities that underpin the HRI extraction layer.
  • DUAL (SYNONYM) — used to generate the hard-coded NA_EDW placeholder row appended via UNION ALL, ensuring the warehouse always receives a non-null fallback category.

Key Columns

  • ID — the lookup code itself (e.g. the job category code). For the appended placeholder row this value is NA_EDW.
  • VALUE — the lookup MEANING, i.e. the descriptive label for the category. An empty string is supplied for the placeholder row.
  • DATE_FROM — the effective start of the category, defaulting to HR_GENERAL.START_OF_TIME where no start date is recorded.
  • DATE_TO — the effective end of the category, defaulting to HR_GENERAL.END_OF_TIME where no end date is recorded.
  • VALID_CODE — the validity indicator carried directly from HRL.ENABLED_FLAG. Because the driving query filters on ENABLED_FLAG = 'Y', every returned row reports Y; the placeholder row also returns Y. This is the column surfaced to users searching on "valid_code".

Common Use Cases and Queries

The view is typically used to populate conformed job category dimensions in the HR warehouse and to drive lookups within HRI dashboards. Because date filtering against SYSDATE is baked in, callers need not add their own effective-date predicates for the current period. A representative query is:

  • SELECT ID, VALUE, DATE_FROM, DATE_TO, VALID_CODE FROM APPS.HRI_CL_JOB_CAT_X_V ORDER BY VALUE;
  • Filtering to genuine categories only: SELECT * FROM APPS.HRI_CL_JOB_CAT_X_V WHERE ID <> 'NA_EDW';
  • Joining to fact data: SELECT j.ID, j.VALUE, f.HEADCOUNT FROM APPS.HRI_CL_JOB_CAT_X_V j, HR_FACT f WHERE j.ID = f.JOB_CATEGORY;

Analysts should note that the view returns only currently enabled, currently effective categories, and that the VALID_CODE column is therefore always Y — the view cannot be used to retrieve disabled or expired job categories.