Merge pull request #34675 from frappe/bank-trans-party-automatch
feat: Auto set Party in Bank Transaction
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 b9be5ec..1f23fe1 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -172,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,
}
)
@@ -196,7 +198,7 @@
):
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)
)
@@ -1356,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/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():