BOM Explosion Query

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)