Search Results msc_component_substitutes




Overview
Explore in AI Chat

Overview

The MSC_COMPONENT_SUBSTITUTES table is a core data object within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) module. It serves as the planning engine's repository for defining and storing component substitution relationships within a bill of material (BOM). This table enables the planning process to consider alternative components that can be used in place of a primary component during manufacturing, which is critical for managing material shortages, cost optimization, and supply flexibility. Its data is populated during the planning data snapshot and is subsequently used by the planning engine's algorithms to generate feasible supply plans that account for available substitute items.

Key Information Stored

The table's structure is defined by a composite primary key that uniquely identifies each substitution record within the context of a specific plan. The key columns are PLAN_ID, BILL_SEQUENCE_ID, SUBSTITUTE_ITEM_ID, SR_INSTANCE_ID, ORGANIZATION_ID, and COMPONENT_SEQUENCE_ID. These fields anchor the substitution to a specific planning instance (PLAN_ID), a specific BOM (BILL_SEQUENCE_ID), and the precise component line within that BOM (COMPONENT_SEQUENCE_ID). The SUBSTITUTE_ITEM_ID holds the identifier for the item that can act as a substitute. The SR_INSTANCE_ID and ORGANIZATION_ID columns provide the necessary multi-organization and instance context for the data. The table's integrity is enforced through foreign key relationships to the MSC_BOM_COMPONENTS and MSC_SYSTEM_ITEMS tables.

Common Use Cases and Queries

A primary use case is analyzing substitution flexibility within a specific plan to mitigate projected material shortages. Planners or custom reports often query this table to list all available substitutes for critical components. A typical query would join MSC_COMPONENT_SUBSTITUTES with MSC_SYSTEM_ITEMS to retrieve substitute item details and with MSC_BOM_COMPONENTS for component context. For example:

SELECT mcs.COMPONENT_SEQUENCE_ID,
msi.SUBSTITUTE_ITEM_ID,
msi2.ITEM_NAME AS SUBSTITUTE_ITEM_NAME
FROM MSC_COMPONENT_SUBSTITUTES mcs,
MSC_SYSTEM_ITEMS msi,
MSC_SYSTEM_ITEMS msi2
WHERE mcs.PLAN_ID = :p_plan_id
AND mcs.SUBSTITUTE_ITEM_ID = msi.INVENTORY_ITEM_ID
AND mcs.SUBSTITUTE_ITEM_ID = msi2.INVENTORY_ITEM_ID
AND mcs.ORGANIZATION_ID = msi.ORGANIZATION_ID;

Another critical use is during the plan launch, where the planning engine's material substitution logic consults this table to evaluate alternative sourcing paths when the primary component supply is insufficient to meet demand.

Related Objects

The table maintains strict referential integrity with other central planning snapshot tables through documented foreign key constraints. The key relationships are:

  • MSC_BOM_COMPONENTS: The foreign key on columns (PLAN_ID, BILL_SEQUENCE_ID, SR_INSTANCE_ID, COMPONENT_SEQUENCE_ID) references MSC_BOM_COMPONENTS. This ties each substitution record directly to a specific component line in a planned BOM.
  • MSC_SYSTEM_ITEMS: The foreign key on columns (PLAN_ID, SUBSTITUTE_ITEM_ID, SR_INSTANCE_ID, ORGANIZATION_ID) references MSC_SYSTEM_ITEMS. This ensures the substitute item is a valid, snapshot item within the specified plan and organization.

These relationships position MSC_COMPONENT_SUBSTITUTES as a dependent child table within the BOM component hierarchy of the planning snapshot.