Hi,
I was able to send outstanding invoices along with new invoice through below code: We can also replicate this in case any of the invoice is overdue.
Dear {{ doc.contact_display or 'Sir' }},
<br><br>
Thank you for your business. Please have Sales Invoice {{ doc.name }} as attached.
<br><br>
{% if frappe.get_list("Sales Invoice",
fields=["name","posting_date","customer","rounded_total","outstanding_amount"],
filters={ "docstatus":1, "customer":doc.customer, "outstanding_amount":(">",0),"name":("!=",doc.name)})
%}
Now including this we have below outstanding invoices for {{ doc.customer }}.<br>
<div align="center">
<table border="1" cellspacing="0" cellpadding="0" width="100%">
<tbody>
<tr>
<td width="18%" valign="top">
<p align="center">
<strong>Invoice</strong>
</p>
</td>
<td width="12%" valign="top">
<p align="center">
<strong>Date</strong>
</p>
</td>
<td width="37%" valign="top">
<p align="center">
<strong>Customer</strong>
</p>
</td>
<td width="13%" valign="top">
<p align="center">
<strong>Invoice Amt</strong>
</p>
</td>
<td width="18%" valign="top">
<p align="center">
<strong>Outstanding Amt</strong>
</p>
</td>
</tr>
{%- set outstanding_invoice = frappe.get_list("Sales Invoice",
fields=["name","posting_date","customer","rounded_total","outstanding_amount"],
filters={ "docstatus":1, "customer":doc.customer, "outstanding_amount":(">",0),"name":("!=",doc.name)})
-%}
{%- for row in outstanding_invoice -%}
<tr>
<td width="18%" valign="top">
<p align="center">
{{ row.name }}
</p>
</td>
<td width="12%" valign="top">
<p align="center">
{{ row.posting_date.strftime("%d-%m-%Y") }}
</p>
</td>
<td width="37%" valign="top">
<p align="center">
{{ row.customer }}
</p>
</td>
<td width="13%" valign="top">
<p align="right">
Rs. {{ row.rounded_total }}
</p>
</td>
<td width="18%" valign="top">
<p align="right">
Rs. {{ row.outstanding_amount}}</strong>
</p>
</td>
</tr>
{% endfor %}
<tr>
<td width="68%" colspan="3" valign="top">
<p align="center">
<strong>Total Outstanding</strong>
</p>
</td>
<td width="13%" valign="top">
<p align="right">
<strong></strong>
</p>
</td>
<td width="18%" valign="top">
<p align="right">
<strong>Rs. {{ outstanding_invoice|sum(attribute='outstanding_amount') }}</strong>
</p>
</td>
</tr>
</tbody>
</table>
</div>
<br>
Request you to process the payment at earliest.
<br><br>
If you have already made payment of any of above invoices please revert with payment details, and accept our gratitude.
<br>
{% endif %}
<br>
Accounts Department<br>
Email: accounts@........com<br>
<strong>{{ doc.company }}</strong>
Final Result:
