Search Results edw_items_pkg




Overview
Explore in AI Chat

Overview

EDW_ITEMS_PKG is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It forms part of the Oracle Enterprise Data Warehouse (EDW) extraction layer that supplies the Oracle Business Intelligence and Daily Business Intelligence (DBI) reporting infrastructure with item and category dimension data. The package provides the foreign-key resolution logic required to populate EDW staging and dimension tables, translating operational item and category identifiers from the transactional EBS schema into surrogate keys used by the warehouse. Its status is VALID in the documented ETRM metadata, confirming that the package compiles cleanly and that all referenced objects are present. The package is classified as an "OTHER" API, indicating that it is an Oracle-internal utility rather than a published, supported public API.

Key Procedures and Functions

The ETRM metadata documents seven callable units within the package body. Each serves a key-resolution or lookup role for EDW item and category processing:

  • ITEM_ORG_FK — Resolves the foreign key that associates an inventory item with its owning item organization, supporting organization-scoped item dimension records.
  • ITEM_REV_FK — Resolves the foreign key for item revisions, enabling the warehouse to track revision-level item attributes.
  • CATEGORY_FK — Derives the surrogate key for item categories, linking items to their assigned category hierarchy nodes.
  • GET_PROD_GRP_FK — Returns the product group foreign key, mapping items to the product group classification used in EDW reporting hierarchies.
  • GET_ITEM_FK — Returns the item foreign key, the central surrogate key used to join fact data to the item dimension.
  • GET_MASTER_PARENT — Identifies the master parent for a master-organized item, essential for correctly roll-up reporting across organizations.

Because the ETRM metadata does not expose parameter lists, only the resolution purpose of each unit is documented here. Callers should consult the package specification in the target instance for exact signatures, which may vary slightly between 12.1.1 and 12.2.2.

Tables Accessed

The package reads from several EBS base tables through APPS synonyms:

  • MTL_PARAMETERS — Provides the organization definition and item master organization context used when resolving organization-scoped keys.
  • MTL_ITEM_CATEGORIES — Supplies the item-to-category assignments that drive CATEGORY_FK resolution.
  • MTL_CATEGORIES_B and MTL_CATEGORIES_KFV — Provide category definitions and the concatenated key flexfield value view used to present category segments.
  • FND_ID_FLEX_STRUCTURES — Identifies the item and category key flexfield structures, necessary for interpreting flexfield segments correctly.
  • EDW_LOCAL_INSTANCE — Provides EDW instance-level configuration and source system context for the extraction session.

The metadata indicates read access for extraction purposes; no write operations against these operational tables are documented.

Usage Notes

EDW_ITEMS_PKG is invoked by the EDW collection and dimension-load programs during nightly or scheduled warehouse refreshes. It is referenced by 22 other database objects in the documented metadata, confirming its role as a shared dependency within the EDW package family. Because it is classified as an internal utility and is not referenced as a public API, customizations should avoid direct calls. Developers extending EDW extracts should invoke it only within the same context as Oracle's delivered collection programs, and should re-validate behavior after applying 12.1.1 or 12.2.2 patches, since Oracle may revise item dimension logic between releases.