Oracle BOM Query

select

   level lev

   ,LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(assembly_item, '/')||'/'||x.component_item path 

   ,x.*

from (

  select

    mp.organization_code

  , ai.item_number assembly_item

  , c.item_num

  , c.operation_seq_num

  , ci.item_number component_item

  , b.bill_sequence_id

  , b.common_bill_sequence_id

  , b.organization_id

  , nvl(b.common_assembly_item_id,b.assembly_item_id) assembly_item_id

  , c.component_item_id

  , c.component_sequence_id

  , b.alternate_bom_designator

  , to_char(c.effectivity_date, 'DD-MON-YYYY HH24:MI:SS') effectivity_date

  , to_char(c.implementation_date, 'DD-MON-YYYY HH24:MI:SS') implementation_date

  , to_char(c.disable_date, 'DD-MON-YYYY HH24:MI:SS') disable_date

 FROM

    bom_bill_of_materials b

  , bom_inventory_components c

  , mtl_item_flexfields ai

  , mtl_item_flexfields ci

  , mtl_parameters mp

 WHERE

   -- bill/component

   nvl(b.common_bill_sequence_id,b.bill_sequence_id) = c.bill_sequence_id

   -- parent item

   and b.assembly_item_id = ai.inventory_item_id

   and b.organization_id = ai.organization_id

   -- component item

   and c.component_item_id = ci.inventory_item_id

   and b.organization_id = ci.organization_id

   -- org name

   and b.organization_id = mp.organization_id

   and mp.organization_code = '&org'

  ) x

   start with x.assembly_item = '&item' and  nvl(x.alternate_bom_designator,'~NULL~')='~NULL~'

   connect by prior x.component_item_id = x.assembly_item_id

         and nvl(x.alternate_bom_designator,'~NULL~')='~NULL~'

         and level <= &levels

   order siblings by x.operation_seq_num, x.item_num