oracle bom展开 sql,Oracle EBS-SQL (BOM-15):检查多层BOM(含common BOM).sql

select       distinct

b.lvl

层次,

b.OPERATION_SEQ_NUM

工序,

msi1.segment1

父件编码,

msi1.description

父件描述,

msi1.item_type                                 父件类型,

msi1.inventory_item_status_code 父件编码状态,

msi2.segment1

子件编码,

msi2.description

子件描述,

msi2.item_type

子件类型,

b.component_quantity

用量,

b.COMPONENT_YIELD_FACTOR              产出率

from inv.mtl_system_items_b msi1,

inv.mtl_system_items_b

msi2,

apps.BOM_BILL_OF_MATERIALS bom,

(select level lvl,

bic.bill_sequence_id,

bic.component_item_id,

bic.component_quantity,

bic.OPERATION_SEQ_NUM,

bic.COMPONENT_YIELD_FACTOR,

bic.COMPONENT_SEQUENCE_ID,

bic.item_num,

bic.wip_supply_type,

bic.supply_subinventory,

bic.effectivity_date

FROM

apps.BOM_INVENTORY_COMPONENTS bic

where bic.disable_date IS

NULL

and bic.IMPLEMENTATION_DATE

is not null

start with

bic.bill_sequence_id in

(select nvl(common_bill_sequence_id,bill_sequence_id)

from apps.BOM_BILL_OF_MATERIALS bom2,

inv.mtl_system_items_b

msi

where bom2.assembly_item_id =

msi.inventory_item_id

and bom2.organization_id = msi.organization_id

and msi.organization_id in(1,157)

and

bom2.alternate_bom_designator is null)

CONNECT BY bill_sequence_id in

prior

(SELECT distinct nvl(common_bill_sequence_id,bill_sequence_id)

FROM apps.BOM_BILL_OF_MATERIALS BO,

inv.mtl_system_items_b    msi

WHERE BO.assembly_item_id = bic.component_item_id

AND BO.organization_id in(1,157)

and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID

and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID

and bo.alternate_bom_designator is null

and disable_date IS NULL)) b

where b.bill_sequence_id =

nvl(bom.common_bill_sequence_id,bom.bill_sequence_id)

and bom.ORGANIZATION_ID in(1,157)

and bom.ORGANIZATION_ID = msi1.ORGANIZATION_ID

and bom.ASSEMBLY_ITEM_ID = msi1.INVENTORY_ITEM_ID

and bom.ORGANIZATION_ID = MSI2.ORGANIZATION_ID

AND b.component_item_id = MSI2.INVENTORY_ITEM_ID

and msi2.inventory_item_status_code <> ‘Inactive‘

order by b.lvl

原文：http://www.cnblogs.com/st-sun/p/3781783.html