Below is the query to get item transaction cost in oracle fusion which can be seen in Review Cost Accounting Distributions form.
SELECT imt.transaction_id
, imt.transaction_interface_id
, imt.source_code
, imt.source_line_id
, cit.external_system_reference
, cit.subinventory_code
, cit.locator_id
, TO_CHAR (FROM_TZ (ct.transaction_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
, 'RRRR-MM-DD HH24:MI:SS') transaction_date
, ct.cost_transaction_type
, TO_CHAR (FROM_TZ (ct.cost_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
, 'RRRR-MM-DD HH24:MI:SS') cost_date
, ct.use_item_cost_flag
, TO_CHAR (FROM_TZ (clc.eff_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
, 'RRRR-MM-DD HH24:MI:SS') eff_date
, clc.layer_cost_id
, clc.cost_source
, clc.cost_reference
, clc.quantity
, clc.uom_code
, clc.unit_cost
, clc.currency_code
, clc.posted_flag
, clc.created_by
, TO_CHAR (FROM_TZ (clc.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
, 'RRRR-MM-DD HH24:MI:SS') creation_date
, clc.last_updated_by
, TO_CHAR (FROM_TZ (clc.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
, 'RRRR-MM-DD HH24:MI:SS') last_update_date
FROM cst_layer_costs clc
, cst_transactions ct
, cst_inv_transactions cit
, inv_material_txns imt
WHERE cit.external_system_ref_id = imt.transaction_id
AND cit.EXTERNAL_SYSTEM_REFERENCE = 'FUSION'
AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id
AND clc.transaction_id = ct.transaction_id
-- AND imt.transaction_id = 1257380
As for average cost, you can query it from CST_PERPAVG_COST table.
