Merge pull request #40695 from ruthra-kumar/ledger_health_monitor
feat: Periodically monitor ledger health
diff --git a/erpnext/accounts/doctype/ledger_health/__init__.py b/erpnext/accounts/doctype/ledger_health/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health/__init__.py
diff --git a/erpnext/accounts/doctype/ledger_health/ledger_health.js b/erpnext/accounts/doctype/ledger_health/ledger_health.js
new file mode 100644
index 0000000..e207dae
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health/ledger_health.js
@@ -0,0 +1,8 @@
+// Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+// frappe.ui.form.on("Ledger Health", {
+// refresh(frm) {
+
+// },
+// });
diff --git a/erpnext/accounts/doctype/ledger_health/ledger_health.json b/erpnext/accounts/doctype/ledger_health/ledger_health.json
new file mode 100644
index 0000000..fb2da3d
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health/ledger_health.json
@@ -0,0 +1,70 @@
+{
+ "actions": [],
+ "autoname": "autoincrement",
+ "creation": "2024-03-26 17:01:47.443986",
+ "doctype": "DocType",
+ "engine": "InnoDB",
+ "field_order": [
+ "voucher_type",
+ "voucher_no",
+ "checked_on",
+ "debit_credit_mismatch",
+ "general_and_payment_ledger_mismatch"
+ ],
+ "fields": [
+ {
+ "fieldname": "voucher_type",
+ "fieldtype": "Data",
+ "label": "Voucher Type"
+ },
+ {
+ "fieldname": "voucher_no",
+ "fieldtype": "Data",
+ "label": "Voucher No"
+ },
+ {
+ "default": "0",
+ "fieldname": "debit_credit_mismatch",
+ "fieldtype": "Check",
+ "label": "Debit-Credit mismatch"
+ },
+ {
+ "fieldname": "checked_on",
+ "fieldtype": "Datetime",
+ "label": "Checked On"
+ },
+ {
+ "default": "0",
+ "fieldname": "general_and_payment_ledger_mismatch",
+ "fieldtype": "Check",
+ "label": "General and Payment Ledger mismatch"
+ }
+ ],
+ "in_create": 1,
+ "index_web_pages_for_search": 1,
+ "links": [],
+ "modified": "2024-04-09 11:16:07.044484",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Ledger Health",
+ "naming_rule": "Autoincrement",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "System Manager",
+ "share": 1,
+ "write": 1
+ }
+ ],
+ "read_only": 1,
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": []
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/ledger_health/ledger_health.py b/erpnext/accounts/doctype/ledger_health/ledger_health.py
new file mode 100644
index 0000000..590ff80
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health/ledger_health.py
@@ -0,0 +1,25 @@
+# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# import frappe
+from frappe.model.document import Document
+
+
+class LedgerHealth(Document):
+ # begin: auto-generated types
+ # This code is auto-generated. Do not modify anything in this block.
+
+ from typing import TYPE_CHECKING
+
+ if TYPE_CHECKING:
+ from frappe.types import DF
+
+ checked_on: DF.Datetime | None
+ debit_credit_mismatch: DF.Check
+ general_and_payment_ledger_mismatch: DF.Check
+ name: DF.Int | None
+ voucher_no: DF.Data | None
+ voucher_type: DF.Data | None
+ # end: auto-generated types
+
+ pass
diff --git a/erpnext/accounts/doctype/ledger_health/test_ledger_health.py b/erpnext/accounts/doctype/ledger_health/test_ledger_health.py
new file mode 100644
index 0000000..d35b39d
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health/test_ledger_health.py
@@ -0,0 +1,109 @@
+# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+import frappe
+from frappe import qb
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import nowdate
+
+from erpnext.accounts.test.accounts_mixin import AccountsTestMixin
+from erpnext.accounts.utils import run_ledger_health_checks
+
+
+class TestLedgerHealth(AccountsTestMixin, FrappeTestCase):
+ def setUp(self):
+ self.create_company()
+ self.create_customer()
+ self.configure_monitoring_tool()
+ self.clear_old_entries()
+
+ def tearDown(self):
+ frappe.db.rollback()
+
+ def configure_monitoring_tool(self):
+ monitor_settings = frappe.get_doc("Ledger Health Monitor")
+ monitor_settings.enable_health_monitor = True
+ monitor_settings.enable_for_last_x_days = 60
+ monitor_settings.debit_credit_mismatch = True
+ monitor_settings.general_and_payment_ledger_mismatch = True
+ exists = [x for x in monitor_settings.companies if x.company == self.company]
+ if not exists:
+ monitor_settings.append("companies", {"company": self.company})
+ monitor_settings.save()
+
+ def clear_old_entries(self):
+ super().clear_old_entries()
+ lh = qb.DocType("Ledger Health")
+ qb.from_(lh).delete().run()
+
+ def create_journal(self):
+ je = frappe.new_doc("Journal Entry")
+ je.company = self.company
+ je.voucher_type = "Journal Entry"
+ je.posting_date = nowdate()
+ je.append(
+ "accounts",
+ {
+ "account": self.debit_to,
+ "party_type": "Customer",
+ "party": self.customer,
+ "debit_in_account_currency": 10000,
+ },
+ )
+ je.append("accounts", {"account": self.income_account, "credit_in_account_currency": 10000})
+ je.save().submit()
+ self.je = je
+
+ def test_debit_credit_mismatch(self):
+ self.create_journal()
+
+ # manually cause debit-credit mismatch
+ gle = frappe.db.get_all(
+ "GL Entry", filters={"voucher_no": self.je.name, "account": self.income_account}
+ )[0]
+ frappe.db.set_value("GL Entry", gle.name, "credit", 8000)
+
+ run_ledger_health_checks()
+ expected = {
+ "voucher_type": self.je.doctype,
+ "voucher_no": self.je.name,
+ "debit_credit_mismatch": True,
+ "general_and_payment_ledger_mismatch": False,
+ }
+ actual = frappe.db.get_all(
+ "Ledger Health",
+ fields=[
+ "voucher_type",
+ "voucher_no",
+ "debit_credit_mismatch",
+ "general_and_payment_ledger_mismatch",
+ ],
+ )
+ self.assertEqual(len(actual), 1)
+ self.assertEqual(expected, actual[0])
+
+ def test_gl_and_pl_mismatch(self):
+ self.create_journal()
+
+ # manually cause GL and PL discrepancy
+ ple = frappe.db.get_all("Payment Ledger Entry", filters={"voucher_no": self.je.name})[0]
+ frappe.db.set_value("Payment Ledger Entry", ple.name, "amount", 11000)
+
+ run_ledger_health_checks()
+ expected = {
+ "voucher_type": self.je.doctype,
+ "voucher_no": self.je.name,
+ "debit_credit_mismatch": False,
+ "general_and_payment_ledger_mismatch": True,
+ }
+ actual = frappe.db.get_all(
+ "Ledger Health",
+ fields=[
+ "voucher_type",
+ "voucher_no",
+ "debit_credit_mismatch",
+ "general_and_payment_ledger_mismatch",
+ ],
+ )
+ self.assertEqual(len(actual), 1)
+ self.assertEqual(expected, actual[0])
diff --git a/erpnext/accounts/doctype/ledger_health_monitor/__init__.py b/erpnext/accounts/doctype/ledger_health_monitor/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor/__init__.py
diff --git a/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.js b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.js
new file mode 100644
index 0000000..cf11276
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.js
@@ -0,0 +1,8 @@
+// Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+// frappe.ui.form.on("Ledger Health Monitor", {
+// refresh(frm) {
+
+// },
+// });
diff --git a/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.json b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.json
new file mode 100644
index 0000000..6e68833
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.json
@@ -0,0 +1,104 @@
+{
+ "actions": [],
+ "allow_rename": 1,
+ "creation": "2024-03-27 09:38:07.427997",
+ "doctype": "DocType",
+ "engine": "InnoDB",
+ "field_order": [
+ "enable_health_monitor",
+ "monitor_section",
+ "monitor_for_last_x_days",
+ "debit_credit_mismatch",
+ "general_and_payment_ledger_mismatch",
+ "section_break_xdsp",
+ "companies"
+ ],
+ "fields": [
+ {
+ "default": "0",
+ "fieldname": "enable_health_monitor",
+ "fieldtype": "Check",
+ "label": "Enable Health Monitor"
+ },
+ {
+ "fieldname": "monitor_section",
+ "fieldtype": "Section Break",
+ "label": "Configuration"
+ },
+ {
+ "default": "0",
+ "fieldname": "debit_credit_mismatch",
+ "fieldtype": "Check",
+ "label": "Debit-Credit Mismatch"
+ },
+ {
+ "default": "0",
+ "fieldname": "general_and_payment_ledger_mismatch",
+ "fieldtype": "Check",
+ "label": "Discrepancy between General and Payment Ledger"
+ },
+ {
+ "default": "60",
+ "fieldname": "monitor_for_last_x_days",
+ "fieldtype": "Int",
+ "in_list_view": 1,
+ "label": "Monitor for Last 'X' days",
+ "reqd": 1
+ },
+ {
+ "fieldname": "section_break_xdsp",
+ "fieldtype": "Section Break",
+ "label": "Companies"
+ },
+ {
+ "fieldname": "companies",
+ "fieldtype": "Table",
+ "options": "Ledger Health Monitor Company"
+ }
+ ],
+ "hide_toolbar": 1,
+ "index_web_pages_for_search": 1,
+ "issingle": 1,
+ "links": [],
+ "modified": "2024-03-27 10:14:16.511681",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Ledger Health Monitor",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "print": 1,
+ "read": 1,
+ "role": "System Manager",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "print": 1,
+ "read": 1,
+ "role": "Accounts Manager",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "print": 1,
+ "read": 1,
+ "role": "Accounts User",
+ "share": 1,
+ "write": 1
+ }
+ ],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": [],
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.py b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.py
new file mode 100644
index 0000000..9f7c569
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.py
@@ -0,0 +1,28 @@
+# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# import frappe
+from frappe.model.document import Document
+
+
+class LedgerHealthMonitor(Document):
+ # begin: auto-generated types
+ # This code is auto-generated. Do not modify anything in this block.
+
+ from typing import TYPE_CHECKING
+
+ if TYPE_CHECKING:
+ from frappe.types import DF
+
+ from erpnext.accounts.doctype.ledger_health_monitor_company.ledger_health_monitor_company import (
+ LedgerHealthMonitorCompany,
+ )
+
+ companies: DF.Table[LedgerHealthMonitorCompany]
+ debit_credit_mismatch: DF.Check
+ enable_health_monitor: DF.Check
+ general_and_payment_ledger_mismatch: DF.Check
+ monitor_for_last_x_days: DF.Int
+ # end: auto-generated types
+
+ pass
diff --git a/erpnext/accounts/doctype/ledger_health_monitor/test_ledger_health_monitor.py b/erpnext/accounts/doctype/ledger_health_monitor/test_ledger_health_monitor.py
new file mode 100644
index 0000000..e0ba443
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor/test_ledger_health_monitor.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+# import frappe
+from frappe.tests.utils import FrappeTestCase
+
+
+class TestLedgerHealthMonitor(FrappeTestCase):
+ pass
diff --git a/erpnext/accounts/doctype/ledger_health_monitor_company/__init__.py b/erpnext/accounts/doctype/ledger_health_monitor_company/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor_company/__init__.py
diff --git a/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.json b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.json
new file mode 100644
index 0000000..87fa3e3
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.json
@@ -0,0 +1,32 @@
+{
+ "actions": [],
+ "allow_rename": 1,
+ "creation": "2024-03-27 10:04:45.727054",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "company"
+ ],
+ "fields": [
+ {
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Company",
+ "options": "Company"
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "istable": 1,
+ "links": [],
+ "modified": "2024-03-27 10:06:22.806155",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Ledger Health Monitor Company",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": []
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.py b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.py
new file mode 100644
index 0000000..5890410
--- /dev/null
+++ b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.py
@@ -0,0 +1,23 @@
+# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# import frappe
+from frappe.model.document import Document
+
+
+class LedgerHealthMonitorCompany(Document):
+ # begin: auto-generated types
+ # This code is auto-generated. Do not modify anything in this block.
+
+ from typing import TYPE_CHECKING
+
+ if TYPE_CHECKING:
+ from frappe.types import DF
+
+ company: DF.Link | None
+ parent: DF.Data
+ parentfield: DF.Data
+ parenttype: DF.Data
+ # end: auto-generated types
+
+ pass
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 869fd42..2edf519 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -13,11 +13,13 @@
from frappe.query_builder.functions import Round, Sum
from frappe.query_builder.utils import DocType
from frappe.utils import (
+ add_days,
cint,
create_batch,
cstr,
flt,
formatdate,
+ get_datetime,
get_number_format_info,
getdate,
now,
@@ -2071,3 +2073,44 @@
def get_party_types_from_account_type(account_type):
return frappe.db.get_all("Party Type", {"account_type": account_type}, pluck="name")
+
+
+def run_ledger_health_checks():
+ health_monitor_settings = frappe.get_doc("Ledger Health Monitor")
+ if health_monitor_settings.enable_health_monitor:
+ period_end = getdate()
+ period_start = add_days(period_end, -abs(health_monitor_settings.monitor_for_last_x_days))
+
+ run_date = get_datetime()
+
+ # Debit-Credit mismatch report
+ if health_monitor_settings.debit_credit_mismatch:
+ for x in health_monitor_settings.companies:
+ filters = {"company": x.company, "from_date": period_start, "to_date": period_end}
+ voucher_wise = frappe.get_doc("Report", "Voucher-wise Balance")
+ res = voucher_wise.execute_script_report(filters=filters)
+ for x in res[1]:
+ doc = frappe.new_doc("Ledger Health")
+ doc.voucher_type = x.voucher_type
+ doc.voucher_no = x.voucher_no
+ doc.debit_credit_mismatch = True
+ doc.checked_on = run_date
+ doc.save()
+
+ # General Ledger and Payment Ledger discrepancy
+ if health_monitor_settings.general_and_payment_ledger_mismatch:
+ for x in health_monitor_settings.companies:
+ filters = {
+ "company": x.company,
+ "period_start_date": period_start,
+ "period_end_date": period_end,
+ }
+ gl_pl_comparison = frappe.get_doc("Report", "General and Payment Ledger Comparison")
+ res = gl_pl_comparison.execute_script_report(filters=filters)
+ for x in res[1]:
+ doc = frappe.new_doc("Ledger Health")
+ doc.voucher_type = x.voucher_type
+ doc.voucher_no = x.voucher_no
+ doc.general_and_payment_ledger_mismatch = True
+ doc.checked_on = run_date
+ doc.save()
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index 9c1521a..a31f011 100644
--- a/erpnext/hooks.py
+++ b/erpnext/hooks.py
@@ -434,6 +434,7 @@
"erpnext.buying.doctype.supplier_quotation.supplier_quotation.set_expired_status",
"erpnext.accounts.doctype.process_statement_of_accounts.process_statement_of_accounts.send_auto_email",
"erpnext.accounts.utils.auto_create_exchange_rate_revaluation_daily",
+ "erpnext.accounts.utils.run_ledger_health_checks",
],
"weekly": [
"erpnext.accounts.utils.auto_create_exchange_rate_revaluation_weekly",