Search Results edw_item_itemorg_temp

Overview

EDW_ITEMS_M_C is a materialized-view maintenance package body owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Oracle Enterprise Data Warehouse (EDW) collection layer, which extracts and stages operational master and transactional data for downstream analytical and reporting consumption. As the mirror package for a materialized view, its procedures perform the incremental collection, transformation, and load of item master data from the EBS base tables into EDW staging and list tables.

The package is classified as OTHER rather than a public or standard API, indicating it is intended for internal invocation by the EDW collection framework rather than by external callers. It depends on EDW_COLLECTION_UTIL, EDW_ITEMS_PKG, EDW_LOG, and the companion package body EDW_ITEMCUSTOM_M_C, all of which provide logging, error handling, and shared extraction utilities. No other database object references EDW_ITEMS_M_C, confirming that it is a top-level collection driver within the EDW refresh cycle.

Key Procedures and Functions

Ten documented procedures make up the public surface of the package. Each is specialized for a distinct item-related entity and its associated staging table:

• PUSH — The primary entry point that orchestrates the collection run for the item domain.
• PUSH_EDW_ITEM_ITEM — Collects base item master records into EDW_ITEM_ITEM_LSTG and EDW_ITEM_ITEM_TEMP.
• PUSH_EDW_ITEM_ITEMORG — Collects item-to-organization assignments into EDW_ITEM_ITEMORG_LSTG and EDW_ITEM_ITEMORG_TEMP; this is the procedure associated with the user's search term.
• PUSH_EDW_ITEM_ITEMREV — Collects item revision data into EDW_ITEM_ITEMREV_LSTG.
• PUSH_EDW_ITEM_ITEM_CAT — Collects item category assignments.
• PUSH_EDW_ITEM_ITEMORG_CAT — Collects organization-specific category assignments.
• PUSH_EDW_ITEM_PROD_LINE — Collects product line hierarchy data.
• PUSH_EDW_ITEM_PROD_CATG — Collects product category hierarchy data.
• PUSH_EDW_ITEM_PROD_GRP — Collects product group hierarchy data.
• PUSH_EDW_ITEM_PRDFAM — Collects product family hierarchy data.

Parameter lists are not published in the ETRM metadata and are therefore not enumerated here.

Tables Accessed

The package reads source data from a defined set of EBS base and descriptive flexfield tables: MTL_SYSTEM_ITEMS_KFV (item master key flexfield view), MTL_CATEGORIES, MTL_CATEGORY_SETS with its _TL and _VL variants, MTL_DEFAULT_CATEGORY_SETS, MTL_ITEM_CATEGORIES, FND_ID_FLEX_SEGMENTS, FND_ID_FLEX_STRUCTURES_VL, and MFG_LOOKUPS. Instance context is obtained from EDW_LOCAL_INSTANCE and EDW_LOCAL_SYSTEM_PARAMETERS, while FND_PROFILE and FND_MESSAGE supply profile values and message text.

Targets include the EDW list and temporary tables: EDW_ITEM_ITEM_LSTG, EDW_ITEM_ITEM_TEMP, EDW_ITEM_ITEMORG_LSTG, EDW_ITEM_ITEMORG_TEMP, EDW_ITEM_ITEMREV_LSTG, EDW_ITEM_ITEM_CAT_LSTG, EDW_ITEM_ITEM_ORG_CAT_LSTG, EDW_ITEM_CATSETI3_C1_LSTG, EDW_ITEM_PRDFAM_LSTG, EDW_ITEM_PROD_CATG_LSTG, EDW_ITEM_PROD_GRP_LSTG, and EDW_ITEM_PROD_LINE_LSTG. Logging is written through EDW_LOG, and dynamic SQL for flexible column handling relies on DBMS_SQL and ALL_TAB_COLUMNS.

Usage Notes

EDW_ITEMS_M_C is invoked by the EDW materialized view refresh schedule, typically driven from concurrent program requests or the EDW collection utility rather than from Oracle Forms. Its procedures execute in sequence to populate staging tables that are subsequently used by the corresponding materialized views in the APPS schema. The presence of the _TEMP tables used by PUSH_EDW_ITEM_ITEM and PUSH_EDW_ITEM_ITEMORG indicates a multi-step load pattern in which intermediate results are materialized before being merged into the final list tables.

Because the package exposes no documented parameters and is not referenced by other database objects, customizations should avoid direct calls to individual PUSH_* procedures. Sites extending the EDW item dimension should instead build on the staged list tables or create their own collection routines that follow the same pattern, preserving the integrity of the standard refresh cycle and the EDW_LOG audit trail.