Search Results component_item_no




Overview
Explore in AI Chat

Overview

The BOM_SUBSTITUTE_COMPONENTS_ERV view is an Oracle EBS seeded database object owned by the APPS schema and registered as VALID under the Bills of Material (BOM) product family. It presents substitute component information associated with a bill of material, allowing substitute items defined against an existing inventory component to be reported alongside that component's identifying attributes, quantities, and change-control data. In Oracle EBS 12.1.1 and 12.2.2, this view serves both as a reporting layer and as an integration point for external systems and concurrent programs that need to query substitute component relationships without traversing the underlying BOM tables directly. Because it resolves multiple base objects into a single denormalized result set, it simplifies query construction for developers writing custom reports, interfaces, or discovery-based integrations against substitute item data.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the following referenced base objects for this view: BOM_BILL_OF_MATERIALS (VIEW), BOM_INVENTORY_COMPONENTS (VIEW), BOM_SUBSTITUTE_COMPONENTS (SYNONYM), FND_GLOBAL (PACKAGE), MFG_LOOKUPS (VIEW), and MTL_SYSTEM_ITEMS_VL (VIEW). The view's SQL text shows that it joins BOM_SUBSTITUTE_COMPONENTS (aliased BSC1) to BOM_INVENTORY_COMPONENTS (aliased BIC1 for the current component record) and again to BOM_INVENTORY_COMPONENTS (aliased BIC2 for the component being referenced). It links to BOM_BILL_OF_MATERIALS to obtain organization context, to MTL_SYSTEM_ITEMS_VL to resolve substitute item descriptions, concatenated segment numbers, and primary UOM, and to MFG_LOOKUPS to translate the ENFORCE_INT_REQUIREMENTS lookup code into a descriptive meaning. The join logic actively references the legacy attribute OLD_COMPONENT_SEQUENCE_ID when a component's IMPLEMENTATION_DATE is null, which is directly relevant to the user's search term.

Key Columns

The view exposes a broad set of identifiers and descriptive attributes. Important columns include ROW_ID (the ROWID of the substitute component record), SUBSTITUTE_COMPONENT_ID, COMPONENT_SEQUENCE_ID (representing the component being referenced), SUBSTITUTE_ITEM_QUANTITY, and COMPONENT_ITEM_NO (the concatenated inventory item number of the substitute). Descriptive columns include PRIMARY_UOM_CODE and DESCRIPTION from MTL_SYSTEM_ITEMS_VL, and ENFORCE_INT_REQUIREMENTS_DESC derived from MFG_LOOKUPS. Change and audit columns such as ACD_TYPE, CHANGE_NOTICE, IMPLEMENTATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID, and PROGRAM_ID support engineering change order tracking and concurrent program traceability. Fifteen ATTRIBUTE flex columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15) provide the standard DFF extensibility.

Common Use Cases and Queries

Typical uses include substitute component reporting for a specific assembly, engineering change order impact analysis, and interface extraction for downstream manufacturing or planning systems. The view intentionally filters records where NVL(ACD_TYPE,1) <> 3, excluding certain obsolete or deleted substitute definitions. The OLD_COMPONENT_SEQUENCE_ID attribute is significant because the view uses a DECODE expression to reconcile component records across implementation dates: when IMPLEMENTATION_DATE is null it substitutes OLD_COMPONENT_SEQUENCE_ID for COMPONENT_SEQUENCE_ID, matching historical records to current ones.

  • List substitutes for a component: SELECT COMPONENT_ITEM_NO, DESCRIPTION, SUBSTITUTE_ITEM_QUANTITY FROM BOM_SUBSTITUTE_COMPONENTS_ERV WHERE COMPONENT_SEQUENCE_ID = :seq_id;
  • Change-ordered substitutes: SELECT SUBSTITUTE_COMPONENT_ID, CHANGE_NOTICE FROM BOM_SUBSTITUTE_COMPONENTS_ERV WHERE CHANGE_NOTICE IS NOT NULL;
  • Interface extraction: SELECT ROW_ID, SUBSTITUTE_COMPONENT_ID, COMPONENT_ITEM_NO, PRIMARY_UOM_CODE FROM BOM_SUBSTITUTE_COMPONENTS_ERV;

Because the view internally reconciles COMPONENT_SEQUENCE_ID with OLD_COMPONENT_SEQUENCE_ID, queries can rely on it to return historically consistent substitute relationships without implementing that reconciliation manually.