Oracle ERP - Hierarchical BOM Query

The query below shows the use of hierarchical query syntax to list the parts of an assembly in a typical bill of material (BOM). The parts are setup in a hierarchical (assembly - component/part) or indented manner.

 

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;