fix: query (#18709)
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 8c2ea73..fc46132 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -648,13 +648,18 @@
orders = []
if voucher_type:
- ref_field = "base_grand_total" if party_account_currency == company_currency else "grand_total"
+ if party_account_currency == company_currency:
+ grand_total_field = "base_grand_total"
+ rounded_total_field = "base_rounded_total"
+ else:
+ grand_total_field = "grand_total"
+ rounded_total_field = "rounded_total"
orders = frappe.db.sql("""
select
name as voucher_no,
- {ref_field} as invoice_amount,
- ({ref_field} - advance_paid) as outstanding_amount,
+ if({rounded_total_field}, {rounded_total_field}, {grand_total_field}) as invoice_amount,
+ (if({rounded_total_field}, {rounded_total_field}, {grand_total_field}) - advance_paid) as outstanding_amount,
transaction_date as posting_date
from
`tab{voucher_type}`
@@ -663,13 +668,14 @@
and docstatus = 1
and company = %s
and ifnull(status, "") != "Closed"
- and {ref_field} > advance_paid
+ and if({rounded_total_field}, {rounded_total_field}, {grand_total_field}) > advance_paid
and abs(100 - per_billed) > 0.01
{condition}
order by
transaction_date, name
""".format(**{
- "ref_field": ref_field,
+ "rounded_total_field": rounded_total_field,
+ "grand_total_field": grand_total_field,
"voucher_type": voucher_type,
"party_type": scrub(party_type),
"condition": condition