Item cost table in oracle fusion: Costing tables in oracle fusion

Item cost table in oracle fusion : Costing tables in oracle fusion

In this post , we will be discuss about the Item cost table in oracle fusion. Item cost table helps to store the item costing information in oracle fusion. We have multiple costing  tables , which helps to store different different costing informations. Item cost tables help to developed the custom bip costing reports in oracle fusion. I will share the list of item cost tables in oracle fusion and the detail sql query to extract the list of item costing details in oracle fusion.

6 Important Cost tables in oracle fusion 

1.CST_STD_COSTS

2.CST_PERPAVG_COST

3.CST_COST_ELEMENTS_B

4.CST_INV_TRANSACTIONS

5.cst_costed_del_attr_onhand_v

6.CST_TRANSACTION_COSTS

7.cst_item_cost_history_v

CST_STD_COSTS :- 

This table helps to stores the information pertaining to standard costs.

CST_PERPAVG_COST:-

This table helps to stores the Current and Historical Perpavg Cost.

CST_COST_ELEMENTS_B :-

This is the base table of all cost elements defined in cost management. These cost elements help in providing break-up of item cost into various elements as per business requirements of cost accounting and management accounting.

CST_INV_TRANSACTIONS:-

This is Costing's Inventory Transactions table and is the source of all inventory transactions within costing. It contains all transactions that are imported into Costing through the interface and also transactions that are created by costing's engines during processing.

Detail Costing SQL Query using Item Cost Table in Oracle Fusion

select sum(a.total) total,a.SUBINVENTORY_CODE,a.inventory_item_id from

(select round(a.QUANTITY_ONHAND*b.TRANSACTION_COST,2) total,a.inventory_item_id

,c.SUBINVENTORY_CODE,a.REC_TRXN_ID,b.transaction_id,b.cost_date,a.QUANTITY_ONHAND,b.TRANSACTION_COST

 from CST_ONHAND_V a,

cst_item_cost_history_v b,

cst_costed_del_attr_onhand_v c

where 1=1

and a.inventory_item_id=:P_ITEM_ID

and a.REC_TRXN_ID=b.transaction_id

and a.inventory_item_id=b.inventory_item_id

and b.cost_date=(select max(cost_date) from cst_item_cost_history_v where transaction_id=b.transaction_id)

and a.REC_TRXN_ID=c.REC_TRXN_ID

group by 

a.inventory_item_id

,a.QUANTITY_ONHAND,b.TRANSACTION_COST,a.REC_TRXN_ID,b.transaction_id,b.cost_date,c.SUBINVENTORY_CODE) a

group by a.SUBINVENTORY_CODE,a.inventory_item_id

Costing tables in oracle fusion