Search Results msc_st_component_substitutes




Overview
Explore in AI Chat

Overview

MSC_ST_COMPONENT_SUBSTITUTES is a staging table within the MSC schema of Oracle E-Business Suite, belonging to the Advanced Supply Chain Planning (ASCP) module. Its documented purpose is to serve as the intermediate landing area used by the collection program to validate and process data before it is committed to the base table MSC_COMPONENT_SUBSTITUTES. In every ASCP collection cycle, source data is extracted from operational systems, written into staging tables such as this one, validated, and only then merged into the destination planning tables. This two-step pattern isolates the planning engine from raw source data and allows the collection process to flag, reject, or correct records before they influence plan output.

The table is documented with 36 physical columns in ETRM 12.2.2 and is owned by the MSC schema. From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone. In practice, this reflects that the staging table functions as a transient load structure rather than a durable modelled entity; it does not behave as a classic hub, link, or satellite because records are typically purged or refreshed once processing completes.

Key Information Stored

The columns fall into three functional groups: business keys and identifiers, descriptive/denormalized attributes, and collection control fields.

Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) are also present.

Common Use Cases and Queries

The most frequent operational use is diagnosing collection failures. Records where PROCESS_FLAG indicates an error, combined with populated ERROR_TEXT, identify substitutes rejected during validation.

  • Error triage: SELECT COMPONENT_SEQUENCE_ID, SUBSTITUTE_ITEM_ID, ERROR_TEXT FROM MSC.MSC_ST_COMPONENT_SUBSTITUTES WHERE PROCESS_FLAG = 'E' OR ERROR_TEXT IS NOT NULL;
  • Source traceability: filtering by SR_INSTANCE_ID, REFRESH_ID, or BATCH_ID isolates a single collection run.
  • Substitute analysis by organization: grouping on ORGANIZATION_CODE and ASSEMBLY_NAME supports reporting on substitute coverage across assemblies.
  • Pre-merge verification: comparing staging rows to MSC_COMPONENT_SUBSTITUTES before and after a collection confirms successful processing.

Related Objects

  • MSC_COMPONENT_SUBSTITUTES — the destination base table this staging structure validates and feeds.
  • PN_COMPANIES_ALL — referenced by the documented foreign key MSC_ST_COMPONENT_SUBSTITUTES.COMPANY_ID → PN_COMPANIES_ALL.
  • MSC_ST_COMPONENTS — the companion staging table for parent component records sharing COMPONENT_SEQUENCE_ID.
  • MSC_COMPONENTS — the base component table providing the parent context.
  • MSC_SYSTEM_ITEMS and MSC_ST_SYSTEM_ITEMS — item master source and staging tables resolving SUBSTITUTE_ITEM_ID.
  • MSC_ST_BOMS / MSC_BOMS — BOM headers supplying BILL_SEQUENCE_ID and ALTERNATE_BOM_DESIGNATOR context.
  • Collection framework tables such as MSC_ST_INSTANCES — resolving SR_INSTANCE_ID and SR_INSTANCE_CODE.