GL Inventory Clearing - Minor rounding variances

Description

GL Payment Selection (PS), Unallocated Cash(UC) =>already resolved

When recording a payment, there is a single line posting to GL DR Payment Se

lection (Vendor Payment) or GL CR Unallocated Cash (Customer Receipt)
If the allocation document refers to multiple invoices, there a multiple line postings to GL CR Payment Selection (Vendor Payment) or GL DR Unallocated Cash (Customer Receipt)
If the payment is made in a currency other than the primary or secondary accounting schema’s, the conversion into schema currency can lead to minor differences between GL DR/CR Payment Selection or GL DR/CR Unallocated Cash.
That means, even if the payment/receipt was fully allocated to invoices, there may be minor left-over variances. For fully allocated payments, the balance on PS and UC should be zero.
You resolved the above issue by implementing a rounding adjustment in program /org.adempiere.base/src/org/compiere/acct/Doc_AllocationHdr.java, method: createRoundingCorrection(…);

GL Inventory clearing =>to be resolved

Similar to the one above:

When recording an invoice, there is a single line posting for a Purchase Order Line to GL DR Inventory Clearing.
If there are multiple material receipt lines for this PO or invoice line, the matched invoice document creates multiple line postings to GL CR Inventory Clearing.
If the invoice is recorded in a currency other than the primary or secondary accounting schema’s, the conversion into schema currency can lead to minor differences between GL DR/CR Inventory Clearing.
That means, although the invoice was fully allocated to material receipts through the matched invoice document, there may be minor left-over variances. For fully allocated invoices, the balance on Inventory Clearing should be zero.
Here is a way to simulate this scenario:
Currency rate: e.g. AUD/THB=22.5062, Account Date=November 2019
Purchase Order in AUD, Qty=500, Price= 23.32, Line Amount=AUD 11,660.00
MR1 Qty=250 = AUD 5830.00 x 22.5062 => DR Inventory/CR NIR THB 131,211.15
MR2 Qty=250 = AUD 5830.00 x 22.5062 => DR Inventory/CR NIR THB 131,211.15
Invoice (Vendor) Qty=500 = AUD 11,660.00 x 22.5062 => DR Inventory Clearing/CR AP THB 262,422.29
Matched Invoice Line1 => DR NIR/CR Inventory Clearing THB 131,211.15
Matched Invoice Line2 => DR NIR/CR Inventory Clearing THB 131,211.15
There is now a minor balance of CR THB .01 due to rounding which needs to be eliminated in all affected accounting schemas (THB and USD schema in our case).

As was implemented in the rounding solution for Payment Selection, there is a difference in how the rounding adjustment in GL Inventory Clearing should be offset for Currency Balancing=Y and Currency Balancing=N.

If Currency Balancing=Y, the rounding adjustment should be posted as follows:

DR Inventory Clearing/CR Currency Balancing OR
CR Inventory Clearing/CR Currency Balancing
If Currency Balancing=N, the rounding adjustment is posted to the largest balance sheet account I believe.

Environment

None

Activity

Show:
Michael Powacht
March 4, 2020, 7:12 AM

Dear Elaine, we found another issue related to Doc_AllocationHdr. A rather rare scenario, but it happens at times.
In the following scenario Unallocated Cash/Payment Selection are not zero in a currency-converted schema (e.g. USD)

1. Payment (on account) in period Dec-2019 for local currency 12000.00
2. Invoice in period Jan-2020 for a local currency amount of 10700.00. The currency rate from local currency to USD (primary schema) is different between Dec-19 and Jan-20
3. Manual payment allocation of 10700.00. There is now a payment left-over amount of 1300.00 in local currency
4. Second invoice in period Jan-2020 for a local currency amount of 1300.00.
5. Manual allocation of the payment left-over amount of 1300.00 to the invoice.
PROBLEM: Unallocated cash balances for the payment and allocation document are not zero in the currency-converted schema.

Elaine Tan
March 5, 2020, 9:48 AM

Patch: 0017-IDEMPIERE-4127-GL-Inventory-Clearing-Minor-rounding-.patch

Fix:
Unallocated cash balances for the payment and allocation document are not zero in the currency-converted schema

Michael Powacht
March 6, 2020, 1:36 AM

@Elaine, works as expected. Please commit.

Elaine Tan
March 10, 2020, 6:31 AM

Issue:
1. AR Payment in period 1 in local schema currency e.g. THB 12000.
Currency rate THB/USD 1/30.212666962751.
2. Customer invoice in period 2 for partial amount of THB 10700.
Currency rate THB/USD 1/29.905289946739.
3. Perform manual partial allocation using window Payment Allocation for THB 10700.
The allocation document posts as follows in USD:
DR Unallocated Cash 357.80,
CR AR 357.80 ,
CR Unallocated Cash 3.65,
DR Currency Balancing 3.65 (not ok).
This is incorrect and 3.65 (or actually 3.64 according to our conversion) should go to Realized Loss instead of Currency Balancing. Currency Balancing should only be used for minor rounding variances (usually .01 or .02) to ensure total Unallocated Cash/Payment Selection/AR/AP/Inventory Clearing and NIR are zero after final 'clearing' OR to ensure total debit and credit in an accounting fact are zero.

Elaine Tan
March 10, 2020, 7:18 AM

Opening a new ticket for the issue above. Please refer to

Assignee

Elaine Tan

Reporter

Michael Powacht

Labels

None

Tested By

None

Fix versions

Priority

Minor
Configure