Merge pull request #39054 from ruthra-kumar/provision_to_set_dimension_in_reconciliation_tool
refactor: provision to filter on dimensions in reconciliation tool
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 2954d2f..62e2181 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -640,7 +640,7 @@
get_outstanding_invoices_or_orders: function(frm, get_outstanding_invoices, get_orders_to_be_billed) {
const today = frappe.datetime.get_today();
- const fields = [
+ let fields = [
{fieldtype:"Section Break", label: __("Posting Date")},
{fieldtype:"Date", label: __("From Date"),
fieldname:"from_posting_date", default:frappe.datetime.add_days(today, -30)},
@@ -655,18 +655,29 @@
fieldname:"outstanding_amt_greater_than", default: 0},
{fieldtype:"Column Break"},
{fieldtype:"Float", label: __("Less Than Amount"), fieldname:"outstanding_amt_less_than"},
- {fieldtype:"Section Break"},
- {fieldtype:"Link", label:__("Cost Center"), fieldname:"cost_center", options:"Cost Center",
- "get_query": function() {
- return {
- "filters": {"company": frm.doc.company}
- }
+ ];
+
+ if (frm.dimension_filters) {
+ let column_break_insertion_point = Math.ceil((frm.dimension_filters.length)/2);
+
+ fields.push({fieldtype:"Section Break"});
+ frm.dimension_filters.map((elem, idx)=>{
+ fields.push({
+ fieldtype: "Link",
+ label: elem.document_type == "Cost Center" ? "Cost Center" : elem.label,
+ options: elem.document_type,
+ fieldname: elem.fieldname || elem.document_type
+ });
+ if(idx+1 == column_break_insertion_point) {
+ fields.push({fieldtype:"Column Break"});
}
- },
- {fieldtype:"Column Break"},
+ });
+ }
+
+ fields = fields.concat([
{fieldtype:"Section Break"},
{fieldtype:"Check", label: __("Allocate Payment Amount"), fieldname:"allocate_payment_amount", default:1},
- ];
+ ]);
let btn_text = "";
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index b8781ef..69f0dda 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -13,6 +13,7 @@
from pypika.functions import Coalesce, Sum
import erpnext
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_dimensions
from erpnext.accounts.doctype.bank_account.bank_account import (
get_bank_account_details,
get_party_bank_account,
@@ -1677,6 +1678,13 @@
condition += " and cost_center='%s'" % args.get("cost_center")
accounting_dimensions_filter.append(ple.cost_center == args.get("cost_center"))
+ # dynamic dimension filters
+ active_dimensions = get_dimensions()[0]
+ for dim in active_dimensions:
+ if args.get(dim.fieldname):
+ condition += " and {0}='{1}'".format(dim.fieldname, args.get(dim.fieldname))
+ accounting_dimensions_filter.append(ple[dim.fieldname] == args.get(dim.fieldname))
+
date_fields_dict = {
"posting_date": ["from_posting_date", "to_posting_date"],
"due_date": ["from_due_date", "to_due_date"],
@@ -1910,6 +1918,12 @@
if doc and hasattr(doc, "cost_center") and doc.cost_center:
condition = " and cost_center='%s'" % cost_center
+ # dynamic dimension filters
+ active_dimensions = get_dimensions()[0]
+ for dim in active_dimensions:
+ if filters.get(dim.fieldname):
+ condition += " and {0}='{1}'".format(dim.fieldname, filters.get(dim.fieldname))
+
if party_account_currency == company_currency:
grand_total_field = "base_grand_total"
rounded_total_field = "base_rounded_total"
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
index fc90c3d..99593de 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
@@ -95,6 +95,8 @@
this.frm.change_custom_button_type(__('Allocate'), null, 'default');
}
+ this.frm.trigger("set_query_for_dimension_filters");
+
// check for any running reconciliation jobs
if (this.frm.doc.receivable_payable_account) {
this.frm.call({
@@ -125,6 +127,25 @@
}
}
+ set_query_for_dimension_filters() {
+ frappe.call({
+ method: "erpnext.accounts.doctype.payment_reconciliation.payment_reconciliation.get_queries_for_dimension_filters",
+ args: {
+ company: this.frm.doc.company,
+ },
+ callback: (r) => {
+ if (!r.exc && r.message) {
+ r.message.forEach(x => {
+ this.frm.set_query(x.fieldname, () => {
+ return {
+ 'filters': x.filters
+ };
+ });
+ });
+ }
+ }
+ });
+ }
company() {
this.frm.set_value('party', '');
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
index ccb9e64..666926f 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
@@ -25,7 +25,9 @@
"invoice_limit",
"payment_limit",
"bank_cash_account",
+ "accounting_dimensions_section",
"cost_center",
+ "dimension_col_break",
"sec_break1",
"invoice_name",
"invoices",
@@ -208,6 +210,18 @@
"fieldname": "payment_name",
"fieldtype": "Data",
"label": "Filter on Payment"
+ },
+ {
+ "collapsible": 1,
+ "collapsible_depends_on": "eval: doc.invoices.length == 0",
+ "depends_on": "eval:doc.receivable_payable_account",
+ "fieldname": "accounting_dimensions_section",
+ "fieldtype": "Section Break",
+ "label": "Accounting Dimensions Filter"
+ },
+ {
+ "fieldname": "dimension_col_break",
+ "fieldtype": "Column Break"
}
],
"hide_toolbar": 1,
@@ -215,7 +229,7 @@
"is_virtual": 1,
"issingle": 1,
"links": [],
- "modified": "2023-11-17 17:33:55.701726",
+ "modified": "2023-12-14 13:38:16.264013",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Reconciliation",
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index ed0921b..b2716c9 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -10,6 +10,7 @@
from frappe.utils import flt, fmt_money, get_link_to_form, getdate, nowdate, today
import erpnext
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_dimensions
from erpnext.accounts.doctype.process_payment_reconciliation.process_payment_reconciliation import (
is_any_doc_running,
)
@@ -70,6 +71,7 @@
self.common_filter_conditions = []
self.accounting_dimension_filter_conditions = []
self.ple_posting_date_filter = []
+ self.dimensions = get_dimensions()[0]
def load_from_db(self):
# 'modified' attribute is required for `run_doc_method` to work properly.
@@ -172,6 +174,14 @@
if self.payment_name:
condition.update({"name": self.payment_name})
+ # pass dynamic dimension filter values to query builder
+ dimensions = {}
+ for x in self.dimensions:
+ dimension = x.fieldname
+ if self.get(dimension):
+ dimensions.update({dimension: self.get(dimension)})
+ condition.update({"accounting_dimensions": dimensions})
+
payment_entries = get_advance_payment_entries_for_regional(
self.party_type,
self.party,
@@ -185,66 +195,67 @@
return payment_entries
def get_jv_entries(self):
- condition = self.get_conditions()
+ je = qb.DocType("Journal Entry")
+ jea = qb.DocType("Journal Entry Account")
+ conditions = self.get_journal_filter_conditions()
+
+ # Dimension filters
+ for x in self.dimensions:
+ dimension = x.fieldname
+ if self.get(dimension):
+ conditions.append(jea[dimension] == self.get(dimension))
if self.payment_name:
- condition += f" and t1.name like '%%{self.payment_name}%%'"
+ conditions.append(je.name.like(f"%%{self.payment_name}%%"))
if self.get("cost_center"):
- condition += f" and t2.cost_center = '{self.cost_center}' "
+ conditions.append(jea.cost_center == self.cost_center)
dr_or_cr = (
"credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type) == "Receivable"
else "debit_in_account_currency"
)
+ conditions.append(jea[dr_or_cr].gt(0))
- bank_account_condition = (
- "t2.against_account like %(bank_cash_account)s" if self.bank_cash_account else "1=1"
+ if self.bank_cash_account:
+ conditions.append(jea.against_account.like(f"%%{self.bank_cash_account}%%"))
+
+ journal_query = (
+ qb.from_(je)
+ .inner_join(jea)
+ .on(jea.parent == je.name)
+ .select(
+ ConstantColumn("Journal Entry").as_("reference_type"),
+ je.name.as_("reference_name"),
+ je.posting_date,
+ je.remark.as_("remarks"),
+ jea.name.as_("reference_row"),
+ jea[dr_or_cr].as_("amount"),
+ jea.is_advance,
+ jea.exchange_rate,
+ jea.account_currency.as_("currency"),
+ jea.cost_center.as_("cost_center"),
+ )
+ .where(
+ (je.docstatus == 1)
+ & (jea.party_type == self.party_type)
+ & (jea.party == self.party)
+ & (jea.account == self.receivable_payable_account)
+ & (
+ (jea.reference_type == "")
+ | (jea.reference_type.isnull())
+ | (jea.reference_type.isin(("Sales Order", "Purchase Order")))
+ )
+ )
+ .where(Criterion.all(conditions))
+ .orderby(je.posting_date)
)
- limit = f"limit {self.payment_limit}" if self.payment_limit else " "
+ if self.payment_limit:
+ journal_query = journal_query.limit(self.payment_limit)
- # nosemgrep
- journal_entries = frappe.db.sql(
- """
- select
- "Journal Entry" as reference_type, t1.name as reference_name,
- t1.posting_date, t1.remark as remarks, t2.name as reference_row,
- {dr_or_cr} as amount, t2.is_advance, t2.exchange_rate,
- t2.account_currency as currency, t2.cost_center as cost_center
- from
- `tabJournal Entry` t1, `tabJournal Entry Account` t2
- where
- t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
- and t2.party_type = %(party_type)s and t2.party = %(party)s
- and t2.account = %(account)s and {dr_or_cr} > 0 {condition}
- and (t2.reference_type is null or t2.reference_type = '' or
- (t2.reference_type in ('Sales Order', 'Purchase Order')
- and t2.reference_name is not null and t2.reference_name != ''))
- and (CASE
- WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
- THEN 1=1
- ELSE {bank_account_condition}
- END)
- order by t1.posting_date
- {limit}
- """.format(
- **{
- "dr_or_cr": dr_or_cr,
- "bank_account_condition": bank_account_condition,
- "condition": condition,
- "limit": limit,
- }
- ),
- {
- "party_type": self.party_type,
- "party": self.party,
- "account": self.receivable_payable_account,
- "bank_cash_account": "%%%s%%" % self.bank_cash_account,
- },
- as_dict=1,
- )
+ journal_entries = journal_query.run(as_dict=True)
return list(journal_entries)
@@ -298,6 +309,7 @@
min_outstanding=-(self.minimum_payment_amount) if self.minimum_payment_amount else None,
max_outstanding=-(self.maximum_payment_amount) if self.maximum_payment_amount else None,
get_payments=True,
+ accounting_dimensions=self.accounting_dimension_filter_conditions,
)
for inv in return_outstanding:
@@ -447,8 +459,15 @@
row = self.append("allocation", {})
row.update(entry)
+ def update_dimension_values_in_allocated_entries(self, res):
+ for x in self.dimensions:
+ dimension = x.fieldname
+ if self.get(dimension):
+ res[dimension] = self.get(dimension)
+ return res
+
def get_allocated_entry(self, pay, inv, allocated_amount):
- return frappe._dict(
+ res = frappe._dict(
{
"reference_type": pay.get("reference_type"),
"reference_name": pay.get("reference_name"),
@@ -464,6 +483,9 @@
}
)
+ res = self.update_dimension_values_in_allocated_entries(res)
+ return res
+
def reconcile_allocations(self, skip_ref_details_update_for_pe=False):
adjust_allocations_for_taxes(self)
dr_or_cr = (
@@ -486,10 +508,10 @@
reconciled_entry.append(payment_details)
if entry_list:
- reconcile_against_document(entry_list, skip_ref_details_update_for_pe)
+ reconcile_against_document(entry_list, skip_ref_details_update_for_pe, self.dimensions)
if dr_or_cr_notes:
- reconcile_dr_cr_note(dr_or_cr_notes, self.company)
+ reconcile_dr_cr_note(dr_or_cr_notes, self.company, self.dimensions)
@frappe.whitelist()
def reconcile(self):
@@ -518,7 +540,7 @@
self.get_unreconciled_entries()
def get_payment_details(self, row, dr_or_cr):
- return frappe._dict(
+ payment_details = frappe._dict(
{
"voucher_type": row.get("reference_type"),
"voucher_no": row.get("reference_name"),
@@ -541,6 +563,12 @@
}
)
+ for x in self.dimensions:
+ if row.get(x.fieldname):
+ payment_details[x.fieldname] = row.get(x.fieldname)
+
+ return payment_details
+
def check_mandatory_to_fetch(self):
for fieldname in ["company", "party_type", "party", "receivable_payable_account"]:
if not self.get(fieldname):
@@ -648,6 +676,13 @@
if not invoices_to_reconcile:
frappe.throw(_("No records found in Allocation table"))
+ def build_dimensions_filter_conditions(self):
+ ple = qb.DocType("Payment Ledger Entry")
+ for x in self.dimensions:
+ dimension = x.fieldname
+ if self.get(dimension):
+ self.accounting_dimension_filter_conditions.append(ple[dimension] == self.get(dimension))
+
def build_qb_filter_conditions(self, get_invoices=False, get_return_invoices=False):
self.common_filter_conditions.clear()
self.accounting_dimension_filter_conditions.clear()
@@ -671,40 +706,30 @@
if self.to_payment_date:
self.ple_posting_date_filter.append(ple.posting_date.lte(self.to_payment_date))
- def get_conditions(self, get_payments=False):
- condition = " and company = '{0}' ".format(self.company)
+ self.build_dimensions_filter_conditions()
- if self.get("cost_center") and get_payments:
- condition = " and cost_center = '{0}' ".format(self.cost_center)
+ def get_journal_filter_conditions(self):
+ conditions = []
+ je = qb.DocType("Journal Entry")
+ jea = qb.DocType("Journal Entry Account")
+ conditions.append(je.company == self.company)
- condition += (
- " and posting_date >= {0}".format(frappe.db.escape(self.from_payment_date))
- if self.from_payment_date
- else ""
- )
- condition += (
- " and posting_date <= {0}".format(frappe.db.escape(self.to_payment_date))
- if self.to_payment_date
- else ""
- )
+ if self.from_payment_date:
+ conditions.append(je.posting_date.gte(self.from_payment_date))
+
+ if self.to_payment_date:
+ conditions.append(je.posting_date.lte(self.to_payment_date))
if self.minimum_payment_amount:
- condition += (
- " and unallocated_amount >= {0}".format(flt(self.minimum_payment_amount))
- if get_payments
- else " and total_debit >= {0}".format(flt(self.minimum_payment_amount))
- )
+ conditions.append(je.total_debit.gte(self.minimum_payment_amount))
+
if self.maximum_payment_amount:
- condition += (
- " and unallocated_amount <= {0}".format(flt(self.maximum_payment_amount))
- if get_payments
- else " and total_debit <= {0}".format(flt(self.maximum_payment_amount))
- )
+ conditions.append(je.total_debit.lte(self.maximum_payment_amount))
- return condition
+ return conditions
-def reconcile_dr_cr_note(dr_cr_notes, company):
+def reconcile_dr_cr_note(dr_cr_notes, company, active_dimensions=None):
for inv in dr_cr_notes:
voucher_type = "Credit Note" if inv.voucher_type == "Sales Invoice" else "Debit Note"
@@ -754,6 +779,15 @@
}
)
+ # Credit Note(JE) will inherit the same dimension values as payment
+ dimensions_dict = frappe._dict()
+ if active_dimensions:
+ for dim in active_dimensions:
+ dimensions_dict[dim.fieldname] = inv.get(dim.fieldname)
+
+ jv.accounts[0].update(dimensions_dict)
+ jv.accounts[1].update(dimensions_dict)
+
jv.flags.ignore_mandatory = True
jv.flags.ignore_exchange_rate = True
jv.remark = None
@@ -787,9 +821,27 @@
inv.against_voucher,
None,
inv.cost_center,
+ dimensions_dict,
)
@erpnext.allow_regional
def adjust_allocations_for_taxes(doc):
pass
+
+
+@frappe.whitelist()
+def get_queries_for_dimension_filters(company: str = None):
+ dimensions_with_filters = []
+ for d in get_dimensions()[0]:
+ filters = {}
+ meta = frappe.get_meta(d.document_type)
+ if meta.has_field("company") and company:
+ filters.update({"company": company})
+
+ if meta.is_tree:
+ filters.update({"is_group": 0})
+
+ dimensions_with_filters.append({"fieldname": d.fieldname, "filters": filters})
+
+ return dimensions_with_filters
diff --git a/erpnext/accounts/doctype/payment_reconciliation_allocation/payment_reconciliation_allocation.json b/erpnext/accounts/doctype/payment_reconciliation_allocation/payment_reconciliation_allocation.json
index 491c678..3f85b21 100644
--- a/erpnext/accounts/doctype/payment_reconciliation_allocation/payment_reconciliation_allocation.json
+++ b/erpnext/accounts/doctype/payment_reconciliation_allocation/payment_reconciliation_allocation.json
@@ -24,7 +24,9 @@
"difference_account",
"exchange_rate",
"currency",
- "cost_center"
+ "accounting_dimensions_section",
+ "cost_center",
+ "dimension_col_break"
],
"fields": [
{
@@ -157,12 +159,21 @@
"fieldname": "gain_loss_posting_date",
"fieldtype": "Date",
"label": "Difference Posting Date"
+ },
+ {
+ "fieldname": "accounting_dimensions_section",
+ "fieldtype": "Section Break",
+ "label": "Accounting Dimensions"
+ },
+ {
+ "fieldname": "dimension_col_break",
+ "fieldtype": "Column Break"
}
],
"is_virtual": 1,
"istable": 1,
"links": [],
- "modified": "2023-11-17 17:33:38.612615",
+ "modified": "2023-12-14 13:38:26.104150",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Reconciliation Allocation",
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 9b70629..65b3aba 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -453,7 +453,19 @@
return cc.name
-def reconcile_against_document(args, skip_ref_details_update_for_pe=False): # nosemgrep
+def _build_dimensions_dict_for_exc_gain_loss(
+ entry: dict | object = None, active_dimensions: list = None
+):
+ dimensions_dict = frappe._dict()
+ if entry and active_dimensions:
+ for dim in active_dimensions:
+ dimensions_dict[dim.fieldname] = entry.get(dim.fieldname)
+ return dimensions_dict
+
+
+def reconcile_against_document(
+ args, skip_ref_details_update_for_pe=False, active_dimensions=None
+): # nosemgrep
"""
Cancel PE or JV, Update against document, split if required and resubmit
"""
@@ -482,6 +494,8 @@
check_if_advance_entry_modified(entry)
validate_allocated_amount(entry)
+ dimensions_dict = _build_dimensions_dict_for_exc_gain_loss(entry, active_dimensions)
+
# update ref in advance entry
if voucher_type == "Journal Entry":
referenced_row = update_reference_in_journal_entry(entry, doc, do_not_save=False)
@@ -489,10 +503,14 @@
# amount and account in args
# referenced_row is used to deduplicate gain/loss journal
entry.update({"referenced_row": referenced_row})
- doc.make_exchange_gain_loss_journal([entry])
+ doc.make_exchange_gain_loss_journal([entry], dimensions_dict)
else:
referenced_row = update_reference_in_payment_entry(
- entry, doc, do_not_save=True, skip_ref_details_update_for_pe=skip_ref_details_update_for_pe
+ entry,
+ doc,
+ do_not_save=True,
+ skip_ref_details_update_for_pe=skip_ref_details_update_for_pe,
+ dimensions_dict=dimensions_dict,
)
doc.save(ignore_permissions=True)
@@ -657,7 +675,7 @@
def update_reference_in_payment_entry(
- d, payment_entry, do_not_save=False, skip_ref_details_update_for_pe=False
+ d, payment_entry, do_not_save=False, skip_ref_details_update_for_pe=False, dimensions_dict=None
):
reference_details = {
"reference_doctype": d.against_voucher_type,
@@ -670,6 +688,7 @@
else payment_entry.get_exchange_rate(),
"exchange_gain_loss": d.difference_amount,
"account": d.account,
+ "dimensions": d.dimensions,
}
if d.voucher_detail_no:
@@ -705,8 +724,9 @@
if not skip_ref_details_update_for_pe:
payment_entry.set_missing_ref_details()
payment_entry.set_amounts()
+
payment_entry.make_exchange_gain_loss_journal(
- frappe._dict({"difference_posting_date": d.difference_posting_date})
+ frappe._dict({"difference_posting_date": d.difference_posting_date}), dimensions_dict
)
if not do_not_save:
@@ -2048,6 +2068,7 @@
ref2_dn,
ref2_detail_no,
cost_center,
+ dimensions,
) -> str:
journal_entry = frappe.new_doc("Journal Entry")
journal_entry.voucher_type = "Exchange Gain Or Loss"
@@ -2081,7 +2102,8 @@
dr_or_cr + "_in_account_currency": 0,
}
)
-
+ if dimensions:
+ journal_account.update(dimensions)
journal_entry.append("accounts", journal_account)
journal_account = frappe._dict(
@@ -2097,7 +2119,8 @@
reverse_dr_or_cr: abs(exc_gain_loss),
}
)
-
+ if dimensions:
+ journal_account.update(dimensions)
journal_entry.append("accounts", journal_account)
journal_entry.save()
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index 7cc4bfe..afbea61 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -7,6 +7,7 @@
import frappe
from frappe import _, bold, qb, throw
from frappe.model.workflow import get_workflow_name, is_transition_condition_satisfied
+from frappe.query_builder import Criterion
from frappe.query_builder.custom import ConstantColumn
from frappe.query_builder.functions import Abs, Sum
from frappe.utils import (
@@ -27,6 +28,7 @@
import erpnext
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
get_accounting_dimensions,
+ get_dimensions,
)
from erpnext.accounts.doctype.pricing_rule.utils import (
apply_pricing_rule_for_free_items,
@@ -1216,7 +1218,9 @@
return True
return False
- def make_exchange_gain_loss_journal(self, args: dict = None) -> None:
+ def make_exchange_gain_loss_journal(
+ self, args: dict = None, dimensions_dict: dict = None
+ ) -> None:
"""
Make Exchange Gain/Loss journal for Invoices and Payments
"""
@@ -1271,6 +1275,7 @@
self.name,
arg.get("referenced_row"),
arg.get("cost_center"),
+ dimensions_dict,
)
frappe.msgprint(
_("Exchange Gain/Loss amount has been booked through {0}").format(
@@ -1351,6 +1356,7 @@
self.name,
d.idx,
self.cost_center,
+ dimensions_dict,
)
frappe.msgprint(
_("Exchange Gain/Loss amount has been booked through {0}").format(
@@ -1415,7 +1421,13 @@
if lst:
from erpnext.accounts.utils import reconcile_against_document
- reconcile_against_document(lst)
+ # pass dimension values to utility method
+ active_dimensions = get_dimensions()[0]
+ for x in lst:
+ for dim in active_dimensions:
+ if self.get(dim.fieldname):
+ x.update({dim.fieldname: self.get(dim.fieldname)})
+ reconcile_against_document(lst, active_dimensions=active_dimensions)
def on_cancel(self):
from erpnext.accounts.doctype.bank_transaction.bank_transaction import (
@@ -2720,47 +2732,37 @@
q = q.select((payment_entry.target_exchange_rate).as_("exchange_rate"))
if condition:
- if condition.get("name", None):
- q = q.where(payment_entry.name.like(f"%{condition.get('name')}%"))
+ # conditions should be built as an array and passed as Criterion
+ common_filter_conditions = []
- q = q.where(payment_entry.company == condition["company"])
- q = (
- q.where(payment_entry.posting_date >= condition["from_payment_date"])
- if condition.get("from_payment_date")
- else q
- )
- q = (
- q.where(payment_entry.posting_date <= condition["to_payment_date"])
- if condition.get("to_payment_date")
- else q
- )
+ common_filter_conditions.append(payment_entry.company == condition["company"])
+ if condition.get("name", None):
+ common_filter_conditions.append(payment_entry.name.like(f"%{condition.get('name')}%"))
+
+ if condition.get("from_payment_date"):
+ common_filter_conditions.append(payment_entry.posting_date.gte(condition["from_payment_date"]))
+
+ if condition.get("to_payment_date"):
+ common_filter_conditions.append(payment_entry.posting_date.lte(condition["to_payment_date"]))
+
if condition.get("get_payments") == True:
- q = (
- q.where(payment_entry.cost_center == condition["cost_center"])
- if condition.get("cost_center")
- else q
- )
- q = (
- q.where(payment_entry.unallocated_amount >= condition["minimum_payment_amount"])
- if condition.get("minimum_payment_amount")
- else q
- )
- q = (
- q.where(payment_entry.unallocated_amount <= condition["maximum_payment_amount"])
- if condition.get("maximum_payment_amount")
- else q
- )
- else:
- q = (
- q.where(payment_entry.total_debit >= condition["minimum_payment_amount"])
- if condition.get("minimum_payment_amount")
- else q
- )
- q = (
- q.where(payment_entry.total_debit <= condition["maximum_payment_amount"])
- if condition.get("maximum_payment_amount")
- else q
- )
+ if condition.get("cost_center"):
+ common_filter_conditions.append(payment_entry.cost_center == condition["cost_center"])
+
+ if condition.get("accounting_dimensions"):
+ for field, val in condition.get("accounting_dimensions").items():
+ common_filter_conditions.append(payment_entry[field] == val)
+
+ if condition.get("minimum_payment_amount"):
+ common_filter_conditions.append(
+ payment_entry.unallocated_amount.gte(condition["minimum_payment_amount"])
+ )
+
+ if condition.get("maximum_payment_amount"):
+ common_filter_conditions.append(
+ payment_entry.unallocated_amount.lte(condition["maximum_payment_amount"])
+ )
+ q = q.where(Criterion.all(common_filter_conditions))
q = q.orderby(payment_entry.posting_date)
q = q.limit(limit) if limit else q
diff --git a/erpnext/controllers/tests/test_accounts_controller.py b/erpnext/controllers/tests/test_accounts_controller.py
index 97d3c5c..fad216d 100644
--- a/erpnext/controllers/tests/test_accounts_controller.py
+++ b/erpnext/controllers/tests/test_accounts_controller.py
@@ -56,6 +56,7 @@
20 series - Sales Invoice against Journals
30 series - Sales Invoice against Credit Notes
40 series - Company default Cost center is unset
+ 50 series - Dimension inheritence
"""
def setUp(self):
@@ -1255,3 +1256,214 @@
)
frappe.db.set_value("Company", self.company, "cost_center", cc)
+
+ def setup_dimensions(self):
+ # create dimension
+ from erpnext.accounts.doctype.accounting_dimension.test_accounting_dimension import (
+ create_dimension,
+ )
+
+ create_dimension()
+ # make it non-mandatory
+ loc = frappe.get_doc("Accounting Dimension", "Location")
+ for x in loc.dimension_defaults:
+ x.mandatory_for_bs = False
+ x.mandatory_for_pl = False
+ loc.save()
+
+ def test_50_dimensions_filter(self):
+ """
+ Test workings of dimension filters
+ """
+ self.setup_dimensions()
+ rate_in_account_currency = 1
+
+ # Invoices
+ si1 = self.create_sales_invoice(qty=1, rate=rate_in_account_currency, do_not_submit=True)
+ si1.department = "Management"
+ si1.save().submit()
+
+ si2 = self.create_sales_invoice(qty=1, rate=rate_in_account_currency, do_not_submit=True)
+ si2.department = "Operations"
+ si2.save().submit()
+
+ # Payments
+ cr_note1 = self.create_sales_invoice(qty=-1, conversion_rate=75, rate=1, do_not_save=True)
+ cr_note1.department = "Management"
+ cr_note1.is_return = 1
+ cr_note1.save().submit()
+
+ cr_note2 = self.create_sales_invoice(qty=-1, conversion_rate=75, rate=1, do_not_save=True)
+ cr_note2.department = "Legal"
+ cr_note2.is_return = 1
+ cr_note2.save().submit()
+
+ pe1 = get_payment_entry(si1.doctype, si1.name)
+ pe1.references = []
+ pe1.department = "Research & Development"
+ pe1.save().submit()
+
+ pe2 = get_payment_entry(si1.doctype, si1.name)
+ pe2.references = []
+ pe2.department = "Management"
+ pe2.save().submit()
+
+ je1 = self.create_journal_entry(
+ acc1=self.debit_usd,
+ acc1_exc_rate=75,
+ acc2=self.cash,
+ acc1_amount=-1,
+ acc2_amount=-75,
+ acc2_exc_rate=1,
+ )
+ je1.accounts[0].party_type = "Customer"
+ je1.accounts[0].party = self.customer
+ je1.accounts[0].department = "Management"
+ je1.save().submit()
+
+ # assert dimension filter's result
+ pr = self.create_payment_reconciliation()
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.invoices), 2)
+ self.assertEqual(len(pr.payments), 5)
+
+ pr.department = "Legal"
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.invoices), 0)
+ self.assertEqual(len(pr.payments), 1)
+
+ pr.department = "Management"
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.invoices), 1)
+ self.assertEqual(len(pr.payments), 3)
+
+ pr.department = "Research & Development"
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.invoices), 0)
+ self.assertEqual(len(pr.payments), 1)
+
+ def test_51_cr_note_should_inherit_dimension(self):
+ self.setup_dimensions()
+ rate_in_account_currency = 1
+
+ # Invoice
+ si = self.create_sales_invoice(qty=1, rate=rate_in_account_currency, do_not_submit=True)
+ si.department = "Management"
+ si.save().submit()
+
+ # Payment
+ cr_note = self.create_sales_invoice(qty=-1, conversion_rate=75, rate=1, do_not_save=True)
+ cr_note.department = "Management"
+ cr_note.is_return = 1
+ cr_note.save().submit()
+
+ pr = self.create_payment_reconciliation()
+ pr.department = "Management"
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.invoices), 1)
+ self.assertEqual(len(pr.payments), 1)
+ invoices = [x.as_dict() for x in pr.invoices]
+ payments = [x.as_dict() for x in pr.payments]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+ self.assertEqual(len(pr.invoices), 0)
+ self.assertEqual(len(pr.payments), 0)
+
+ # There should be 2 journals, JE(Cr Note) and JE(Exchange Gain/Loss)
+ exc_je_for_si = self.get_journals_for(si.doctype, si.name)
+ exc_je_for_cr_note = self.get_journals_for(cr_note.doctype, cr_note.name)
+ self.assertNotEqual(exc_je_for_si, [])
+ self.assertEqual(len(exc_je_for_si), 2)
+ self.assertEqual(len(exc_je_for_cr_note), 2)
+ self.assertEqual(exc_je_for_si, exc_je_for_cr_note)
+
+ for x in exc_je_for_si + exc_je_for_cr_note:
+ with self.subTest(x=x):
+ self.assertEqual(
+ [cr_note.department, cr_note.department],
+ frappe.db.get_all("Journal Entry Account", filters={"parent": x.parent}, pluck="department"),
+ )
+
+ def test_52_dimension_inhertiance_exc_gain_loss(self):
+ # Sales Invoice in Foreign Currency
+ self.setup_dimensions()
+ rate = 80
+ rate_in_account_currency = 1
+ dpt = "Research & Development"
+
+ si = self.create_sales_invoice(qty=1, rate=rate_in_account_currency, do_not_save=True)
+ si.department = dpt
+ si.save().submit()
+
+ pe = self.create_payment_entry(amount=1, source_exc_rate=82).save()
+ pe.department = dpt
+ pe = pe.save().submit()
+
+ pr = self.create_payment_reconciliation()
+ pr.department = dpt
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.invoices), 1)
+ self.assertEqual(len(pr.payments), 1)
+ invoices = [x.as_dict() for x in pr.invoices]
+ payments = [x.as_dict() for x in pr.payments]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+ self.assertEqual(len(pr.invoices), 0)
+ self.assertEqual(len(pr.payments), 0)
+
+ # Exc Gain/Loss journals should inherit dimension from parent
+ journals = self.get_journals_for(si.doctype, si.name)
+ self.assertEqual(
+ [dpt, dpt],
+ frappe.db.get_all(
+ "Journal Entry Account",
+ filters={"parent": ("in", [x.parent for x in journals])},
+ pluck="department",
+ ),
+ )
+
+ def test_53_dimension_inheritance_on_advance(self):
+ self.setup_dimensions()
+ dpt = "Research & Development"
+
+ adv = self.create_payment_entry(amount=1, source_exc_rate=85)
+ adv.department = dpt
+ adv.save().submit()
+ adv.reload()
+
+ # Sales Invoices in different exchange rates
+ si = self.create_sales_invoice(qty=1, conversion_rate=82, rate=1, do_not_submit=True)
+ si.department = dpt
+ advances = si.get_advance_entries()
+ self.assertEqual(len(advances), 1)
+ self.assertEqual(advances[0].reference_name, adv.name)
+ si.append(
+ "advances",
+ {
+ "doctype": "Sales Invoice Advance",
+ "reference_type": advances[0].reference_type,
+ "reference_name": advances[0].reference_name,
+ "reference_row": advances[0].reference_row,
+ "advance_amount": 1,
+ "allocated_amount": 1,
+ "ref_exchange_rate": advances[0].exchange_rate,
+ "remarks": advances[0].remarks,
+ },
+ )
+ si = si.save().submit()
+
+ # Outstanding in both currencies should be '0'
+ adv.reload()
+ self.assertEqual(si.outstanding_amount, 0)
+ self.assert_ledger_outstanding(si.doctype, si.name, 0.0, 0.0)
+
+ # Exc Gain/Loss journals should inherit dimension from parent
+ journals = self.get_journals_for(si.doctype, si.name)
+ self.assertEqual(
+ [dpt, dpt],
+ frappe.db.get_all(
+ "Journal Entry Account",
+ filters={"parent": ("in", [x.parent for x in journals])},
+ pluck="department",
+ ),
+ )
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index e21d7bd..f33fff0 100644
--- a/erpnext/hooks.py
+++ b/erpnext/hooks.py
@@ -539,6 +539,8 @@
"Account Closing Balance",
"Supplier Quotation",
"Supplier Quotation Item",
+ "Payment Reconciliation",
+ "Payment Reconciliation Allocation",
]
get_matching_queries = (
diff --git a/erpnext/public/js/utils/dimension_tree_filter.js b/erpnext/public/js/utils/dimension_tree_filter.js
index 3f70c09..27d00ba 100644
--- a/erpnext/public/js/utils/dimension_tree_filter.js
+++ b/erpnext/public/js/utils/dimension_tree_filter.js
@@ -25,6 +25,10 @@
},
setup_filters(frm, doctype) {
+ if (doctype == 'Payment Entry' && this.accounting_dimensions) {
+ frm.dimension_filters = this.accounting_dimensions
+ }
+
if (this.accounting_dimensions) {
this.accounting_dimensions.forEach((dimension) => {
frappe.model.with_doctype(dimension['document_type'], () => {