fix: payment document link fix (#18301)
diff --git a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
index eca5975..1923f78 100644
--- a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
+++ b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
@@ -12,11 +12,11 @@
columns = get_columns()
if not filters.get("account"): return columns, []
-
+
account_currency = frappe.db.get_value("Account", filters.account, "account_currency")
data = get_entries(filters)
-
+
from erpnext.accounts.utils import get_balance_on
balance_as_per_system = get_balance_on(filters["account"], filters["report_date"])
@@ -24,7 +24,7 @@
for d in data:
total_debit += flt(d.debit)
total_credit += flt(d.credit)
-
+
amounts_not_reflected_in_system = get_amounts_not_reflected_in_system(filters)
bank_bal = flt(balance_as_per_system) - flt(total_debit) + flt(total_credit) \
@@ -39,7 +39,7 @@
"credit": total_credit,
"account_currency": account_currency
},
- get_balance_row(_("Cheques and Deposits incorrectly cleared"), amounts_not_reflected_in_system,
+ get_balance_row(_("Cheques and Deposits incorrectly cleared"), amounts_not_reflected_in_system,
account_currency),
{},
get_balance_row(_("Calculated Bank Statement balance"), bank_bal, account_currency)
@@ -56,8 +56,15 @@
"width": 90
},
{
+ "fieldname": "payment_document",
+ "label": _("Payment Document Type"),
+ "fieldtype": "Link",
+ "options": "DocType",
+ "width": 220
+ },
+ {
"fieldname": "payment_entry",
- "label": _("Payment Entry"),
+ "label": _("Payment Document"),
"fieldtype": "Dynamic Link",
"options": "payment_document",
"width": 220
@@ -100,7 +107,7 @@
"label": _("Clearance Date"),
"fieldtype": "Date",
"width": 110
- },
+ },
{
"fieldname": "account_currency",
"label": _("Currency"),
@@ -112,9 +119,9 @@
def get_entries(filters):
journal_entries = frappe.db.sql("""
- select "Journal Entry" as payment_document, jv.posting_date,
- jv.name as payment_entry, jvd.debit_in_account_currency as debit,
- jvd.credit_in_account_currency as credit, jvd.against_account,
+ select "Journal Entry" as payment_document, jv.posting_date,
+ jv.name as payment_entry, jvd.debit_in_account_currency as debit,
+ jvd.credit_in_account_currency as credit, jvd.against_account,
jv.cheque_no as reference_no, jv.cheque_date as ref_date, jv.clearance_date, jvd.account_currency
from
`tabJournal Entry Account` jvd, `tabJournal Entry` jv
@@ -122,13 +129,13 @@
and jvd.account = %(account)s and jv.posting_date <= %(report_date)s
and ifnull(jv.clearance_date, '4000-01-01') > %(report_date)s
and ifnull(jv.is_opening, 'No') = 'No'""", filters, as_dict=1)
-
+
payment_entries = frappe.db.sql("""
- select
- "Payment Entry" as payment_document, name as payment_entry,
- reference_no, reference_date as ref_date,
- if(paid_to=%(account)s, received_amount, 0) as debit,
- if(paid_from=%(account)s, paid_amount, 0) as credit,
+ select
+ "Payment Entry" as payment_document, name as payment_entry,
+ reference_no, reference_date as ref_date,
+ if(paid_to=%(account)s, received_amount, 0) as debit,
+ if(paid_from=%(account)s, paid_amount, 0) as credit,
posting_date, ifnull(party,if(paid_from=%(account)s,paid_to,paid_from)) as against_account, clearance_date,
if(paid_to=%(account)s, paid_to_account_currency, paid_from_account_currency) as account_currency
from `tabPayment Entry`
@@ -156,25 +163,25 @@
return sorted(list(payment_entries)+list(journal_entries+list(pos_entries)),
key=lambda k: k['posting_date'] or getdate(nowdate()))
-
+
def get_amounts_not_reflected_in_system(filters):
je_amount = frappe.db.sql("""
select sum(jvd.debit_in_account_currency - jvd.credit_in_account_currency)
from `tabJournal Entry Account` jvd, `tabJournal Entry` jv
where jvd.parent = jv.name and jv.docstatus=1 and jvd.account=%(account)s
- and jv.posting_date > %(report_date)s and jv.clearance_date <= %(report_date)s
+ and jv.posting_date > %(report_date)s and jv.clearance_date <= %(report_date)s
and ifnull(jv.is_opening, 'No') = 'No' """, filters)
je_amount = flt(je_amount[0][0]) if je_amount else 0.0
-
+
pe_amount = frappe.db.sql("""
select sum(if(paid_from=%(account)s, paid_amount, received_amount))
from `tabPayment Entry`
- where (paid_from=%(account)s or paid_to=%(account)s) and docstatus=1
+ where (paid_from=%(account)s or paid_to=%(account)s) and docstatus=1
and posting_date > %(report_date)s and clearance_date <= %(report_date)s""", filters)
pe_amount = flt(pe_amount[0][0]) if pe_amount else 0.0
-
+
return je_amount + pe_amount
def get_balance_row(label, amount, account_currency):