Results for “psa_lookup_types”

22 results




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

Overview

PSA_LOOKUP_TYPES is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined against the Public Sector Financials (PSA) product. It presents the set of seeded lookup types that belong to the Public Sector Applications, exposing the lookup type name, its description, and a derived indicator of whether the lookup values are user-updateable. In EBS 12.1.1 and 12.2.2 the view is shipped with a status of VALID in the ETRM metadata, meaning it is a supported, documented object rather than a dormant or invalidated artifact.

The view exists primarily to give Public Sector Financials functionality and reporting a filtered, product-scoped window onto the central lookup repository. Rather than requiring reports, integrations, or concurrent programs to filter the global FND_LOOKUP_TYPES_VL view by application and security group on every access, PSA_LOOKUP_TYPES encapsulates that logic and the decode of customization semantics in a single reusable definition. For the reported search term user_updateable_flag, this view is the object where that derived flag is calculated and surfaced.

Underlying Base Objects

The view is defined over two documented base objects: FND_LOOKUP_TYPES_VL, the translatable view over FND_LOOKUP_TYPES, and, by reference in the security-group subquery, FND_LOOKUP_TYPES (recorded as a synonym in the metadata). The main SELECT reads LV.LOOKUP_TYPE, LV.DESCRIPTION, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE from FND_LOOKUP_TYPES_VL LV.

Two filters constrain the result set. First, LV.VIEW_APPLICATION_ID must equal 8450, the application identifier for Public Sector Financials, which restricts output to PSA-owned lookup types. Second, SECURITY_GROUP_ID must equal the maximum security group id available to the session for the same application and lookup type, resolved through a correlated subquery that inspects USERENV('CLIENT_INFO'). This pattern is consistent with Oracle's multi-tenant and multi-org security-group handling in the lookup architecture.

Key Columns

  • LOOKUP_TYPE — the internal name of the seeded lookup type; the join key used throughout PSA configuration and code.
  • DESCRIPTION — the translated description of the lookup type, inherited from FND_LOOKUP_TYPES_VL.
  • USER_UPDATEABLE_FLAG — a derived Y/N column produced by DECODE(LV.CUSTOMIZATION_LEVEL, 'E', 'Y', 'N'). A value of Y indicates the customization level is 'E' (extensible/user-extensible), meaning users may extend the lookup by adding values; N indicates the lookup is not user-updateable. Because it is computed, the flag is read-only.
  • CREATED_BY / CREATION_DATE — the standard EBS who/audit columns recording the creation of the underlying lookup type row.
  • LAST_UPDATED_BY / LAST_UPDATE_DATE — the standard audit columns capturing the most recent modification of the underlying row.

Common Use Cases and Queries

Typical uses include reporting on the seeded Public Sector lookup types, auditing which lookups are user-extensible before a configuration change, and joining this view to FND_LOOKUP_VALUES to obtain the corresponding values for each type. A basic listing:

SELECT LOOKUP_TYPE, DESCRIPTION, USER_UPDATEABLE_FLAG
FROM   APPS.PSA_LOOKUP_TYPES
ORDER BY LOOKUP_TYPE;

To isolate ext