select
msi.segment1,
msi.description,
msi.item_type
from inv.mtl_system_items_b msi
where msi.organization_id = X
and msi.item_type in ('SA','FG') --成品、子装配件
and msi.inventory_item_status_code = 'Active'
and exists ( select null from bom.bom_structures_b bsb,
bom.bom_components_b bcb
where bsb.organization_id = msi.organization_id
and bsb.assembly_item_id = msi.inventory_item_id
and bsb.bill_sequence_id = bcb.bill_sequence_id
and nvl(bcb.disable_date,sysdate+1)> sysdate
and rownum = 1)
and not exists (select null from bom.bom_operational_routings bor,
bom.bom_operation_sequences bos
where bor.organization_id = msi.organization_id
and bor.assembly_item_id = msi.inventory_item_id
and bor.routing_sequence_id = bos.routing_sequence_id
and nvl(bos.disable_date,sysdate+1)>sysdate
and rownum = 1)
转载于:https://www.cnblogs.com/st-sun/p/3778400.html