Introduction:
This SQL query is used to fetching the data of Bill of Materials Explosion details like Order Level, Root Assembly, Assembly Item, Assembly Item Description, Assembly Item Type, Component Item, Component Item Description, Path Name, Component Item Type, Component Item Status, Item Seq, Operation Seq, Component Quantity, Effettivity Date, Disable Date
Cause of the issue:
Business wants a report that details of BOM tree
How do we solve:
Create a report in BI publisher using below SQL query to extract the Bill of Materials Explosion details.
SQL Query:
SELECT DISTINCT LPAD (‘ ‘, LEVEL * 2) || LEVEL order_level,
CONNECT_BY_ROOT msib.segment1 root_assembly,
msib.segment1 assembly_item,
msib.description assembly_description,
msib.inventory_item_status_code assembly_item_status,
msib.item_type assembly_item_type,
SYS_CONNECT_BY_PATH (msib2.segment1, ‘/’) PATH,
msib2.segment1 AS component_item,
msib2.item_type component_item_type,
msib2.description component_item_description,
msib2.inventory_item_status_code component_item_status,
bic.item_num, bic.operation_seq_num, bic.component_quantity,
bic.effectivity_date, bic.disable_date,
SYSDATE rec_creation_date, -1 rec_created_by
FROM bom.bom_components_b bic,
bom.bom_structures_b bom,
inv.mtl_system_items_b msib,
inv.mtl_system_items_b msib2,
mtl_parameters mp
WHERE 1 = 1
AND bic.bill_sequence_id = bom.bill_sequence_id
AND SYSDATE BETWEEN bic.effectivity_date
AND NVL (bic.disable_date, SYSDATE)
AND bom.assembly_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
AND bic.component_item_id = msib2.inventory_item_id
AND bom.organization_id = msib2.organization_id
AND mp.organization_id = msib.organization_id
AND mp.organization_id = rec_make_item.organization_id
–:p_org_code /* organization here */
AND bom.alternate_bom_designator IS NULL
START WITH msib.segment1 = rec_make_item.segment1
/* component item to be used here */
CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
ORDER BY PATH, item_num ASC
