BOM Bill of Materials Upload
Description
BOM Bill of Materials Upload creates and updates standard bills of material in Oracle EBS from Excel – the bill header, its components and component substitutes – through the standard BOM business object API. Primary and alternate bills are supported; common bills are not. The upload maintains bills only; it does not create the assembly or component items, routings or organizations.
When to use it
- Mass-create new bills of material for many assemblies instead of entering them in the Bills of Material form.
- Add components to existing bills, or change their quantity, yield, planning percent, supply type, supply subinventory and locator.
- Retire components by end-dating them, or move them to another operation sequence or effectivity date.
- Add, replace or remove substitute components across many bills.
- Create alternate bills.
- Populate bill, component or substitute descriptive flexfields in bulk.
Before you start
- The upload is available in your responsibility, and the inventory organization is one your responsibility can access.
- The assembly item exists in the organization and is BOM allowed, and the component and substitute items exist in the organization.
- If the assembly has a routing, any Operation Seq you enter exists on it.
Choose a template
| Template | Use it for |
|---|---|
| BOM Upload Template (default) | Bill, component and substitute maintenance. |
| BOM Upload Template with DFF Attributes | The same columns plus the bill, component and substitute descriptive flexfield columns. |
Step 1 – Set the parameters
Open BOM Bill of Materials Upload in Blitz Report, choose the template and set the parameters:
| Parameter | Meaning |
|---|---|
| Upload Mode | Create opens an empty sheet for new bills; Create, Update (default) downloads existing bills for editing and lets you add new bills and components. |
| Organization Code | The inventory organization whose bills are downloaded. |
| Restrict Organization | Leave at the default Yes. |
| Assembly | Downloads the bill of this assembly. Leave blank for all bills of the organization. |
| Alternate BOM | Downloads this alternate bill. Leave blank for the primary bill. |
| Include Sub-Assemblies | Yes also downloads the bills of the sub-assemblies on the selected bill. |
| Implemented Only | Yes downloads implemented components only. |
| Display | Which components to download by effectivity: All, Current or Future and current (default), relative to the Effective Date. |
| Effective Date | The date the Display option is evaluated at. Defaults to now. |
Step 2 – Run to download the Excel file
Click Run. The Excel file downloads and opens with one row per component (and per substitute), or an empty sheet in Create mode.
Step 3 – Enter or change the bill and its components
Each row is one component of a bill. The bill is identified by Organization Code, Assembly Item and, for an alternate bill, Alternate BOM, repeated on every component row; Bill Comment is the only header field. For a new component enter the Component Item, Item Seq, Operation Seq (1 when the assembly has no routing; blank defaults to 1), Component Quantity and Date Effective From (required). Inverse Quantity follows the Component Quantity automatically. On a downloaded component change the quantity, Yield, Planning Percent, Include In Cost Rollup, Supply Type, Supply Subinventory or Supply Locator; changing its Operation Seq or Date Effective From moves the existing component rather than adding a new one. To retire a component, set its Date Effective To. To create a new bill, enter rows with a new Assembly Item and its components.
For substitutes, enter the Substitute Item and Substitute Quantity on the component’s row. Replacing the substitute item on a downloaded row replaces the substitute; clearing it removes the substitute.
Step 4 – Validate and Save
Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.
Step 5 – Upload the file
In Blitz Report click Upload and select the saved file. This submits the upload request, which processes each bill with all its changed rows in one call to the BOM API: end-dated components first, then the active components and their substitutes.
Step 6 – Review the result report
When the request completes, a result report opens listing every uploaded row with its status and message. The message applies to the whole bill, for example BOM Successfully Created or BOM Successfully Updated.
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. Components are end-dated, not deleted. Substitutes are removed by clearing the Substitute Item.
Do I need to set an action?
No. The upload creates or updates each bill, component and substitute depending on whether it already exists.
Why was my whole bill rejected when only one row is wrong?
A bill is processed as a unit. If any of its rows fails validation, or the BOM API rejects one of its components, none of the bill’s changes are saved and every row of that bill shows the error.
Can I maintain common bills?
No. Common bills are neither downloaded nor created by the upload.
How do I load descriptive flexfield values?
Use the BOM Upload Template with DFF Attributes template, which adds the bill, component and substitute context and attribute columns with lists of values.
Troubleshooting
| Message | Cause | What to do |
|---|---|---|
| You cannot update an existing BOM in Create Upload Mode | The bill already exists and Upload Mode is Create. | Download it in Create, Update mode and change it there. |
| Date Effective From is required | A component row has no Date Effective From. | Enter a Date Effective From on every component row. |
| One or more rows for this BOM have an error | Another row of the same bill failed validation, so the bill was not processed. | Correct the flagged row(s) of the bill and upload again. |
| Error: 1:… (n/table) | The BOM API rejected a component or substitute of the bill; the text lists each API message. | Correct the value named in the message (for example an operation sequence not on the routing) and upload the bill again. |
| BOM DFF Error / Component DFF Error / Substitute DFF Error | A descriptive flexfield value is invalid for its context. | Pick the values from the column lists of values. |
SQL 443 lines
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, 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, bom.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, 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, to_number(null) upload_row 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 (+) |
Parameters 9
| Parameter | Type | List of values SQL | |
|---|---|---|---|
| Upload Mode | LOV |
| |
| Organization Code | LOV | ||
| Restrict Organization | LOV Oracle | ||
| Assembly | LOV |
| |
| Alternate BOM | LOV | ||
| Include Sub-Assemblies | LOV Oracle | ||
| Implemented Only | LOV Oracle | ||
| Display | LOV | ||
| Effective Date | DateTime |





