Invoice Price Variations for Average PO costing method
Description
Environment
Attachments
Activity
Michael Powacht 3 days ago
Thank you ,
As discussed, I tried patching iDempiere 11 with your commits, but there are larger conflicts because your commits were developed for iDempiere 12, where patched programs probably underwent other changes in release 12, especially in org.adempiere.base/src/org/compiere/acct/Doc_MatchInv.java.
Therefore, it’s best we first upgrade and test release 12 and test your commits against iDempiere 12, which should be completed within April.
Elaine Tan last week
Hello
This ticket is ready for testing.
Changes:
If it is a reverse-accrual matched invoice, perform a stock coverage check using the current stock quantity to prevent any leftover amount in the inventory GL
If the allocation is created as a result of the invoice reversal, do not use RLG/RLL, as this will cause the AP balance not to be zero
Commit:
Thanks,
Elaine
Michael Powacht March 10, 2025 at 3:53 AM
Hello ,
during iDempiere release-11 testing, our team found the following issue in relation to reverse-accrual, stock coverage check, and payment allocation posting. The same issue was identified in demo system :
Case price variance/exchange rate, system does not perform a stock coverage check in reverse-accrual for product invoice. This works correctly in reverse-accrual for landed cost invoice.
AP is not zero in alternative schema after reverse-accrual for product invoice. Including all invoice which is from function “ Create lines from”. AP is zero if invoice line added directly, landed cost invoice and other charge etc.,
Here is a way to simulate the issue:
Create PO in THB. # PO800313ZTH25
Line#10 TTP-Product023, Qty= 100, price = 100
Line#20 TTP-Product024, Qty = 100, price = 185
Create MR in Jan-25 # MR500357ZTH25
Line#10 TTP-Product023, Qty= 85, price = 100
Line#20 TTP-Product024, Qty = 100, price = 185
Create inventory decrease#1
Line#10 TTP-Product023, Qty= 50
Line#20 TTP-Product024, Qty= 50
---------------------------------------
Stock Balance
TTP-Product023 ,Qty = 35, Unit cost = 100
TTP-Product02, Qty = 50, Unit cost = 185
---------------------------------------
Create Product invoice in Feb-25 # VI1000361ZTH25
Line#10 TTP-Product023, Qty= 100, price = 107
Line#20 TTP-Product024, Qty = 100, price = 178
**Matched invoice of each line posting correctly from stock coverage check.
Create inventory decrease#2
Line#10 TTP-Product023, Qty= 15
Line#20 TTP-Product024, Qty= 50
---------------------------------------
Stock Balance
TTP-Product023 ,Qty = 20, Unit cost = 107
TTP-Product024, Qty = 0, Unit cost = 175 -> Amount in inventory GL = 0
---------------------------------------
Reverse-Accrual VI1000361ZTH25 in Mar-25
Reversal matched invoice uses original value, not perform stock coverage check from current stock Qty. This causes leftover amount in inventory GL.
Reversal invoice posting uses original value but there is RLG/RLL adjusted in PA posting. This causes AP not zero in USD. This PA posting issue only happens with “ Create lines from” invoice. For landed cost and other direct invoice, PA posting is zero. So there is no AP amount left.
Michael Powacht March 3, 2025 at 1:58 AM
Patch was successfully tested in version 11.
Please deploy in master, and versions 11 and 12.
Michael Powacht February 28, 2025 at 5:33 AM
Error message 'java.lang.ArithmeticException: Non-terminating decimal expansion; no exact representable decimal result' in matched invoice for price variance with insufficient stock coverage.
Issue was discussed with
Currently, the system is designed to post invoice price variations (IPV) occurring between PO and Vendor invoice back into inventory and updates the costing records, which is OK as long as there is sufficient stock coverage and no quantities have been sold to the customer between Material Receipt and the invoice containing price variations. Problems arise if there is insufficient stock coverage and the entire IPV is posted back to inventory. This will distort our Inventory value because the entire IPV will be distributed to the remaining stock quantity. This is an incorrect accounting treatment. What the system should do is to determine how many quantities are still on stock and only allocate the proportional IPV back to inventory while the rest of the IPV shall get posted to a price variation account in the P/L. As an example, the following link explains how this works in an SAP environment:
https://help.sap.com/saphelp_46c/helpdata/en/a8/b994d9452b11d189430000e829fbbd/frameset.htm
The attached document contains a step-by-step simulation which explains today's situation and the associated problems.
Based on example values in the above document, here are the requirements on how the system should behave in terms of accounting and costing treatment:
Matched Invoice
NIR = +50 (Not Invoiced Receipts)
IC = -60 (Inventory Clearing)
INV = (1*[12-10]) = +2 (Inventory Asset)
IPV = (4*[12-10]) = +8 (Invoice Price Variance)
The resulting Average PO cost would be:
Scenario Init: 5 with $50 total value => $10/piece MAP (Moving Average Price or Average PO)
After Ship: 1 with $10 total value => $10/piece MAP
After Inv: 1 with $12 total value => $12/piece MAP (where $12 is derived from $10 from After Ship + $2 from Matched Invoice inventory adjustment)
Please kindly help to resolve this issue.