No, you have to work it out by going through the inventory transaction history.  It can be seen on the receipts and shipments by analyzing the quantity and value received / shipped (anywhere posting controls M1 and M3 are hit).  The math always works out in my experience, but proving it is quite another matter.

This has worked well for me to evaluate the cost change for an item if you have history on it.

Run from a quick report or the SQL Query Tool

I didn’t develop it, was done for me by Anthesis.

SELECT row_id,
       tran_id      Transaction_ID,
       applied_date,
       contract     Site,
       part_no,
       rev_qty      Qty,
       val          Cost_Value,
       cum_total    Cumulative_Qty,
       cum_val      Cumulative_Cost_Value,
       
       CASE
         WHEN cum_total = 0 THEN
          (lag(cum_val, 1) over(ORDER BY row_id)) /
          (lag(cum_total, 1) over(ORDER BY row_id))
         ELSE
          cum_val / cum_total
       END wac_value

  FROM (SELECT row_id,
               tran_id,
               applied_date,
               contract,
               part_no,
               rev_qty,
               val,
               
               SUM(rev_qty) over(PARTITION BY contract, part_no ORDER BY row_id) cum_total,
               
               SUM(val) over(PARTITION BY contract, part_no ORDER BY row_id) cum_val
        
          FROM (SELECT
                
                 ith.transaction_id tran_id,
                 
                 ma.company,
                 
                 ith.contract contract,
                 
                 ith.part_no,
                 
                 ith.transaction_code,
                 
                 ith.reject_code scrap_cause,
                 
                 ma.date_applied applied_date,
                 
                 CASE
                   WHEN ith.transaction_id = lag(transaction_id, 1)
                    over(ORDER BY transaction_id ASC)
                   
                    THEN
                    0
                 
                   ELSE
                    DECODE(direction, '-', -quantity, '0', 0, quantity)
                 
                 END rev_qty,
                 
                 NVL(DECODE(ma.debit_credit, 'D', ma.value, 'C', -ma.value),
                     0) val,
                 
                 ma.str_code str_code,
                 
                 row_number() over(PARTITION BY ith.contract, ith.part_no ORDER BY ith.transaction_id) row_id
                
                  FROM inst1app.inventory_transaction_hist ith
                
                  LEFT JOIN inst1app.mpccom_accounting ma
                
                    ON ith.accounting_id = ma.accounting_id
                
                 WHERE (ma.str_code = 'M1' OR ma.str_code = 'M3')
                      
                   AND ith.contract LIKE NVL('&Site', '%')
                   AND ith.part_no LIKE NVL('&Part_No', '%')
                
                 ORDER BY ith.contract, ith.part_no
                
                )) x

 ORDER BY contract, part_no
 

I’d be curious if this works for you as is…..

Also, the attachment on this post is one that I did the long way through the supplier invoice updates as well for price differences.

ShawnBerk Superhero-  has anyone tested this SQL report ?

@Emila75 

As I mentioned in my original post - yes, I have used it.

This has worked well for me to evaluate the cost change for an item if you have history on it.

but it gives me error, i can’t run it. Can you please check the sql ??  I appreciate it!

Without knowing the error, I can’t help you find the problem.

It is probably permissions related, but I can’t explain that blind.

It worked for me, but I have no one else that has reported it worked or not for them.

Worked fine for me but just need to change the application owner on the two table references to suit your company

            row_number() over(PARTITION BY ith.contract, ith.part_no ORDER BY ith.transaction_id) row_id
                
                  FROM inst1app.inventory_transaction_hist ith
                
                  LEFT JOIN inst1app.mpccom_accounting ma
                
                    ON ith.accounting_id = ma.accounting_id

Hope this helps