Oracle EBS-SQL (BOM-16):检查多层BOM.sql
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')
页:
[1]