BOM Details 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)
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