Query to Find the Immediate Parent in a BOM

SELECT DISTINCT SUBSTR (SYS_CONNECT_BY_PATH (msib.inventory_item_id, '/') || '/'
                      , 2
                      , INSTR ((SYS_CONNECT_BY_PATH (msib.inventory_item_id, '/') || '/')
                             , '/'
                             , 2
                              ) - 2
                       ) current_parent_item
              , SUBSTR (SYS_CONNECT_BY_PATH (msib.segment1, '/') || '/'
                      , 2
                      , INSTR ((SYS_CONNECT_BY_PATH (msib.segment1, '/') || '/')
                             , '/'
                             , 2
                              ) - 2
                       ) current_parent_item
           FROM bom.bom_components_b bic
              , bom.bom_structures_b bom
              , inv.mtl_system_items_b msib
              , inv.mtl_system_items_b msib2
          WHERE 1 = 1
            AND bic.bill_sequence_id = bom.bill_sequence_id
            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 bom.organization_id = :p_organization_id
            AND bom.assembly_item_id = :p_assembly_item_id
     START WITH bic.component_item_id = :p_component_item_id
     CONNECT BY bic.component_item_id = PRIOR msib.inventory_item_id