Oracle EBS R12: Finding / Querying (SQL) Item Cost Histories

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
1 01-Nov-16 Misc Receipt 20 15 0 15
2 05-Nov-16 Misc Receipt 30 20 15 18
3 09-Nov-16 Misc Issue -10 Avg 18 18
4 10-Nov-16 Misc Receipt 50 21 18 19.67
5 12-Nov-16 Misc Issue -15 Avg 19.67 19.67

These transactions have been entered (as stated above) in the application as follows:

SC1.jpg

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.

SC2.jpg

Click on the cost history button.

SC3.jpg

View the cost history of the item:

sc3

As demonstrated functionally above, the complete cost history can also be obtained technically by running the following SQL:

SELECT   msi.concatenated_segments item_code,

mmt.transaction_date,

mtt.transaction_type_name,

mmt.transaction_quantity,

mcd.prior_cost,

mcd.new_cost

FROM mtl_cst_actual_cost_details mcd,

mtl_system_items_kfv msi,

mtl_material_transactions mmt,

mtl_transaction_types mtt

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:

SC3.jpg

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s