select msi1.segment1 父件编码,
msi1.description 父件描述,
msi1.primary_uom_code 父件单位,
msi2.segment1 子件编码,
msi2.description 子件描述,
msi2.primary_uom_code 子件单位,
bcb.component_quantity BOM用量,
bcb.component_yield_factor 产出率,
cos.item_cost 子件成本
from inv.mtl_system_items_b msi1,
inv.mtl_system_items_b msi2,
bom.bom_structures_b bsb,
bom.bom_components_b bcb,
(select cic.inventory_item_id,
cic.organization_id,
nvl((select max(mc.actual_cost) item_cost
from MTL_CST_ACTUAL_COST_DETAILS mc
where nvl(mc.actual_cost, 0) <> 0
and mc.inventory_item_id = cic.inventory_item_id
and mc.organization_id = cic.organization_id
and exists
(select 'X'
from (select mct.transaction_id,
mct.inventory_item_id,
mct.organization_id,
max(mct.creation_date)
from MTL_CST_ACTUAL_COST_DETAILS mct
where nvl(mct.actual_cost, 0) <> 0
and mct.organization_id = Y
and trunc(mct.creation_date) <=to_date('&DATE_YYYY_MM_DD', 'yyyy-mm-dd')
group by mct.transaction_id,
mct.inventory_item_id,
mct.organization_id) a
where a.transaction_id = mc.transaction_id
and a.inventory_item_id = mc.inventory_item_id
and a.organization_Id = mc.organization_id
and a.transaction_id = mc.transaction_id)
group by 1),
cic.item_cost) item_cost
from bom.cst_item_costs cic
where cic.cost_type_id = 2)cos
where msi1.inventory_item_id = bsb.assembly_item_id
and msi1.organization_id = bsb.organization_id
and msi2.inventory_item_id = bcb.component_item_id
and msi2.organization_id = to_number(bcb.pk2_value)
and bsb.bill_sequence_id = bcb.bill_sequence_id
and bcb.disable_date is null
and msi1.organization_id = Y
and msi2.inventory_item_id = cos.inventory_item_id
and msi2.organization_id = cos.organization_Id
and msi1.segment1 = '&item_number'