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