Results for “msc_st_bom_components”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MSC_ST_BOM_COMPONENTS is a staging table in the MSC schema belonging to Oracle Advanced Supply Chain Planning (ASCP) in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to hold unvalidated, unprocessed bill-of-material component data collected from one or more source systems before that data is cleaned, validated, and merged into the production planning table MSC_BOM_COMPONENTS. The collection program writes raw rows into this staging table, applies validation logic, and then transfers only valid records into the destination table used by the planning engine.
The table carries 68 documented columns that mirror both the transactional BOM structure (component sequence, usage quantity, effectivity dates) and planning-specific attributes (planning factor, ATP flag, component yield factor, WIP supply type). Because it is a staging object, rows are transient: they are tied to a specific collection run via REQUEST_ID, SR_INSTANCE_ID, and REFRESH_ID, and are typically purged or overwritten on subsequent collections.
From a heuristic Data Vault modeling perspective, the metadata classifies this object as standalone. This suggests it is best modeled as a satellite-like staging construct rather than a true hub or link. It does not naturally function as a hub because its identifiers are collection-scoped and transient, and it does not function as a link because the relationships it carries are denormalized copies of source BOM relationships rather than durable associations.
Key Information Stored
The table's most significant columns fall into several categories:
- Primary/identity columns: COMPONENT_SEQUENCE_ID uniquely identifies a component row. ORGANIZATION_ID, INVENTORY_ITEM_ID, and BILL_SEQUENCE_ID together form the business-key candidates that tie the component to its parent bill and organization.
- Component definition: USING_ASSEMBLY_ID, COMPONENT_TYPE, SCALING_TYPE, REVISION, and CHANGE_NOTICE describe the component and the engineering change context.
- Quantity and units: USAGE_QUANTITY, UOM_CODE, PLANNING_FACTOR, COMPONENT_YIELD_FACTOR, and SCALE_MULTIPLE capture how much of the component is consumed.
- Effectivity: EFFECTIVITY_DATE, DISABLE_DATE, OLD_EFFECTIVITY_DATE, and SUGGESTED_EFFECTIVITY_DATE control when the component is active.
- Planning attributes: WIP_SUPPLY_TYPE, ATP_FLAG, OPTIONAL_COMPONENT, and DRIVING_ITEM_ID drive planning and ATP behavior.
- Collection/processing metadata: PROCESS_FLAG, ERROR_TEXT, MESSAGE_ID, DATA_SOURCE_TYPE, SR_INSTANCE_ID, REFRESH_ID, REQUEST_ID, BATCH_ID, and ST_TRANSACTION_ID track the collection and validation state of each row.
- Source audit columns: SOURCE_ORGANIZATION_ID, SOURCE_BILL_SEQUENCE_ID, SOURCE_COMPONENT_SEQUENCE_ID, SOURCE_USING_ASSEMBLY_ID, and SOURCE_INVENTORY_ITEM_ID preserve the originating identifiers from the source instance.
Common Use Cases and Queries
Typical uses involve monitoring collection health, diagnosing validation failures, and reconciling source-to-target transfers.
- Find failed rows:
SELECT COMPONENT_SEQUENCE_ID, ERROR_TEXT, PROCESS_FLAG FROM MSC_ST_BOM_COMPONENTS WHERE PROCESS_FLAG = 'ERROR'; - Inspect a collection run:
SELECT * FROM MSC_ST_BOM_COMPONENTS WHERE REQUEST_ID = :request_id AND REFRESH_ID = :refresh_id; - Count components per organization:
SELECT ORGANIZATION_ID, COUNT(*) FROM MSC_ST_BOM_COMPONENTS GROUP BY ORGANIZATION_ID; - Reconcile source to staging: join SOURCE_COMPONENT_SEQUENCE_ID against the source BOM table in the originating instance.
Related Objects
The following objects are most relevant based on documented foreign keys and the staging role:
- MSC_BOM_COMPONENTS — the destination production table.
- ENG_REVISED_ITEMS — referenced by REVISED_ITEM_SEQUENCE_ID.
- PN_COMPANIES_ALL — referenced by COMPANY_ID.
- BOM_COMPONENTS (source) — the transactional BOM table collected into staging.
- MTL_SYSTEM_ITEMS_B — for INVENTORY_ITEM_ID and DRIVING_ITEM_ID lookups.
- MTL_PARAMETERS — for ORGANIZATION_CODE and organization context.
-
The staging table used by the collection program to valid and process data for table MSC_BOM_COMPONENTS.
-
The staging table used by the collection program to valid and process data for table MSC_BOM_COMPONENTS.
-
12.1.1 DBA Data 12.1.1
-
List of staging tables used by Collections
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
List of staging tables used by Collections
-
12.2.2 DBA Data 12.2.2
-
12.2.2 DBA Data 12.2.2
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.1.1 DBA Data 12.1.1
-
12.2.2 FND Design Data 12.2.2