Merge branch 'develop' of https://github.com/frappe/erpnext into balancing-accounting-dimensions
diff --git a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.js b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.js
index 2fa1d53..2f53f7b 100644
--- a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.js
+++ b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.js
@@ -15,6 +15,17 @@
};
});
+ frm.set_query("offsetting_account", "dimension_defaults", function(doc, cdt, cdn) {
+ let d = locals[cdt][cdn];
+ return {
+ filters: {
+ company: d.company,
+ root_type: ["in", ["Asset", "Liability"]],
+ is_group: 0
+ }
+ }
+ });
+
if (!frm.is_new()) {
frm.add_custom_button(__('Show {0}', [frm.doc.document_type]), function () {
frappe.set_route("List", frm.doc.document_type);
diff --git a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
index 15c84d4..cfe5e6e 100644
--- a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
+++ b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
@@ -39,6 +39,8 @@
if not self.is_new():
self.validate_document_type_change()
+ self.validate_dimension_defaults()
+
def validate_document_type_change(self):
doctype_before_save = frappe.db.get_value("Accounting Dimension", self.name, "document_type")
if doctype_before_save != self.document_type:
@@ -46,6 +48,14 @@
message += _("Please create a new Accounting Dimension if required.")
frappe.throw(message)
+ def validate_dimension_defaults(self):
+ companies = []
+ for default in self.get("dimension_defaults"):
+ if default.company not in companies:
+ companies.append(default.company)
+ else:
+ frappe.throw(_("Company {0} is added more than once").format(frappe.bold(default.company)))
+
def after_insert(self):
if frappe.flags.in_test:
make_dimension_in_accounting_doctypes(doc=self)
diff --git a/erpnext/accounts/doctype/accounting_dimension_detail/accounting_dimension_detail.json b/erpnext/accounts/doctype/accounting_dimension_detail/accounting_dimension_detail.json
index e9e1f43..7b6120a 100644
--- a/erpnext/accounts/doctype/accounting_dimension_detail/accounting_dimension_detail.json
+++ b/erpnext/accounts/doctype/accounting_dimension_detail/accounting_dimension_detail.json
@@ -8,7 +8,10 @@
"reference_document",
"default_dimension",
"mandatory_for_bs",
- "mandatory_for_pl"
+ "mandatory_for_pl",
+ "column_break_lqns",
+ "automatically_post_balancing_accounting_entry",
+ "offsetting_account"
],
"fields": [
{
@@ -50,6 +53,23 @@
"fieldtype": "Check",
"in_list_view": 1,
"label": "Mandatory For Profit and Loss Account"
+ },
+ {
+ "default": "0",
+ "fieldname": "automatically_post_balancing_accounting_entry",
+ "fieldtype": "Check",
+ "label": "Automatically post balancing accounting entry"
+ },
+ {
+ "fieldname": "offsetting_account",
+ "fieldtype": "Link",
+ "label": "Offsetting Account",
+ "mandatory_depends_on": "eval: doc.automatically_post_balancing_accounting_entry",
+ "options": "Account"
+ },
+ {
+ "fieldname": "column_break_lqns",
+ "fieldtype": "Column Break"
}
],
"istable": 1,
diff --git a/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
index 8c96480..486e01e 100644
--- a/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
@@ -1736,6 +1736,61 @@
rate = flt(sle.stock_value_difference) / flt(sle.actual_qty)
self.assertAlmostEqual(returned_inv.items[0].rate, rate)
+ def test_offsetting_entries_for_accounting_dimensions(self):
+ from erpnext.accounts.doctype.account.test_account import create_account
+ from erpnext.accounts.report.trial_balance.test_trial_balance import (
+ clear_dimension_defaults,
+ create_accounting_dimension,
+ disable_dimension,
+ )
+
+ create_account(
+ account_name="Offsetting",
+ company="_Test Company",
+ parent_account="Temporary Accounts - _TC",
+ )
+
+ create_accounting_dimension(company="_Test Company", offsetting_account="Offsetting - _TC")
+
+ branch1 = frappe.new_doc("Branch")
+ branch1.branch = "Location 1"
+ branch1.insert(ignore_if_duplicate=True)
+ branch2 = frappe.new_doc("Branch")
+ branch2.branch = "Location 2"
+ branch2.insert(ignore_if_duplicate=True)
+
+ pi = make_purchase_invoice(
+ company="_Test Company",
+ customer="_Test Supplier",
+ do_not_save=True,
+ do_not_submit=True,
+ rate=1000,
+ price_list_rate=1000,
+ qty=1,
+ )
+ pi.branch = branch1.branch
+ pi.items[0].branch = branch2.branch
+ pi.save()
+ pi.submit()
+
+ expected_gle = [
+ ["_Test Account Cost for Goods Sold - _TC", 1000, 0.0, nowdate(), branch2.branch],
+ ["Creditors - _TC", 0.0, 1000, nowdate(), branch1.branch],
+ ["Offsetting - _TC", 1000, 0.0, nowdate(), branch1.branch],
+ ["Offsetting - _TC", 0.0, 1000, nowdate(), branch2.branch],
+ ]
+
+ check_gl_entries(
+ self,
+ pi.name,
+ expected_gle,
+ nowdate(),
+ voucher_type="Purchase Invoice",
+ additional_columns=["branch"],
+ )
+ clear_dimension_defaults("Branch")
+ disable_dimension()
+
def set_advance_flag(company, flag, default_account):
frappe.db.set_value(
@@ -1748,9 +1803,16 @@
)
-def check_gl_entries(doc, voucher_no, expected_gle, posting_date, voucher_type="Purchase Invoice"):
+def check_gl_entries(
+ doc,
+ voucher_no,
+ expected_gle,
+ posting_date,
+ voucher_type="Purchase Invoice",
+ additional_columns=None,
+):
gl = frappe.qb.DocType("GL Entry")
- q = (
+ query = (
frappe.qb.from_(gl)
.select(gl.account, gl.debit, gl.credit, gl.posting_date)
.where(
@@ -1761,7 +1823,12 @@
)
.orderby(gl.posting_date, gl.account, gl.creation)
)
- gl_entries = q.run(as_dict=True)
+
+ if additional_columns:
+ for col in additional_columns:
+ query = query.select(gl[col])
+
+ gl_entries = query.run(as_dict=True)
for i, gle in enumerate(gl_entries):
doc.assertEqual(expected_gle[i][0], gle.account)
@@ -1769,6 +1836,12 @@
doc.assertEqual(expected_gle[i][2], gle.credit)
doc.assertEqual(getdate(expected_gle[i][3]), gle.posting_date)
+ if additional_columns:
+ j = 4
+ for col in additional_columns:
+ doc.assertEqual(expected_gle[i][j], gle[col])
+ j += 1
+
def create_tax_witholding_category(category_name, company, account):
from erpnext.accounts.utils import get_fiscal_year
diff --git a/erpnext/accounts/general_ledger.py b/erpnext/accounts/general_ledger.py
index b942a0c..3803836 100644
--- a/erpnext/accounts/general_ledger.py
+++ b/erpnext/accounts/general_ledger.py
@@ -28,6 +28,7 @@
):
if gl_map:
if not cancel:
+ make_acc_dimensions_offsetting_entry(gl_map)
validate_accounting_period(gl_map)
validate_disabled_accounts(gl_map)
gl_map = process_gl_map(gl_map, merge_entries)
@@ -51,6 +52,63 @@
make_reverse_gl_entries(gl_map, adv_adj=adv_adj, update_outstanding=update_outstanding)
+def make_acc_dimensions_offsetting_entry(gl_map):
+ accounting_dimensions_to_offset = get_accounting_dimensions_for_offsetting_entry(
+ gl_map, gl_map[0].company
+ )
+ no_of_dimensions = len(accounting_dimensions_to_offset)
+ if no_of_dimensions == 0:
+ return
+
+ offsetting_entries = []
+
+ for gle in gl_map:
+ for dimension in accounting_dimensions_to_offset:
+ offsetting_entry = gle.copy()
+ debit = flt(gle.credit) / no_of_dimensions if gle.credit != 0 else 0
+ credit = flt(gle.debit) / no_of_dimensions if gle.debit != 0 else 0
+ offsetting_entry.update(
+ {
+ "account": dimension.offsetting_account,
+ "debit": debit,
+ "credit": credit,
+ "debit_in_account_currency": debit,
+ "credit_in_account_currency": credit,
+ "remarks": _("Offsetting for Accounting Dimension") + " - {0}".format(dimension.name),
+ "against_voucher": None,
+ }
+ )
+ offsetting_entry["against_voucher_type"] = None
+ offsetting_entries.append(offsetting_entry)
+
+ gl_map += offsetting_entries
+
+
+def get_accounting_dimensions_for_offsetting_entry(gl_map, company):
+ acc_dimension = frappe.qb.DocType("Accounting Dimension")
+ dimension_detail = frappe.qb.DocType("Accounting Dimension Detail")
+
+ acc_dimensions = (
+ frappe.qb.from_(acc_dimension)
+ .inner_join(dimension_detail)
+ .on(acc_dimension.name == dimension_detail.parent)
+ .select(acc_dimension.fieldname, acc_dimension.name, dimension_detail.offsetting_account)
+ .where(
+ (acc_dimension.disabled == 0)
+ & (dimension_detail.company == company)
+ & (dimension_detail.automatically_post_balancing_accounting_entry == 1)
+ )
+ ).run(as_dict=True)
+
+ accounting_dimensions_to_offset = []
+ for acc_dimension in acc_dimensions:
+ values = set([entry.get(acc_dimension.fieldname) for entry in gl_map])
+ if len(values) > 1:
+ accounting_dimensions_to_offset.append(acc_dimension)
+
+ return accounting_dimensions_to_offset
+
+
def validate_disabled_accounts(gl_map):
accounts = [d.account for d in gl_map if d.account]
diff --git a/erpnext/accounts/report/trial_balance/test_trial_balance.py b/erpnext/accounts/report/trial_balance/test_trial_balance.py
new file mode 100644
index 0000000..4682ac4
--- /dev/null
+++ b/erpnext/accounts/report/trial_balance/test_trial_balance.py
@@ -0,0 +1,118 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and Contributors
+# MIT License. See license.txt
+
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import today
+
+from erpnext.accounts.report.trial_balance.trial_balance import execute
+
+
+class TestTrialBalance(FrappeTestCase):
+ def setUp(self):
+ from erpnext.accounts.doctype.account.test_account import create_account
+ from erpnext.accounts.doctype.cost_center.test_cost_center import create_cost_center
+ from erpnext.accounts.utils import get_fiscal_year
+
+ self.company = create_company()
+ create_cost_center(
+ cost_center_name="Test Cost Center",
+ company="Trial Balance Company",
+ parent_cost_center="Trial Balance Company - TBC",
+ )
+ create_account(
+ account_name="Offsetting",
+ company="Trial Balance Company",
+ parent_account="Temporary Accounts - TBC",
+ )
+ self.fiscal_year = get_fiscal_year(today(), company="Trial Balance Company")[0]
+ create_accounting_dimension()
+
+ def test_offsetting_entries_for_accounting_dimensions(self):
+ """
+ Checks if Trial Balance Report is balanced when filtered using a particular Accounting Dimension
+ """
+ from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+
+ frappe.db.sql("delete from `tabSales Invoice` where company='Trial Balance Company'")
+ frappe.db.sql("delete from `tabGL Entry` where company='Trial Balance Company'")
+
+ branch1 = frappe.new_doc("Branch")
+ branch1.branch = "Location 1"
+ branch1.insert(ignore_if_duplicate=True)
+ branch2 = frappe.new_doc("Branch")
+ branch2.branch = "Location 2"
+ branch2.insert(ignore_if_duplicate=True)
+
+ si = create_sales_invoice(
+ company=self.company,
+ debit_to="Debtors - TBC",
+ cost_center="Test Cost Center - TBC",
+ income_account="Sales - TBC",
+ do_not_submit=1,
+ )
+ si.branch = "Location 1"
+ si.items[0].branch = "Location 2"
+ si.save()
+ si.submit()
+
+ filters = frappe._dict(
+ {"company": self.company, "fiscal_year": self.fiscal_year, "branch": ["Location 1"]}
+ )
+ total_row = execute(filters)[1][-1]
+ self.assertEqual(total_row["debit"], total_row["credit"])
+
+ def tearDown(self):
+ clear_dimension_defaults("Branch")
+ disable_dimension()
+
+
+def create_company(**args):
+ args = frappe._dict(args)
+ company = frappe.get_doc(
+ {
+ "doctype": "Company",
+ "company_name": args.company_name or "Trial Balance Company",
+ "country": args.country or "India",
+ "default_currency": args.currency or "INR",
+ }
+ )
+ company.insert(ignore_if_duplicate=True)
+ return company.name
+
+
+def create_accounting_dimension(**args):
+ args = frappe._dict(args)
+ document_type = args.document_type or "Branch"
+ if frappe.db.exists("Accounting Dimension", document_type):
+ accounting_dimension = frappe.get_doc("Accounting Dimension", document_type)
+ accounting_dimension.disabled = 0
+ else:
+ accounting_dimension = frappe.new_doc("Accounting Dimension")
+ accounting_dimension.document_type = document_type
+ accounting_dimension.insert()
+
+ accounting_dimension.set("dimension_defaults", [])
+ accounting_dimension.append(
+ "dimension_defaults",
+ {
+ "company": args.company or "Trial Balance Company",
+ "automatically_post_balancing_accounting_entry": 1,
+ "offsetting_account": args.offsetting_account or "Offsetting - TBC",
+ },
+ )
+ accounting_dimension.save()
+
+
+def disable_dimension(**args):
+ args = frappe._dict(args)
+ document_type = args.document_type or "Branch"
+ dimension = frappe.get_doc("Accounting Dimension", document_type)
+ dimension.disabled = 1
+ dimension.save()
+
+
+def clear_dimension_defaults(dimension_name):
+ accounting_dimension = frappe.get_doc("Accounting Dimension", dimension_name)
+ accounting_dimension.dimension_defaults = []
+ accounting_dimension.save()
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index 5a9e950..376571f 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -259,7 +259,7 @@
lft, rgt = frappe.db.get_value("Cost Center", filters.cost_center, ["lft", "rgt"])
cost_center = frappe.qb.DocType("Cost Center")
opening_balance = opening_balance.where(
- closing_balance.cost_center.in_(
+ closing_balance.cost_center.isin(
frappe.qb.from_(cost_center)
.select("name")
.where((cost_center.lft >= lft) & (cost_center.rgt <= rgt))