Bom Explosion Query

Below query can be handy to explode and Query BOM Details

SELECT 

  DISTINCT LPAD (' ', LEVEL * 2) || LEVEL order_level, 

  msib.segment1 assembly_item, 

  msib.description assembly_description, 

  msib.inventory_item_status_code assembly_item_status, 

  SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH, 

  msib2.segment1 AS component_item, 

  msib2.description component_item_description, 

  msib2.inventory_item_status_code component_item_status, 

  bic.item_num, 

  bic.operation_seq_num, 

  bic.component_quantity 

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_code = : p_org_code 

  /* organization here */

  AND bom.alternate_bom_designator IS NULL START WITH msib.segment1 = : p_item_number 

  /*  component item to be used here */

  CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id 

ORDER BY 

  PATH