Merge pull request #35794 from ruthra-kumar/exchange_revaluation_only_post_on_account_currency_based_on_scenario
fix: Exchange Rate Revaluation should only post on the currency that has balance in a 'zero' balance account
diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json
index 09482d7..7cd498d 100644
--- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json
+++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json
@@ -61,7 +61,10 @@
"column_break_25",
"frozen_accounts_modifier",
"tab_break_dpet",
- "show_balance_in_coa"
+ "show_balance_in_coa",
+ "banking_tab",
+ "enable_party_matching",
+ "enable_fuzzy_matching"
],
"fields": [
{
@@ -383,6 +386,26 @@
"fieldname": "show_taxes_as_table_in_print",
"fieldtype": "Check",
"label": "Show Taxes as Table in Print"
+ },
+ {
+ "fieldname": "banking_tab",
+ "fieldtype": "Tab Break",
+ "label": "Banking"
+ },
+ {
+ "default": "0",
+ "description": "Auto match and set the Party in Bank Transactions",
+ "fieldname": "enable_party_matching",
+ "fieldtype": "Check",
+ "label": "Enable Automatic Party Matching"
+ },
+ {
+ "default": "0",
+ "depends_on": "enable_party_matching",
+ "description": "Approximately match the description/party name against parties",
+ "fieldname": "enable_fuzzy_matching",
+ "fieldtype": "Check",
+ "label": "Enable Fuzzy Matching"
}
],
"icon": "icon-cog",
@@ -390,7 +413,7 @@
"index_web_pages_for_search": 1,
"issingle": 1,
"links": [],
- "modified": "2023-06-13 18:47:46.430291",
+ "modified": "2023-06-15 16:35:45.123456",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Accounts Settings",
diff --git a/erpnext/accounts/doctype/bank_transaction/auto_match_party.py b/erpnext/accounts/doctype/bank_transaction/auto_match_party.py
new file mode 100644
index 0000000..5d94a08
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_transaction/auto_match_party.py
@@ -0,0 +1,178 @@
+from typing import Tuple, Union
+
+import frappe
+from frappe.utils import flt
+from rapidfuzz import fuzz, process
+
+
+class AutoMatchParty:
+ """
+ Matches by Account/IBAN and then by Party Name/Description sequentially.
+ Returns when a result is obtained.
+
+ Result (if present) is of the form: (Party Type, Party,)
+ """
+
+ def __init__(self, **kwargs) -> None:
+ self.__dict__.update(kwargs)
+
+ def get(self, key):
+ return self.__dict__.get(key, None)
+
+ def match(self) -> Union[Tuple, None]:
+ result = None
+ result = AutoMatchbyAccountIBAN(
+ bank_party_account_number=self.bank_party_account_number,
+ bank_party_iban=self.bank_party_iban,
+ deposit=self.deposit,
+ ).match()
+
+ fuzzy_matching_enabled = frappe.db.get_single_value("Accounts Settings", "enable_fuzzy_matching")
+ if not result and fuzzy_matching_enabled:
+ result = AutoMatchbyPartyNameDescription(
+ bank_party_name=self.bank_party_name, description=self.description, deposit=self.deposit
+ ).match()
+
+ return result
+
+
+class AutoMatchbyAccountIBAN:
+ def __init__(self, **kwargs) -> None:
+ self.__dict__.update(kwargs)
+
+ def get(self, key):
+ return self.__dict__.get(key, None)
+
+ def match(self):
+ if not (self.bank_party_account_number or self.bank_party_iban):
+ return None
+
+ result = self.match_account_in_party()
+ return result
+
+ def match_account_in_party(self) -> Union[Tuple, None]:
+ """Check if there is a IBAN/Account No. match in Customer/Supplier/Employee"""
+ result = None
+ parties = get_parties_in_order(self.deposit)
+ or_filters = self.get_or_filters()
+
+ for party in parties:
+ party_result = frappe.db.get_all(
+ "Bank Account", or_filters=or_filters, pluck="party", limit_page_length=1
+ )
+
+ if party == "Employee" and not party_result:
+ # Search in Bank Accounts first for Employee, and then Employee record
+ if "bank_account_no" in or_filters:
+ or_filters["bank_ac_no"] = or_filters.pop("bank_account_no")
+
+ party_result = frappe.db.get_all(
+ party, or_filters=or_filters, pluck="name", limit_page_length=1
+ )
+
+ if party_result:
+ result = (
+ party,
+ party_result[0],
+ )
+ break
+
+ return result
+
+ def get_or_filters(self) -> dict:
+ or_filters = {}
+ if self.bank_party_account_number:
+ or_filters["bank_account_no"] = self.bank_party_account_number
+
+ if self.bank_party_iban:
+ or_filters["iban"] = self.bank_party_iban
+
+ return or_filters
+
+
+class AutoMatchbyPartyNameDescription:
+ def __init__(self, **kwargs) -> None:
+ self.__dict__.update(kwargs)
+
+ def get(self, key):
+ return self.__dict__.get(key, None)
+
+ def match(self) -> Union[Tuple, None]:
+ # fuzzy search by customer/supplier & employee
+ if not (self.bank_party_name or self.description):
+ return None
+
+ result = self.match_party_name_desc_in_party()
+ return result
+
+ def match_party_name_desc_in_party(self) -> Union[Tuple, None]:
+ """Fuzzy search party name and/or description against parties in the system"""
+ result = None
+ parties = get_parties_in_order(self.deposit)
+
+ for party in parties:
+ filters = {"status": "Active"} if party == "Employee" else {"disabled": 0}
+ names = frappe.get_all(party, filters=filters, pluck=party.lower() + "_name")
+
+ for field in ["bank_party_name", "description"]:
+ if not self.get(field):
+ continue
+
+ result, skip = self.fuzzy_search_and_return_result(party, names, field)
+ if result or skip:
+ break
+
+ if result or skip:
+ # Skip If: It was hard to distinguish between close matches and so match is None
+ # OR if the right match was found
+ break
+
+ return result
+
+ def fuzzy_search_and_return_result(self, party, names, field) -> Union[Tuple, None]:
+ skip = False
+ result = process.extract(query=self.get(field), choices=names, scorer=fuzz.token_set_ratio)
+ party_name, skip = self.process_fuzzy_result(result)
+
+ if not party_name:
+ return None, skip
+
+ return (
+ party,
+ party_name,
+ ), skip
+
+ def process_fuzzy_result(self, result: Union[list, None]):
+ """
+ If there are multiple valid close matches return None as result may be faulty.
+ Return the result only if one accurate match stands out.
+
+ Returns: Result, Skip (whether or not to discontinue matching)
+ """
+ PARTY, SCORE, CUTOFF = 0, 1, 80
+
+ if not result or not len(result):
+ return None, False
+
+ first_result = result[0]
+ if len(result) == 1:
+ return (first_result[PARTY] if first_result[SCORE] > CUTOFF else None), True
+
+ second_result = result[1]
+ if first_result[SCORE] > CUTOFF:
+ # If multiple matches with the same score, return None but discontinue matching
+ # Matches were found but were too close to distinguish between
+ if first_result[SCORE] == second_result[SCORE]:
+ return None, True
+
+ return first_result[PARTY], True
+ else:
+ return None, False
+
+
+def get_parties_in_order(deposit: float) -> list:
+ parties = ["Supplier", "Employee", "Customer"] # most -> least likely to receive
+ if flt(deposit) > 0:
+ parties = ["Customer", "Supplier", "Employee"] # most -> least likely to pay
+
+ return parties
diff --git a/erpnext/accounts/doctype/bank_transaction/bank_transaction.json b/erpnext/accounts/doctype/bank_transaction/bank_transaction.json
index 768d2f0..b32022e 100644
--- a/erpnext/accounts/doctype/bank_transaction/bank_transaction.json
+++ b/erpnext/accounts/doctype/bank_transaction/bank_transaction.json
@@ -33,7 +33,11 @@
"unallocated_amount",
"party_section",
"party_type",
- "party"
+ "party",
+ "column_break_3czf",
+ "bank_party_name",
+ "bank_party_account_number",
+ "bank_party_iban"
],
"fields": [
{
@@ -63,7 +67,7 @@
"fieldtype": "Select",
"in_standard_filter": 1,
"label": "Status",
- "options": "\nPending\nSettled\nUnreconciled\nReconciled"
+ "options": "\nPending\nSettled\nUnreconciled\nReconciled\nCancelled"
},
{
"fieldname": "bank_account",
@@ -202,11 +206,30 @@
"fieldtype": "Data",
"label": "Transaction Type",
"length": 50
+ },
+ {
+ "fieldname": "column_break_3czf",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "bank_party_name",
+ "fieldtype": "Data",
+ "label": "Party Name/Account Holder (Bank Statement)"
+ },
+ {
+ "fieldname": "bank_party_iban",
+ "fieldtype": "Data",
+ "label": "Party IBAN (Bank Statement)"
+ },
+ {
+ "fieldname": "bank_party_account_number",
+ "fieldtype": "Data",
+ "label": "Party Account No. (Bank Statement)"
}
],
"is_submittable": 1,
"links": [],
- "modified": "2022-05-29 18:36:50.475964",
+ "modified": "2023-06-06 13:58:12.821411",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Transaction",
@@ -260,4 +283,4 @@
"states": [],
"title_field": "bank_account",
"track_changes": 1
-}
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
index b441af9..f82337f 100644
--- a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
+++ b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
@@ -15,6 +15,9 @@
self.clear_linked_payment_entries()
self.set_status()
+ if frappe.db.get_single_value("Accounts Settings", "enable_party_matching"):
+ self.auto_set_party()
+
_saving_flag = False
# nosemgrep: frappe-semgrep-rules.rules.frappe-modifying-but-not-comitting
@@ -146,6 +149,26 @@
payment_entry.payment_document, payment_entry.payment_entry, clearance_date, self
)
+ def auto_set_party(self):
+ from erpnext.accounts.doctype.bank_transaction.auto_match_party import AutoMatchParty
+
+ if self.party_type and self.party:
+ return
+
+ result = AutoMatchParty(
+ bank_party_account_number=self.bank_party_account_number,
+ bank_party_iban=self.bank_party_iban,
+ bank_party_name=self.bank_party_name,
+ description=self.description,
+ deposit=self.deposit,
+ ).match()
+
+ if result:
+ party_type, party = result
+ frappe.db.set_value(
+ "Bank Transaction", self.name, field={"party_type": party_type, "party": party}
+ )
+
@frappe.whitelist()
def get_doctypes_for_bank_reconciliation():
diff --git a/erpnext/accounts/doctype/bank_transaction/test_auto_match_party.py b/erpnext/accounts/doctype/bank_transaction/test_auto_match_party.py
new file mode 100644
index 0000000..36ef1fc
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_transaction/test_auto_match_party.py
@@ -0,0 +1,151 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import nowdate
+
+from erpnext.accounts.doctype.bank_transaction.test_bank_transaction import create_bank_account
+
+
+class TestAutoMatchParty(FrappeTestCase):
+ @classmethod
+ def setUpClass(cls):
+ create_bank_account()
+ frappe.db.set_single_value("Accounts Settings", "enable_party_matching", 1)
+ frappe.db.set_single_value("Accounts Settings", "enable_fuzzy_matching", 1)
+ return super().setUpClass()
+
+ @classmethod
+ def tearDownClass(cls):
+ frappe.db.set_single_value("Accounts Settings", "enable_party_matching", 0)
+ frappe.db.set_single_value("Accounts Settings", "enable_fuzzy_matching", 0)
+
+ def test_match_by_account_number(self):
+ create_supplier_for_match(account_no="000000003716541159")
+ doc = create_bank_transaction(
+ withdrawal=1200,
+ transaction_id="562213b0ca1bf838dab8f2c6a39bbc3b",
+ account_no="000000003716541159",
+ iban="DE02000000003716541159",
+ )
+
+ self.assertEqual(doc.party_type, "Supplier")
+ self.assertEqual(doc.party, "John Doe & Co.")
+
+ def test_match_by_iban(self):
+ create_supplier_for_match(iban="DE02000000003716541159")
+ doc = create_bank_transaction(
+ withdrawal=1200,
+ transaction_id="c5455a224602afaa51592a9d9250600d",
+ account_no="000000003716541159",
+ iban="DE02000000003716541159",
+ )
+
+ self.assertEqual(doc.party_type, "Supplier")
+ self.assertEqual(doc.party, "John Doe & Co.")
+
+ def test_match_by_party_name(self):
+ create_supplier_for_match(supplier_name="Jackson Ella W.")
+ doc = create_bank_transaction(
+ withdrawal=1200,
+ transaction_id="1f6f661f347ff7b1ea588665f473adb1",
+ party_name="Ella Jackson",
+ iban="DE04000000003716545346",
+ )
+ self.assertEqual(doc.party_type, "Supplier")
+ self.assertEqual(doc.party, "Jackson Ella W.")
+
+ def test_match_by_description(self):
+ create_supplier_for_match(supplier_name="Microsoft")
+ doc = create_bank_transaction(
+ description="Auftraggeber: microsoft payments Buchungstext: msft ..e3006b5hdy. ref. j375979555927627/5536",
+ withdrawal=1200,
+ transaction_id="8df880a2d09c3bed3fea358ca5168c5a",
+ party_name="",
+ )
+ self.assertEqual(doc.party_type, "Supplier")
+ self.assertEqual(doc.party, "Microsoft")
+
+ def test_skip_match_if_multiple_close_results(self):
+ create_supplier_for_match(supplier_name="Adithya Medical & General Stores")
+ create_supplier_for_match(supplier_name="Adithya Medical And General Stores")
+
+ doc = create_bank_transaction(
+ description="Paracetamol Consignment, SINV-0009",
+ withdrawal=24.85,
+ transaction_id="3a1da4ee2dc5a980138d56ef3460cbd9",
+ party_name="Adithya Medical & General",
+ )
+
+ # Mapping is skipped as both Supplier names have the same match score
+ self.assertEqual(doc.party_type, None)
+ self.assertEqual(doc.party, None)
+
+
+def create_supplier_for_match(supplier_name="John Doe & Co.", iban=None, account_no=None):
+ if frappe.db.exists("Supplier", {"supplier_name": supplier_name}):
+ # Update related Bank Account details
+ if not (iban or account_no):
+ return
+
+ frappe.db.set_value(
+ dt="Bank Account",
+ dn={"party": supplier_name},
+ field={"iban": iban, "bank_account_no": account_no},
+ )
+ return
+
+ # Create Supplier and Bank Account for the same
+ supplier = frappe.new_doc("Supplier")
+ supplier.supplier_name = supplier_name
+ supplier.supplier_group = "Services"
+ supplier.supplier_type = "Company"
+ supplier.insert()
+
+ if not frappe.db.exists("Bank", "TestBank"):
+ bank = frappe.new_doc("Bank")
+ bank.bank_name = "TestBank"
+ bank.insert(ignore_if_duplicate=True)
+
+ if not frappe.db.exists("Bank Account", supplier.name + " - " + "TestBank"):
+ bank_account = frappe.new_doc("Bank Account")
+ bank_account.account_name = supplier.name
+ bank_account.bank = "TestBank"
+ bank_account.iban = iban
+ bank_account.bank_account_no = account_no
+ bank_account.party_type = "Supplier"
+ bank_account.party = supplier.name
+ bank_account.insert()
+
+
+def create_bank_transaction(
+ description=None,
+ withdrawal=0,
+ deposit=0,
+ transaction_id=None,
+ party_name=None,
+ account_no=None,
+ iban=None,
+):
+ doc = frappe.new_doc("Bank Transaction")
+ doc.update(
+ {
+ "doctype": "Bank Transaction",
+ "description": description or "1512567 BG/000002918 OPSKATTUZWXXX AT776000000098709837 Herr G",
+ "date": nowdate(),
+ "withdrawal": withdrawal,
+ "deposit": deposit,
+ "currency": "INR",
+ "bank_account": "Checking Account - Citi Bank",
+ "transaction_id": transaction_id,
+ "bank_party_name": party_name,
+ "bank_party_account_number": account_no,
+ "bank_party_iban": iban,
+ }
+ )
+ doc.insert()
+ doc.submit()
+ doc.reload()
+
+ return doc
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 2843824..9f55ba1 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -612,7 +612,7 @@
frm.events.set_unallocated_amount(frm);
},
- get_outstanding_invoice: function(frm) {
+ get_outstanding_invoices_or_orders: function(frm, get_outstanding_invoices, get_orders_to_be_billed) {
const today = frappe.datetime.get_today();
const fields = [
{fieldtype:"Section Break", label: __("Posting Date")},
@@ -642,12 +642,29 @@
{fieldtype:"Check", label: __("Allocate Payment Amount"), fieldname:"allocate_payment_amount", default:1},
];
+ let btn_text = "";
+
+ if (get_outstanding_invoices) {
+ btn_text = "Get Outstanding Invoices";
+ }
+ else if (get_orders_to_be_billed) {
+ btn_text = "Get Outstanding Orders";
+ }
+
frappe.prompt(fields, function(filters){
frappe.flags.allocate_payment_amount = true;
frm.events.validate_filters_data(frm, filters);
frm.doc.cost_center = filters.cost_center;
- frm.events.get_outstanding_documents(frm, filters);
- }, __("Filters"), __("Get Outstanding Documents"));
+ frm.events.get_outstanding_documents(frm, filters, get_outstanding_invoices, get_orders_to_be_billed);
+ }, __("Filters"), __(btn_text));
+ },
+
+ get_outstanding_invoices: function(frm) {
+ frm.events.get_outstanding_invoices_or_orders(frm, true, false);
+ },
+
+ get_outstanding_orders: function(frm) {
+ frm.events.get_outstanding_invoices_or_orders(frm, false, true);
},
validate_filters_data: function(frm, filters) {
@@ -673,7 +690,7 @@
}
},
- get_outstanding_documents: function(frm, filters) {
+ get_outstanding_documents: function(frm, filters, get_outstanding_invoices, get_orders_to_be_billed) {
frm.clear_table("references");
if(!frm.doc.party) {
@@ -697,6 +714,13 @@
args[key] = filters[key];
}
+ if (get_outstanding_invoices) {
+ args["get_outstanding_invoices"] = true;
+ }
+ else if (get_orders_to_be_billed) {
+ args["get_orders_to_be_billed"] = true;
+ }
+
frappe.flags.allocate_payment_amount = filters['allocate_payment_amount'];
return frappe.call({
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.json b/erpnext/accounts/doctype/payment_entry/payment_entry.json
index 3927eca..6224d40 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.json
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.json
@@ -48,7 +48,8 @@
"base_received_amount",
"base_received_amount_after_tax",
"section_break_14",
- "get_outstanding_invoice",
+ "get_outstanding_invoices",
+ "get_outstanding_orders",
"references",
"section_break_34",
"total_allocated_amount",
@@ -356,12 +357,6 @@
"label": "Reference"
},
{
- "depends_on": "eval:doc.docstatus==0",
- "fieldname": "get_outstanding_invoice",
- "fieldtype": "Button",
- "label": "Get Outstanding Invoice"
- },
- {
"fieldname": "references",
"fieldtype": "Table",
"label": "Payment References",
@@ -728,12 +723,24 @@
"fieldname": "section_break_60",
"fieldtype": "Section Break",
"hide_border": 1
+ },
+ {
+ "depends_on": "eval:doc.docstatus==0",
+ "fieldname": "get_outstanding_invoices",
+ "fieldtype": "Button",
+ "label": "Get Outstanding Invoices"
+ },
+ {
+ "depends_on": "eval:doc.docstatus==0",
+ "fieldname": "get_outstanding_orders",
+ "fieldtype": "Button",
+ "label": "Get Outstanding Orders"
}
],
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2023-02-14 04:52:30.478523",
+ "modified": "2023-06-19 11:38:04.387219",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Entry",
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index b6d3e5a..1f23fe1 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -151,6 +151,19 @@
if self.payment_type == "Internal Transfer":
return
+ if self.party_type in ("Customer", "Supplier"):
+ self.validate_allocated_amount_with_latest_data()
+ else:
+ fail_message = _("Row #{0}: Allocated Amount cannot be greater than outstanding amount.")
+ for d in self.get("references"):
+ if (flt(d.allocated_amount)) > 0 and flt(d.allocated_amount) > flt(d.outstanding_amount):
+ frappe.throw(fail_message.format(d.idx))
+
+ # Check for negative outstanding invoices as well
+ if flt(d.allocated_amount) < 0 and flt(d.allocated_amount) < flt(d.outstanding_amount):
+ frappe.throw(fail_message.format(d.idx))
+
+ def validate_allocated_amount_with_latest_data(self):
latest_references = get_outstanding_reference_documents(
{
"posting_date": self.posting_date,
@@ -159,6 +172,8 @@
"payment_type": self.payment_type,
"party": self.party,
"party_account": self.paid_from if self.payment_type == "Receive" else self.paid_to,
+ "get_outstanding_invoices": True,
+ "get_orders_to_be_billed": True,
}
)
@@ -168,7 +183,7 @@
d = frappe._dict(d)
latest_lookup.update({(d.voucher_type, d.voucher_no): d})
- for d in self.get("references").copy():
+ for d in self.get("references"):
latest = latest_lookup.get((d.reference_doctype, d.reference_name))
# The reference has already been fully paid
@@ -183,22 +198,18 @@
):
frappe.throw(
_(
- "{0} {1} has already been partly paid. Please use the 'Get Outstanding Invoice' button to get the latest outstanding amount."
+ "{0} {1} has already been partly paid. Please use the 'Get Outstanding Invoice' or the 'Get Outstanding Orders' button to get the latest outstanding amounts."
).format(d.reference_doctype, d.reference_name)
)
- d.outstanding_amount = latest.outstanding_amount
-
fail_message = _("Row #{0}: Allocated Amount cannot be greater than outstanding amount.")
- if (flt(d.allocated_amount)) > 0:
- if flt(d.allocated_amount) > flt(d.outstanding_amount):
- frappe.throw(fail_message.format(d.idx))
+ if (flt(d.allocated_amount)) > 0 and flt(d.allocated_amount) > flt(latest.outstanding_amount):
+ frappe.throw(fail_message.format(d.idx))
# Check for negative outstanding invoices as well
- if flt(d.allocated_amount) < 0:
- if flt(d.allocated_amount) < flt(d.outstanding_amount):
- frappe.throw(fail_message.format(d.idx))
+ if flt(d.allocated_amount) < 0 and flt(d.allocated_amount) < flt(latest.outstanding_amount):
+ frappe.throw(fail_message.format(d.idx))
def delink_advance_entry_references(self):
for reference in self.references:
@@ -1347,62 +1358,75 @@
condition += " and company = {0}".format(frappe.db.escape(args.get("company")))
common_filter.append(ple.company == args.get("company"))
- outstanding_invoices = get_outstanding_invoices(
- args.get("party_type"),
- args.get("party"),
- args.get("party_account"),
- common_filter=common_filter,
- posting_date=posting_and_due_date,
- min_outstanding=args.get("outstanding_amt_greater_than"),
- max_outstanding=args.get("outstanding_amt_less_than"),
- accounting_dimensions=accounting_dimensions_filter,
- )
-
- outstanding_invoices = split_invoices_based_on_payment_terms(outstanding_invoices)
-
- for d in outstanding_invoices:
- d["exchange_rate"] = 1
- if party_account_currency != company_currency:
- if d.voucher_type in frappe.get_hooks("invoice_doctypes"):
- d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
- elif d.voucher_type == "Journal Entry":
- d["exchange_rate"] = get_exchange_rate(
- party_account_currency, company_currency, d.posting_date
- )
- if d.voucher_type in ("Purchase Invoice"):
- d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "bill_no")
-
- # Get all SO / PO which are not fully billed or against which full advance not paid
- orders_to_be_billed = []
- orders_to_be_billed = get_orders_to_be_billed(
- args.get("posting_date"),
- args.get("party_type"),
- args.get("party"),
- args.get("company"),
- party_account_currency,
- company_currency,
- filters=args,
- )
-
- # Get negative outstanding sales /purchase invoices
+ outstanding_invoices = []
negative_outstanding_invoices = []
- if args.get("party_type") != "Employee" and not args.get("voucher_no"):
- negative_outstanding_invoices = get_negative_outstanding_invoices(
+
+ if args.get("get_outstanding_invoices"):
+ outstanding_invoices = get_outstanding_invoices(
args.get("party_type"),
args.get("party"),
args.get("party_account"),
+ common_filter=common_filter,
+ posting_date=posting_and_due_date,
+ min_outstanding=args.get("outstanding_amt_greater_than"),
+ max_outstanding=args.get("outstanding_amt_less_than"),
+ accounting_dimensions=accounting_dimensions_filter,
+ )
+
+ outstanding_invoices = split_invoices_based_on_payment_terms(outstanding_invoices)
+
+ for d in outstanding_invoices:
+ d["exchange_rate"] = 1
+ if party_account_currency != company_currency:
+ if d.voucher_type in frappe.get_hooks("invoice_doctypes"):
+ d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
+ elif d.voucher_type == "Journal Entry":
+ d["exchange_rate"] = get_exchange_rate(
+ party_account_currency, company_currency, d.posting_date
+ )
+ if d.voucher_type in ("Purchase Invoice"):
+ d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "bill_no")
+
+ # Get negative outstanding sales /purchase invoices
+ if args.get("party_type") != "Employee" and not args.get("voucher_no"):
+ negative_outstanding_invoices = get_negative_outstanding_invoices(
+ args.get("party_type"),
+ args.get("party"),
+ args.get("party_account"),
+ party_account_currency,
+ company_currency,
+ condition=condition,
+ )
+
+ # Get all SO / PO which are not fully billed or against which full advance not paid
+ orders_to_be_billed = []
+ if args.get("get_orders_to_be_billed"):
+ orders_to_be_billed = get_orders_to_be_billed(
+ args.get("posting_date"),
+ args.get("party_type"),
+ args.get("party"),
+ args.get("company"),
party_account_currency,
company_currency,
- condition=condition,
+ filters=args,
)
data = negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
if not data:
+ if args.get("get_outstanding_invoices") and args.get("get_orders_to_be_billed"):
+ ref_document_type = "invoices or orders"
+ elif args.get("get_outstanding_invoices"):
+ ref_document_type = "invoices"
+ elif args.get("get_orders_to_be_billed"):
+ ref_document_type = "orders"
+
frappe.msgprint(
_(
- "No outstanding invoices found for the {0} {1} which qualify the filters you have specified."
- ).format(_(args.get("party_type")).lower(), frappe.bold(args.get("party")))
+ "No outstanding {0} found for the {1} {2} which qualify the filters you have specified."
+ ).format(
+ ref_document_type, _(args.get("party_type")).lower(), frappe.bold(args.get("party"))
+ )
)
return data
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.json b/erpnext/accounts/doctype/sales_invoice/sales_invoice.json
index 7b68dd4..ab4aab3 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.json
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.json
@@ -320,6 +320,7 @@
},
{
"default": "0",
+ "depends_on": "eval: !doc.is_debit_note",
"fieldname": "is_return",
"fieldtype": "Check",
"hide_days": 1,
@@ -1960,6 +1961,7 @@
},
{
"default": "0",
+ "depends_on": "eval: !doc.is_return",
"description": "Issue a debit note with 0 qty against an existing Sales Invoice",
"fieldname": "is_debit_note",
"fieldtype": "Check",
@@ -2155,7 +2157,7 @@
"link_fieldname": "consolidated_invoice"
}
],
- "modified": "2023-06-03 16:22:16.219333",
+ "modified": "2023-06-19 16:02:05.309332",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Sales Invoice",
diff --git a/erpnext/accounts/doctype/shareholder/shareholder.json b/erpnext/accounts/doctype/shareholder/shareholder.json
index e94aea9..e80b057 100644
--- a/erpnext/accounts/doctype/shareholder/shareholder.json
+++ b/erpnext/accounts/doctype/shareholder/shareholder.json
@@ -1,4 +1,5 @@
{
+ "actions": [],
"autoname": "naming_series:",
"creation": "2017-12-25 16:50:53.878430",
"doctype": "DocType",
@@ -111,11 +112,12 @@
"read_only": 1
}
],
- "modified": "2019-11-17 23:24:11.395882",
+ "links": [],
+ "modified": "2023-04-10 22:02:20.406087",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Shareholder",
- "name_case": "Title Case",
+ "naming_rule": "By \"Naming Series\" field",
"owner": "Administrator",
"permissions": [
{
@@ -158,6 +160,7 @@
"search_fields": "folio_no",
"sort_field": "modified",
"sort_order": "DESC",
+ "states": [],
"title_field": "title",
"track_changes": 1
}
\ No newline at end of file
diff --git a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
index 4f7b836..b788a32 100644
--- a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
+++ b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
@@ -20,56 +20,6 @@
default: 'In Location'
},
{
- "fieldname":"filter_based_on",
- "label": __("Period Based On"),
- "fieldtype": "Select",
- "options": ["Fiscal Year", "Date Range"],
- "default": "Fiscal Year",
- "reqd": 1
- },
- {
- "fieldname":"from_date",
- "label": __("Start Date"),
- "fieldtype": "Date",
- "default": frappe.datetime.add_months(frappe.datetime.nowdate(), -12),
- "depends_on": "eval: doc.filter_based_on == 'Date Range'",
- "reqd": 1
- },
- {
- "fieldname":"to_date",
- "label": __("End Date"),
- "fieldtype": "Date",
- "default": frappe.datetime.nowdate(),
- "depends_on": "eval: doc.filter_based_on == 'Date Range'",
- "reqd": 1
- },
- {
- "fieldname":"from_fiscal_year",
- "label": __("Start Year"),
- "fieldtype": "Link",
- "options": "Fiscal Year",
- "default": frappe.defaults.get_user_default("fiscal_year"),
- "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
- "reqd": 1
- },
- {
- "fieldname":"to_fiscal_year",
- "label": __("End Year"),
- "fieldtype": "Link",
- "options": "Fiscal Year",
- "default": frappe.defaults.get_user_default("fiscal_year"),
- "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
- "reqd": 1
- },
- {
- "fieldname":"date_based_on",
- "label": __("Date Based On"),
- "fieldtype": "Select",
- "options": ["Purchase Date", "Available For Use Date"],
- "default": "Purchase Date",
- "reqd": 1
- },
- {
fieldname:"asset_category",
label: __("Asset Category"),
fieldtype: "Link",
@@ -90,21 +40,66 @@
reqd: 1
},
{
+ fieldname:"only_existing_assets",
+ label: __("Only existing assets"),
+ fieldtype: "Check"
+ },
+ {
fieldname:"finance_book",
label: __("Finance Book"),
fieldtype: "Link",
options: "Finance Book",
- depends_on: "eval: doc.filter_by_finance_book == 1",
},
{
- fieldname:"filter_by_finance_book",
- label: __("Filter by Finance Book"),
- fieldtype: "Check"
+ "fieldname": "include_default_book_assets",
+ "label": __("Include Default Book Assets"),
+ "fieldtype": "Check",
+ "default": 1
},
{
- fieldname:"only_existing_assets",
- label: __("Only existing assets"),
- fieldtype: "Check"
+ "fieldname":"filter_based_on",
+ "label": __("Period Based On"),
+ "fieldtype": "Select",
+ "options": ["--Select a period--", "Fiscal Year", "Date Range"],
+ "default": "--Select a period--",
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("Start Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.nowdate(), -12),
+ "depends_on": "eval: doc.filter_based_on == 'Date Range'",
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.nowdate(),
+ "depends_on": "eval: doc.filter_based_on == 'Date Range'",
+ },
+ {
+ "fieldname":"from_fiscal_year",
+ "label": __("Start Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
+ },
+ {
+ "fieldname":"to_fiscal_year",
+ "label": __("End Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
+ },
+ {
+ "fieldname":"date_based_on",
+ "label": __("Date Based On"),
+ "fieldtype": "Select",
+ "options": ["Purchase Date", "Available For Use Date"],
+ "default": "Purchase Date",
+ "depends_on": "eval: doc.filter_based_on == 'Date Range' || doc.filter_based_on == 'Fiscal Year'",
},
]
};
diff --git a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
index 984b3fd..f810819 100644
--- a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
+++ b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
@@ -2,9 +2,11 @@
# For license information, please see license.txt
+from itertools import chain
+
import frappe
from frappe import _
-from frappe.query_builder.functions import Sum
+from frappe.query_builder.functions import IfNull, Sum
from frappe.utils import cstr, flt, formatdate, getdate
from erpnext.accounts.report.financial_statements import (
@@ -13,7 +15,6 @@
validate_fiscal_year,
)
from erpnext.assets.doctype.asset.asset import get_asset_value_after_depreciation
-from erpnext.assets.doctype.asset.depreciation import get_depreciation_accounts
def execute(filters=None):
@@ -64,11 +65,9 @@
def get_data(filters):
-
data = []
conditions = get_conditions(filters)
- depreciation_amount_map = get_finance_book_value_map(filters)
pr_supplier_map = get_purchase_receipt_supplier_map()
pi_supplier_map = get_purchase_invoice_supplier_map()
@@ -102,20 +101,27 @@
]
assets_record = frappe.db.get_all("Asset", filters=conditions, fields=fields)
- assets_linked_to_fb = None
+ assets_linked_to_fb = get_assets_linked_to_fb(filters)
- if filters.filter_by_finance_book:
- assets_linked_to_fb = frappe.db.get_all(
- doctype="Asset Finance Book",
- filters={"finance_book": filters.finance_book or ("is", "not set")},
- pluck="parent",
- )
+ company_fb = frappe.get_cached_value("Company", filters.company, "default_finance_book")
+
+ if filters.include_default_book_assets and company_fb:
+ finance_book = company_fb
+ elif filters.finance_book:
+ finance_book = filters.finance_book
+ else:
+ finance_book = None
+
+ depreciation_amount_map = get_asset_depreciation_amount_map(filters, finance_book)
for asset in assets_record:
if assets_linked_to_fb and asset.asset_id not in assets_linked_to_fb:
continue
- asset_value = get_asset_value_after_depreciation(asset.asset_id, filters.finance_book)
+ asset_value = get_asset_value_after_depreciation(
+ asset.asset_id, finance_book
+ ) or get_asset_value_after_depreciation(asset.asset_id)
+
row = {
"asset_id": asset.asset_id,
"asset_name": asset.asset_name,
@@ -126,7 +132,7 @@
or pi_supplier_map.get(asset.purchase_invoice),
"gross_purchase_amount": asset.gross_purchase_amount,
"opening_accumulated_depreciation": asset.opening_accumulated_depreciation,
- "depreciated_amount": get_depreciation_amount_of_asset(asset, depreciation_amount_map, filters),
+ "depreciated_amount": get_depreciation_amount_of_asset(asset, depreciation_amount_map),
"available_for_use_date": asset.available_for_use_date,
"location": asset.location,
"asset_category": asset.asset_category,
@@ -140,14 +146,23 @@
def prepare_chart_data(data, filters):
labels_values_map = {}
- date_field = frappe.scrub(filters.date_based_on)
+ if filters.filter_based_on not in ("Date Range", "Fiscal Year"):
+ filters_filter_based_on = "Date Range"
+ date_field = "purchase_date"
+ filters_from_date = min(data, key=lambda a: a.get(date_field)).get(date_field)
+ filters_to_date = max(data, key=lambda a: a.get(date_field)).get(date_field)
+ else:
+ filters_filter_based_on = filters.filter_based_on
+ date_field = frappe.scrub(filters.date_based_on)
+ filters_from_date = filters.from_date
+ filters_to_date = filters.to_date
period_list = get_period_list(
filters.from_fiscal_year,
filters.to_fiscal_year,
- filters.from_date,
- filters.to_date,
- filters.filter_based_on,
+ filters_from_date,
+ filters_to_date,
+ filters_filter_based_on,
"Monthly",
company=filters.company,
ignore_fiscal_year=True,
@@ -184,59 +199,76 @@
}
-def get_depreciation_amount_of_asset(asset, depreciation_amount_map, filters):
- if asset.calculate_depreciation:
- depr_amount = depreciation_amount_map.get(asset.asset_id) or 0.0
- else:
- depr_amount = get_manual_depreciation_amount_of_asset(asset, filters)
+def get_assets_linked_to_fb(filters):
+ afb = frappe.qb.DocType("Asset Finance Book")
- return flt(depr_amount, 2)
-
-
-def get_finance_book_value_map(filters):
- date = filters.to_date if filters.filter_based_on == "Date Range" else filters.year_end_date
-
- return frappe._dict(
- frappe.db.sql(
- """ Select
- ads.asset, SUM(depreciation_amount)
- FROM `tabAsset Depreciation Schedule` ads, `tabDepreciation Schedule` ds
- WHERE
- ds.parent = ads.name
- AND ifnull(ads.finance_book, '')=%s
- AND ads.docstatus=1
- AND ds.parentfield='depreciation_schedule'
- AND ds.schedule_date<=%s
- AND ds.journal_entry IS NOT NULL
- GROUP BY ads.asset""",
- (cstr(filters.finance_book or ""), date),
- )
+ query = frappe.qb.from_(afb).select(
+ afb.parent,
)
+ if filters.include_default_book_assets:
+ company_fb = frappe.get_cached_value("Company", filters.company, "default_finance_book")
-def get_manual_depreciation_amount_of_asset(asset, filters):
+ if filters.finance_book and company_fb and cstr(filters.finance_book) != cstr(company_fb):
+ frappe.throw(_("To use a different finance book, please uncheck 'Include Default Book Assets'"))
+
+ query = query.where(
+ (afb.finance_book.isin([cstr(filters.finance_book), cstr(company_fb), ""]))
+ | (afb.finance_book.isnull())
+ )
+ else:
+ query = query.where(
+ (afb.finance_book.isin([cstr(filters.finance_book), ""])) | (afb.finance_book.isnull())
+ )
+
+ assets_linked_to_fb = list(chain(*query.run(as_list=1)))
+
+ return assets_linked_to_fb
+
+
+def get_depreciation_amount_of_asset(asset, depreciation_amount_map):
+ return depreciation_amount_map.get(asset.asset_id) or 0.0
+
+
+def get_asset_depreciation_amount_map(filters, finance_book):
date = filters.to_date if filters.filter_based_on == "Date Range" else filters.year_end_date
- (_, _, depreciation_expense_account) = get_depreciation_accounts(asset)
-
+ asset = frappe.qb.DocType("Asset")
gle = frappe.qb.DocType("GL Entry")
+ aca = frappe.qb.DocType("Asset Category Account")
+ company = frappe.qb.DocType("Company")
- result = (
+ query = (
frappe.qb.from_(gle)
- .select(Sum(gle.debit))
- .where(gle.against_voucher == asset.asset_id)
- .where(gle.account == depreciation_expense_account)
+ .join(asset)
+ .on(gle.against_voucher == asset.name)
+ .join(aca)
+ .on((aca.parent == asset.asset_category) & (aca.company_name == asset.company))
+ .join(company)
+ .on(company.name == asset.company)
+ .select(asset.name.as_("asset"), Sum(gle.debit).as_("depreciation_amount"))
+ .where(
+ gle.account == IfNull(aca.depreciation_expense_account, company.depreciation_expense_account)
+ )
.where(gle.debit != 0)
.where(gle.is_cancelled == 0)
- .where(gle.posting_date <= date)
- ).run()
+ .where(asset.docstatus == 1)
+ .groupby(asset.name)
+ )
- if result and result[0] and result[0][0]:
- depr_amount = result[0][0]
+ if finance_book:
+ query = query.where(
+ (gle.finance_book.isin([cstr(finance_book), ""])) | (gle.finance_book.isnull())
+ )
else:
- depr_amount = 0
+ query = query.where((gle.finance_book.isin([""])) | (gle.finance_book.isnull()))
- return depr_amount
+ if filters.filter_based_on in ("Date Range", "Fiscal Year"):
+ query = query.where(gle.posting_date <= date)
+
+ asset_depr_amount_map = query.run()
+
+ return dict(asset_depr_amount_map)
def get_purchase_receipt_supplier_map():
diff --git a/erpnext/buying/doctype/supplier/supplier.json b/erpnext/buying/doctype/supplier/supplier.json
index 1bf7f58..f009789 100644
--- a/erpnext/buying/doctype/supplier/supplier.json
+++ b/erpnext/buying/doctype/supplier/supplier.json
@@ -457,7 +457,7 @@
"link_fieldname": "party"
}
],
- "modified": "2023-02-18 11:05:50.592270",
+ "modified": "2023-05-09 15:34:13.408932",
"modified_by": "Administrator",
"module": "Buying",
"name": "Supplier",
diff --git a/erpnext/selling/doctype/customer/customer.json b/erpnext/selling/doctype/customer/customer.json
index c133cd3..72a1594 100644
--- a/erpnext/selling/doctype/customer/customer.json
+++ b/erpnext/selling/doctype/customer/customer.json
@@ -568,7 +568,7 @@
"link_fieldname": "party"
}
],
- "modified": "2023-02-18 11:04:46.343527",
+ "modified": "2023-05-09 15:38:40.255193",
"modified_by": "Administrator",
"module": "Selling",
"name": "Customer",
diff --git a/erpnext/selling/page/point_of_sale/point_of_sale.py b/erpnext/selling/page/point_of_sale/point_of_sale.py
index 62b3105..fd23381 100644
--- a/erpnext/selling/page/point_of_sale/point_of_sale.py
+++ b/erpnext/selling/page/point_of_sale/point_of_sale.py
@@ -65,7 +65,7 @@
"item_code": item_code,
"batch_no": batch_no,
},
- fields=["uom", "stock_uom", "currency", "price_list_rate", "batch_no"],
+ fields=["uom", "currency", "price_list_rate", "batch_no"],
)
def __sort(p):
diff --git a/erpnext/setup/doctype/employee/employee.json b/erpnext/setup/doctype/employee/employee.json
index 99693d9..6cb4292 100644
--- a/erpnext/setup/doctype/employee/employee.json
+++ b/erpnext/setup/doctype/employee/employee.json
@@ -78,7 +78,9 @@
"salary_mode",
"bank_details_section",
"bank_name",
+ "column_break_heye",
"bank_ac_no",
+ "iban",
"personal_details",
"marital_status",
"family_background",
@@ -804,17 +806,26 @@
{
"fieldname": "column_break_104",
"fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "column_break_heye",
+ "fieldtype": "Column Break"
+ },
+ {
+ "depends_on": "eval:doc.salary_mode == 'Bank'",
+ "fieldname": "iban",
+ "fieldtype": "Data",
+ "label": "IBAN"
}
],
"icon": "fa fa-user",
"idx": 24,
"image_field": "image",
"links": [],
- "modified": "2022-09-13 10:27:14.579197",
+ "modified": "2023-03-30 15:57:05.174592",
"modified_by": "Administrator",
"module": "Setup",
"name": "Employee",
- "name_case": "Title Case",
"naming_rule": "By \"Naming Series\" field",
"owner": "Administrator",
"permissions": [
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.json b/erpnext/stock/doctype/stock_entry/stock_entry.json
index fe42b1f..564c380 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.json
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.json
@@ -125,7 +125,8 @@
"oldfieldname": "purpose",
"oldfieldtype": "Select",
"options": "Material Issue\nMaterial Receipt\nMaterial Transfer\nMaterial Transfer for Manufacture\nMaterial Consumption for Manufacture\nManufacture\nRepack\nSend to Subcontractor",
- "read_only": 1
+ "read_only": 1,
+ "search_index": 1
},
{
"fieldname": "company",
@@ -678,7 +679,7 @@
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2023-06-16 14:59:10.917235",
+ "modified": "2023-06-19 18:23:40.748114",
"modified_by": "Administrator",
"module": "Stock",
"name": "Stock Entry",
diff --git a/pyproject.toml b/pyproject.toml
index c119ada..012ffb1 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -12,6 +12,7 @@
"pycountry~=22.3.5",
"Unidecode~=1.3.6",
"barcodenumber~=0.5.0",
+ "rapidfuzz~=2.15.0",
# integration dependencies
"gocardless-pro~=1.22.0",