Oracle Ebs-sql (CST-1): Check BOM historical cost query (Average costs). SQL

Select Msi1.segment1 Parent Code,

Msi1.description Parent Part description,

Msi1.primary_uom_code Parent Unit,

Msi2.segment1 Sub-piece code,

Msi2.description description of child parts,

Msi2.primary_uom_code Unit of child units,

Bcb.component_quantity BOM Usage,

Bcb.component_yield_factor yield,

Cos.item_cost cost of sub-parts

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 '