Merge pull request #40920 from nabinhait/pe-fetch-reference-docs-amounts
fix: Fetch correct outstanding and total amount for reference journal entry
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 0ac0156..2145812 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -1665,6 +1665,8 @@
frm.doc.payment_type == "Receive"
? frm.doc.paid_from_account_currency
: frm.doc.paid_to_account_currency,
+ party_type: frm.doc.party_type,
+ party: frm.doc.party,
},
callback: function (r, rt) {
if (r.message) {
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 3193a0d..48813a0 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -496,7 +496,11 @@
continue
ref_details = get_reference_details(
- d.reference_doctype, d.reference_name, self.party_account_currency
+ d.reference_doctype,
+ d.reference_name,
+ self.party_account_currency,
+ self.party_type,
+ self.party,
)
# Only update exchange rate when the reference is Journal Entry
@@ -2175,33 +2179,42 @@
return frappe.get_cached_value("Company", company, fields, as_dict=1)
-def get_outstanding_on_journal_entry(name):
- gl = frappe.qb.DocType("GL Entry")
- res = (
- frappe.qb.from_(gl)
- .select(
- Case()
- .when(
- gl.party_type == "Customer",
- Coalesce(Sum(gl.debit_in_account_currency - gl.credit_in_account_currency), 0),
- )
- .else_(Coalesce(Sum(gl.credit_in_account_currency - gl.debit_in_account_currency), 0))
- .as_("outstanding_amount")
- )
+def get_outstanding_on_journal_entry(voucher_no, party_type, party):
+ ple = frappe.qb.DocType("Payment Ledger Entry")
+
+ outstanding = (
+ frappe.qb.from_(ple)
+ .select(Sum(ple.amount_in_account_currency))
.where(
- (Coalesce(gl.party_type, "") != "")
- & (gl.is_cancelled == 0)
- & ((gl.voucher_no == name) | (gl.against_voucher == name))
+ (ple.against_voucher_no == voucher_no)
+ & (ple.party_type == party_type)
+ & (ple.party == party)
+ & (ple.delinked == 0)
)
- ).run(as_dict=True)
+ ).run()
- outstanding_amount = res[0].get("outstanding_amount", 0) if res else 0
+ outstanding_amount = outstanding[0][0] if outstanding else 0
- return outstanding_amount
+ total = (
+ frappe.qb.from_(ple)
+ .select(Sum(ple.amount_in_account_currency))
+ .where(
+ (ple.voucher_no == voucher_no)
+ & (ple.party_type == party_type)
+ & (ple.party == party)
+ & (ple.delinked == 0)
+ )
+ ).run()
+
+ total_amount = total[0][0] if total else 0
+
+ return outstanding_amount, total_amount
@frappe.whitelist()
-def get_reference_details(reference_doctype, reference_name, party_account_currency):
+def get_reference_details(
+ reference_doctype, reference_name, party_account_currency, party_type=None, party=None
+):
total_amount = outstanding_amount = exchange_rate = account = None
ref_doc = frappe.get_doc(reference_doctype, reference_name)
@@ -2216,12 +2229,13 @@
exchange_rate = 1
elif reference_doctype == "Journal Entry" and ref_doc.docstatus == 1:
- total_amount = ref_doc.get("total_amount")
if ref_doc.multi_currency:
exchange_rate = get_exchange_rate(party_account_currency, company_currency, ref_doc.posting_date)
else:
exchange_rate = 1
- outstanding_amount = get_outstanding_on_journal_entry(reference_name)
+ outstanding_amount, total_amount = get_outstanding_on_journal_entry(
+ reference_name, party_type, party
+ )
elif reference_doctype == "Payment Entry":
if reverse_payment_details := frappe.db.get_all(
diff --git a/erpnext/accounts/doctype/payment_entry/test_payment_entry.py b/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
index 03bd21f..2eedf42 100644
--- a/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
@@ -1074,7 +1074,9 @@
pe.source_exchange_rate = 50
pe.save()
- ref_details = get_reference_details(so.doctype, so.name, pe.paid_from_account_currency)
+ ref_details = get_reference_details(
+ so.doctype, so.name, pe.paid_from_account_currency, "Customer", so.customer
+ )
expected_response = {
"account": get_party_account("Customer", so.customer, so.company),
"account_type": None, # only applies for Reverse Payment Entry
diff --git a/erpnext/accounts/doctype/payment_request/payment_request.py b/erpnext/accounts/doctype/payment_request/payment_request.py
index 5272294..c4a99c0 100644
--- a/erpnext/accounts/doctype/payment_request/payment_request.py
+++ b/erpnext/accounts/doctype/payment_request/payment_request.py
@@ -638,7 +638,11 @@
if payment_request_name:
ref_details = get_reference_details(
- ref.reference_doctype, ref.reference_name, doc.party_account_currency
+ ref.reference_doctype,
+ ref.reference_name,
+ doc.party_account_currency,
+ doc.party_type,
+ doc.party,
)
pay_req_doc = frappe.get_doc("Payment Request", payment_request_name)
status = pay_req_doc.status