[Proposal] Payment Reconciliation Improvement

ERPNext Accounting
,
1

Current Method

Currently ERPNext’s method of Payment Reconciliation is to modify the original (submitted) Payment Entry or Journal Entry and repost its GL Entries so that its against_voucher points to the Invoice to reconcile with or the invoice “knock off.”

There are 2 ways to reconcile payments and their mechanisms are almost the same:

  1. Using Payment Reconciliation Tool
  2. Selecting advance payments in Sales Invoice and Purchase Invoice
Example of Current Method Payment Entry before Reconciliation: 
Bank                       100 Dr
    Debtors - Customer A       100 Cr (Against Sales Order)

Payment Entry after Reconciliation:

Bank                       100 Dr
    Debtors - Customer A       100 Cr (Against Sales Invoice)

Note that you cannot tell from the GL Entries that a reconciliation was performed and you cannot tell when it was performed either.

Problem with the Current Method

  1. Allows mutability in accounting entries: the original (submitted) payment documents are modified and their GL Entries are reposted
  2. The original payment documents lose the information about the fact that it was an advance payment
  3. It doesn’t allow reconciling Credit/Debit Notes with Invoices
  4. It doesn’t allow reconciling arbitrary documents with opposite balances (for example Employee Advance with Purchase Invoice)

Proposed Solution

  • Convert Payment Reconciliation Tool to Payment Reconciliation Voucher
  • Payment Reconciliation Voucher should create its own GL Entries to reconcile payments upon submission (see examples below)
  • Query GL Entries directly to get unreconciled payments and outstanding invoice documents (this will also get Credit/Debit notes to reconcile with invoices)
  • Allow selecting arbitrary unreconciled payments from any party (for example to allow Employee Advance) to reconcile with invoices
  • Automatically create Payment Reconciliation Voucher for invoices with Advance Payments table

Advantages of Proposed Solution

  • Maintains a clean audit trail
  • Reconciliation can be reversed (by cancelling Payment Reconciliation Voucher)
  • Sales/Purchase Order Advance Paid Amount does not become 0 upon reconciliation (at least when calculating from GL Entries)
  • Generalizes payments and invoices as negative-balance-vouchers and positive-balance-vouchers which should make it easier for both developers and users

Example: Sales Invoice (reference for examples below) 
Debtors - Customer A     100 Dr
    Sales                    100 Cr

Outstanding Amount of Sales Invoice (according to Accounts Receivable report): 100

Example: Advance Payment against Sales Order

Payment Entry (remains the same before and after reconciliation):

Bank                       100 Dr
    Debtors - Customer A       100 Cr (Against Sales Order)

Outstanding Amount of Sales Order (according to Accounts Receivable report): -100
Outstanding Amount of Sales Invoice (according to Accounts Receivable report): 100

Payment Reconciliation Voucher:

Debtors - Customer A       100 Dr (Against Sales Order)
    Debtors - Customer A       100 Cr (Against Sales Invoice)

Outstanding Amount of Sales Order (according to Accounts Receivable report): 0
Outstanding Amount of Sales Invoice (according to Accounts Receivable report): 0

Example: Advance Payment as Unallocated Amount **Payment Entry** (remains the same before and after reconciliation): 
Bank                       100 Dr
    Debtors - Customer A       100 Cr (not against any document)

Outstanding Amount of Payment Entry (according to Accounts Receivable report): -100
Outstanding Amount of Sales Invoice (according to Accounts Receivable report): 100

Payment Reconciliation Voucher:

Debtors - Customer A       100 Dr (Against Payment Entry)
    Debtors - Customer A       100 Cr (Against Sales Invoice)

Outstanding Amount of Payment Entry (according to Accounts Receivable report): 0
Outstanding Amount of Sales Invoice (according to Accounts Receivable report): 0

3 Likes
2

Agreed for increasing immutability.

Some points to take care of:

  1. Reports that depend on the status of the against_voucher as a means to identify balance should be changed.
  2. Custom queries / scripts needs to be identified. Would be great to agree on a standardized approach to identify which items would be affected. This approach, once shared, will help in all such changes that are expected to be carried out within ERPNext for enhancements. Probably something exists that I am not yet aware of.
  3. Would this improvement also allow transactions like Security Deposits to be received in multi-currency? Security Deposit is a liability but to be booked for a customer so that it can be levelled off upon returning. Something like Sales Invoice/Credit Note.
3

Yes I agree with the fact that we have to understand the effects of this change on other functionality. I will be writing a more detailed proposal for that.

I don’t see how this is related to this proposal. If you think it is then we can discuss it on Telegram and see if this use case can be included.