BOM Common Bill of Materials Upload

➜ Back to Library
Description

BOM Common Bill of Materials Upload points the bills of material in one or more target organizations at a source organization’s bills – so the target assemblies share, rather than copy, the source bill. It walks the whole bill structure and commons any referenced sub-assembly and substitute-item bills automatically. It creates common-bill references only; it does not change components or delete anything.

When to use it

  • Share a master bill maintained in one organization with other organizations instead of keeping independent copies.
  • Point a new organization’s assemblies at existing bills in bulk during a rollout.
  • Common many bills at once, including all their sub-assembly and substitute bills, without doing each by hand.
  • Common to all sibling organizations, to all organizations below a hierarchy node, or to a specific organization.
  • Review, per target organization, which assemblies already have bills or are missing items before committing.

Before you start

  • Blitz Report is installed and you are signed in to a Manufacturing or Bills of Material responsibility with access to the target organizations.
  • The source and target organizations share the same item master.
  • The assembly item, and all of the bill’s component and substitute items, are already defined in the target organization.

Choose a template

TemplateUse it for
Common BOM Upload Template (default)The download lists every bill and target-organization combination with the Create Common Bom column blank, for you to flag the ones to common.
Automatic Common BOM Upload TemplateThe download comes back with Create Common Bom already set to Yes on every combination – you clear or remove the rows you do not want.

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

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

ParameterPurpose
Source Organization CodeThe organization whose bills will be made common into the targets.
Common BOM ScopeWhich organizations to common into: All, Hierarchy or Organization.
Target Hierarchy / Target OrganizationThe hierarchy or specific organization, when Scope is Hierarchy or Organization.
Assembly / Alternate BOMOptionally restrict the download to one assembly or alternate.
Show Missing BOMS onlyYes shows only combinations not yet defined in the target.

Run the upload to download and open the Excel file.

Step 2 – Flag the bills to common

Each row is one bill for one target organization. Set Create Common Bom to Yes on the rows you want made common (in the Automatic template these are already set – clear the ones you do not want). The readiness columns – Is Item Defined In Tgt, Is Bom Defined In Tgt and Comp Items Not Defined In Tgt – show, before you upload, whether the target organization is ready.

Step 3 – Validate and Save

Click Validate and Save. This checks the rows and saves the file. Correct anything it flags before continuing.

Step 4 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates the common-bill references (and any sub-assembly bills that also need commoning). When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Common-bill references in the target organizations, including any sub-assembly and substitute-item bills that needed commoning.
  • A result report listing every row with a status and a message.

Common questions

Does it copy the components into the target organization?
No. It creates a common bill – the target assembly’s bill references the source bill, so changes to the source flow through.

What if I leave Create Common Bom blank?
That row is skipped and reported as no action taken. Only rows flagged Yes are processed.

Why does a row say the bill already exists?
A bill already exists for that assembly and alternate in the target organization, so nothing is changed. The Is Bom Defined In Tgt column shows Yes for these.

What’s the difference between the two templates?
The default template comes back with the flag blank for you to choose; the Automatic template comes back with every row already flagged Yes, so you only remove the ones you do not want.

Does it also common the sub-assemblies?
Yes – it walks the whole bill structure and commons any sub-assembly or substitute-item bill that exists in the source but not yet in the target.

Troubleshooting

MessageCauseWhat to do
This API only supports upload of existing BOMsThe row was not produced by the download (for example a row typed in by hand).Use rows from the generated template; re-download and flag the rows you need.
No action takenCreate Common Bom was not set to Yes on that row.Set Create Common Bom to Yes for the rows you want processed.
Item is not defined in the target organizationThe assembly item itself is not assigned in the target organization.Assign the item to the target organization first, then re-run.
No action – bill already existsA bill already exists for that assembly and alternate in the target.None needed – the bill is already present.
Component items are not assigned in the target organizationSome component or substitute item is missing in the target.Assign the listed items in the target organization, then re-upload.
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_kfv msik,
    bom_bill_of_materials bbom
   where
     mp.organization_id = msik.organization_id and
     msik.organization_id = bbom.organization_id and
     msik.inventory_item_id = bbom.assembly_item_id and
     msik.bom_enabled_flag = 'Y' and
     msik.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
     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_source_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,
  --
  msiv2.concatenated_segments component_item,
  msiv2.description component_description,
  bic.item_num item_seq,
  bic.operation_seq_num operation_seq,
  bic.component_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,
  xxen_util.meaning(decode(bic.implementation_date,null,2,1),'SYS_YES_NO',700) component_implemented_flag,
  bic.implementation_date component_implementation_date,
  --
  bbom.bill_sequence_id,
  bbom.organization_id,
  bbom.assembly_item_id,
  bic.component_sequence_id,
  bic.component_item_id,
  mp.master_organization_id
 from
  bom_bill_of_materials    bbom
 ,bom_inventory_components bic
 ,mtl_system_items_vl      msiv1
 ,mtl_system_items_vl      msiv2
 ,mtl_parameters           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,
  xxen_util.meaning(bsc.enforce_int_requirements,'BOM_ENFORCE_INT_REQUIREMENTS',700) subst_integer_requirements
 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
  )
)
--
-- Main Query Starts Here
select /*+ push_pred(bom_subst) */
 --process--
 case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_upload.action_meaning(xxen_upload.action_update) else null end action_,
 case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_upload.status_meaning(xxen_upload.status_new) else null end status_,
 case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING', 'XXEN_REPORT_TRANSLATIONS', 0) else null end message_,
 null request_id_,
 null modified_columns_,
 to_char(null) row_id,
 :p_enable_attrs_update enable_attrs_update,
 case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_util.meaning('Y','YES_NO',0) else null end create_common_bom,
 boms.source_organization,
 boms.assembly_item,
 boms.assembly_description,
 boms.alternate_bom,
 boms.target_organization,
 boms.is_item_defined_in_tgt,
 boms.is_bom_defined_in_tgt,
 boms.is_common_bom_in_tgt,
 boms.comp_items_not_defined_in_tgt || nvl2(comp_items_not_defined_in_tgt,nvl2(subst_items_not_defined_in_tgt,',',null),null) || subst_items_not_defined_in_tgt comp_items_not_defined_in_tgt,
 boms.bill_sequence_id,
 boms.assembly_item_id,
 boms.source_organization_id,
 boms.target_organization_id
from
(
 select
  bom.organization_code source_organization,
  bom.assembly_item,
  bom.assembly_description,
  bom.alternate_bom,
  --
  mp.organization_code target_organization,
  --
  nvl((select 'Yes' from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bom.assembly_item_id),'No') is_item_defined_in_tgt,
  nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.assembly_item_id = bom.assembly_item_id and nvl(bbom2.alternate_bom_designator,'?') = nvl(bom.alternate_bom,'?') and rownum <= 1),'No') is_bom_defined_in_tgt,
  nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.common_organization_id = bom.organization_id and bbom2.common_assembly_item_id = bom.assembly_item_id and rownum <= 1),'No') is_common_bom_in_tgt,
  (select distinct
          listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
   from   bom_inventory_components bic,
          mtl_system_items_vl msiv
   where  bic.bill_sequence_id = bom.bill_sequence_id and
          msiv.organization_id = bom.organization_id and
          msiv.inventory_item_id = bic.component_item_id and
          not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bic.component_item_id)
   ) comp_items_not_defined_in_tgt,
  (select distinct
          listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
   from   bom_inventory_components bic,
          bom_substitute_components bsc,
          mtl_system_items_vl msiv
   where  bic.bill_sequence_id = bom.bill_sequence_id and
          bsc.component_sequence_id = bic.component_sequence_id and
          msiv.organization_id = bom.organization_id and
          msiv.inventory_item_id = bsc.substitute_component_id and
          not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bsc.substitute_component_id)
   ) subst_items_not_defined_in_tgt,
  --
  bom.bill_sequence_id,
  bom.assembly_item_id,
  bom.organization_id source_organization_id,
  mp.organization_id target_organization_id
 from
  bom,
  mtl_parameters mp
 where
 mp.master_organization_id = bom.master_organization_id and
 mp.organization_id != bom.organization_id and
 mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
 2=2
 union -- substitutes
 select
  bom.organization_code source_organization,
  bom_subst.substitute_item,
  bom_subst.substitute_description,
  bbom.alternate_bom_designator alternate_bom,
  --
  mp.organization_code target_organization,
  --
  nvl((select 'Yes' from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bom_subst.substitute_component_id),'No') is_item_defined_in_tgt,
  nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.assembly_item_id = bom_subst.substitute_component_id and rownum <= 1),'No') is_bom_defined_in_tgt,
  nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.common_organization_id = bbom.organization_id and bbom2.common_assembly_item_id = bbom.assembly_item_id and rownum <= 1),'No') is_common_bom_in_tgt,
  (select distinct
          listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
   from   bom_inventory_components bic,
          mtl_system_items_vl msiv
   where  bic.bill_sequence_id = bbom.bill_sequence_id and
          msiv.organization_id = bbom.organization_id and
          msiv.inventory_item_id = bom_subst.substitute_component_id and
          not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bom_subst.substitute_component_id)
   ) comp_items_not_defined_in_tgt,
  (select distinct
          listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
   from   bom_inventory_components bic,
          bom_substitute_components bsc,
          mtl_system_items_vl msiv
   where  bic.bill_sequence_id = bbom.bill_sequence_id and
          bsc.component_sequence_id = bic.component_sequence_id and
          msiv.organization_id = bbom.organization_id and
          msiv.inventory_item_id = bsc.substitute_component_id and
          not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bsc.substitute_component_id)
   ) subst_items_not_defined_in_tgt,
  --
  bbom.bill_sequence_id,
  bom_subst.substitute_component_id,
  bom.organization_id source_organization_id,
  mp.organization_id target_organization_id
 from
  bom,
  bom_subst,
  bom_bill_of_materials bbom,
  mtl_parameters mp
 where
 :p_explode_bom = 'Y' and
 bom.component_sequence_id = bom_subst.component_sequence_id and
 bbom.organization_id = bom.organization_id and
 bbom.assembly_item_id =  bom_subst.substitute_component_id and
 mp.master_organization_id = bom.master_organization_id and
 mp.organization_id != bom.organization_id and
 mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
 2=2
) boms
where
 :p_display_option = :p_display_option and
 :p_scope = :p_scope and
 3=3
Parameter NameSQL textValidation
Source Organization Code
 
LOV
Common BOM Scope
 
LOV
Target Hierarchy
mp.organization_id in
(
select
poe.organization_id_child
from
per_org_structure_elements  poe
connect by
poe.organization_id_parent   = prior poe.organization_id_child and
poe.org_structure_version_id = prior poe.org_structure_version_id
start with
poe.organization_id_parent =
(select
 mp.organization_id
 from mtl_parameters mp
 where mp.organization_code = :p_source_organization_code
)
and poe.org_structure_version_id =
(select
 posv.org_structure_version_id
 from
 per_org_structure_versions  posv,
 per_organization_structures pos
 where
 posv.organization_structure_id = pos.organization_structure_id and
 sysdate between posv.date_from and nvl(posv.date_to,sysdate) and
 ltrim(rtrim(pos.name)) = ltrim(rtrim(:p_organization_hierarchy)) and
 (pos.business_group_id = to_number(fnd_profile.value('PER_BUSINESS_GROUP_ID')) or pos.business_group_id is null)
)
)
LOV
Target Organization
mp.organization_code = :p_target_organization
LOV
Enable Attributes Update
 
LOV Oracle
Assembly
msik.concatenated_segments=:p_assembly
LOV
Alternate BOM
 
LOV
Show Sub-Assemblies
 
LOV Oracle
Show Missing BOMS only
boms.is_bom_defined_in_tgt = 'No'
LOV Oracle
Auto Populate Upload Columns
 
LOV
Implemented Only
 
LOV Oracle
Display
 
LOV
Effective Date
 
DateTime
Download
Related Blitz Reports
BOM Bill of Materials Upload BOM Bill of Material Structure FND Attached Documents CST Supply Chain Indented Bills of Material Cost WIP MES Job Traveler Data Template BOM Explosion Ferrotec Work Order Creation Upload Process TESTEP BOM XXDPF Shortages
Blitz Report™