Search Results msc_st_item_categories




Overview
Explore in AI Chat

Overview

MSC_ST_ITEM_CATEGORIES is an Oracle Advanced Supply Chain Planning (MSC) staging table that serves as the temporary landing and validation surface for data destined for the production table MSC_ITEM_CATEGORIES. During a data collection run, the MSC collection program writes extracted source records into this staging table, validates each row against planning business rules and referential constraints, sets processing flags, and then transfers successfully validated records into the permanent planning table. This two-phase design isolates the collection and validation workload from the live planning structures and allows failed rows to remain in staging with diagnostics rather than corrupting the target table.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone, meaning the foreign-key analysis did not expose it as a classical hub, link, or satellite. The single documented foreign key, COMPANY_ID referencing PN_COMPANIES_ALL in the same MSC schema, suggests the table behaves more like a staging satellite keyed on the item/category/instance combination rather than a hub entity in its own right. This classification should be treated as a modeling suggestion only, since staging tables intentionally denormalize attributes that would otherwise be split across hubs, links, and satellites in a canonical warehouse design.

Key Information Stored

The table holds 39 documented columns in the 12.2.2 schema under owner MSC. The most operationally significant columns are:

The table is documented without a declared surrogate primary key; identity is effectively the combination of source instance, item, organization, and category, with the SOURCE_* columns (SOURCE_ORG_ID, SOURCE_INVENTORY_ITEM_ID, SOURCE_SR_CATEGORY_SET_ID, SOURCE_SR_CATEGORY_ID) preserving the original source keys for reconciliation back to the upstream system.

Common Use Cases and Queries

The primary use case is monitoring collection health. DBAs and planning administrators query staging rows filtered by PROCESS_FLAG to identify failed records and inspect ERROR_TEXT. A typical pattern is:

  • SELECT REQUEST_ID, PROCESS_FLAG, ERROR_TEXT, COUNT(*) FROM MSC.MSC_ST_ITEM_CATEGORIES WHERE REFRESH_ID = :refresh GROUP BY REQUEST_ID, PROCESS_FLAG, ERROR_TEXT;
  • SELECT * FROM MSC.MSC_ST_ITEM_CATEGORIES WHERE PROCESS_FLAG = 'E' OR ERROR_TEXT IS NOT NULL;
  • Comparing staged rows to the promoted rows in MSC_ITEM_CATEGORIES to detect records that were silently dropped during validation.
  • Auditing multi-instance collections by grouping on SR_INSTANCE_CODE and SR_INSTANCE_ID.
  • Reconciling SOURCE_* columns against the originating EBS item/category tables to confirm source-to-planning mapping.

Because this is a staging object, rows are typically purged or overwritten each refresh cycle; reporting queries should therefore be scoped by REFRESH_ID or REQUEST_ID.

Related Objects