Query to get item cost in oracle fusion

Query to get item cost in oracle fusion

In this post , We will be discuss about the Query to get item cost in oracle fusion . This query will help to extract the Item costing details in oracle fusion. I will share the complete information about item cost tables in oracle fusion and the sql query using cost tables to get item cost in oracle fusion. Please find below the detail query to get item cost in oracle fusion and the detail item cost table list.

6 Important Tables to get item cost 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

Detail SQL Query to get item cost 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

How to Query the Error Code for Failed load

select ciitc.*, ciitc.error_code
from CST_I_INCOMING_TXN_COSTS ciitc
WHERE EXISTS
(select 1 from cst_i_inv_transactions ciit
where ciit.load_request_id=ciitc.load_request_id
and ciit.external_system_reference=ciitc.external_system_reference
and ciit.transaction_interface_line_num=ciitc.external_system_ref_id
)
and ciitc.ERROR_CODE is NOT NULL
and ciitc.external_system_source='C';