Results for “edw_items_pkg”

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

Overview

EDW_ITEMS_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite, classified under the ETRM API taxonomy as OTHER. Its name and header comment ($Header: ENIITEMS.pls) indicate that it belongs to the Enterprise Data Warehouse (EDW) / Enterprise Information integration layer rather than to the transactional item master itself. The package supplies a set of foreign-key resolution functions used to translate Oracle EBS item master entities into canonical surrogate keys for an external data warehouse or reporting model. Rather than returning business rows, each function returns a VARCHAR2 key string that identifies an item, item-organization combination, revision, category, product group, or interest assignment in the target dimensional schema.

The package is declared AUTHID CURRENT_USER, so it executes with the privileges of the calling schema, and it applies PRAGMA RESTRICT_REFERENCES (WNDS, WNPS, RNPS) to most functions to guarantee that they perform no writes and do not depend on package state — a requirement for safe invocation from SQL and from a WHERE clause.

Key Procedures and Functions

• ITEM_ORG_FK — Returns the warehouse foreign key for an item within a specific inventory organization. Two overloads exist: one accepting a category name (p_item_category) and one accepting a numeric p_item_category_id, allowing callers to supply either representation.
• ITEM_REV_FK — Returns the foreign key for a specific item revision within an organization. Used where revision-controlled items must be uniquely identified in the target model.
• CATEGORY_FK — Resolves a category foreign key from a functional area, a control value, and a category identifier.
• GET_PROD_GRP_FK — Returns the product group foreign key for an item and organization.
• GET_ITEM_FK — Returns an item foreign key derived from item, organization, interest type, and primary/secondary code identifiers, supporting interest- or code-based item partitions.
• GET_MASTER_PARENT — Accepts an organization identifier and returns a NUMBER identifying the master (parent) organization associated with it. This is the object the user searched for. Notably, its RESTRICT_REFERENCES pragma is commented out in the source, implying it is permitted to read database state in a way the other functions are not.

Tables Accessed

• EDW_LOCAL_INSTANCE — Resolves the local instance code used to qualify generated keys in multi-instance warehouse deployments.
• MTL_PARAMETERS — Supplies organization-level item master defaults, including master organization designation used by GET_MASTER_PARENT.
• MTL_ITEM_CATEGORIES and MTL_CATEGORIES_B / MTL_CATEGORIES_KFV — Provide category assignments and the descriptive flexfield key view needed to build category and item-organization keys.
• FND_ID_FLEX_STRUCTURES — Identifies the key flexfield structure definitions underlying item and category key construction.

Usage Notes

EDW_ITEMS_PKG is not a user-facing API and is not exposed through standard Item Master forms. It is invoked by extraction, transformation, and load routines, by EDW staging packages, and by custom SQL that must project EBS item data into a dimensional model. The presence of PRAGMA RESTRICT_REFERENCES confirms the functions are intended to be callable directly from SELECT statements. Its documented reference by 22 other packages indicates a shared utility role within the EDW integration layer. Because the package reads configuration tables such as MTL_PARAMETERS and EDW_LOCAL_INSTANCE, results are instance- and organization-context sensitive; callers should supply the correct organization identifier, particularly when invoking GET_MASTER_PARENT.