select level level_id,
t.*
from (select msi1.segment1 farther_item,
msi1.inventory_item_id farther_item_id,
msi1.description farther_desc,
mtr.bill_sequence_id,
bic.item_num,
bic.operation_seq_num,
bic.component_item_id sub_item_id,
msi2.segment1 sub_item,
msi2.description sub_desc,
bic.component_quantity,
bic.effectivity_date,
bic.disable_date,
msi1.item_type farther_item_code,
fcl1.meaning farther_item_type,
msi2.item_type sub_item_code,
fcl2.meaning sub_item_type
from bom_bill_of_materials mtr,
mtl_system_items_b msi1,
fnd_common_lookups fcl1,
bom_inventory_components bic,
mtl_system_items_b msi2,
fnd_common_lookups fcl2
where mtr.assembly_item_id = msi1.inventory_item_id
and mtr.bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = msi2.inventory_item_id
and fcl1.lookup_type = 'ITEM_TYPE'
and fcl1.lookup_code = msi1.item_type
and fcl2.lookup_type = 'ITEM_TYPE'
and fcl2.lookup_code = msi2.item_type
and msi1.inventory_item_status_code = 'Active'
and msi2.inventory_item_status_code = 'Active'
and msi2.organization_id = 190
and mtr.organization_id = 190
and msi1.organization_id = 190
and bic.effectivity_date <= sysdate
and nvl(bic.disable_date,sysdate+1) > sysdate
) t
connect by prior t.sub_item = t.farther_item
start with t.farther_item = '8903600-00-010';