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.

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;

Shown below is a snapshot of the BOM.

Hierarchical Query ERP BOM

Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: October 13, 2013

  1090