Results for “msc_component_substitutes”

50+ results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC_COMPONENT_SUBSTITUTES is an Advanced Supply Chain Planning (MSC) table that stores the substitute components defined for a bill of material (BOM) within a planning context. In Oracle EBS, when a manufacturing or assembly component has one or more acceptable substitutes, those alternatives must be known to the planning engine so that it can choose the most feasible component based on supply, availability, and priority. This table captures that relationship between a primary BOM component and its possible substitutes, expressed against a specific plan, organization, and source instance.

From a data modeling perspective, the metadata classifies this object as a link table (heuristic Data Vault classification). This is appropriate because the table records associations between entities — a BOM component and a substitute item — rather than describing an entity in its own right. It therefore behaves as a junction or association table in the MSC schema.

Key Information Stored

The table contains 39 documented columns, of which the following are the most significant for functional and reporting purposes:

  • PLAN_ID — Identifies the Advanced Supply Chain plan to which the substitute relationship applies.
  • ORGANIZATION_ID — The inventory organization in which the substitute is defined.
  • BILL_SEQUENCE_ID — References the BOM structure; part of the composite primary key and a foreign key to MSC_BOM_COMPONENTS.
  • COMPONENT_SEQUENCE_ID — Identifies the primary BOM component for which substitutes exist.
  • SUBSTITUTE_ITEM_ID — The substitute item, foreign key to MSC_SYSTEM_ITEMS.
  • SR_INSTANCE_ID — Source instance identifier, supporting multi-source and multi-org planning.
  • USAGE_QUANTITY — Quantity of the substitute required per assembly.
  • PRIORITY — Ranking of the substitute, which determines selection order when multiple substitutes exist.
  • ROUNDING_DIRECTION — Indicates how substitute usage quantities are rounded.
  • APPLIED — Flag indicating whether the substitute has been applied during planning.
  • NEW_PLAN_ID, NEW_PLAN_LIST, SIMULATION_SET_ID — Support plan simulation and copying scenarios.
  • REFRESH_NUMBER — Tracks the data refresh cycle for the planning snapshot.

The surrogate primary key is defined by MSC_COMPONENT_SUBSTITUTES_PK on (PLAN_ID, BILL_SEQUENCE_ID, SUBSTITUTE_ITEM_ID, SR_INSTANCE_ID, ORGANIZATION_ID, COMPONENT_SEQUENCE_ID). A business-key candidate is captured by the unique index MSC_COMPONENT_SUBSTITUTES_U1, which extends the key with NEW_PLAN_ID and SIMULATION_SET_ID.

Common Use Cases and Queries

Planners and reporting developers query this table to review substitute coverage, validate planning decisions, and audit how the engine selected an alternate component. A typical query joins to MSC_BOM_COMPONENTS and MSC_SYSTEM_ITEMS:

SELECT cs.PLAN_ID, cs.ORGANIZATION_ID, cs.COMPONENT_SEQUENCE_ID,
  si.SEGMENT1 substitute_item, cs.USAGE_QUANTITY, cs.PRIORITY
FROM MSC.MSC_COMPONENT_SUBSTITUTES cs, MSC.MSC_SYSTEM_ITEMS si
WHERE cs.SUBSTITUTE_ITEM_ID = si.INVENTORY_ITEM_ID
  AND cs.ORGANIZATION_ID = si.ORGANIZATION_ID
  AND cs.PLAN_ID = :p_plan_id;

Common scenarios include: auditing substitutes with no priority assigned, comparing planned versus substitute usage quantities, and reviewing simulation sets before promoting changes to a live plan.

Related Objects

  • MSC_BOM_COMPONENTS — Parent table joined on BILL_SEQUENCE_ID and COMPONENT_SEQUENCE_ID.
  • MSC_SYSTEM_ITEMS — Joined on SUBSTITUTE_ITEM_ID, ORGANIZATION_ID, PLAN_ID, and SR_INSTANCE_ID.
  • MSC_PLANS — Provides the plan definition associated with PLAN_ID.
  • MSC_SIMULATION_SETS — Supports simulation_set_id scenarios.
  • MSC_ORG_ITEM_RELATIONS — Supplies item-organization context.