BOM Level Based Query

BOM  Explosion  Query:

select  distinct

       level,msib.segment1 "parent item",msib.description,bom.ALTERNATE_BOM_DESIGNATOR,

       mir1.REVISION "Assembly Item Revision",bic.OPERATION_SEQ_NUM,

       msib2.segment1 as "component",bic.ITEM_NUM,msib2.description,mir2.revision "Component revision",ml.Meaning

from   bom_components_b bic,

       bom_structures_b bom,

       mtl_system_items_b msib,

       mtl_system_items_b msib2,

       mtl_item_revisions mir1,

       mtl_item_revisions mir2,

       mfg_lookups  ml

where  1=1

       and bic.bill_sequence_id = bom.bill_sequence_id

       and bic.disable_date is null

       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 = 144/* organization id here */

       and bic.effectivity_date < sysdate

       and mir1.inventory_item_id=msib.inventory_item_id

       and mir2.inventory_item_id=msib2.inventory_item_id

       and ml.lookup_type='WIP_SUPPLY'

       and ml.lookup_code=bic.WIP_SUPPLY_TYPE

start with msib.segment1 = 'FG-AXI30001' /* top parent item here */

connect by nocycle prior bic.component_item_id = msib.inventory_item_id

order by level