+20
Apps10 Upd11.
Weighted Average, Cost per Part.
I have (1) of part A in stock, inventory value = $1000.
I receive (1) of part A into stock, actual cost = $2000.
I now have (2) of part A in stock, inventory value = $1500/unit, total of $3000.
When the inventory unit value changed from $1000 to $1500, is this revaluation ‘event’ visible anywhere, is there any history of this for someone to see? Or is this just behind the scenes and no record of it is stored?
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.
+1
ShawnBerk Superhero- has anyone tested this SQL report ?
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.
+1
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.
+3
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
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
