select rownum seq_num
      ,top_item
      ,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

  
connect by bbm.ASSEMBLY_ITEM_ID = prior bic.COMPONENT_ITEM_ID
start with msi.segment1=:P_ITEM
and ood.ORGANIZATION_CODE =:P_ORG