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.

2 comments:

  1. Hi How to connect it to CST_PERPAVG_COST

    ReplyDelete
  2. wah mantap ini mas Luhung.
    btw ini ambil dari cost yang di input per transaksi kan ya?
    misalnya dari misc receipt gitu, input manual untuk item pricenya
    harusnya angkanya sesuai dengan hasil query ini ya?

    ReplyDelete

Silahkan pos komentarnya... :)