Bank Reconciliation Statement Report not filtering for the selected company

[Omar-Abdullah-Shaikh-Al-Zoor]

Information about bug

When Selecting the company filter, all the journal entries and payment entries are displayed, which belong to the selected bank account, regardless of the company filter set. The company is not set in the filters:

This is applied to the
get_entries_for_bank_reconciliation_statement
and get_amounts_not_reflected_in_system_for_bank_reconciliation_statement functions in the report

It just needs and company = %(company)s in the where statements

def get_journal_entries(filters):
return 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,
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
where jvd.parent = jv.name and jv.docstatus=1
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,
)

def get_payment_entries(filters):
return 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_after_tax, 0) as debit,
if(paid_from=%(account)s, paid_amount_after_tax, 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
where
(paid_from=%(account)s or paid_to=%(account)s) and docstatus=1
and posting_date <= %(report_date)s
and ifnull(clearance_date, '4000-01-01') > %(report_date)s
""",
filters,
as_dict=1,
)

def get_pos_entries(filters):
return frappe.db.sql(
"""
select
"Sales Invoice Payment" as payment_document, sip.name as payment_entry, sip.amount as debit,
si.posting_date, si.debit_to as against_account, sip.clearance_date,
account.account_currency, 0 as credit
from tabSales Invoice Payment sip, tabSales Invoice si, tabAccount account
where
sip.account=%(account)s and si.docstatus=1 and sip.parent = si.name
and account.name = sip.account and si.posting_date <= %(report_date)s and
ifnull(sip.clearance_date, '4000-01-01') > %(report_date)s
order by
si.posting_date ASC, si.name DESC
""",
filters,
as_dict=1,
)

**def get_amounts_not_reflected_in_system_for_bank_reconciliation_statement(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 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
	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

Module

accounts

Version

Frappe version: 15.49.1
ERPNext Version: 15.45.0

Installation method

manual install

Relevant log output / Stack trace / Full Error Message.

Payment Entry	ACC-PAY-2024-00008	0	9876	Supplier 1	1234	2024-12-10		AED
	Bank Statement balance as per General Ledger	0	14876					AED
								
	Outstanding Cheques and Deposits to clear	0	9876					AED
	Cheques and Deposits incorrectly cleared	0	0					AED
								
	Calculated Bank Statement balance	0	5000					AED