Offer return invoices for matching

[barredterra]

Use case

We receive a purchase invoice for $100 and, before the first invoice is paid, a credit note for $10. We create a bank transfer for the outstanding $90.

Problem

The credit note does not show up in the bank reconciliation tool. We could only reconcile with the $100 invoice and would leave the $10 unreconciled. Instead, we have to take the long route of creating a Payment Entry in the full form and fetching outstanding invoices there.

Proposed Solution

Show unpaid return invoices as well:

def get_unpaid_pi_matching_query(exact_match, exact_party_match, currency, company):
    purchase_invoice = frappe.qb.DocType("Purchase Invoice")

    party_condition = purchase_invoice.supplier == Parameter("%(party)s")
    party_match = frappe.qb.terms.Case().when(party_condition, 1).else_(0)

    grand_total_condition = purchase_invoice.grand_total == Parameter("%(amount)s")
    amount_match = frappe.qb.terms.Case().when(grand_total_condition, 1).else_(0)

    query = (
        frappe.qb.from_(purchase_invoice)
        .select(
            (party_match + amount_match + 1).as_("rank"),
            ConstantColumn("Purchase Invoice").as_("doctype"),
            purchase_invoice.name.as_("name"),
            purchase_invoice.outstanding_amount.as_("paid_amount"),
            purchase_invoice.bill_no.as_("reference_no"),
            purchase_invoice.bill_date.as_("reference_date"),
            purchase_invoice.supplier.as_("party"),
            ConstantColumn("Supplier").as_("party_type"),
            purchase_invoice.posting_date,
            purchase_invoice.currency,
            party_match.as_("party_match"),
            amount_match.as_("amount_match"),
        )
        .where(purchase_invoice.docstatus == 1)
        .where(purchase_invoice.company == company)
-       .where(purchase_invoice.is_return == 0)
-       .where(purchase_invoice.outstanding_amount > 0.0)
+       .where(purchase_invoice.outstanding_amount != 0.0)
        .where(purchase_invoice.currency == currency)
    )

    if exact_match:
        query = query.where(grand_total_condition)
    if exact_party_match:
        query = query.where(party_condition)

    return str(query)

The same applies to Sales Invoice.

[marination]

@barredterra Cant the bank transaction be reconciled with the unpaid invoice?

I tried the use case on the sales side. Due to the debit note, the original invoice's outstanding becomes 90 from 100. The Bank Transaction is also for 90 Euros, so it knocks off right ?

[barredterra]

Note to self: check if this is still an issue

[barredterra]

@marination after a customer reported a similar issue again, I reproduced this on the selling side:

1. Create a Sales Invoice
2. Create a Bank Transaction for the full amount
3. Reconcile the Sales Invoice with a Bank Transaction
4. Create a credit note, refunding above sales invoice (make sure to enable Update Outstanding Against Self)
5. Create a Bank Transaction for the refund
6. Try to reconcile the bank transaction with the credit note

-> The credit note does not show up in our Bank Reconciliation Tool, no matter what filters I set.

According to a customer, the same issue exists for the corresponding workflow on the selling side. Also, the likely workaround of creating a Payment Entry first, then reconciling it, does not work (cannot receive payment from supplier without allocating a credit note).

[marination]

2 cases:

• Pre Payment: Total +$90 = $100 SINV + $10 RET-SINV (100 -10) [Here we want to knock off a SI and its return together against the 100]
• Post Payment:
  • Total +$100 = $100 SINV (Paid and Reconciled)
  • Refund -$10 = $10 RET-SINV (Standalone) [Here we want to knock off a return SI against a withdrawal. Currently we only fetch PIs against a withdrawal]

If we allows unpaid invoices, unpaid returns should also be allowed to make the tool useful. It will be annoying if half the reco has to be done manually.