Results for “hr_standard_lookups”

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

Overview

HR_STANDARD_LOOKUPS is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and presents Human Resources-specific standard lookup values in a single, denormalized result set. The view is defined as "HRMS specific view based on FND_LOOKUP_TYPES and FND_LOOKUP_VALUES," meaning it consolidates lookup definitions and their corresponding code values into one queryable object. Its status is VALID in the ETRM documentation.

Unlike the generic FND_LOOKUPS view, which surfaces lookups across all applications, HR_STANDARD_LOOKUPS restricts its result set to lookups whose view application is Oracle Human Resources. It is therefore the appropriate source when a report, concurrent program, or integration must enumerate valid HR lookup codes as they appear to end users, without the noise of unrelated application lookups. Because lookups are frequently referenced by descriptive flexfields, value sets, and HR setup screens, the view serves as a stable, read-only bridge between the underlying flexfield lookup infrastructure and downstream reporting.

Underlying Base Objects

The view is defined over two synonyms, FND_LOOKUP_TYPES and FND_LOOKUP_VALUES. FND_LOOKUP_TYPES holds one row per lookup type, describing the lookup's owning application, meaning, and description. FND_LOOKUP_VALUES holds one row per lookup code belonging to a lookup type, including the translated meaning, description, enablement, and effective dates. The join is performed on LOOKUP_TYPE, SECURITY_GROUP_ID, and VIEW_APPLICATION_ID, ensuring that each value row is matched to its correct type row within the same security and view-application context.

Two predicates tightly scope the view. First, FLV.LANGUAGE = USERENV('LANG') restricts the output to the session's current language, so only translated meanings for the runtime language are returned. Second, FLV.VIEW_APPLICATION_ID = 3 fixes the view application to Oracle Human Resources (application_id 3), and FLV.SECURITY_GROUP_ID = 0 selects the standard, non-seeded security group. Together these filters make HR_STANDARD_LOOKUPS a language-sensitive, HR-only projection of the flexfield lookup tables.

Key Columns

• APPLICATION_ID — The application identifier sourced from FND_LOOKUP_TYPES; for this view it resolves to the HR application.
• LOOKUP_TYPE — The internal name of the lookup type, the primary grouping key for values.
• LOOKUP_CODE — The stored code value referenced by transactions and setup records.
• MEANING — The user-facing, language-specific display value for the code.
• DESCRIPTION — Optional longer text describing the lookup code.
• ENABLED_FLAG — Indicates whether the value is currently active (Y) or disabled (N).
• START_DATE_ACTIVE / END_DATE_ACTIVE — The effective date range for the value's validity.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard audit columns inherited from FND_LOOKUP_VALUES.

Common Use Cases and Queries

The most frequent use is enumerating the valid values of a specific HR lookup for a report or LOV replacement. Because the view is language-aware and effective-date aware, downstream logic must additionally test the enabled flag and date window. A typical query is:

SELECT lookup_code, meaning, description
FROM apps.hr_standard_lookups
WHERE lookup_type = :p_lookup_type
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE)
ORDER BY meaning;

Other scenarios include validating that a code supplied by an interface is still active before loading it into an HR interface table, driving a value set of "Table" type that references HR_STANDARD_LOOKUPS directly, and building cross-reference extracts that map stored codes to their translated meanings. When the source of a search is the FND_LOOKUP_TYPES object, HR_STANDARD_LOOKUPS provides the HR-scoped, value-level detail that complements the type-level metadata available from the lookup types table itself.