Search Results hr_leg_lookups




Overview
Explore in AI Chat

Overview

HR_LEG_LOOKUPS is an APPS-owned, HRMS-specific view that exposes Oracle E-Business Suite lookup values filtered for legislation sensitivity. It is documented at status VALID in the PER (Human Resources) product for both 12.1.1 and 12.2.2. As the name suggests, the view combines the generic Oracle Application Object Library lookup infrastructure (FND_LOOKUPS) with HR legislation context, so that a user or process in a given legislative environment sees only those lookup codes whose TAG directives permit display in that context. In Oracle EBS reporting and integration, the view functions as the sanctioned access path for HR-relevant lookup values, replacing direct queries against FND_LOOKUP_VALUES where legislation-driven visibility rules must be honored. Because the view is defined in APPS and references SYNONYMs, it is safe to reference from custom concurrent programs, BI Publisher data models, and inbound/outbound interfaces that require lookup-enabled values such as lookup types, meanings, and date ranges.

Underlying Base Objects

The documented base objects referenced by HR_LEG_LOOKUPS are FND_LOOKUP_TYPES (SYNONYM), FND_LOOKUP_VALUES (SYNONYM), and HR_API (PACKAGE). Functionally, the view is a projection over FND_LOOKUP_VALUES: the SELECT list draws LOOKUP_TYPE, LOOKUP_CODE, MEANING, DATE and audit columns, and the ENABLED_FLAG, START_DATE_ACTIVE, and END_DATE_ACTIVE attributes directly from that table. The view text reveals three important access rules. First, it restricts rows to LANGUAGE = USERENV('LANG'), returning the runtime language only. Second, it filters with VIEW_APPLICATION_ID = 3, tying results to the Human Resources application. Third, it applies a security group filter that compares SECURITY_GROUP_ID against either zero or FND_GLOBAL.LOOKUP_SECURITY_GROUP based on the client_info indicator. The legislation sensitivity is implemented by comparing the TAG column against HR_API.GET_LEGISLATION_CONTEXT: a leading "+" in the tag includes the row when the legislation context is found, a leading "-" excludes it, and an empty or absent tag defaults to visible ('Y'). Collectively, FND_LOOKUP_VALUES supplies the data, FND_LOOKUP_TYPES provides the type-level structure, and HR_API supplies the legislation context used to evaluate visibility.

Key Columns

  • APPLICATION_ID — the application identifier exposed by the view. The view text emits a literal value (808 for lookup types beginning with 'HXT', otherwise 800) rather than a stored column, reflecting the HR application context.
  • LOOKUP_TYPE — the lookup type name from FND_LOOKUP_VALUES; identifies the logical lookup set.
  • LOOKUP_CODE — the stored lookup code, the value typically persisted in transaction tables.
  • MEANING — the user-facing, translatable description of the lookup code.
  • ENABLED_FLAG — indicates whether the lookup value is currently active.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range within which the lookup code is valid.
  • DESCRIPTION — supplementary descriptive text associated with the lookup value.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns supporting traceability and delta extraction.

Common Use Cases and Queries

Typical scenarios include validating a stored code against an active HR lookup set, populating LOVs or BI Publisher parameters, and reconciling interface files against legislation-filtered values. Because the view enforces language, application, security group, and legislation rules, queries are shorter and less error-prone than equivalent joins on the base tables.

SELECT LOOKUP_CODE, MEANING
FROM   APPS.HR_LEG_LOOKUPS
WHERE  LOOKUP_TYPE = 'SOME_HR_LOOKUP_TYPE'
AND    ENABLED_FLAG = 'Y'
AND    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
                   AND NVL(END_DATE_ACTIVE, SYSDATE);

SELECT LOOKUP_TYPE, COUNT(*)
FROM   APPS.HR_LEG_LOOKUPS
WHERE  ENABLED_FLAG = 'Y'
GROUP  BY LOOKUP_TYPE
ORDER  BY 1;

Note that the view does not expose the underlying TAG column, so callers cannot re-evaluate legislation rules themselves; the visibility decision is applied inside the view through HR_API.GET_LEGISLATION_CONTEXT. When migrating or comparing environments, the audit columns support incremental extraction, while the effective-date columns should always be considered when the calling process is date sensitive.