BOM展开得到叶子节点

BOM展开得到叶子节点

[@more@]

select * from
(
select DISTINCT
msi1.segment1 P_item,
to_char(b.lvl) LV,
nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,
msi2.segment1 C_item,msi2.description,
b.component_quantity Component_Qty
from inv.mtl_system_items msi1,
inv.mtl_system_items msi2,
bom.bom_bill_of_materials bom,
bom.bom_operation_sequences bos,
(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 bom.bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
( select bill_sequence_id
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
--and msi.segment1 like 'FCC1SAA4CSNNBG%'
--and msi.segment1 like 'FGC1SAAMRSYN%'
and msi.segment1 = 'TREE' --Bom level SJNN0018A
and msi.organization_id =13
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id in
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 13
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 = bom.bill_sequence_id
and bom.ORGANIZATION_ID = 13
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.SEGMENT1 not lIKE 'F9%'
--AND MSI2.SEGMENT1 not like 'R__B%'
--and msi2.segment1 not like 'F8%'
--and msi2.segment1 not like 'R9%'
--and msi2.segment1 not like 'Z%'
order by 2
) b
where b.c_item not in(

select DISTINCT
msi1.segment1 P_item
/*to_char(b.lvl) LV,
nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,
msi2.segment1 C_item,msi2.description,
b.component_quantity Component_Qty*/
from inv.mtl_system_items msi1,
inv.mtl_system_items msi2,
bom.bom_bill_of_materials bom,
bom.bom_operation_sequences bos,
(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 bom.bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
( select bill_sequence_id
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
--and msi.segment1 like 'FCC1SAA4CSNNBG%'
--and msi.segment1 like 'FGC1SAAMRSYN%'
and msi.segment1 = 'TREE' --Bom level SJNN0018A
and msi.organization_id =13
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id in
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 13
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 = bom.bill_sequence_id
and bom.ORGANIZATION_ID = 13
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.SEGMENT1 not lIKE 'F9%'
--AND MSI2.SEGMENT1 not like 'R__B%'
--and msi2.segment1 not like 'F8%'
--and msi2.segment1 not like 'R9%'
--and msi2.segment1 not like 'Z%'
--order by 2
)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/104451/viewspace-1009986/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/104451/viewspace-1009986/