Results for “hr_lookups”

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

Overview

The HR_LOOKUPS view is an Oracle Application Object Library construct owned by the APPS schema and classified under the PER (Human Resources) product. It exposes HRMS-specific lookup information maintained in the Oracle E-Business Suite, presenting a filtered, security-aware projection of the generic lookup value table. In EBS 12.1.1 and 12.2.2, lookups are the primary configurable mechanism for driving list-of-values, validation, and descriptive flexfield behavior, and HRMS relies heavily on them for values such as lookup codes, status indicators, and legislation-dependent options.

Functionally, HR_LOOKUPS acts as a curated window into FND_LOOKUP_VALUES, restricted to the Human Resources application context (VIEW_APPLICATION_ID = 3) and further limited by language, tag-based legislation context, and security group filtering. It is therefore not a copy of the underlying data but a live, view-time computation. For reporting, integration, and troubleshooting, HR_LOOKUPS is the authoritative source of the HRMS lookup definitions that appear in application forms and self-service screens.

Underlying Base Objects

Per the documented metadata, the view is defined over FND_LOOKUP_VALUES, joined conceptually to FND_LOOKUP_TYPES, with logic referencing the HR_API package. In the ETRM 12.2.2 listing these appear as synonyms (FND_LOOKUP_TYPES and FND_LOOKUP_VALUES) plus the HR_API database package. The view text selects from FND_LOOKUP_VALUES (aliased FLV) and applies several predicates:

• FLV.VIEW_APPLICATION_ID = 3 — confines output to lookups visible within the HRMS application context.
• FLV.LANGUAGE = USERENV('LANG') — returns only the rows for the session language, so translated meanings are surfaced correctly.
• A security-group expression using FND_GLOBAL.LOOKUP_SECURITY_GROUP — enforces lookup-level security based on client information.
• A tag predicate referencing HR_API.GET_LEGISLATION_CONTEXT — includes or excludes rows based on whether the tag starts with '+', '-', or neither, thereby honoring legislation-specific lookup configuration.

The reference to FND_LOOKUP_TYPES is implicit in the classic FND lookup model, in which FND_LOOKUP_VALUES is the child of FND_LOOKUP_TYPES by LOOKUP_TYPE. The APPLICATION_ID column is derived rather than stored: the DECODE expression maps lookup types beginning with 'HXT' to 808 and all others to 800, reflecting the HRMS application identifiers used in the EBS data model.

Key Columns

• APPLICATION_ID — Derived value (808 for HXT-prefixed lookup types, otherwise 800) identifying the owning application context.
• LOOKUP_TYPE — The lookup category or "type" name, distinguishing groups of related codes.
• LOOKUP_CODE — The internal, stored code value used by the application.
• MEANING — The user-facing, translatable display text shown in forms and reports.
• ENABLED_FLAG — Indicates whether the lookup code is active and selectable at runtime.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Date bounds defining the effective period of the lookup code.
• DESCRIPTION — Optional longer explanation of the lookup code.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking creation and modification.

Common Use Cases and Queries

Typical scenarios include validating lookup values for HRMS integrations, generating reference extracts of active codes, and diagnosing why a particular code is not appearing in a form (usually a language, tag/legislation, or security-group filter). A representative query lists currently enabled lookups for a given type:

SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, START_DATE_ACTIVE, END_DATE_ACTIVE FROM APPS.HR_LOOKUPS WHERE LOOKUP_TYPE = :p_type AND ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE) AND NVL(END_DATE_ACTIVE, SYSDATE) ORDER BY MEANING;

Because the view depends on session context — USERENV('LANG'), USERENV('CLIENT_INFO'), and the HR_API legislation context — results can vary between sessions and responsibility-driven client information. Integrations and reports should therefore initialize the environment (for example via FND_GLOBAL.APPS_INITIALIZE) before querying, to ensure language and security-group predicates resolve as intended.