Search Results edw_spsc_function_lstg

Overview

EDW_SPSC_FUNCTION_LSTG is a staging (interface) table owned by the POA schema and delivered as part of the Purchasing Intelligence module in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to serve as the "Interface table for the UNSPSC Function level," positioning it within the extract-transform-load pipeline that populates the Purchasing Intelligence data warehouse from operational procurement data. The UNSPSC (United Nations Standard Products and Services Code) classification is used throughout Purchasing Intelligence for spend analysis, supplier performance reporting, and category management; this table handles the Function segment of that hierarchy before records are validated and promoted to their final analytical targets.

From a heuristic Data Vault modeling perspective, the metadata classifies this object as standalone, meaning no FK relationships to other business entities were mined. In practice, an interface table such as this behaves like a transient staging satellite or an unmodelled load artifact rather than a durable hub or link: it holds descriptive attributes and load-control columns staged for a single batch and is typically truncated or purged after processing. The standalone classification should therefore be treated as a modeling suggestion rather than an indication that the table participates in a conformed warehouse hierarchy.

Key Information Stored

The table exposes 20 documented columns. The most operationally significant include:

• ROW_ID — the surrogate row identifier for the staging record, and the only column participating in a documented foreign key (to CS_SYSTEMS_ALL_B_TEMP).
• REQUEST_ID — the concurrent request that loaded the batch, used to track and reconcile a specific ETL run.
• FUNCTION_PK — the surrogate primary-key value for the UNSPSC Function entity being staged.
• FUNCTION_CODE — the business key: the coded UNSPSC Function value itself, the natural identifier most often used in reconciliation.
• FUNCTION_DP and NAME — the descriptive label and display name for the Function record.
• COMMODITY_FK_KEY and COMMODITY_FK — linkage to the related commodity classification.
• OPERATION_CODE — indicates the DML action to be applied during promotion (insert, update, or delete).
• COLLECTION_STATUS and ERROR_CODE — validation outcome flags used to report rejected or failed rows.
• UPDATE_FACT_FLAG — controls whether downstream fact tables require refreshing.
• INSTANCE — identifies the source EBS instance or operating unit context.
• CREATION_DATE and LAST_UPDATE_DATE — standard audit columns supporting batch diagnostics.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — extensibility columns for client-specific staging data.

Common Use Cases and Queries

The primary use case is monitoring and troubleshooting the UNSPSC Function load into Purchasing Intelligence. Typical patterns include verifying that a concurrent request staged the expected volume of rows, identifying rejected records by error status, and confirming that FUNCTION_CODE values are unique within a batch. For example:

SELECT REQUEST_ID, COUNT(*) FROM POA.EDW_SPSC_FUNCTION_LSTG GROUP BY REQUEST_ID;

SELECT FUNCTION_CODE, NAME, ERROR_CODE FROM POA.EDW_SPSC_FUNCTION_LSTG WHERE COLLECTION_STATUS IS NULL OR ERROR_CODE IS NOT NULL;

Reporting use cases center on data-quality dashboards, reconciliation between staged and promoted Function counts, and diagnostic queries joining ROW_ID back to CS_SYSTEMS_ALL_B_TEMP to trace source-system provenance. Because the table is transient, queries should always be scoped by REQUEST_ID or CREATION_DATE.

Related Objects

The following objects are most relevant to working with this interface table:

• POA.EDW_SPSC_FUNCTION_LSTG.ROW_ID → CS_SYSTEMS_ALL_B_TEMP — the sole documented foreign-key relationship, linking staged rows to source-system registration data.
• UNSPSC Function hierarchy tables (Function, Family, Class, Commodity levels) — the final analytical targets that staged rows are promoted into.
• Commodity classification tables — referenced via COMMODITY_FK and COMMODITY_FK_KEY.
• Purchasing Intelligence fact and dimension views — consumers of the promoted Function records.
• Concurrent Programs and Concurrent Requests (FND_CONCURRENT_REQUESTS) — linked through REQUEST_ID for batch tracking.
• Additional EDW_SPSC_*_LSTG staging tables — sibling interface tables following the same load pattern for other UNSPSC levels.