-- 没有加入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