AR Payment Allocation: Incorrect Currency Balancing Postings

Description

The scenario results in incorrect Currency Balancing postings in USD schema and can be simulated as follows:

  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.

Environment

None

Activity

Show:
Elaine Tan
March 24, 2020, 10:08 AM

Hi Low,

Please find below the patch file.

Thanks,
Elaine

Michael Powacht
March 26, 2020, 6:51 AM

Hi Elaine, we deployed your Doc_AllocationHdr.java rounding variance changes in Production this morning and after 1/2 day postings I ran a few checks against the DB. It seems there is still a problem with Unallocated Cash/Payment Selection not zero after allocation. I found 2 cases and both the cases seem to involve invoices and credit memos.

Simulation data:

Invoices/Credit Memos period 1 THB/USD: 29.905289946739:
Invoice1: THB 9,362.50
Invoice2: THB 9,362.50
Invoice3: THB 2,587.26
Invoice4: THB 14,519.90
Invoice5: THB 14,329.44
Credit Memo1: -936.25
Credit Memo2: -936.25

Payment period 2 THB/USD 31.526248754713
Payment 1: GL Unallocated Cash THB 48,289.10 = USD 1,531.71

Allocation period 2 THB/USD same as payment
GL Unallocated Cash Total for all invoices = USD 1,531.71
Difference=.01

 

Cheers, Michael

 

Elaine Tan
March 27, 2020, 8:27 AM

Patch: 202003271610-IDEMPIERE-4200.patch

Fixed allocation postings involving credit memo or negative invoice balance

Michael Powacht
March 28, 2020, 4:00 AM

@Elaine, successfully tested your patch. Please deploy in public repo.

Carlos Ruiz
June 13, 2020, 2:18 PM

Commit https://github.com/idempiere/idempiere/commit/132cc4
created a warning:

The method createRealizedGainLoss(DocLine, MAcctSchema, Fact, MAccount, MInvoice, BigDecimal, BigDecimal) from the type Doc_AllocationHdr is never used locally

I think that method can be deleted

Assignee

Heng Sin Low

Reporter

Elaine Tan

Labels

None

Tested By

Michael Powacht

Fix versions

Priority

Minor
Configure