Results for “bombv_substitute_bom_comps”

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

Overview

The BOMBV_SUBSTITUTE_BOM_COMPS view is a business intelligence (OBIA/ETRM) extraction view owned by the APPS schema within the Oracle E-Business Suite Bills of Material (BOM) product family. It is designated as "Retrofitted," indicating that it was created or modified to support backported functionality or a data-extraction layer rather than as part of the original BOM transactional schema. Functionally, the view flattens substitute component information from the bill of materials structure into a single denormalized result set that joins substitution records to their parent bill, organization, and item context.

This view is a read-only construct, enforced by the WITH READ ONLY clause at the end of its defining SQL. It is intended for reporting, integration, and ETL workloads that must surface substitute item detail without navigating the underlying normalized BOM tables directly. It is not a form-backed view and does not support DML.

Underlying Base Objects

The documented ETRM metadata for this view lists the following referenced objects: BOM_BILL_OF_MATERIALS (VIEW), BOM_INVENTORY_COMPONENTS (VIEW), BOM_SUBSTITUTE_COMPONENTS (SYNONYM), FND_GLOBAL (PACKAGE), HR_ALL_ORGANIZATION_UNITS (SYNONYM), MTL_PARAMETERS (SYNONYM), and MTL_SYSTEM_ITEMS (SYNONYM).

The defining SQL joins these objects as follows:

• BOM_SUBSTITUTE_COMPONENTS SU joins to BOM_INVENTORY_COMPONENTS CO on COMPONENT_SEQUENCE_ID, establishing the link between a substitute item and its primary component.
• BOM_BILL_OF_MATERIALS BI joins to CO on BILL_SEQUENCE_ID, providing the parent bill header including the alternate BOM designator.
• MTL_SYSTEM_ITEMS is referenced three times (SY1, SY2, SY3) to resolve the assembly item, the primary component item, and the substitute item respectively, all constrained by organization ID.
• MTL_PARAMETERS PA supplies the organization code, while HR_ALL_ORGANIZATION_UNITS AL supplies the organization name.
• FND_GLOBAL is referenced through the _SEC:BI.ORGANIZATION_ID token in the WHERE clause, which applies Oracle's organization-level security (multi-org) to the query.

Key Columns

The view exposes the following documented columns:

• SUBSTITUTE_ITEM_QUANTITY — the quantity of the substitute item, mapped directly from SU.SUBSTITUTE_ITEM_QUANTITY. This is the column most directly relevant to the search term "substitute_item_quantity."
• START_EFFECTIVITY_DATE — the effectivity date of the parent component record (CO.EFFECTIVITY_DATE).
• OPERATION_SEQUENCE_NUMBER — the operation sequence to which the component is tied.
• ALTERNATE_BOM_CODE — the alternate BOM designator from the bill header.
• ORGANIZATION_CODE and ORGANIZATION_NAME — the inventory organization identification.
• SUBSTITUTE_COMPONENT_ID and COMPONENT_SEQUENCE_ID — surrogate keys linking to the substitute and parent component records.
• ORGANIZATION_ID, CHANGE_NOTICE, and the audit columns UPDATED_ON, UPDATED_BY, CREATED_ON, and CREATED_BY.
• Descriptive tokens _KF:COMPONENT_ITEM_NAME, _KF:BILL_ITEM_NAME, and the lookup attribute _LA:ACD_TYPE (mapped to MFG_LOOKUPS:ECG_ACTION:MEANING) that supply display names through the OBIA keyword-flex mechanism.

Common Use Cases and Queries

The primary use case is reporting substitute item quantities for components on a bill of materials. A typical query filters by organization and assembly:

SELECT ORGANIZATION_CODE, ALTERNATE_BOM_CODE, OPERATION_SEQUENCE_NUMBER, SUBSTITUTE_ITEM_QUANTITY, SUBSTITUTE_COMPONENT_ID FROM APPS.BOMBV_SUBSTITUTE_BOM_COMPS WHERE ORGANIZATION_ID = :org_id AND COMPONENT_SEQUENCE_ID = :comp_seq;

Integration scenarios use the view to feed substitute-component data into data warehouses or downstream planning systems, leveraging the pre-joined organization name, bill designator, and audit columns. Because the view applies organization security through FND_GLOBAL, callers must initialize the multi-org context (via FND_GLOBAL.APPS_INITIALIZE or an equivalent session setup) before querying, otherwise the _SEC predicate may suppress all rows. Analysts searching on "substitute_item_quantity" should note that this column returns the substitute quantity as stored on the substitute component record concatenated to the parent component, and it should be interpreted relative to the component's basis quantity rather than as an independent on-hand figure.