Search Results edw_lookup_pkg




Overview
Explore in AI Chat

Overview

EDW_LOOKUP_PKG is a stored PL/SQL package body owned by the APPS schema in Oracle E-Business Suite. It is an Enterprise Data Warehouse (EDW) utility rather than a transactional API, and it serves as the lookup-resolution layer for the EBS reporting and data-extraction stack. Its principal business function is to resolve and validate Oracle EBS lookup code values — the flexible key/value pairs maintained through the application's Lookup Codes setup — so that downstream extraction programs, materialized views, and OBIEE/E-Business Suite Analytics (formerly Daily Business Intelligence) content can join transactional data to its descriptive meaning.

The package is classified as OTHER within the ETRM registry, meaning it is not one of the certified public APIs and is not exposed through the standard interface tables. It is a valid, compiled object in the APPS schema and depends on the standard SYS.STANDARD package. It is referenced by five other packages, confirming its role as a shared internal service utility.

Key Procedures and Functions

The ETRM metadata documents a single entry point associated with this package body: LOOKUP_CODE_FK. Within the metadata it is also catalogued as the foreign-key object EDW_LOOKUP_CODE_FKV, indicating that the procedure or function is used as a lookup value source for foreign-key-style joins in the EDW layer.

  • LOOKUP_CODE_FK — Resolves lookup code values against the EBS lookup definitions so that a stored code (for example a status, type, or category code) can be translated into its meaning or validated as current and enabled. Because the ETRM record does not expose the formal parameter list, the intended contract should be confirmed by inspecting the deployed source in the APPS schema before reuse.

No other documented procedures or functions are recorded for this object in the ETRM 12.1.1 metadata.

Tables Accessed

The documented dependency information lists the package body as referencing the view or synonym EDW_LOOKUP_CODE_FKV and its own package specification EDW_LOOKUP_PKG, along with SYS.STANDARD. No base application tables (for example FND_LOOKUP_VALUES, FND_LOOKUPS, or the various _TL translation tables) are enumerated in the ETRM excerpt. In practice, lookup resolution logic of this kind derives from the Foundation lookup views, but the metadata as recorded restricts the confirmed dependency to EDW_LOOKUP_CODE_FKV. Any additional table access should be verified against the compiled source rather than assumed.

Usage Notes

EDW_LOOKUP_PKG is invoked programmatically, not through a form or a registered concurrent program of its own. Its typical callers are the five dependent packages identified in the dependency report, along with custom EDW extraction code and reporting joins that need consistent lookup decoding. Because the package is owned by APPS and declared VALID, it is available to any session with EXECUTE privileges on the APPS schema object.

Two cautions apply. First, because the object is classified as OTHER and carries no documented parameter signatures, it must be treated as an internal, non-certified utility; Oracle does not guarantee its interface across upgrades from 12.1.1 to 12.2.2, so custom code should be retested after any patch or upgrade. Second, the metadata records that EDW_LOOKUP_PKG is not referenced by any database object, meaning it is a leaf dependency — no views, triggers, or constraints rely on it, so changes to it carry no cascade risk within the database, but may silently break external reporting logic. Developers should review the deployed source before depending on its behaviour.