BOM Bill of Materials Upload

➜ Back to Library
Description

BOM Bill of Materials Upload creates and updates manufacturing bills of material – the assembly bill headers, their components and component substitutes – in Oracle EBS from Excel, through the standard BOM API. It maintains the bills and their components; it does not create the items, routings or organizations themselves.

When to use it

  • Mass-create new bills of material for many assemblies at once instead of entering them in the Bills of Material form.
  • Add, change or retire components on existing bills (quantity, supply type, planning percent, yield, effectivity dates).
  • Maintain substitute components across many bills.
  • Roll out the same bill structure across several organizations by changing the Organization Code per row.
  • Populate component, bill or substitute descriptive flexfields in bulk.

Before you start

  • Blitz Report is installed and you are signed in to a Manufacturing or Bills of Material responsibility with access to the inventory organization.
  • The assembly item exists and is BOM-enabled, and the component items exist in the organization.
  • Any operation sequence you reference already exists on the assembly’s routing.

Choose a template

TemplateUse it for
BOM Upload Template (default)Standard bill and component maintenance.
BOM Upload Template with DFF AttributesThe standard columns plus the bill, component and substitute descriptive flexfield columns.

Step 1 – Choose a template and mode, set the parameters and download

In Blitz Report, open BOM Bill of Materials Upload, choose your template, and set the parameters:

ParameterPurpose
Upload ModeCreate (empty template) or Create, Update (download existing bills). Default is Create, Update.
Organization CodeThe inventory organization whose bills are downloaded.
AssemblyOptionally download the bill of a specific assembly.
Alternate BOMDownload a specific alternate bill instead of the primary bill.
Include Sub-Assemblies / Implemented Only / Display / Effective DateControl which components are downloaded.

Run the upload to download and open the Excel file. Each bill, component and substitute is automatically handled as a create or an update depending on whether it already exists.

Step 2 – Enter the bill and its components

The sheet carries one row per component; the bill header (Organization Code, Assembly Item, optional Alternate BOM) repeats on each of its component rows. On each component row enter the Component Item, the Component Quantity, the Date Effective From (required), and where they apply the Operation Seq, Supply Type and supply subinventory or locator. To retire a component, set its Date Effective To – components are end-dated rather than deleted.

Step 3 – Add substitute components (optional)

To give a component a substitute item, enter the Substitute Item and Substitute Quantity on the component’s row. Changing the substitute to a different item replaces the previous one.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.

Step 5 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates or updates each bill through the BOM API. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created and updated bills of material with their components and substitutes.
  • A result report listing every row with a status (success or error) and a message.

Common questions

How do I delete a component from a bill?
Set its Date Effective To (disable date) to retire it – components are end-dated, not physically deleted. Only substitute items are physically removed, by changing the substitute on a component.

Why can’t I create a new bill?
Check the Upload Mode. In Update mode new bills are rejected; use Create or Create, Update. Editing an existing bill in Create mode is also rejected.

Do I need to set an action?
No. The upload detects create versus update automatically from whether the bill, component or substitute already exists.

How do components attach to operations?
Through the Operation Seq, which must be an existing operation on the assembly’s routing. If the assembly has no routing, leave it at the default of 1.

Which template should I use?
Use BOM Upload Template for normal maintenance; use the DFF template only when you need to enter descriptive flexfield values.

Troubleshooting

MessageCauseWhat to do
You cannot update an existing BOM in Create Upload ModeThe bill already exists but Upload Mode is Create.Re-run in Create, Update mode.
You cannot create a new BOM in Update Upload ModeNo bill exists yet but you are in Update mode.Use Create or Create, Update mode.
Date Effective From is requiredA component row has no effectivity start date.Enter a Date Effective From on every component row.
End Item Unit Number From is requiredThe assembly uses unit-number effectivity but the field is blank.Provide the End Item Unit Number From for that component.
One or more rows for this BOM have an errorA row on the bill failed, so the whole bill is held back.Fix the flagged row(s); the entire bill is processed as a unit.
with
bom_tree as
(
 select  /*+ materialize */ distinct
  bbom.bill_sequence_id
 from
  bom_bill_of_materials    bbom
 ,bom_inventory_components bic
 where
  bbom.common_bill_sequence_id = bic.bill_sequence_id (+) and
  bbom.assembly_type = 1 and -- BOM
  nvl(bbom.effectivity_control,1) < 3 and
  nvl(bic.eco_for_production,2) = 2 and
  --
  &lp_effective_display_clause1
  &lp_effective_display_clause2
  --
  nvl(bbom.alternate_bom_designator,'?') = nvl(:p_alternate_bom,'?') and
  -- exclude common boms for the moment
  bbom.bill_sequence_id = bbom.common_bill_sequence_id and
  bbom.common_organization_id is null and
  bbom.common_assembly_item_id is null
 connect by nocycle
  prior decode(:p_explode_bom,'Y',bic.component_item_id,null) = bbom.assembly_item_id and
  prior decode(:p_explode_bom,'Y',bbom.organization_id,null) = bbom.organization_id and
  prior decode(:p_explode_bom,'Y',nvl(bbom.alternate_bom_designator,'?'),null) = nvl(bbom.alternate_bom_designator,'?')
 start with
  bbom.bill_sequence_id in
(select /*+ materialize */
  bbom.bill_sequence_id
 from
  mtl_parameters mp,
  mtl_system_items_vl msiv,
  bom_bill_of_materials bbom
 where
   mp.organization_id = msiv.organization_id and
   msiv.organization_id = bbom.organization_id and
   msiv.inventory_item_id = bbom.assembly_item_id and
   msiv.bom_enabled_flag = 'Y' and
   msiv.bom_item_type != 5 and -- exclude product family
   bbom.assembly_type = 1 and -- BOM
   nvl(bbom.effectivity_control,1) < 3 and
   nvl(bbom.alternate_bom_designator,'?') = nvl(:p_alternate_bom,'?') and
   -- exclude common boms for the moment
   bbom.bill_sequence_id = bbom.common_bill_sequence_id and
   bbom.common_organization_id is null and
   bbom.common_assembly_item_id is null and
   mp.organization_code = :p_organization_code and
   1=1
)
),
bom as
(
 select
  mp.organization_code,
  msiv1.concatenated_segments assembly_item,
  msiv1.description assembly_description,
  bbom.specific_assembly_comment bill_comment,
  xxen_util.meaning(decode(bbom.implementation_date,null,2,1),'SYS_YES_NO',700) bill_implemented_flag,
  bbom.implementation_date bill_implementation_date,
  bbom.effectivity_control effectivity_control_code,
  xxen_util.meaning(bbom.effectivity_control,'MTL_EFFECTIVITY_CONTROL',700) effectivity_control,
  bbom.alternate_bom_designator alternate_bom,
  xxen_util.meaning(nvl2(bbom.common_assembly_item_id,1,2),'SYS_YES_NO',700) common_bill_flag,
  xxen_util.display_flexfield_context(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category) bill_attribute_category,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE1',bbom.rowid) bom_bill_attribute1,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE2',bbom.rowid) bom_bill_attribute2,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE3',bbom.rowid) bom_bill_attribute3,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE4',bbom.rowid) bom_bill_attribute4,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE5',bbom.rowid) bom_bill_attribute5,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE6',bbom.rowid) bom_bill_attribute6,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE7',bbom.rowid) bom_bill_attribute7,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE8',bbom.rowid) bom_bill_attribute8,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE9',bbom.rowid) bom_bill_attribute9,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE10',bbom.rowid) bom_bill_attribute10,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE11',bbom.rowid) bom_bill_attribute11,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE12',bbom.rowid) bom_bill_attribute12,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE13',bbom.rowid) bom_bill_attribute13,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE14',bbom.rowid) bom_bill_attribute14,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE15',bbom.rowid) bom_bill_attribute15,
  --
  msiv2.concatenated_segments component_item,
  msiv2.description component_description,
  bic.item_num item_seq,
  bic.operation_seq_num operation_seq,
  bic.component_quantity,
  round(decode(bic.component_quantity,0,0,1/bic.component_quantity),37) inverse_quantity,
  bic.effectivity_date  date_effective_from,
  bic.disable_date      date_effective_to,
  bic.from_end_item_unit_number end_item_unit_number_from,
  bic.to_end_item_unit_number end_item_unit_number_to,
  nvl(bic.basis_type,1) basis_type,
  xxen_util.meaning(nvl(bic.basis_type,1),'BOM_BASIS_TYPE',700) basis,
  bic.planning_factor planning_percent,
  bic.component_yield_factor yield,
  xxen_util.meaning(bic.enforce_int_requirements,'BOM_ENFORCE_INT_REQUIREMENTS',700) enforce_integer_quantity,
  xxen_util.meaning(bic.include_in_cost_rollup,'SYS_YES_NO',700) include_in_cost_rollup,
  xxen_util.meaning(bic.wip_supply_type,'WIP_SUPPLY',700) supply_type,
  bic.supply_subinventory,
  (select milk.concatenated_segments from mtl_item_locations_kfv milk where milk.inventory_location_id = bic.supply_locator_id) supply_locator,
  xxen_util.meaning(decode(bic.implementation_date,null,2,1),'SYS_YES_NO',700) component_implemented_flag,
  bic.implementation_date component_implementation_date,
  xxen_util.display_flexfield_context(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category) component_attribute_category,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE1',bic.rowid) bom_comp_attribute1,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE2',bic.rowid) bom_comp_attribute2,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE3',bic.rowid) bom_comp_attribute3,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE4',bic.rowid) bom_comp_attribute4,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE5',bic.rowid) bom_comp_attribute5,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE6',bic.rowid) bom_comp_attribute6,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE7',bic.rowid) bom_comp_attribute7,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE8',bic.rowid) bom_comp_attribute8,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE9',bic.rowid) bom_comp_attribute9,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE10',bic.rowid) bom_comp_attribute10,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE11',bic.rowid) bom_comp_attribute11,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE12',bic.rowid) bom_comp_attribute12,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE13',bic.rowid) bom_comp_attribute13,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE14',bic.rowid) bom_comp_attribute14,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE15',bic.rowid) bom_comp_attribute15,
  --
  bbom.bill_sequence_id,
  bbom.organization_id,
  bbom.assembly_item_id,
  bic.component_sequence_id,
  bic.component_item_id
 from
  bom_bill_of_materials  bbom
 ,bom_inventory_components bic
 ,(select msiv.* from mtl_system_items_vl msiv where msiv.organization_id = :p_organization_id) msiv1
 ,(select msiv.* from mtl_system_items_vl msiv where msiv.organization_id = :p_organization_id) msiv2
 ,(select mp.* from mtl_parameters mp where mp.organization_id = :p_organization_id) mp
 where
  bbom.common_bill_sequence_id = bic.bill_sequence_id (+) and
  bbom.organization_id = mp.organization_id and
  bbom.assembly_item_id = msiv1.inventory_item_id and
  bbom.organization_id = msiv1.organization_id and
  bic.component_item_id = msiv2.inventory_item_id (+) and
  bbom.organization_id = nvl(msiv2.organization_id,bbom.organization_id) and
  --
  bbom.bill_sequence_id in (select bom_tree.bill_sequence_id from bom_tree) and
  --
  &lp_effective_display_clause1
  &lp_effective_display_clause2
  --
  nvl(bic.eco_for_production,2) = 2
),
bom_subst as
( --from bom_substitute_components_v
 select
  bic2.component_sequence_id,
  bsc.substitute_component_id,
  msiv.concatenated_segments substitute_item,
  msiv.description substitute_description,
  bsc.substitute_item_quantity substitute_quantity,
  round(decode(bsc.substitute_item_quantity,0,0,1/bsc.substitute_item_quantity),37) substitute_inverse_quantity,
  xxen_util.meaning(bsc.enforce_int_requirements,'BOM_ENFORCE_INT_REQUIREMENTS',700) subst_integer_requirements,
  xxen_util.display_flexfield_context(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category) substitute_attribute_category,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE1',bsc.rowid) bom_sub_attribute1,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE2',bsc.rowid) bom_sub_attribute2,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE3',bsc.rowid) bom_sub_attribute3,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE4',bsc.rowid) bom_sub_attribute4,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE5',bsc.rowid) bom_sub_attribute5,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE6',bsc.rowid) bom_sub_attribute6,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE7',bsc.rowid) bom_sub_attribute7,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE8',bsc.rowid) bom_sub_attribute8,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE9',bsc.rowid) bom_sub_attribute9,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE10',bsc.rowid) bom_sub_attribute10,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE11',bsc.rowid) bom_sub_attribute11,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE12',bsc.rowid) bom_sub_attribute12,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE13',bsc.rowid) bom_sub_attribute13,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE14',bsc.rowid) bom_sub_attribute14,
  xxen_util.display_flexfield_value(702,'BOM_SUBSTITUTE_COMPONENTS',bsc.attribute_category,'ATTRIBUTE15',bsc.rowid) bom_sub_attribute15
 from
  bom_substitute_components bsc,
  bom_bill_of_materials bbom,
  bom_inventory_components bic1,
  bom_inventory_components bic2, /* current component record */
  mtl_system_items_vl msiv /* component being referenced */
 where
  bsc.component_sequence_id = bic1.component_sequence_id and
  bic1.bill_sequence_id = bbom.bill_sequence_id and
  bsc.substitute_component_id = msiv.inventory_item_id and
  bbom.organization_id = msiv.organization_id and
  nvl(bsc.acd_type, 1) <> 3 and
  bic2.bill_sequence_id = bic1.bill_sequence_id and
  decode(bic2.implementation_date, null, bic2.old_component_sequence_id, bic2.component_sequence_id) = decode(bic1.implementation_date, null, bic1.old_component_sequence_id, bic1.component_sequence_id) and
  bic1.effectivity_date =
  (select
    max(bic3.effectivity_date)
   from
    bom_substitute_components bsc3,
    bom_inventory_components bic3
   where
    bic3.component_sequence_id = bsc3.component_sequence_id and
    decode(bic3.implementation_date, null, bic3.old_component_sequence_id, bic3.component_sequence_id) = decode(bic1.implementation_date, null, bic1.old_component_sequence_id, bic1.component_sequence_id) and
    bic3.bill_sequence_id = bic1.bill_sequence_id and
    bsc3.substitute_component_id = bsc.substitute_component_id and
    bic3.effectivity_date <= bic2.effectivity_date
  )
),
processed_boms as
(select /*+ ordered use_nl(bbom) push_pred(bom_subst) */ distinct
  xm.organization_code,
  xm.assembly_item,
  (select msit.description from mtl_system_items_tl msit where msit.organization_id = bbom.organization_id and msit.inventory_item_id = bbom.assembly_item_id and msit.language = userenv('lang')) assembly_description,
  bbom.specific_assembly_comment bill_comment,
  xxen_util.meaning(decode(bbom.implementation_date,null,2,1),'SYS_YES_NO',700) bill_implemented_flag,
  bbom.implementation_date bill_implementation_date,
  bbom.effectivity_control effectivity_control_code,
  xxen_util.meaning(bbom.effectivity_control,'MTL_EFFECTIVITY_CONTROL',700) effectivity_control,
  bbom.alternate_bom_designator alternate_bom,
  xxen_util.meaning(nvl2(bbom.common_assembly_item_id,1,2),'SYS_YES_NO',700) common_bill_flag,
  xxen_util.display_flexfield_context(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category) bill_attribute_category,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE1',bbom.rowid) bom_bill_attribute1,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE2',bbom.rowid) bom_bill_attribute2,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE3',bbom.rowid) bom_bill_attribute3,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE4',bbom.rowid) bom_bill_attribute4,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE5',bbom.rowid) bom_bill_attribute5,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE6',bbom.rowid) bom_bill_attribute6,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE7',bbom.rowid) bom_bill_attribute7,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE8',bbom.rowid) bom_bill_attribute8,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE9',bbom.rowid) bom_bill_attribute9,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE10',bbom.rowid) bom_bill_attribute10,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE11',bbom.rowid) bom_bill_attribute11,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE12',bbom.rowid) bom_bill_attribute12,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE13',bbom.rowid) bom_bill_attribute13,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE14',bbom.rowid) bom_bill_attribute14,
  xxen_util.display_flexfield_value(702,'BOM_BILL_OF_MATERIALS',bbom.attribute_category,'ATTRIBUTE15',bbom.rowid) bom_bill_attribute15,
  --
  msik.concatenated_segments component_item,
  (select msit.description from mtl_system_items_tl msit where msit.organization_id = bbom.organization_id and msit.inventory_item_id = bic.component_item_id and msit.language = userenv('lang'))component_description,
  bic.item_num item_seq,
  bic.operation_seq_num operation_seq,
  bic.component_quantity,
  round(decode(bic.component_quantity,0,0,1/bic.component_quantity),37) inverse_quantity,
  bic.effectivity_date  date_effective_from,
  bic.disable_date      date_effective_to,
  bic.from_end_item_unit_number end_item_unit_number_from,
  bic.to_end_item_unit_number end_item_unit_number_to,
  nvl(bic.basis_type,1) basis_type,
  xxen_util.meaning(nvl(bic.basis_type,1),'BOM_BASIS_TYPE',700) basis,
  bic.planning_factor planning_percent,
  bic.component_yield_factor yield,
  xxen_util.meaning(bic.enforce_int_requirements,'BOM_ENFORCE_INT_REQUIREMENTS',700) enforce_integer_quantity,
  xxen_util.meaning(bic.include_in_cost_rollup,'SYS_YES_NO',700) include_in_cost_rollup,
  xxen_util.meaning(bic.wip_supply_type,'WIP_SUPPLY',700) supply_type,
  bic.supply_subinventory,
  (select milk.concatenated_segments from mtl_item_locations_kfv milk where milk.inventory_location_id = bic.supply_locator_id) supply_locator,
  xxen_util.meaning(decode(bic.implementation_date,null,2,1),'SYS_YES_NO',700) component_implemented_flag,
  bic.implementation_date component_implementation_date,
  xxen_util.display_flexfield_context(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category) component_attribute_category,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE1',bic.rowid) bom_comp_attribute1,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE2',bic.rowid) bom_comp_attribute2,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE3',bic.rowid) bom_comp_attribute3,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE4',bic.rowid) bom_comp_attribute4,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE5',bic.rowid) bom_comp_attribute5,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE6',bic.rowid) bom_comp_attribute6,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE7',bic.rowid) bom_comp_attribute7,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE8',bic.rowid) bom_comp_attribute8,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE9',bic.rowid) bom_comp_attribute9,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE10',bic.rowid) bom_comp_attribute10,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE11',bic.rowid) bom_comp_attribute11,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE12',bic.rowid) bom_comp_attribute12,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE13',bic.rowid) bom_comp_attribute13,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE14',bic.rowid) bom_comp_attribute14,
  xxen_util.display_flexfield_value(702,'BOM_INVENTORY_COMPONENTS',bic.attribute_category,'ATTRIBUTE15',bic.rowid) bom_comp_attribute15,
  --
  bom_subst.substitute_item,
  bom_subst.substitute_description,
  bom_subst.substitute_quantity,
  bom_subst.substitute_inverse_quantity,
  bom_subst.subst_integer_requirements,
  bom_subst.substitute_attribute_category,
  bom_subst.bom_sub_attribute1,
  bom_subst.bom_sub_attribute2,
  bom_subst.bom_sub_attribute3,
  bom_subst.bom_sub_attribute4,
  bom_subst.bom_sub_attribute5,
  bom_subst.bom_sub_attribute6,
  bom_subst.bom_sub_attribute7,
  bom_subst.bom_sub_attribute8,
  bom_subst.bom_sub_attribute9,
  bom_subst.bom_sub_attribute10,
  bom_subst.bom_sub_attribute11,
  bom_subst.bom_sub_attribute12,
  bom_subst.bom_sub_attribute13,
  bom_subst.bom_sub_attribute14,
  bom_subst.bom_sub_attribute15,
  --
  bbom.bill_sequence_id,
  bbom.organization_id,
  bbom.assembly_item_id,
  bic.component_sequence_id,
  bic.component_item_id,
  bom_subst.substitute_component_id
 from
  (select /*+ materialize */
   distinct
   xm.organization_code,
   xm.assembly_item,
   xm.alternate_bom,
   (select mp.organization_id from mtl_parameters mp where mp.organization_code=xm.organization_code) inv_org_id,
   (select msik.inventory_item_id from mtl_parameters mp,mtl_system_items_kfv msik where mp.organization_code=xm.organization_code and msik.organization_id = mp.organization_id and msik.concatenated_segments = xm.assembly_item) bom_item_id
   from
   xxen_bom_bill_of_materi_1523_u xm
  ) xm,
  bom_bill_of_materials  bbom,
  bom_inventory_components bic,
  mtl_system_items_kfv msik,
  bom_subst
 where
  xm.inv_org_id = bbom.organization_id and
  xm.bom_item_id = bbom.assembly_item_id and
  nvl(xm.alternate_bom,' ') = nvl(bbom.alternate_bom_designator,' ') and
  --
  bbom.common_bill_sequence_id = bic.bill_sequence_id and
  --
  &lp_effective_display_clause1
  &lp_effective_display_clause2
  --
  nvl(bic.eco_for_production,2) = 2 and
  bbom.organization_id = msik.organization_id and
  bic.component_item_id = msik.inventory_item_id and
  --
  bic.component_sequence_id = bom_subst.component_sequence_id (+)
)
--
-- Main Query Starts Here
select /*+ push_pred(bom_subst) */
 --process--
 null action_,
 null status_,
 null message_,
 null request_id_,
 null modified_columns_,
 :p_upload_mode upload_mode,
 :p_enable_attrs_update enable_attrs_update,
 --
 bom.organization_code,
 bom.assembly_item,
 bom.assembly_description,
 bom.bill_comment,
 bom.alternate_bom,
 bom.bill_implementation_date bill_implementation_date,
 bom.bill_implemented_flag,
 bom.common_bill_flag,
 bom.effectivity_control,
 bom.effectivity_control_code,
 bom.bill_attribute_category,
 bom.bom_bill_attribute1,
 bom.bom_bill_attribute2,
 bom.bom_bill_attribute3,
 bom.bom_bill_attribute4,
 bom.bom_bill_attribute5,
 bom.bom_bill_attribute6,
 bom.bom_bill_attribute7,
 bom.bom_bill_attribute8,
 bom.bom_bill_attribute9,
 bom.bom_bill_attribute10,
 bom.bom_bill_attribute11,
 bom.bom_bill_attribute12,
 bom.bom_bill_attribute13,
 bom.bom_bill_attribute14,
 bom.bom_bill_attribute15,
 --
 bom.item_seq,
 bom.operation_seq operation_seq_old,
 bom.operation_seq,
 bom.component_item,
 bom.component_description,
 bom.component_quantity,
 bom.inverse_quantity,
 bom.date_effective_from date_effective_from_old,
 bom.date_effective_from date_effective_from,
 bom.date_effective_to date_effective_to,
 bom.end_item_unit_number_from end_item_unit_number_from_old,
 bom.end_item_unit_number_from,
 bom.end_item_unit_number_to,
 bom.basis_type,
 bom.basis,
 bom.planning_percent,
 bom.yield,
 bom.enforce_integer_quantity,
 bom.include_in_cost_rollup,
 bom.supply_type,
 bom.supply_subinventory,
 bom.supply_locator,
 bom.component_implemented_flag,
 bom.component_implementation_date component_implementation_date,
 bom.component_attribute_category,
 bom.bom_comp_attribute1,
 bom.bom_comp_attribute2,
 bom.bom_comp_attribute3,
 bom.bom_comp_attribute4,
 bom.bom_comp_attribute5,
 bom.bom_comp_attribute6,
 bom.bom_comp_attribute7,
 bom.bom_comp_attribute8,
 bom.bom_comp_attribute9,
 bom.bom_comp_attribute10,
 bom.bom_comp_attribute11,
 bom.bom_comp_attribute12,
 bom.bom_comp_attribute13,
 bom.bom_comp_attribute14,
 bom.bom_comp_attribute15,
 --
 bom_subst.substitute_item substitute_item_old,
 bom_subst.substitute_item,
 bom_subst.substitute_description,
 bom_subst.substitute_quantity,
 bom_subst.substitute_inverse_quantity,
 bom_subst.subst_integer_requirements,
 bom_subst.substitute_attribute_category,
 bom_subst.bom_sub_attribute1,
 bom_subst.bom_sub_attribute2,
 bom_subst.bom_sub_attribute3,
 bom_subst.bom_sub_attribute4,
 bom_subst.bom_sub_attribute5,
 bom_subst.bom_sub_attribute6,
 bom_subst.bom_sub_attribute7,
 bom_subst.bom_sub_attribute8,
 bom_subst.bom_sub_attribute9,
 bom_subst.bom_sub_attribute10,
 bom_subst.bom_sub_attribute11,
 bom_subst.bom_sub_attribute12,
 bom_subst.bom_sub_attribute13,
 bom_subst.bom_sub_attribute14,
 bom_subst.bom_sub_attribute15,
 --
 bom.bill_sequence_id,
 bom.organization_id,
 bom.assembly_item_id,
 bom.component_sequence_id,
 bom.component_item_id,
 bom_subst.substitute_component_id,
 to_char(null) row_id
from
 bom,
 bom_subst
where
 :p_display_option = :p_display_option and
 nvl(:p_restrict_org,'?') = nvl(:p_restrict_org,'?') and
 bom.component_sequence_id = bom_subst.component_sequence_id (+)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Organization Code
 
LOV
Restrict Organization
 
LOV Oracle
Assembly
msiv.concatenated_segments=:p_assembly
LOV
Alternate BOM
 
LOV
Include Sub-Assemblies
 
LOV Oracle
Implemented Only
 
LOV Oracle
Display
 
LOV
Effective Date
 
DateTime
Download
Blitz Report™