feat: Cascade closing balances on PCV submit
diff --git a/erpnext/accounts/doctype/closing_balance/closing_balance.json b/erpnext/accounts/doctype/closing_balance/closing_balance.json
index 5a47f72..f99e792 100644
--- a/erpnext/accounts/doctype/closing_balance/closing_balance.json
+++ b/erpnext/accounts/doctype/closing_balance/closing_balance.json
@@ -8,28 +8,18 @@
"field_order": [
"closing_date",
"account",
- "party_type",
- "party",
"cost_center",
"debit",
"credit",
"account_currency",
"debit_in_account_currency",
"credit_in_account_currency",
- "against",
- "against_voucher_type",
- "against_voucher",
- "voucher_type",
- "voucher_no",
- "voucher_detail_no",
"project",
"is_opening",
- "is_advance",
"fiscal_year",
"company",
"finance_book",
- "to_rename",
- "due_date"
+ "period_closing_voucher"
],
"fields": [
{
@@ -55,21 +45,6 @@
"search_index": 1
},
{
- "fieldname": "party_type",
- "fieldtype": "Link",
- "label": "Party Type",
- "options": "DocType",
- "search_index": 1
- },
- {
- "fieldname": "party",
- "fieldtype": "Dynamic Link",
- "in_standard_filter": 1,
- "label": "Party",
- "options": "party_type",
- "search_index": 1
- },
- {
"fieldname": "cost_center",
"fieldtype": "Link",
"in_filter": 1,
@@ -114,60 +89,6 @@
"options": "account_currency"
},
{
- "fieldname": "against",
- "fieldtype": "Text",
- "in_filter": 1,
- "label": "Against",
- "oldfieldname": "against",
- "oldfieldtype": "Text"
- },
- {
- "fieldname": "against_voucher_type",
- "fieldtype": "Link",
- "label": "Against Voucher Type",
- "oldfieldname": "against_voucher_type",
- "oldfieldtype": "Data",
- "options": "DocType",
- "search_index": 1
- },
- {
- "fieldname": "against_voucher",
- "fieldtype": "Dynamic Link",
- "in_filter": 1,
- "label": "Against Voucher",
- "oldfieldname": "against_voucher",
- "oldfieldtype": "Data",
- "options": "against_voucher_type",
- "search_index": 1
- },
- {
- "fieldname": "voucher_type",
- "fieldtype": "Link",
- "in_filter": 1,
- "label": "Voucher Type",
- "oldfieldname": "voucher_type",
- "oldfieldtype": "Select",
- "options": "DocType",
- "search_index": 1
- },
- {
- "fieldname": "voucher_no",
- "fieldtype": "Dynamic Link",
- "in_filter": 1,
- "in_standard_filter": 1,
- "label": "Voucher No",
- "oldfieldname": "voucher_no",
- "oldfieldtype": "Data",
- "options": "voucher_type",
- "search_index": 1
- },
- {
- "fieldname": "voucher_detail_no",
- "fieldtype": "Data",
- "label": "Voucher Detail No",
- "read_only": 1
- },
- {
"fieldname": "project",
"fieldtype": "Link",
"label": "Project",
@@ -183,14 +104,6 @@
"options": "No\nYes"
},
{
- "fieldname": "is_advance",
- "fieldtype": "Select",
- "label": "Is Advance",
- "oldfieldname": "is_advance",
- "oldfieldtype": "Select",
- "options": "No\nYes"
- },
- {
"fieldname": "fiscal_year",
"fieldtype": "Link",
"in_filter": 1,
@@ -216,23 +129,16 @@
"options": "Finance Book"
},
{
- "default": "1",
- "fieldname": "to_rename",
- "fieldtype": "Check",
- "hidden": 1,
- "label": "To Rename",
- "search_index": 1
- },
- {
- "fieldname": "due_date",
- "fieldtype": "Date",
- "label": "Due Date"
+ "fieldname": "period_closing_voucher",
+ "fieldtype": "Link",
+ "label": "Period Closing Voucher",
+ "options": "Period Closing Voucher"
}
],
"icon": "fa fa-list",
"in_create": 1,
"links": [],
- "modified": "2023-02-21 15:20:59.586811",
+ "modified": "2023-02-22 19:28:14.490403",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Closing Balance",
@@ -261,7 +167,6 @@
"role": "Auditor"
}
],
- "search_fields": "voucher_no,account,against_voucher",
"sort_field": "modified",
"sort_order": "DESC",
"states": []
diff --git a/erpnext/accounts/doctype/closing_balance/closing_balance.py b/erpnext/accounts/doctype/closing_balance/closing_balance.py
index 899749f..572553d 100644
--- a/erpnext/accounts/doctype/closing_balance/closing_balance.py
+++ b/erpnext/accounts/doctype/closing_balance/closing_balance.py
@@ -1,9 +1,42 @@
# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt
-# import frappe
+import frappe
from frappe.model.document import Document
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
+ get_accounting_dimensions,
+)
+
class ClosingBalance(Document):
- pass
+ def aggregate_with_last_closing_balance(self, accounting_dimensions):
+ closing_balance = frappe.qb.DocType("Closing Balance")
+
+ query = (
+ frappe.qb.from_(closing_balance)
+ .select(closing_balance.debit, closing_balance.credit)
+ .where(
+ closing_balance.closing_date < self.closing_date,
+ )
+ )
+
+ for dimension in accounting_dimensions:
+ query = query.where(closing_balance[dimension] == self.get(dimension))
+
+ query.orderby(closing_balance.closing_date, order=frappe.qb.desc).limit(1)
+
+ last_closing_balance = query.run(as_dict=1)
+
+ if last_closing_balance:
+ self.debit += last_closing_balance[0].debit
+ self.credit += last_closing_balance[0].credit
+
+
+def make_closing_entries(closing_entries):
+ accounting_dimensions = get_accounting_dimensions()
+ for entry in closing_entries:
+ cle = frappe.new_doc("Closing Balance")
+ cle.update(entry)
+ cle.aggregate_with_last_closing_balance(accounting_dimensions)
+ cle.submit()
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 ca98bee..af90d97 100644
--- a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
+++ b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
@@ -21,6 +21,7 @@
def on_submit(self):
self.db_set("gle_processing_status", "In Progress")
self.make_gl_entries()
+ self.make_closing_entries()
def on_cancel(self):
self.db_set("gle_processing_status", "In Progress")
@@ -90,16 +91,37 @@
else:
process_gl_entries(gl_entries)
+ def make_closing_entries(self):
+ closing_entries = self.get_grouped_gl_entries()
+ if closing_entries:
+ if len(closing_entries) > 5000:
+ frappe.enqueue(process_closing_entries, gl_entries=closing_entries, queue="long")
+ frappe.msgprint(
+ _("The Opening Entries will be processed in the background, it can take a few minutes."),
+ alert=True,
+ )
+ else:
+ process_closing_entries(closing_entries)
+
+ def get_grouped_gl_entries(self):
+ closing_entries = []
+ for acc in self.get_balances_based_on_dimensions(
+ group_by_account=True, report_type=["Profit and Loss", "Balance Sheet"], for_aggregation=True
+ ):
+ closing_entries.append(self.get_closing_entries(acc))
+
+ return closing_entries
+
def get_gl_entries(self):
gl_entries = []
# pl account
- for acc in self.get_pl_balances_based_on_dimensions(group_by_account=True):
+ for acc in self.get_balances_based_on_dimensions(group_by_account=True):
if flt(acc.bal_in_company_currency):
gl_entries.append(self.get_gle_for_pl_account(acc))
# closing liability account
- for acc in self.get_pl_balances_based_on_dimensions(group_by_account=False):
+ for acc in self.get_balances_based_on_dimensions(group_by_account=False):
if flt(acc.bal_in_company_currency):
gl_entries.append(self.get_gle_for_closing_account(acc))
@@ -147,6 +169,25 @@
self.update_default_dimensions(gl_entry, acc)
return gl_entry
+ def get_closing_entries(self, acc):
+ closing_entry = self.get_gl_dict(
+ {
+ "closing_date": self.posting_date,
+ "period_closing_voucher": self.name,
+ "account": acc.account,
+ "cost_center": acc.cost_center,
+ "finance_book": acc.finance_book,
+ "account_currency": acc.account_currency,
+ "debit_in_account_currency": flt(acc.debit_in_account_currency),
+ "debit": flt(acc.debit),
+ "credit_in_account_currency": flt(acc.credit_in_account_currency),
+ "credit": abs(flt(acc.bal_in_company_currency)) if flt(acc.bal_in_company_currency) < 0 else 0,
+ },
+ item=acc,
+ )
+
+ return closing_entry
+
def update_default_dimensions(self, gl_entry, acc):
if not self.accounting_dimensions:
self.accounting_dimensions = get_accounting_dimensions()
@@ -154,9 +195,24 @@
for dimension in self.accounting_dimensions:
gl_entry.update({dimension: acc.get(dimension)})
- def get_pl_balances_based_on_dimensions(self, group_by_account=False):
+ def get_balances_based_on_dimensions(
+ self, group_by_account=False, report_type=["Profit and Loss"], for_aggregation=False
+ ):
"""Get balance for dimension-wise pl accounts"""
+ if for_aggregation:
+ balance_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",
+ ]
+ else:
+ balance_fields = [
+ "sum(t1.debit_in_account_currency) as debit_in_account_currency",
+ "sum(t1.credit_in_account_currency) as credit_in_account_currency",
+ "sum(t1.debit) as debit",
+ "sum(t1.credit) as credit",
+ ]
+
dimension_fields = ["t1.cost_center", "t1.finance_book"]
self.accounting_dimensions = get_accounting_dimensions()
@@ -169,27 +225,39 @@
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
+ {balance_fields}
from `tabGL Entry` t1, `tabAccount` t2
where
t1.is_cancelled = 0
and t1.account = t2.name
- and t2.report_type = 'Profit and Loss'
+ and t2.report_type in ("{report_type}")
and t2.docstatus < 2
and t2.company = %s
and t1.posting_date between %s and %s
group by {dimension_fields}
""".format(
- dimension_fields=", ".join(dimension_fields)
+ dimension_fields=", ".join(dimension_fields),
+ balance_fields=", ".join(balance_fields),
+ report_type='", "'.join(report_type),
),
(self.company, self.get("year_start_date"), self.posting_date),
as_dict=1,
)
+def process_closing_entries(closing_entries):
+ from erpnext.accounts.doctype.closing_balance.closing_balance import make_closing_entries
+
+ try:
+ make_closing_entries(closing_entries)
+ except Exception as e:
+ frappe.db.rollback()
+ frappe.log_error(e)
+
+
def process_gl_entries(gl_entries):
from erpnext.accounts.general_ledger import make_gl_entries
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 211f074..d0873e8 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -327,3 +327,5 @@
erpnext.patches.v14_0.change_autoname_for_tax_withheld_vouchers
erpnext.patches.v14_0.set_pick_list_status
erpnext.patches.v15_0.update_asset_value_for_manual_depr_entries
+erpnext.patches.v14_0.create_accounting_dimensions_for_closing_balance
+#erpnext.patches.v14_0.update_closing_balances