Query Costs shown in Review Item Costs

Summary:

Query Costs shown in Review Item Costs

Content (please ensure you mask any confidential information):

I need to get Item costs as shown in the 'Review Item Costs ' UI. This includes inventory items that doesn't currently have any onhands available. I put together the below query, using tables CST_TRANSACTIONS, CST_TRANSACTION_COSTS and CST_VAL_UNIT_COMBINATIONS

SELECT * FROM (

SELECT CST.INVENTORY_ITEM_ID, COMB.SUBINVENTORY_CODE, TRANS.UNIT_COST, CST.COST_DATE,ROW_NUMBER() OVER (PARTITION BY CST.INVENTORY_ITEM_ID, COMB.SUBINVENTORY_CODE ORDER BY CST.INVENTORY_ITEM_ID, CST.COST_DATE DESC) rownum1

FROM cst_transactions CST, CST_TRANSACTION_COSTS TRANS, CST_VAL_UNIT_COMBINATIONS COMB