Search Results po_lookup_types




Overview
Explore in AI Chat

Overview

PO_LOOKUP_TYPES is an APPS-owned database view in Oracle E-Business Suite that exposes a filtered, language-aware subset of Oracle Purchasing lookup type definitions. It is not a base table; it is a projection built over the FND lookups infrastructure, deliberately narrowed to the Purchasing application context (VIEW_APPLICATION_ID = 201). Because the view joins the translation table to the base lookup types table and resolves the current runtime language through USERENV('LANG'), it returns lookup type metadata in the session's active language rather than in a single fixed language.

In Oracle EBS 12.1.1 and 12.2.2, this view functions as a reporting and integration access path for lookup types belonging to the Purchasing module. It shields consumers from the complexity of FND_LOOKUP_TYPES_TL joins, language filtering, and security group resolution, presenting a single flattened result set that reports and interfaces can query directly.

Underlying Base Objects

The view is defined over two documented base objects, both referenced as synonyms in the APPS schema:

The join predicates match LOOKUP_TYPE, SECURITY_GROUP_ID, and VIEW_APPLICATION_ID across both tables. The predicate T.LANGUAGE = USERENV('LANG') restricts results to the current session language. The view further constrains VIEW_APPLICATION_ID to 201 (Purchasing) and resolves SECURITY_GROUP_ID to the maximum value available among security groups 0 and the client-info-derived group, using the SUBSTRB/USERENV('CLIENT_INFO') idiom at offset 55.

Notably, the view definition also projects placeholder NULL columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — created with TO_NUMBER(NULL) and TO_DATE(NULL), a common technique to satisfy a fixed column signature expected by concurrent program or interface consumers.

Key Columns

  • LOOKUP_TYPE — the internal name of the lookup type (for example, a Purchasing document or approval-related lookup). This is the primary identifying value.
  • MEANING — the language-specific, user-facing description of the lookup type, resolved from the translation table for the session language.
  • USER_UPDATABLE_FLAG — documented in the view column list; indicates whether the lookup type can be maintained by users, subject to customization level.
  • DESCRIPTION — descriptive text associated with the lookup type per the view column list.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN, inherited from the translation table, supporting change tracking and audit reporting.
  • CUSTOMIZATION_LEVEL — sourced from the base table, indicating the extensibility level of the lookup type.
  • Placeholder program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, returned as NULL to preserve a standard column contract.

Common Use Cases and Queries

The view is typically used to populate LOVs, validate lookup-type existence in integration code, and drive reports over Purchasing lookup metadata without embedding FND join logic. A representative query retrieves all user-facing lookup types available in the current language:

  • SELECT lookup_type, meaning, description, customization_level FROM apps.po_lookup_types ORDER BY lookup_type;
  • SELECT lookup_type, meaning FROM apps.po_lookup_types WHERE lookup_type LIKE 'PO%';
  • SELECT lookup_type, last_update_date, last_updated_by FROM apps.po_lookup_types WHERE last_update_date >= SYSDATE - 30;

Because results depend on USERENV('LANG') and USERENV('CLIENT_INFO'), sessions with different language or security-group contexts may return different row sets. Consumers should therefore avoid relying on cached results across sessions and should invoke the view within the same runtime context in which the data is required.