Repository: frappe/erpnext
Pull request: fix: Period Closing Voucher is considering GL entries with is_cancelled=1
**Period Closing Voucher is considering GL entry with is_cancelled=1 as well**

Frappe version - v13.23.0 (version-13)
ERPNext version - v13.23.0 [(version-13)

Module
accounts

Version
Frappe version - v13.27.0 (version-13)
ERPNext version -  v13.27.1 (version-13)

Installation method
FrappeCloud

Currently there is an condition to check cancelled documents. but It's added for Account, Changed to GL Entry.
#30849

Problem statement:
Period Closing Voucher is considering GL entry with is_cancelled=1 as well
### Information about bug

The period closing voucher is considering the canceled GL entries as well. 

If we cancel any transaction and do not delete it and try to post the period closing voucher then the balance of ledger is not mataching with a period closing voucher. 

Steps to reproduce -
1. Create one Journal entry with a sales account 
2. Cancel this journal entry (do not delete it)
3. Post the period closing voucher ( it is considering the sum of debit GL account - the sum of credit of GL account) 
4. Point no 3 should not consider if the GL entry is having is_cancelled=1.

### Module

accounts

### Version

Currently, I am using the below git repo -
Frappe version-13
ERPNext version-13

### Installation method

manual install

### Relevant log output / Stack trace / Full Error Message.

```shell
There is no error but the data which is posted in the period closing voucher is not matching the general ledger of the particular account.

In General Ledger Report is showing the correct closing balances but while posting the period closing voucher it is actually calculating the amount with a SQL query in period_closing_voucher.py. 

Problematic method - 

	def get_pl_balances(self):
		"""Get balance for dimension-wise pl accounts"""

		dimension_fields = ["t1.cost_center", "t1.finance_book"]

		self.accounting_dimensions = get_accounting_dimensions()
		for dimension in self.accounting_dimensions:
			dimension_fields.append("t1.{0}".format(dimension))

		return frappe.db.sql(
			"""
			select
				t1.account, t2.account_currency, {dimension_fields},
				sum(t1.debit_in_account_currency) - sum(t1.credit_in_account_currency) as bal_in_account_currency,
				sum(t1.debit) - sum(t1.credit) as bal_in_company_currency
			from `tabGL Entry` t1, `tabAccount` t2
			where t1.account = t2.name and t2.report_type = 'Profit and Loss'
			and t2.docstatus < 2 and t2.company = %s
			and t1.posting_date between %s and %s
			group by t1.account, {dimension_fields}
		""".format(
				dimension_fields=", ".join(dimension_fields)
			),
			(self.company, self.get("year_start_date"), self.posting_date),
			as_dict=1,
		)
```

Hints from issue discussion:

Commit message: fix: Period Closing Voucher - Period Closing Voucher is considering GL entry with is_cancelled=1 as well

Patch to review:
diff --git a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
index f66cf1c9f1a7..2b1d3e6d8117 100644
--- a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
+++ b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
@@ -167,7 +167,7 @@ def get_pl_balances(self):
 				sum(t1.debit) - sum(t1.credit) as bal_in_company_currency
 			from `tabGL Entry` t1, `tabAccount` t2
 			where t1.account = t2.name and t2.report_type = 'Profit and Loss'
-			and t2.docstatus < 2 and t2.company = %s
+			and t1.docstatus < 2 and t2.company = %s
 			and t1.posting_date between %s and %s
 			group by t1.account, {dimension_fields}
 		""".format(


Code review comments:
diff_hunk: @@ -167,7 +167,7 @@ def get_pl_balances(self):
 				sum(t1.debit) - sum(t1.credit) as bal_in_company_currency
 			from `tabGL Entry` t1, `tabAccount` t2
 			where t1.account = t2.name and t2.report_type = 'Profit and Loss'
-			and t2.docstatus < 2 and t2.company = %s
+			and t1.docstatus < 2 and t2.company = %s
path: erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
text: @user1:
```suggestion
			and t2.docstatus < 2 and t2.company = %s
```

diff_hunk: @@ -167,7 +167,7 @@ def get_pl_balances(self):
 				sum(t1.debit) - sum(t1.credit) as bal_in_company_currency
 			from `tabGL Entry` t1, `tabAccount` t2
 			where t1.account = t2.name and t2.report_type = 'Profit and Loss'
path: erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
text: @user1:
```suggestion
			where t1.is_cancelled = 0 and t1.account = t2.name and t2.report_type = 'Profit and Loss'
```