Thursday, November 26, 2020

Oracle Fusion: Query to Get Item Cost per Transactions (Layer Cost)



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.