select rownum seq_num,

       lpad(to_char(level), decode(level, 1, 1, level + 1), '.') bom_level,

       bbm.ASSEMBLY_ITEM_ID,

       msi.segment1 assembly_item,

       msi.description assembly_description,

       bbm.COMMON_ASSEMBLY_ITEM_ID,

       bic.item_NUM,

       bbm.COMMON_BILL_SEQUENCE_ID,

       bbm.BILL_SEQUENCE_ID,

       msic.segment1 component_item,

       msic.description c_item_description,

       bic.COMPONENT_ITEM_ID,

       bic.COMPONENT_QUANTITY,

       msic.primary_unit_of_measure,

       bic.COMPONENT_YIELD_FACTOR,

       bic.EFFECTIVITY_DATE,

       bic.ATTRIBUTE1,

       bic.ATTRIBUTE2,

       bic.CHANGE_NOTICE,

       ood.ORGANIZATION_CODE,

       ood.ORGANIZATION_NAME,

       ood.ORGANIZATION_ID

  from bom_bill_of_materials         bbm,

       bom_inventory_components     bic,

       mtl_system_items_b               msi,

       mtl_system_items_b             msic,

       org_organization_definitions    ood

 where bbm.ASSEMBLY_ITEM_ID = msi.inventory_item_id

   and bbm.ORGANIZATION_ID = msi.organization_id

   and bic.COMPONENT_ITEM_ID = msic.inventory_item_id

   and bic.PK2_VALUE = msic.organization_id

   and bbm.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID

   and (bic.DISABLE_DATE is null or bic.DISABLE_DATE >= sysdate)

   and bic.EFFECTIVITY_DATE <= sysdate

   and ood.ORGANIZATION_ID = msi.organization_id

   and ood.ORGANIZATION_id = X

connect by bbm.ASSEMBLY_ITEM_ID = prior bic.COMPONENT_ITEM_ID

 start with msi.segment1 in (select msi.segment1

                                           from inv.mtl_system_items_b msi

                                         where msi.organization_id = X

                          ---and msi.segment1 like '10%'

                            and msi.item_type = 'FG')