Search Results psp_lookups




Overview
Explore in AI Chat

Overview

PSP_LOOKUPS is a validity-checked view owned by the APPS schema in Oracle E-Business Suite, registered under the PSP (Labor Distribution) product family. It is documented as VALID in both the 12.1.1 and 12.2.2 releases and serves as a restricted projection of the Oracle Application Object Library lookup repository, exposing only those lookup types and lookup values that belong to Oracle Labor Distribution. In functional terms, the view answers the question "which codes does Labor Distribution define?" without requiring callers to know the internal numeric identifiers used by the shared FND lookup tables.

Because Oracle EBS stores reference data for every application in two common tables, unrestricted queries against FND_LOOKUPS or FND_LOOKUP_VALUES return entries belonging to hundreds of unrelated products. PSP_LOOKUPS resolves that ambiguity by scoping the result set to a single application, which makes it suitable for reports, concurrent programs, personalizations, and integration extracts that must resolve a Labor Distribution code to its meaning, description, and effective dates.

Underlying Base Objects

The documented definition of the view references two objects, both exposed to APPS through public synonyms: FND_LOOKUP_TYPES and FND_LOOKUP_VALUES. The view joins these on LOOKUP_TYPE, then filters FND_LOOKUP_TYPES.APPLICATION_ID = 8403, the application identifier registered for Oracle Labor Distribution, and restricts FND_LOOKUP_VALUES.LANGUAGE to USERENV('LANG') so that the caller receives meanings and descriptions in the session language rather than in every installed language.

The result is a denormalized join in which lookup-type metadata is combined with individual lookup values. The twelve exposed columns are drawn directly from the two sources: LOOKUP_TYPE identifies the code set and originates from both tables, while LOOKUP_CODE, MEANING, DESCRIPTION, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, and the standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) come from FND_LOOKUP_VALUES. Because the view is a stored SQL statement and not a table, it holds no data of its own; it inherits the read consistency and security behavior of the underlying lookup tables.

Key Columns

  • LOOKUP_TYPE — the lookup code set, the primary grouping key for Labor Distribution reference data.
  • LOOKUP_CODE — the stored, language-independent code value used by application logic and interfaces.
  • MEANING — the translatable display name shown to users; language-sensitive through USERENV('LANG').
  • DESCRIPTION — supplementary translatable text describing the code.
  • ENABLED_FLAG — Y or N; indicates whether the value may currently be selected.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range for the value; a NULL end date indicates no scheduled expiry.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN support change tracking and supportability analysis.

Common Use Cases and Queries

Typical uses include validating a code supplied by an external interface before inserting it into Labor Distribution tables, populating value sets and list-of-values definitions, and producing reference-data extracts. A standard query listing the enabled values for one code set follows:

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

To enumerate the code sets available to the product: SELECT DISTINCT lookup_type FROM apps.psp_lookups ORDER BY lookup_type;. For audit review of recently changed reference data, filter on LAST_UPDATE_DATE and join LAST_UPDATED_BY to FND_USER. Because the view enforces the language restriction automatically, no additional language predicate is required; results are returned in the session language, falling back to the base language as configured.