Aakvatech Limited - Auto-Create Payment Reconciliation in ERPNext

ERPNext can process queued payment reconciliations automatically, but it does not automatically create the Process Payment Reconciliation documents. This post explains how we closed that gap with a sa

 · 9 min read

Auto-Creating Process Payment Reconciliation Records in ERPNext

ERPNext includes a strong payment reconciliation engine. In version 15, the Process Payment Reconciliation DocType can queue reconciliation jobs, allocate payments against invoices, and process them in the background.

However, there is one practical gap: ERPNext expects the Process Payment Reconciliation document to already exist.

In other words, ERPNext can process queued reconciliation documents automatically, but it does not automatically create those queued documents for parties that have outstanding invoices and unreconciled payments.

This article explains how we addressed that gap using a Scheduled Server Script.

Why this matters

For companies with high transaction volume, manually creating Process Payment Reconciliation records is repetitive and easy to miss.

The native flow works like this:

Existing submitted Process Payment Reconciliation
        ↓
Status = Queued
        ↓
Scheduled job processes queued records
        ↓
ERPNext fetches invoices and payments
        ↓
ERPNext allocates and reconciles them

The missing step is:

Detect party/account combinations with reconciliation activity
        ↓
Create Process Payment Reconciliation automatically
        ↓
Submit it
        ↓
Let ERPNext's native scheduler process it

The goal was not to rewrite ERPNext’s reconciliation logic. The goal was to create the missing entry point and then let ERPNext do the reconciliation using its standard mechanisms.

Understanding the native ERPNext flow

ERPNext’s scheduled reconciliation process starts from this method:

erpnext.accounts.doctype.process_payment_reconciliation.process_payment_reconciliation.trigger_reconciliation_for_queued_docs

This method scans for submitted Process Payment Reconciliation records with:

docstatus = 1
status = "Queued"

It then triggers reconciliation jobs for eligible documents.

The important point is that this method does not create Process Payment Reconciliation documents. It only processes records that already exist.

That is where a custom Scheduled Server Script can help.

Final solution

We created a Scheduled Server Script that:

  1. Finds candidate party/account combinations from Payment Ledger Entry.
  2. Uses ERPNext’s native Payment Reconciliation.get_unreconciled_entries() method to validate whether reconciliation is actually possible.
  3. Checks that both outstanding invoices and unreconciled payments exist.
  4. Avoids duplicate active Process Payment Reconciliation records.
  5. Adds the required default_advance_account using a company-wise dictionary.
  6. Creates and submits a Process Payment Reconciliation document.
  7. Leaves the actual reconciliation to ERPNext’s native queue processor.

The final design is:

Payment Ledger Entry
  → discover company / party type / party / receivable-payable account

Company-wise advance account dictionary
  → assign default advance account

Payment Reconciliation
  → verify invoices and payments exist

Process Payment Reconciliation
  → create and submit queued document

Native ERPNext scheduler
  → perform reconciliation

Why Payment Ledger Entry was used

The first version considered using Party Account as the candidate source. That approach works, but it is configuration-driven.

Party Account tells us:

Which account is configured for this party?

For automatic reconciliation, a better question is:

Which party/account combinations actually have transaction activity?

That is why Payment Ledger Entry is a better source for candidate discovery.

The script uses Payment Ledger Entry to identify combinations of:

company
party_type
party
account
account_type

Then it lets ERPNext’s own Payment Reconciliation logic decide whether there are both invoices and payments to reconcile.

Why the against_voucher_no filter was removed

During testing, the original discovery query included this condition:

and ifnull(against_voucher_no, '') = ''

This turned out to be too restrictive.

Some relevant Payment Ledger Entry rows may have against_voucher_no populated and still belong to a party/account combination that should be checked for reconciliation.

The final approach is intentionally broader at the SQL discovery level and stricter at the ERPNext validation level:

SQL query → broad candidate discovery
Payment Reconciliation.get_unreconciled_entries() → actual eligibility check

This avoids missing valid candidates.

Handling Server Script safe_exec limitations

While building the Scheduled Server Script, a few Frappe Server Script limitations appeared.

The first issue was:

builtins.AttributeError: module has no attribute 'get_single'

So this was replaced:

frappe.get_single("Accounts Settings")

with:

frappe.get_doc("Accounts Settings", "Accounts Settings")

Another issue was:

builtins.AttributeError: module has no attribute 'get_traceback'

So this was replaced:

frappe.get_traceback()

with:

str(e)

This keeps the script compatible with Frappe’s Server Script safe_exec environment.

Why the advance account was added back

Initially, we tried removing default_advance_account, assuming it was not needed because the company was not using separate party advance accounts.

However, the Process Payment Reconciliation DocType still required the field in that environment. To keep the script reliable, a company-wise dictionary was added.

Example:

DEFAULT_ADVANCE_ACCOUNT_BY_COMPANY = {
    "Company A": {
        "Customer": "Customer Advances - CA",
        "Supplier": "Supplier Advances - CA"
    },
    "Company B": {
        "Customer": "Customer Advances - CB",
        "Supplier": "Supplier Advances - CB"
    }
}

This allows each company to use the correct customer and supplier advance accounts.

Scheduled Server Script

Below is the final script pattern.

# Auto Create Process Payment Reconciliation
# Script Type: Scheduled
#
# Purpose:
# Automatically create and submit Process Payment Reconciliation documents
# from actual Payment Ledger Entry activity.
#
# Discovery logic:
# Payment Ledger Entry
#   -> company / party_type / party / receivable_payable_account
#
# Advance account:
# Company-wise dictionary
#   -> Customer advance account / Supplier advance account
#
# Validation:
# Payment Reconciliation
#   -> verify both invoices and payments exist
#
# Execution:
# Process Payment Reconciliation
#   -> create and submit queued document

MAX_DOCS_TO_CREATE = 100
MAX_CANDIDATES_TO_CHECK = 500

# Leave empty to include all companies.
# Example:
# COMPANIES = ["Company A", "Company B"]
COMPANIES = []

INVOICE_LOOKBACK_DAYS = 365
PAYMENT_LOOKBACK_DAYS = 365

ACTIVE_PPR_STATUSES = ["Queued", "Running", "Paused"]

# -------------------------------------------------------------------------
# Configure this section.
#
# Use exact Company names and exact Account names.
#
# Customer -> advance received account
# Supplier -> advance paid account
# -------------------------------------------------------------------------

DEFAULT_ADVANCE_ACCOUNT_BY_COMPANY = {
    "Company A": {
        "Customer": "Customer Advances - CA",
        "Supplier": "Supplier Advances - CA"
    },
    "Company B": {
        "Customer": "Customer Advances - CB",
        "Supplier": "Supplier Advances - CB"
    }
}

# -------------------------------------------------------------------------

created_docs = []
skipped_docs = []
error_logs = []

accounts_settings = frappe.get_doc("Accounts Settings", "Accounts Settings")

if not accounts_settings.auto_reconcile_payments:
    frappe.log_error(
        "Auto Reconciliation of Payments is disabled in Accounts Settings.",
        "Auto Create Process Payment Reconciliation"
    )
else:
    date_rows = frappe.db.sql("""
        select
            curdate() as today,
            date_sub(curdate(), interval %(invoice_lookback_days)s day) as from_invoice_date,
            date_sub(curdate(), interval %(payment_lookback_days)s day) as from_payment_date
    """, {
        "invoice_lookback_days": INVOICE_LOOKBACK_DAYS,
        "payment_lookback_days": PAYMENT_LOOKBACK_DAYS
    }, as_dict=True)

    today = date_rows[0].today
    from_invoice_date = date_rows[0].from_invoice_date
    from_payment_date = date_rows[0].from_payment_date

    company_condition = ""
    sql_params = {
        "from_payment_date": from_payment_date,
        "limit": MAX_CANDIDATES_TO_CHECK
    }

    if COMPANIES:
        company_condition = " and company in %(companies)s "
        sql_params["companies"] = tuple(COMPANIES)

    candidate_rows = frappe.db.sql("""
        select
            company,
            party_type,
            party,
            account as receivable_payable_account,
            account_type,
            max(posting_date) as latest_posting_date,
            count(*) as ple_count
        from `tabPayment Ledger Entry`
        where
            delinked = 0
            and account_type in ('Receivable', 'Payable')
            and party_type in ('Customer', 'Supplier')
            and ifnull(company, '') != ''
            and ifnull(party_type, '') != ''
            and ifnull(party, '') != ''
            and ifnull(account, '') != ''
            and posting_date >= %(from_payment_date)s
            """ + company_condition + """
        group by
            company,
            party_type,
            party,
            account,
            account_type
        order by
            max(posting_date) desc
        limit %(limit)s
    """, sql_params, as_dict=True)

    for candidate in candidate_rows:
        if len(created_docs) >= MAX_DOCS_TO_CREATE:
            break

        company = candidate.company
        party_type = candidate.party_type
        party = candidate.party
        receivable_payable_account = candidate.receivable_payable_account

        if not company:
            continue

        if not party_type:
            continue

        if not party:
            continue

        if not receivable_payable_account:
            continue

        default_advance_account = None

        if company in DEFAULT_ADVANCE_ACCOUNT_BY_COMPANY:
            if party_type in DEFAULT_ADVANCE_ACCOUNT_BY_COMPANY[company]:
                default_advance_account = DEFAULT_ADVANCE_ACCOUNT_BY_COMPANY[company][party_type]

        if not default_advance_account:
            skipped_docs.append(
                "Skipped missing advance account mapping"
                + " | Company: " + company
                + " | Party Type: " + party_type
                + " | Party: " + party
                + " | Account: " + receivable_payable_account
            )
            continue

        existing_ppr = frappe.get_all(
            "Process Payment Reconciliation",
            filters={
                "docstatus": 1,
                "company": company,
                "party_type": party_type,
                "party": party,
                "receivable_payable_account": receivable_payable_account,
                "default_advance_account": default_advance_account,
                "status": ["in", ACTIVE_PPR_STATUSES]
            },
            fields=["name", "status"],
            limit_page_length=1
        )

        if existing_ppr:
            skipped_docs.append(
                "Skipped active PPR exists"
                + " | Company: " + company
                + " | Party Type: " + party_type
                + " | Party: " + party
                + " | Account: " + receivable_payable_account
                + " | Advance Account: " + default_advance_account
                + " | Existing PPR: " + existing_ppr[0].name
            )
            continue

        try:
            pr = frappe.new_doc("Payment Reconciliation")
            pr.company = company
            pr.party_type = party_type
            pr.party = party
            pr.receivable_payable_account = receivable_payable_account
            pr.default_advance_account = default_advance_account
            pr.from_invoice_date = from_invoice_date
            pr.to_invoice_date = today
            pr.from_payment_date = from_payment_date
            pr.to_payment_date = today
            pr.invoice_limit = 1
            pr.payment_limit = 1

            pr.get_unreconciled_entries()

            has_invoices = 0
            has_payments = 0

            if pr.invoices and len(pr.invoices) > 0:
                has_invoices = 1

            if pr.payments and len(pr.payments) > 0:
                has_payments = 1

            if not has_invoices:
                skipped_docs.append(
                    "Skipped no outstanding invoices"
                    + " | Company: " + company
                    + " | Party Type: " + party_type
                    + " | Party: " + party
                    + " | Account: " + receivable_payable_account
                    + " | Advance Account: " + default_advance_account
                )
                continue

            if not has_payments:
                skipped_docs.append(
                    "Skipped no unreconciled payments"
                    + " | Company: " + company
                    + " | Party Type: " + party_type
                    + " | Party: " + party
                    + " | Account: " + receivable_payable_account
                    + " | Advance Account: " + default_advance_account
                )
                continue

            ppr = frappe.get_doc({
                "doctype": "Process Payment Reconciliation",
                "company": company,
                "party_type": party_type,
                "party": party,
                "receivable_payable_account": receivable_payable_account,
                "default_advance_account": default_advance_account,
                "from_invoice_date": from_invoice_date,
                "to_invoice_date": today,
                "from_payment_date": from_payment_date,
                "to_payment_date": today
            })

            ppr.insert(ignore_permissions=True)
            ppr.submit()

            created_docs.append(ppr.name)

            frappe.db.commit()

        except Exception as e:
            frappe.db.rollback()

            error_logs.append(
                "Company: " + company
                + "\nParty Type: " + party_type
                + "\nParty: " + party
                + "\nAccount: " + receivable_payable_account
                + "\nAdvance Account: " + default_advance_account
                + "\nError: " + str(e)
            )

    summary = []

    summary.append("Auto Create Process Payment Reconciliation Summary")
    summary.append("")
    summary.append("Created: " + str(len(created_docs)))
    summary.append("Skipped: " + str(len(skipped_docs)))
    summary.append("Errors: " + str(len(error_logs)))

    if created_docs:
        summary.append("")
        summary.append("Created Process Payment Reconciliation documents:")
        summary.extend(created_docs)

    if skipped_docs:
        summary.append("")
        summary.append("Skipped records, first 100:")
        summary.extend(skipped_docs[:100])

    if error_logs:
        summary.append("")
        summary.append("Errors:")
        summary.extend(error_logs)

    frappe.log_error(
        "\n".join(summary),
        "Auto Create Process Payment Reconciliation Summary"
    )

Setup steps

Create a new Server Script:

Script Type: Scheduled
Name: Auto Create Process Payment Reconciliation
Frequency: Hourly or Daily

Then update this block with your real company and account names:

DEFAULT_ADVANCE_ACCOUNT_BY_COMPANY = {
    "Company A": {
        "Customer": "Customer Advances - CA",
        "Supplier": "Supplier Advances - CA"
    }
}

Make sure the account names are exact ERPNext account names.

How to use it

After saving the Scheduled Server Script:

  1. Confirm Accounts Settings > Auto Reconciliation of Payments is enabled.
  2. Confirm the native scheduled method is active:
erpnext.accounts.doctype.process_payment_reconciliation.process_payment_reconciliation.trigger_reconciliation_for_queued_docs
  1. Run the script manually once, or wait for the schedule.
  2. Check Process Payment Reconciliation for newly created submitted records.
  3. Check Error Log for the summary entry:
Auto Create Process Payment Reconciliation Summary

The script will log:

Created: number of PPR records created
Skipped: candidates skipped
Errors: records that failed

Duplicate prevention

The script avoids creating duplicate active reconciliation jobs by checking for existing submitted Process Payment Reconciliation records with status:

Queued
Running
Paused

The duplicate check uses:

company
party_type
party
receivable_payable_account
default_advance_account

This is aligned with the native queue-processing behavior and reduces the chance of overlapping reconciliation jobs.

Troubleshooting

frappe.get_single is not available

Use:

frappe.get_doc("Accounts Settings", "Accounts Settings")

instead of:

frappe.get_single("Accounts Settings")

frappe.get_traceback is not available

Use:

str(e)

inside exception handling.

No candidates are found

Run a direct SQL check against Payment Ledger Entry:

select
    company,
    party_type,
    party,
    account as receivable_payable_account,
    account_type,
    max(posting_date) as latest_posting_date,
    count(*) as ple_count
from `tabPayment Ledger Entry`
where
    delinked = 0
    and account_type in ('Receivable', 'Payable')
    and party_type in ('Customer', 'Supplier')
    and ifnull(company, '') != ''
    and ifnull(party_type, '') != ''
    and ifnull(party, '') != ''
    and ifnull(account, '') != ''
    and posting_date >= date_sub(curdate(), interval 365 day)
group by
    company,
    party_type,
    party,
    account,
    account_type
order by
    max(posting_date) desc
limit 500;

PPR records are created but not processed

Check that the native scheduled job is running:

erpnext.accounts.doctype.process_payment_reconciliation.process_payment_reconciliation.trigger_reconciliation_for_queued_docs

Also check the value of Accounts Settings.reconciliation_queue_size.

Final recommendation

This solution keeps ERPNext’s core reconciliation logic intact. The custom Scheduled Server Script only creates the missing Process Payment Reconciliation records.

That is the right boundary for a customization:

Custom script = discovery and document creation
ERPNext core = allocation, reconciliation, logging, and queue processing

This makes the feature easier to maintain, easier to debug, and safer during ERPNext upgrades.

Reference articles and discussions

Aakvatech Limited is a Frappe Gold Partner and ERPNext implementation company headquartered in Dar es Salaam, Tanzania, operating across East Africa and the UAE.

This article was co-created using AI to accelerate drafting, with final insights curated and validated by the author. Any customer, personal, or sensitive data referenced during drafting has been anonymized or masked where applicable. All contributors, reference URLs, tools, and materials used to assist this content curation are credited in the Reference section.

Published on
Mitesh P Choksi
Add a comment
Ctrl+Enter to add comment
AW
Agnes Gabusha 10 hours ago

This was a very useful read from an ERPNext implementation perspective. We recently faced similar reconciliation workload challenges in a client environment with high payment transaction volume, overall, this is a solid reference for ERPNext implementors looking to automate reconciliation workflows while still preserving ERPNext standard accounting behavior and framework compatibility.