Bulk payment entry

How we can create bulk payment entry for invoices from purchase/sales invoice for multiple customer/supplier?

Hey, I just completed implementation of automated payment to suppliers using Payment Entry and Payment Order. It’s all 100% automated and I am in love with the system :D. I might be publishing a detailed article soon.

Let me know if you need any specific details.

If your objective is only to create bulk payment entry for once, then a scheduler script can do the job.

Please show me the flow or share code snippest so i can understand in detail.

Thanks in advance


I want to make payment of these three invoices at once by clicking on payment from action menu.

But when click on payment shows error like this.

We have created a payables aggregation application for exactly this purpose, hope it can solve your issue.

3 Likes

Well, what you pointed out is not possible in ERPNext. Instead what I did was:

  1. Create proper Payment Terms Template for each suppliers.

  1. Added necessary fields for bank details.

  1. Upon submit of Purchase Invoice, a server script would trigger, which will generate payment request for that particular invoice as per the Payment Terms Schedule.

if doc.enable_automatic_payment == 1:
    for line in doc.get("payment_schedule"):
        pr = frappe.get_doc({"doctype":"Payment Request"})
        pr.payment_request_type = "Outward"
        pr.transaction_date = line.get("due_date")
        pr.party_type = "Supplier"
        pr.party = doc.get("supplier")
        pr.reference_doctype = "Purchase Invoice"
        pr.reference_name = doc.get("name")
        pr.grand_total = line.get("payment_amount")
        pr.bank_account = frappe.db.get_value("Bank Account",{"account_name":"DUMMY"})
        pr.mode_of_payment = frappe.db.get_value("Mode of Payment",{"name":"Bank Transfer (API)"})
        #frappe.show_alert({message:__('Payment requests created.'),indicator:'green'}, 5);
        pr.save()
        pr.submit()
  1. Then, we setup a Weekly Scheduler Job in Server Script, which would generate Payment Orders after aggregation of all payment requests per supplier.

for party_list in frappe.db.get_list("Payment Request",fields=["party"],filters=[["Status","=","Initiated"],["transaction_date","<",frappe.utils.frappe.utils.nowdate],["payment_request_type","=","Outward"], ["reference_doctype","=","Purchase Invoice"]],group_by="party"):
    doc = frappe.get_doc({
        "doctype":"Payment Order",
        "payment_order_type":"Payment Request",
        "party":party_list.party,
        "company_bank_account":frappe.db.get_value("Bank Account",{"is_automatic_payout_account":1},"name"),
        })
    for payment_request in frappe.db.get_list("Payment Request",fields=["name","party","reference_doctype","reference_name","grand_total","bank_account"],filters=[["Status","=","Initiated"],["transaction_date","<",frappe.utils.frappe.utils.nowdate],["payment_request_type","=","Outward"],["party","=",party_list.party]]):
        doc.append("references",{
            "reference_doctype":payment_request.reference_doctype,
            "reference_name":payment_request.reference_name,
            "amount":payment_request.grand_total,
            "supplier": frappe.db.get_value("Supplier",{"name":payment_request.party}),
            "payment_request":payment_request.name,
            "bank_account":payment_request.bank_account
            })
    bank_details = frappe.db.get_value ("Supplier",{"name":doc.party},["account_name","bank_account_number","ifsc", "contact_name", "mobile", "email"],as_dict=True)
    doc.account_name = bank_details.account_name
    doc.account_number = bank_details.bank_account_number
    doc.branch_code = bank_details.ifsc
    doc.contact_name = bank_details.contact_name
    doc.email = bank_details.email
    doc.mobile = bank_details.mobile
    doc.total_amount = 0
    amount = 0
    for line in doc.get("references"):
        amount = line.amount
        doc.total_amount = amount + doc.total_amount
    doc.save()
    doc.submit()


  1. On generation of Payment Orders, a web hook will be triggered, which will send the payment information to Bank’s API via Make.com.

We are considering to move from Make.com to N8N. There are better ways to do the entire process in ERPNext, but since I am a non-coder, I have used jugaad and workarounds like Make.com to make things possible.

The above is just a brief of what I did, there is a lot more to it, like 3 to 4 validation rules for Purchase Invoices etc, to ensure that payments are not triggered by mistake and also adding new fields in Supplier, Purchase Order, Purchase Invoice, Payment Requests and Payment Order.

This whole process has brought down the workload of our accounts team to a very considerable extent.

I am now working on automated posting of Payment Entries when the payments are processed by the bank.

2 Likes

welldone :+1:

1 Like

Just stumbled upon this thread… I presume it is seen from an Asian view on this topic. If someone looks into it from a ISO 20022, this would suggest a bank reconciliation. There are a few tools around, that will read the bank transactions (e.g. camt.053) and on that basis close Sales Invoice and Purchase Invoices (by creating automated individual payment entry records - not one bulk, for traceability).

One example is the “Bank Wizard”: GitHub - libracore/erpnextswiss: ERPNext application for Switzerland-specific use cases

Hope this helps someone.

1 Like

Oh in India this is not possible. How companies do automatic reconciliation here is a little complicated. I have always wondered - why cant the banks add more details into their statement or at least include valuable info into the statements.

For automated reconciliation, here in India we create a virtual account for each customer. All the money that comes into this account is automatically posted in the customers ledger. But there a loads of bottlenecks for virtual accounts. Not every bank have this feature. Even if the banks have this feature not every company can get access to this feature. Some banks provide this feature only to large corporates.
Thanks to new fintech boom in India. The fintech companies offer many of such services like virtual accounts for free or for a nominal charge.

1 Like