Oracle ERP - Hierarchical BOM Query
SELECT
CONNECT_BY_ROOT q_bom.assembly_num root_assembly,
q_bom.assembly_num, q_bom.component_num, q_bom.qty,
SUBSTR(SYS_CONNECT_BY_PATH(q_bom.assembly_Num, ' <-- '),5) assembly_path,
LEVEL bom_level, CONNECT_BY_ISCYCLE is_cycle
FROM
(SELECT mb1.segment1 assembly_num, mb2.segment1 component_num, bc.component_quantity qty
FROM bom.bom_components_b bc,
bom.bom_structures_b bs,
inv.mtl_system_items_b mb1,
inv.mtl_system_items_b mb2
WHERE bs.assembly_item_id = mb1.inventory_item_id
AND bc.component_item_id = mb2.inventory_item_id
AND bc.bill_sequence_id = bs.bill_sequence_id
AND mb1.organization_id = mb2.organization_id
AND bs.organization_id = mb2.organization_id
AND bc.disable_date Is Null
AND bs.alternate_bom_designator IS NULL
AND mb1.organization_id = 90101110109
) q_bom
START WITH q_bom.assembly_Num = '&i_comp'
CONNECT BY NOCYCLE PRIOR q_bom.component_num = q_bom.assembly_num
ORDER SIBLINGS BY q_bom.assembly_Num;