Search Results msc_st_boms




Overview
Explore in AI Chat

Overview

MSC_ST_BOMS is a staging table in the MSC schema (Advanced Supply Chain Planning) in Oracle EBS 12.1.1 and 12.2.2. Its documented purpose is to hold data collected by the collection program so that it can be validated and processed into the base planning table MSC_BOMS. In other words, MSC_ST_BOMS is the transient landing area between source bills of material and the planning engine's own BOM snapshot: rows arrive from the source system, the collection program validates them, and valid rows are merged into MSC_BOMS, the table the planner actually reads when exploding assemblies and computing dependent demand.

The documented physical schema for 12.2.2 shows the table owned by MSC with 41 columns. From the FK structure mined for this object, its heuristic Data Vault classification is standalone. This is a reasonable modeling suggestion because the only documented foreign key is a reference to PN_COMPANIES_ALL via COMPANY_ID; the table does not anchor a hub-and-link graph of its own. In Data Vault terms it behaves more like a staging/pit structure (a processing staging table) than a conformed hub or link, since its identity is driven by the business key of the assembly/BOM it represents rather than by a stable surrogate key of its own.

Key Information Stored

The columns that carry the most planning meaning are:

No surrogate primary key is documented in the metadata; the nearest business-key candidates are the combination of ORGANIZATION_ID, ASSEMBLY_ITEM_ID, ALTERNATE_BOM_DESIGNATOR and BILL_SEQUENCE_ID. Standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE) are present, along with ITEM_PROCESS_COST and OPERATION_SEQ_NUM for cost and operation context.

Common Use Cases and Queries

The typical operational use is diagnosing collection errors before the data reaches MSC_BOMS. A planner or DBA checks rows that failed validation:

  • SELECT BILL_SEQUENCE_ID, ORGANIZATION_CODE, ASSEMBLY_NAME, PROCESS_FLAG, ERROR_TEXT FROM MSC_ST_BOMS WHERE PROCESS_FLAG <> 1;
  • Reconciliation against the target snapshot: SELECT s.* FROM MSC_ST_BOMS s WHERE NOT EXISTS (SELECT 1 FROM MSC_BOMS b WHERE b.BILL_SEQUENCE_ID = s.BILL_SEQUENCE_ID);
  • Run-level audit: SELECT REQUEST_ID, SR_INSTANCE_ID, REFRESH_ID, COUNT(*) FROM MSC_ST_BOMS GROUP BY REQUEST_ID, SR_INSTANCE_ID, REFRESH_ID;
  • Organization-level exceptions: SELECT ORGANIZATION_CODE, COUNT(*) FROM MSC_ST_BOMS WHERE PROCESS_FLAG <> 1 GROUP BY ORGANIZATION_CODE;

Reporting use cases include tracking collection throughput (rows per refresh/batch), identifying alternates and deleted bills before planning, and auditing whether every collected bill was successfully promoted to MSC_BOMS.

Related Objects

  • MSC_BOMS – the target planning table; MSC_ST_BOMS is its staging feed.
  • PN_COMPANIES_ALL – referenced through MSC_ST_BOMS.COMPANY_ID.
  • MSC_BOM_COMPONENTS – the component-level snapshot for bills loaded into MSC_BOMS.
  • MSC_SYSTEM_ITEMS_B – supplies item context for ASSEMBLY_ITEM_ID.
  • MSC_ST_BOM_COMPONENTS – the parallel staging table for BOM components.

Because MSC_ST_BOMS is populated and consumed by the MSC collection program rather than by end-user transactions, its most important integration surface is the concurrent program that performs the collection/validation run, invoked from the planning collection manager.