selectdistinct
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
子件类型,
ponent_quantity
用量,
PONENT_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,
ponent_item_id,
ponent_quantity,
bic.OPERATION_SEQ_NUM,
PONENT_YIELD_FACTOR,
PONENT_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 anization_id = anization_id
and anization_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 = ponent_item_id
AND anization_id in(1,157)
and ANIZATION_ID = ANIZATION_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(mon_bill_sequence_id,bom.bill_sequence_id)
and ANIZATION_ID in(1,157)
and ANIZATION_ID = ANIZATION_ID
and bom.ASSEMBLY_ITEM_ID = msi1.INVENTORY_ITEM_ID
and ANIZATION_ID = ANIZATION_ID
AND ponent_item_id = MSI2.INVENTORY_ITEM_ID
and msi2.inventory_item_status_code <> ‘Inactive‘
order by b.lvl
原文：/st-sun/p/3781783.html
