fix: GL Entries for AP/AR Summary without SQL join
SQL query modified to fetch only those GL Entries for Accounts Payable Summary and Accounts Receivable Summary reports where the corresponding payment entry is not in cancelled state.
diff --git a/erpnext/accounts/party.py b/erpnext/accounts/party.py
index 7d53db2..e01cb6e 100644
--- a/erpnext/accounts/party.py
+++ b/erpnext/accounts/party.py
@@ -606,20 +606,19 @@
cond = "1=1"
if posting_date:
if future_payment:
- cond = "gle.posting_date <= '{0}' OR DATE(creation) <= '{0}' """.format(posting_date)
+ cond = "posting_date <= '{0}' OR DATE(creation) <= '{0}' """.format(posting_date)
else:
- cond = "gle.posting_date <= '{0}'".format(posting_date)
+ cond = "posting_date <= '{0}'".format(posting_date)
if company:
- cond += "and gle.company = {0}".format(frappe.db.escape(company))
+ cond += "and company = {0}".format(frappe.db.escape(company))
- data = frappe.db.sql(""" SELECT gle.party, sum(gle.{0}) as amount
- FROM `tabGL Entry` gle
- INNER JOIN `tabPayment Entry` pe ON pe.name = gle.voucher_no
+ data = frappe.db.sql(""" SELECT party, sum({0}) as amount
+ FROM `tabGL Entry`
WHERE
- gle.party_type = %s and gle.against_voucher is null
- and pe.docstatus = 1
- and {1} GROUP BY gle.party"""
+ party_type = %s and against_voucher is null
+ and is_cancelled = 0
+ and {1} GROUP BY party"""
.format(("credit") if party_type == "Customer" else "debit", cond) , party_type)
if data: