Search Results edw_sicm_sic_hook




Overview
Explore in AI Chat

Overview

EDW_SICM_SIC_HOOK is a PL/SQL package body owned by the APPS schema that forms part of the Oracle E-Business Suite Enterprise Data Warehouse (EDW) Standard Industry Code (SIC) staging and enrichment framework. Its principal role is to serve as a post-dimension-collection hook: once SIC code dimension data has been staged and validated, this package propagates the resulting SIC code foreign keys into the AP (Accounts Payable) fact tables housed in the warehouse. The SIC code is a classification of supplier trading partners by industry; associating the SIC surrogate key with AP transaction facts enables spend analysis, supplier segmentation, and regulatory or diversity reporting at the SIC level.

The package follows the standard EDW hook pattern, exposing a single entry point that the warehouse load orchestration layer invokes after dimension collection completes. It is a warehouse-side, batch-oriented utility rather than an online transaction component. The header comment dates the source to 2002, indicating the object predates the current 12.1.1 and 12.2.2 releases but remains present and referenceable in both.

The search term "tpartner_loc_pk_key" corresponds directly to a column selected within this package. TPartner_Loc_PK_Key is the surrogate key of the trading partner location dimension, used to join staged SIC assignments to AP fact rows via the supplier foreign key.

Key Procedures and Functions

  • POST_DIM_COLLECT — the sole documented procedure/function. It accepts an object name indicating the dimension collection context and returns a BOOLEAN success indicator. Internally, it opens a cursor over EDW_SICM_SIC_LSTG selecting SIC_CODE_PK values flagged with Update_Fact_Flag = 'Y', then iterates the results. For each staged SIC code, it performs correlated updates against multiple AP fact tables, setting their SIC_CODE_FK_KEY to the corresponding surrogate key from EDW_SICM_SIC_LTC. The WHERE clause restricts each update to fact rows whose Supplier_FK_Key matches a TPartner_Loc_PK_Key derived by joining POA_DNB_SIC_CODE, POA_DNB_TRD_PRTNR, and EDW_TPRT_TPARTNER_LOC_LTC on matching SIC_CODE values. The procedure therefore resolves the Dun & Bradstreet SIC assignment through the trading partner location dimension before writing the warehouse key.

Tables Accessed

  • EDW_SICM_SIC_LSTG — staging table of SIC codes; read to identify which codes require fact updates via Update_Fact_Flag.
  • EDW_SICM_SIC_LTC — SIC dimension table; read to obtain the SIC_CODE_PK_KEY surrogate key written into the fact tables.
  • EDW_TPRT_TPARTNER_LOC_LTC — trading partner location dimension; supplies TPartner_Loc_PK_Key, the value matched against the AP fact Supplier_FK_Key.
  • POA_DNB_SIC_CODE and POA_DNB_TRD_PRTNR — Dun & Bradstreet supplier and SIC assignment sources used to resolve the SIC-to-trading-partner relationship.
  • FII_AP_HOLD_DATA_F, FII_AP_INV_ON_HOLD_F, FII_AP_INV_LINES_F, FII_AP_INV_PAYMTS_F, FII_AP_SCH_PAYMTS_F — AP fact tables updated with the resolved SIC_CODE_FK_KEY (hold data, invoices on hold, invoice lines, invoice payments, and scheduled payments respectively).

Usage Notes

EDW_SICM_SIC_HOOK is invoked programmatically as a warehouse hook, not through a standard EBS form. It is called by the SIC dimension collection process after staging records are validated, at which point the Update_Fact_Flag is set and the fact-propagation logic runs. It is referenced by one other package in the documented metadata, confirming its role as a called dependency rather than a caller. Custom implementations extending SIC-based AP analytics may invoke POST_DIM_COLLECT directly, provided the AP fact and dimension tables are populated. Because updates are correlated subqueries keyed on the SIC staging list, execution should follow a completed dimension load; running it against an unpopulated or partially loaded warehouse yields no updates rather than errors.