Search Results post_dim_collect

Overview

EDW_SICM_UNSPSC_HOOK is a customization hook package in the Oracle E-Business Suite data warehouse layer that supports the Supply Chain Intelligence (SICM) product classification infrastructure. Its principal responsibility is to propagate United Nations Standard Products and Services Code (UNSPSC) function classifications into the Accounts Payable invoice fact table so that spend analysis reporting can aggregate purchasing activity by UNSPSC category. The package is owned by APPS and is classified as an OTHER API, indicating that it is not a public, supported integration interface but rather an internal extension point invoked by the warehouse load or collection process. The header comment references a 2003 revision, confirming that the object originated in an early release of the E-Business Suite analytics stack and has been carried forward through the 12.1.1 and 12.2.2 code lines largely unchanged.

Key Procedures and Functions

The package exposes a single documented function, POST_DIM_COLLECT. Its name reflects its role as a post-processing step in the dimension collection cycle: it is executed after the core dimension and fact staging logic has completed, and it returns a Boolean indicator of success. The function opens a cursor over the EDW_SPSC_FUNCTION_LSTG staging table, selecting each Function_PK whose Update_Fact_Flag is set to 'Y'. For every such function, it updates the UNSPSC foreign key column on the FII_AP_INV_LINES_F fact table, deriving the target key from the EDW_SPSC_FUNCTION_LTC lookup table. The update is restricted to fact rows whose item foreign key resolves through a three-way join between POA_DNB_ITEMS, EDW_ITEM_ITEMREV_LTC, and EDW_ITEM_ITEMORG_LTC, effectively limiting processing to items for which a valid organization and revision mapping exists. Because the loop iterates once per flagged function, the function performs an incremental, flag-driven refresh rather than a full-table rebuild, which keeps the collection window short.

Tables Accessed

• EDW_SPSC_FUNCTION_LSTG — read via cursor c_items to obtain the list of UNSPSC functions marked for fact update.
• EDW_SPSC_FUNCTION_LTC — read as the source of the derived UNSPSC Function_PK_Key value written to the fact table.
• FII_AP_INV_LINES_F — the AP invoice line fact table; the UNSPSC_FK_Key column is updated for qualifying rows.
• POA_DNB_ITEMS — provides the item-to-function association used to filter fact rows.
• EDW_ITEM_ITEMREV_LTC — supplies the item revision key that links to the fact table's Item_FK_Key.
• EDW_ITEM_ITEMORG_LTC — supplies the item-organization key used to join items to their revisions.

Usage Notes

POST_DIM_COLLECT is not intended for direct invocation from Oracle Forms or standard concurrent programs. It is called programmatically by the dimension collection framework, typically as the final phase of a warehouse load that refreshes the UNSPSC dimension hierarchy. Administrators troubleshooting spend reporting should note that new or changed UNSPSC assignments will not appear on AP invoice facts until EDW_SPSC_FUNCTION_LSTG rows carry Update_Fact_Flag = 'Y' and the collection process is executed to completion. The function returns TRUE unconditionally on successful completion of the loop, so callers should treat the Boolean as a control-flow signal rather than a row-count confirmation. Because the package performs set-based updates against a fact table, it should be scheduled within a maintenance window to avoid contention with concurrent AP transaction processing.