Merge pull request #39591 from ruthra-kumar/conversion_on_future_payments
refactor: Do proper currency conversion on Future Payments column in AR/AP report
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 3a70afc..e3fa5e8 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -5,7 +5,7 @@
from collections import OrderedDict
import frappe
-from frappe import _, qb, scrub
+from frappe import _, qb, query_builder, scrub
from frappe.query_builder import Criterion
from frappe.query_builder.functions import Date, Substring, Sum
from frappe.utils import cint, cstr, flt, getdate, nowdate
@@ -576,6 +576,8 @@
def get_future_payments_from_payment_entry(self):
pe = frappe.qb.DocType("Payment Entry")
pe_ref = frappe.qb.DocType("Payment Entry Reference")
+ ifelse = query_builder.CustomFunction("IF", ["condition", "then", "else"])
+
return (
frappe.qb.from_(pe)
.inner_join(pe_ref)
@@ -587,6 +589,11 @@
(pe.posting_date).as_("future_date"),
(pe_ref.allocated_amount).as_("future_amount"),
(pe.reference_no).as_("future_ref"),
+ ifelse(
+ pe.payment_type == "Receive",
+ pe.source_exchange_rate * pe_ref.allocated_amount,
+ pe.target_exchange_rate * pe_ref.allocated_amount,
+ ).as_("future_amount_in_base_currency"),
)
.where(
(pe.docstatus < 2)
@@ -623,13 +630,24 @@
query = query.select(
Sum(jea.debit_in_account_currency - jea.credit_in_account_currency).as_("future_amount")
)
+ query = query.select(Sum(jea.debit - jea.credit).as_("future_amount_in_base_currency"))
else:
query = query.select(
Sum(jea.credit_in_account_currency - jea.debit_in_account_currency).as_("future_amount")
)
+ query = query.select(Sum(jea.credit - jea.debit).as_("future_amount_in_base_currency"))
else:
query = query.select(
- Sum(jea.debit if self.account_type == "Payable" else jea.credit).as_("future_amount")
+ Sum(jea.debit if self.account_type == "Payable" else jea.credit).as_(
+ "future_amount_in_base_currency"
+ )
+ )
+ query = query.select(
+ Sum(
+ jea.debit_in_account_currency
+ if self.account_type == "Payable"
+ else jea.credit_in_account_currency
+ ).as_("future_amount")
)
query = query.having(qb.Field("future_amount") > 0)
@@ -645,14 +663,19 @@
row.remaining_balance = row.outstanding
row.future_amount = 0.0
for future in self.future_payments.get((row.voucher_no, row.party), []):
- if row.remaining_balance > 0 and future.future_amount:
- if future.future_amount > row.outstanding:
+ if self.filters.in_party_currency:
+ future_amount_field = "future_amount"
+ else:
+ future_amount_field = "future_amount_in_base_currency"
+
+ if row.remaining_balance > 0 and future.get(future_amount_field):
+ if future.get(future_amount_field) > row.outstanding:
row.future_amount = row.outstanding
- future.future_amount = future.future_amount - row.outstanding
+ future[future_amount_field] = future.get(future_amount_field) - row.outstanding
row.remaining_balance = 0
else:
- row.future_amount += future.future_amount
- future.future_amount = 0
+ row.future_amount += future.get(future_amount_field)
+ future[future_amount_field] = 0
row.remaining_balance = row.outstanding - row.future_amount
row.setdefault("future_ref", []).append(
diff --git a/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py
index 976935b..6ff81be 100644
--- a/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py
@@ -772,3 +772,92 @@
# post sorting output should be [[Additional Debtors, ...], [Debtors, ...]]
report_output = sorted(report_output, key=lambda x: x[0])
self.assertEqual(expected_data, report_output)
+
+ def test_future_payments_on_foreign_currency(self):
+ self.customer2 = (
+ frappe.get_doc(
+ {
+ "doctype": "Customer",
+ "customer_name": "Jane Doe",
+ "type": "Individual",
+ "default_currency": "USD",
+ }
+ )
+ .insert()
+ .submit()
+ )
+
+ si = self.create_sales_invoice(do_not_submit=True)
+ si.posting_date = add_days(today(), -1)
+ si.customer = self.customer2
+ si.currency = "USD"
+ si.conversion_rate = 80
+ si.debit_to = self.debtors_usd
+ si.save().submit()
+
+ # full payment in USD
+ pe = get_payment_entry(si.doctype, si.name)
+ pe.posting_date = add_days(today(), 1)
+ pe.base_received_amount = 7500
+ pe.received_amount = 7500
+ pe.source_exchange_rate = 75
+ pe.save().submit()
+
+ filters = frappe._dict(
+ {
+ "company": self.company,
+ "report_date": today(),
+ "range1": 30,
+ "range2": 60,
+ "range3": 90,
+ "range4": 120,
+ "show_future_payments": True,
+ "in_party_currency": False,
+ }
+ )
+ report = execute(filters)[1]
+ self.assertEqual(len(report), 1)
+
+ expected_data = [8000.0, 8000.0, 500.0, 7500.0]
+ row = report[0]
+ self.assertEqual(
+ expected_data, [row.invoiced, row.outstanding, row.remaining_balance, row.future_amount]
+ )
+
+ filters.in_party_currency = True
+ report = execute(filters)[1]
+ self.assertEqual(len(report), 1)
+ expected_data = [100.0, 100.0, 0.0, 100.0]
+ row = report[0]
+ self.assertEqual(
+ expected_data, [row.invoiced, row.outstanding, row.remaining_balance, row.future_amount]
+ )
+
+ pe.cancel()
+ # partial payment in USD on a future date
+ pe = get_payment_entry(si.doctype, si.name)
+ pe.posting_date = add_days(today(), 1)
+ pe.base_received_amount = 6750
+ pe.received_amount = 6750
+ pe.source_exchange_rate = 75
+ pe.paid_amount = 90 # in USD
+ pe.references[0].allocated_amount = 90
+ pe.save().submit()
+
+ filters.in_party_currency = False
+ report = execute(filters)[1]
+ self.assertEqual(len(report), 1)
+ expected_data = [8000.0, 8000.0, 1250.0, 6750.0]
+ row = report[0]
+ self.assertEqual(
+ expected_data, [row.invoiced, row.outstanding, row.remaining_balance, row.future_amount]
+ )
+
+ filters.in_party_currency = True
+ report = execute(filters)[1]
+ self.assertEqual(len(report), 1)
+ expected_data = [100.0, 100.0, 10.0, 90.0]
+ row = report[0]
+ self.assertEqual(
+ expected_data, [row.invoiced, row.outstanding, row.remaining_balance, row.future_amount]
+ )