Multi-level BOM

-- Multilevel BOM
Select chlid_items.level_num,
Fmsib. segment1 parent_item,
Msib. segment1 component_item,
Chlid_items.bill_sequence_id
From mtl_system_items_ B msib,
(Select level level_num,
Bic. component_item_id,
Bic. bill_sequence_id
From bom_inventory_components Bic
Where Bic. bill_sequence_id is not null
And nvl (Bic. disable_date, sysdate + 1)> sysdate
Start with BIC. bill_sequence_id = (select BSB. bill_sequence_id
From mtl_system_items_ B msib,
Bom_structures_ B BSB
Where BSB. assembly_item_id = msib. inventory_item_id
And BSB. organization_id = msib. organization_id
And BSB. organization_id = 108
And BSB. assembly_item_id = 310)
Connect by Bic. bill_sequence_id = prior
(Select BSB. bill_sequence_id
From bom_structures_ B BSB
Where Bic. component_item_id = BSB. assembly_item_id) chlid_items,
Bom_structures_ B BSB,
Mtl_system_items_ B fmsib
Where msib. inventory_item_id = chpolic_items.component_item_id
And BSB. bill_sequence_id = chlid_items.bill_sequence_id
And BSB. assembly_item_id = fmsib. inventory_item_id
And fmsib. organization_id = BSB. organization_id
And msib. organization_id = BSB. organization_id;