-- 没有加入BOM的物料

select

    msi.segment1 item_no,

    msi.description,

    msi.primary_uom_code uom,

    --msi.item_type,

    (select meaning from FND_LOOKUP_VALUES_VL where 1=1 AND lookup_type = 'ITEM_TYPE' and lookup_code=msi.item_type) item_type,

    nvl((SELECT SUM (moq.primary_transaction_quantity) on_hand

        FROM mtl_onhand_quantities_detail moq

        WHERE 1 = 1 AND moq.inventory_item_id = msi.inventory_item_id AND moq.organization_id = msi.organization_id), 0) on_hand_qty

from mtl_system_items_b msi

where 1=1

and msi.eng_item_flag = 'N'

and msi.organization_id = 190

--and msi.inventory_item_id= 15395

--and msi.segment1 = '9SL4000030AFDIF0'

and not exists(

    select  'x'

    --       bom.assembly_item_id,

    --       bic.component_item_id,

    --       bom.organization_id

      from bom_bill_of_materials bom,

           bom_inventory_components bic

     where bom.BILL_SEQUENCE_ID = bic.bill_sequence_id

       and bom.organization_id = msi.organization_id--190

       --and bom.assembly_item_id = 15395

       --and bic.component_item_id = 11898

       and ( bom.assembly_item_id = msi.inventory_item_id or bic.component_item_id = msi.inventory_item_id)

   )

---------------------------------------------------------------------------------------------------------------------------------------------------------

-- bom list

select level,

lst.assembly_item_id,

lst.component_item_id,

lst.organization_id,

lst.farther,

lst.component

  from (select bom.assembly_item_id,

               bic.component_item_id,

               bom.organization_id

               ,(select msi.segment1 from inv.mtl_system_items_b msi where msi.organization_id=123 and  msi.inventory_item_id = bom.assembly_item_id) farther,

               (select msi.segment1 from inv.mtl_system_items_b msi where msi.organization_id=123 and msi.inventory_item_id = bic.component_item_id) component

          from bom_bill_of_materials bom,

               bom_inventory_components bic

         where bom.BILL_SEQUENCE_ID = bic.bill_sequence_id

           and bom.organization_id = 123) lst

start with (lst.assembly_item_id = 15395)--15395)

connect by lst.assembly_item_id = prior lst.component_item_id