Search Results edw_sicm_unspsc_hook

Overview

EDW_SICM_UNSPSC_HOOK is an Oracle E-Business Suite PL/SQL package owned by the APPS schema and classified under the ETRM metadata as an "OTHER" API type. Its name follows the Oracle Enterprise Data Warehouse (EDW) and Supplier Information and Content Management (SICM) naming conventions, and it participates in the UNSPSC (United Nations Standard Products and Services Code) classification integration layer. This package functions as a post-collection hook that runs after the EDW dimensional collection processes have extracted source data, allowing UNSPSC product and service classification data to be reconciled, transformed, or enriched before downstream reporting and analytics consume the collected records. It is bound to the EBS data model through references to the seeded EDW staging and derived tables, positioning it as an internal integration utility rather than an end-user-facing feature.

Key Procedures and Functions

The ETRM documentation records a single documented program unit within this package:

• POST_DIM_COLLECT — Invoked as a post-processing step following dimensional data collection. Its role is to apply UNSPSC-related logic to the collected dimensional data. The procedure is not described with an explicit parameter list in the source metadata, and no signature is asserted here.

Because only one procedure is exposed in the documented metadata, the package should be treated as a narrowly scoped hook rather than a general-purpose UNSPSC API. Its package body contains the SQL statements that drive the collected data, consistent with the ETRM structure that lists both a package specification and a package body.

Tables Accessed

The package reads and references the following tables through APPS synonyms, each serving a defined purpose in the collection pipeline:

• EDW_ITEM_ITEMORG_LTC — Item-to-item-organization level collection table, providing item context for classification linkage.
• EDW_ITEM_ITEMREV_LTC — Item revision level collection table, used where revision-specific data influences classification output.
• EDW_SPSC_FUNCTION_LSTG — UNSPSC function staging table, holding incoming classification function records for validation and processing.
• EDW_SPSC_FUNCTION_LTC — UNSPSC function level collection table, the target store for transformed function records.
• FII_AP_INV_LINES_F — Accounts Payable invoice line fact table, supplying spend and transaction data that may be associated with UNSPSC classification.
• POA_DNB_ITEMS — Purchasing/Procurement D&B item table, providing enriched item and supplier content used in classification matching.

Usage Notes

EDW_SICM_UNSPSC_HOOK is referenced by one other package and itself references the seeded EDW_POA_UNSPSC_HOOK, forming a web of interdependent UNSPSC hook packages across the APPS schema. It is typically invoked programmatically rather than from a form or a standalone concurrent program; the calling context is the EDW dimensional collection framework, where POST_DIM_COLLECT executes at the conclusion of a collection run. Customizations and extensions should respect the dependency chain: packages that include EDW_SICM_UNSPSC_HOOK in their own logic will be affected by any change to this hook. Direct invocation outside the standard collection sequence is not recommended, as the procedure assumes the collected dimensional tables are already populated. Organizations upgrading between EBS 12.1.1 and 12.2.2 should verify that dependent hook packages remain valid after patching, since the ETRM dependency information confirms cross-package references in both directions.