Search Results mrp_ap_component_substitutes_v

Overview

The view MRP_AP_COMPONENT_SUBSTITUTES_V is a Master Scheduling/MRP (MRP) reporting object in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes substitute component relationships defined on bills of material (BOMs) as interpreted by the Advanced Planning (AP) snapshot data model. Substitute components allow an assembly to be built using an alternate component when the primary component is unavailable, and this view presents those substitutions together with the assembly, organization, and bill context required by planning and integration processes.

The view belongs to the MRP_AP_*_SN family of snapshot tables that Oracle's planning engine materializes from the operational BOM and inventory tables. It is documented as not implemented in this database, meaning the object exists in the ETRM dictionary but is not created in every environment; deployments that rely on it must verify its presence before use. Its ETRM owner is not documented and no base objects are separately listed, so the authoritative definition is the embedded view text.

Underlying Base Objects

The view is defined over six documented base objects:

• MRP_AP_MRP_PARAS_SN — planning parameters snapshot, joined on ORGANIZATION_ID.
• MRP_AP_SYS_ITEMS_SN — system items snapshot; referenced twice, as MSIK for the substitute item and MSIK2 for the assembly item.
• MRP_AP_BOMS_SN (BBOM) — bill of materials snapshot carrying BILL_SEQUENCE_ID, ORGANIZATION_ID, and the assembly item.
• MRP_AP_INV_COMPS_SN (BIC) — component snapshot linking components to bills.
• MRP_AP_SUB_COMPS_SN (BSC) — substitute component snapshot supplying substitute item and quantity.

Joins are keyed on BILL_SEQUENCE_ID and COMPONENT_SEQUENCE_ID, with organization and inventory item identifiers resolving the substitute and assembly items. Each source table also contributes an internal RN row-number column (RN1 through RN6, with MP.RN aliased as RN6) used by the snapshot refresh logic.

Key Columns

• BILL_SEQUENCE_ID — surrogate key of the bill of materials record for the assembly.
• COMPONENT_SEQUENCE_ID — surrogate key identifying the primary component line to which the substitute applies.
• SUBSTITUTE_ITEM_ID — the inventory item identifier of the substitute component (sourced from SUBSTITUTE_COMPONENT_ID).
• USAGE_QUANTITY — the quantity of the substitute consumed per assembly (sourced from SUBSTITUTE_ITEM_QUANTITY).
• ORGANIZATION_ID — the inventory organization in which the bill and substitution apply.

A notable filtering predicate references BOM_ITEM_TYPE. The view returns rows where the assembly has BOM_ITEM_TYPE = 4 and the component type is not in (1, 2), or where the assembly type is in (1, 2, 5). In Oracle BOM terminology, type 4 denotes a planning bill or model, type 1 a standard item, type 2 a model, and type 5 a planning item. This clause restricts output to substitution relationships meaningful to planning.

Common Use Cases and Queries

Typical uses include validating substitute component availability in planning snapshots, exporting substitute structures to external planning systems, and diagnosing discrepancies between the operational BOM and the MRP snapshot. The bom_item_type search term reflects the common need to filter by assembly or component item type.

Sample query:

• SELECT bill_sequence_id, component_sequence_id, substitute_item_id, usage_quantity, organization_id FROM mrp_ap_component_substitutes_v WHERE organization_id = :org_id;
• Join substitute_item_id to MTL_SYSTEM_ITEMS_B on INVENTORY_ITEM_ID for descriptive attributes and BOM_ITEM_TYPE.
• Aggregate USAGE_QUANTITY by substitute_item_id to estimate substitute demand for a planning run.