The purpose of this article is to demonstrate how an Item Cost History can be obtained from Oracle for any required purpose, in an average costing setup. This article will display the method to extract cost histories both functionally and technically.
For this demonstration, the following transactions have been processed in Oracle, for a single item:
Item: 0134.00017 (DISPENSER 10x20x01 350 GM2)
|Sr. No||Transaction Date||Transaction Type||Quantity||Cost||Average Cost Before Transaction||Average Cost after Transaction|
These transactions have been entered (as stated above) in the application as follows:
In order to view the cost histories functionally, navigate as follows:
Navigation: Cost Management – SLA > Item Costs > Item Cost History
Enter the item being demonstrated.
Click on the cost history button.
View the cost history of the item:
As demonstrated functionally above, the complete cost history can also be obtained technically by running the following SQL:
SELECT msi.concatenated_segments item_code,
FROM mtl_cst_actual_cost_details mcd,
WHERE 1 = 1
AND mcd.inventory_item_id = msi.inventory_item_id
AND mcd.organization_id = msi.organization_id
AND mcd.transaction_id = mmt.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.transaction_type_id = mtt.transaction_type_id
ORDER BY mcd.creation_date DESC
This SQL will provide the detailed cost history of an item. The transaction wise PRIOR_COST and NEW_COST for an item are available in the mtl_cst_actual_cost_details table. The output of this SQL for the item being demonstrated is as follows: