Search Results edw_item_itemrev_ltc

Overview

EDW_SICM_INV_LINES_HOOK is a customization hook package in the Oracle E-Business Suite (EBS) data warehouse and spend classification infrastructure. Its purpose is to enrich invoice line fact staging records with classification attributes—specifically DUNS numbers, SIC codes, and UNSPSC commodity codes—before those records are processed through the standard fact collection and load cycle. In EBS 12.1.1 and 12.2.2, this package sits within the Oracle Procurement and Spend Analytics (formerly Daily Business Intelligence) and Enterprise Data Warehouse (EDW) family of objects, alongside the FII_ and POA_ schema objects that populate the AP invoice fact tables used for spend analysis reporting.

The package is classified as the "hook" extension point for the invoice lines staging table. Rather than modifying shipped collection logic, Oracle exposes a Pre_Fact_Collect entry point that customer or Oracle-supplied code can use to enrich staging data while the records are still marked as READY for collection. This design allows classification enrichment to occur before the fact rows are committed to the warehouse.

Key Procedures and Functions

The package body exposes a single documented function:

• PRE_FACT_COLLECT – The hook function invoked before fact collection. It accepts the object name of the fact being processed and returns a Boolean indicating success. Internally, it performs two enrichment updates against the invoice lines fact staging table: first, it populates DUNS_FK and SIC_CODE_FK by joining the staging supplier key to the DUNS trading partner master; second, it populates UNSPSC_FK by joining through the item revision and item organization level tables to the DUNS items master. The function returns TRUE upon completion.

Tables Accessed

• FII_AP_INV_LINES_FSTG – The AP invoice lines fact staging table. This is the primary target, with DUNS_FK, SIC_CODE_FK, and UNSPSC_FK updated for rows whose Collection_Status is 'READY'.
• POA_DNB_TRD_PRTNR – The DUNS trading partner master. Read to obtain DUNS and SIC code values keyed by trading partner.
• EDW_ITEM_ITEMREV_LTC – Level table mapping item revisions to item revision primary keys. Referenced in the user's search term "edw_item_itemrev_ltc"; it links the fact item key to the item revision key.
• EDW_ITEM_ITEMORG_LTC – Item organization level table that relates item organization keys to item revision keys and item number keys.
• POA_DNB_ITEMS – The DUNS items master providing the UNSPSC function code keyed by item primary key.

Usage Notes

EDW_SICM_INV_LINES_HOOK is invoked automatically as part of the SICM (Spend Item Classification Management) fact collection pipeline. When the invoice lines fact collection executes, the framework calls PRE_FACT_COLLECT, passing the fact object name, so that classification attributes are populated before the standard load proceeds. It is not intended for direct user invocation and has no concurrent program or form of its own.

The package is referenced by one other package within the APPS schema, consistent with the hook pattern where a collection driver calls the hook. Because the UNSPSC update logic was revised under Bug 3046583 to derive data from the level tables (EDW_ITEM_ITEMREV_LTC and EDW_ITEM_ITEMORG_LTC) rather than from the star dimension table EDW_TIME_M, the level tables are the authoritative join path for item-to-commodity resolution. Customizations should not alter the READY collection status filter, since the updates intentionally target only records eligible for collection. Functional and technical consultants examining "edw_item_itemrev_ltc" in this context should recognize it as a dependent level table consumed by this hook, not as an independent API.