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')