Want to Fetch Customer Account Balance on Pos Invoice

ERPNext Selling/CRM
,
1

Can anyone give code for pos invoice which is in jinja format to show customer balance. i am using following code but it didnt show exact result.
"{% set invoices = frappe.get_list(‘Sales Invoice’, {‘customer’: doc.customer, ‘docstatus’: 1}, [‘outstanding_amount’]) %}
{% set total_unpaid_amount = invoices | map(attribute=‘outstanding_amount’) | sum %}

Your outstanding balance as of {{ doc.posting_date.strftime('%d-%m-%Y') }} is: {{ total_unpaid_amount | default(0.0) }}

"
2

Hi @am1024,

Please apply it and check it.

{% set invoices = frappe.get_all('Sales Invoice', filters={'customer': doc.customer, 'docstatus': 1}, fields=['outstanding_amount']) %}

{% set total_unpaid_amount = [0] %}
{% for invoice in invoices %}
    {% set total_unpaid_invoice = invoice.outstanding_amount %}
    {% if total_unpaid_amount.append(total_unpaid_amount.pop() + total_unpaid_invoice) %}{% endif %}
{% endfor %}

Your outstanding balance as of {{ frappe.utils.get_datetime(doc.posting_date).strftime("%d-%m-%Y") }} is: {{ total_unpaid_amount.pop() | default(0.0) }}

Output:

I tried with Delivery Note custom print format for the testing.

image
image4320×2154 261 KB

Thank You!

3

Thanks! @NCP It perfectly works (Y).

4

@NCP Is it possible to also add unallocated payments so if payment reconciliation is not done it shows correct amount ?

5

It’s hard to get in print format.

6

OK Thanks this works great. (Y)

7

Hello I managed to display the unpaid and paid POS Invoices as well as the Outstanding Balances using the code below but am failing to add the 3 totals to that I get the correct Balance including the UnConsolidated POS Invoices. Adding the 3 allows me to see the actual outstanding balance including unposted POS Invoice transactions.

The challenge now is to Add the 3 totals

{% set invoices_unpaid = frappe.get_all(‘POS Invoice’, filters={‘customer’: doc.customer, ‘status’: “Unpaid”}, fields=[‘grand_total’]) %}
{% set invoices_paid = frappe.get_all(‘POS Invoice’, filters={‘customer’: doc.customer, ‘status’: “Paid”}, fields=[‘grand_total’]) %}

{% set total_unpaid_amount = [0] %}
{% for invoice_unpaid in invoices_unpaid %}
{% set total_unpaid_invoice = invoice_unpaid.grand_total %}
{% if total_unpaid_amount.append(total_unpaid_amount.pop() + total_unpaid_invoice) %}{% endif %}
{% endfor %}

{% set total_paid_amount = [0] %}
{% for invoice_paid in invoices_paid %}
{% set total_paid_invoice = invoice_paid.grand_total %}
{% if total_paid_amount.append(total_paid_amount.pop() + total_paid_invoice) %}{% endif %}
{% endfor %}

Your outstanding balance as of {{ frappe.utils.get_datetime(doc.posting_date).strftime(“%d-%m-%Y”) }} is: {{ total_unpaid_amount.pop() | default(0.0) }}
Your paid amount as of {{ frappe.utils.get_datetime(doc.posting_date).strftime(“%d-%m-%Y”) }} is: {{ total_paid_amount.pop() | default(0.0) }}

{% if doc.customer %}
{% set ledger_entries = frappe.get_all(“GL Entry”, filters={“party_type”: “Customer”, “party”: doc.customer}, fields=[“SUM(debit) - SUM(credit) as balance”]) %}
{% if ledger_entries %}
{% set balance = ledger_entries[0].balance %}
Customer Balance: {{ frappe.format_value(balance, { “fieldtype”: “Currency” }) }}
{% endif %}
{% endif %}