Search Results bom_substitute_components

Overview

The BOM_SUBSTITUTE_COMPONENTS table is a core data object within the Oracle E-Business Suite (EBS) Bills of Material (BOM) module. It serves as the primary repository for defining and managing substitute components for items listed on a bill of material. A substitute component is an alternate part that can be used in place of the primary component during manufacturing or assembly processes, typically to address material shortages, cost optimization, or engineering changes. This table's existence is critical for enabling flexible production planning and execution within Oracle Manufacturing and Supply Chain applications, as it allows the system to propose or validate alternative materials based on defined priorities and effectivity dates.

Key Information Stored

The table's structure is designed to link a substitute item to a specific primary component within a bill. The primary key is SUBSTITUTE_COMPONENT_ID, which uniquely identifies each substitute relationship. The most critical foreign key is COMPONENT_SEQUENCE_ID, which links the substitute record back to its parent primary component defined in the BOM_COMPONENTS_B table. This establishes the essential "substitute-for" relationship. While the provided metadata does not list all columns, standard implementations of this table typically include fields such as the SUBSTITUTE_ITEM_ID, a priority or sequence number to rank multiple substitutes, effectivity dates (EFFECTIVITY_DATE and DISABLE_DATE) to control when the substitute is valid, and an ACD_TYPE column (as noted in the primary key definition) which often indicates the type of change document associated with the record.

Common Use Cases and Queries

A primary use case is generating a report of all approved substitutes for a given assembly or component to aid procurement and production scheduling. Another common scenario is within a Material Requirements Planning (MRP) or workflow process that automatically suggests alternate components when the primary item is unavailable. A typical query would join this table to BOM_COMPONENTS_B and inventory item tables to produce a usable list.

Sample Query Pattern:
SELECT bc.assembly_item_id,
bc.component_item_id AS primary_component,
bsc.substitute_item_id,
bsc.substitute_component_id
FROM bom_components_b bc,
bom_substitute_components bsc
WHERE bc.component_sequence_id = bsc.component_sequence_id
AND bc.assembly_item_id = :p_assembly_item_id
AND SYSDATE BETWEEN bc.effectivity_date AND NVL(bc.disable_date, SYSDATE+1);

Related Objects

• BOM_COMPONENTS_B: This is the foundational parent table. Every record in BOM_SUBSTITUTE_COMPONENTS must reference a valid COMPONENT_SEQUENCE_ID from BOM_COMPONENTS_B, defining which primary component the substitute is for.
• MTL_SYSTEM_ITEMS_B (or related item master views): The actual item identifiers for both the primary component (via BOM_COMPONENTS_B) and the substitute item are stored in the inventory item master.
• BOM APIs and Packages: Standard Oracle APIs, such as those in the BOM-related PL/SQL packages, are used to create, update, and validate substitute component relationships programmatically, ensuring data integrity.