feat: Record Advance Payments as Liability
Ability to let user record advance payments as liability instead of a negative asset.
Issue #34282
diff --git a/erpnext/accounts/doctype/party_account/party_account.json b/erpnext/accounts/doctype/party_account/party_account.json
index 6933057..719b474 100644
--- a/erpnext/accounts/doctype/party_account/party_account.json
+++ b/erpnext/accounts/doctype/party_account/party_account.json
@@ -6,7 +6,9 @@
"engine": "InnoDB",
"field_order": [
"company",
- "account"
+ "account",
+ "advances_received_account",
+ "advances_paid_account"
],
"fields": [
{
@@ -22,14 +24,26 @@
"fieldname": "account",
"fieldtype": "Link",
"in_list_view": 1,
- "label": "Account",
+ "label": "Default Account",
+ "options": "Account"
+ },
+ {
+ "fieldname": "advances_received_account",
+ "fieldtype": "Link",
+ "label": "Advances Received Account",
+ "options": "Account"
+ },
+ {
+ "fieldname": "advances_paid_account",
+ "fieldtype": "Link",
+ "label": "Advances Paid Account",
"options": "Account"
}
],
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2022-04-04 12:31:02.994197",
+ "modified": "2023-06-02 13:00:06.885744",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Party Account",
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 2843824..7bd3bb8 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -18,14 +18,20 @@
},
setup: function(frm) {
+ advance_payments_as_liability = frappe.db.get_value("Company", {"company_name": frm.doc.company}, "book_advance_payments_as_liability");
+
+ if(advance_payments_as_liability && frm.doc.payment_type == 'Receive'){
+ account_type = "Payable";
+ }
+ else{
+ account_type = "Receivable";
+ }
+
frm.set_query("paid_from", function() {
frm.events.validate_company(frm);
-
- var account_types = in_list(["Pay", "Internal Transfer"], frm.doc.payment_type) ?
- ["Bank", "Cash"] : [frappe.boot.party_account_types[frm.doc.party_type]];
return {
filters: {
- "account_type": ["in", account_types],
+ "account_type": account_type,
"is_group": 0,
"company": frm.doc.company
}
@@ -74,12 +80,15 @@
frm.set_query("paid_to", function() {
frm.events.validate_company(frm);
-
- var account_types = in_list(["Receive", "Internal Transfer"], frm.doc.payment_type) ?
- ["Bank", "Cash"] : [frappe.boot.party_account_types[frm.doc.party_type]];
+ if(advance_payments_as_liability && in_list(['Receive', 'Internal Transfer'], cur_frm.doc.payment_type)){
+ account_type = ["Bank", "Cash"];
+ }
+ else{
+ account_type = "Receivable";
+ }
return {
filters: {
- "account_type": ["in", account_types],
+ "account_type": ["in", account_type],
"is_group": 0,
"company": frm.doc.company
}
@@ -270,6 +279,25 @@
},
payment_type: function(frm) {
+ advance_payments_as_liability = frappe.db.get_value("Company", {"company_name": frm.doc.company}, "book_advance_payments_as_liability");
+
+ if(advance_payments_as_liability && frm.doc.payment_type == 'Receive'){
+ account_type = ["Payable"];
+ }
+ else{
+ account_type = ["Bank", "Cash"];
+ }
+
+ frm.set_query("paid_from", function() {
+ frm.events.validate_company(frm);
+ return {
+ filters: {
+ "account_type": ["in", account_type],
+ "is_group": 0,
+ "company": frm.doc.company
+ }
+ }
+ });
if(frm.doc.payment_type == "Internal Transfer") {
$.each(["party", "party_balance", "paid_from", "paid_to",
"references", "total_allocated_amount"], function(i, field) {
@@ -318,6 +346,10 @@
}
},
+ company: function(frm){
+ frm.trigger('party');
+ },
+
party: function(frm) {
if (frm.doc.contact_email || frm.doc.contact_person) {
frm.set_value("contact_email", "");
@@ -332,7 +364,7 @@
frm.set_party_account_based_on_party = true;
let company_currency = frappe.get_doc(":Company", frm.doc.company).default_currency;
-
+
return frappe.call({
method: "erpnext.accounts.doctype.payment_entry.payment_entry.get_party_details",
args: {
@@ -340,7 +372,8 @@
party_type: frm.doc.party_type,
party: frm.doc.party,
date: frm.doc.posting_date,
- cost_center: frm.doc.cost_center
+ cost_center: frm.doc.cost_center,
+ is_advance: !(frm.doc.references)
},
callback: function(r, rt) {
if(r.message) {
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 3df48e2..ed2158a 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -1563,13 +1563,12 @@
@frappe.whitelist()
-def get_party_details(company, party_type, party, date, cost_center=None):
+def get_party_details(company, party_type, party, date, cost_center=None, is_advance=False):
bank_account = ""
if not frappe.db.exists(party_type, party):
frappe.throw(_("Invalid {0}: {1}").format(party_type, party))
- party_account = get_party_account(party_type, party, company)
-
+ party_account = get_party_account(party_type, party, company, is_advance)
account_currency = get_account_currency(party_account)
account_balance = get_balance_on(party_account, date, cost_center=cost_center)
_party_name = "title" if party_type == "Shareholder" else party_type.lower() + "_name"
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
index 08d38dd..2e9628c 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
@@ -29,6 +29,26 @@
};
});
+ this.frm.set_query('default_advances_received_account', () => {
+ return {
+ filters: {
+ "company": this.frm.doc.company,
+ "is_group": 0,
+ "root_type": "Liability"
+ }
+ };
+ });
+
+ this.frm.set_query('default_advances_paid_account', () => {
+ return {
+ filters: {
+ "company": this.frm.doc.company,
+ "is_group": 0,
+ "root_type": "Asset"
+ }
+ };
+ });
+
this.frm.set_query('bank_cash_account', () => {
return {
filters:[
@@ -124,7 +144,7 @@
this.frm.trigger("clear_child_tables");
if (!this.frm.doc.receivable_payable_account && this.frm.doc.party_type && this.frm.doc.party) {
- return frappe.call({
+ frappe.call({
method: "erpnext.accounts.party.get_party_account",
args: {
company: this.frm.doc.company,
@@ -136,7 +156,38 @@
this.frm.set_value("receivable_payable_account", r.message);
}
this.frm.refresh();
+ }
+ });
+ frappe.call({
+ method: "erpnext.accounts.party.get_party_account",
+ args: {
+ company: this.frm.doc.company,
+ party_type: this.frm.doc.party_type,
+ party: this.frm.doc.party,
+ is_advance: 1
+ },
+ callback: (r) => {
+ if (!r.exc && r.message) {
+ this.frm.set_value("default_advances_received_account", r.message);
+ }
+ this.frm.refresh();
+ }
+ });
+
+ frappe.call({
+ method: "erpnext.accounts.party.get_party_account",
+ args: {
+ company: this.frm.doc.company,
+ party_type: (this.frm.doc.party_type == 'Customer')?'Supplier':'Customer',
+ party: this.frm.doc.party,
+ is_advance: 1
+ },
+ callback: (r) => {
+ if (!r.exc && r.message) {
+ this.frm.set_value("default_advances_paid_account", r.message);
+ }
+ this.frm.refresh();
}
});
}
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
index 18d3485..21b8392 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
@@ -7,9 +7,11 @@
"field_order": [
"company",
"party_type",
- "column_break_4",
"party",
+ "column_break_4",
"receivable_payable_account",
+ "default_advances_received_account",
+ "default_advances_paid_account",
"col_break1",
"from_invoice_date",
"from_payment_date",
@@ -185,13 +187,30 @@
"fieldtype": "Link",
"label": "Cost Center",
"options": "Cost Center"
+ },
+ {
+ "depends_on": "eval:doc.party_type",
+ "fieldname": "default_advances_received_account",
+ "fieldtype": "Link",
+ "label": "Default Advances Received Account",
+ "mandatory_depends_on": "doc.party_type",
+ "options": "Account",
+ "reqd": 1
+ },
+ {
+ "depends_on": "eval:doc.party_type",
+ "fieldname": "default_advances_paid_account",
+ "fieldtype": "Link",
+ "label": "Default Advances Paid Account",
+ "mandatory_depends_on": "doc.party_type",
+ "options": "Account"
}
],
"hide_toolbar": 1,
"icon": "icon-resize-horizontal",
"issingle": 1,
"links": [],
- "modified": "2022-04-29 15:37:10.246831",
+ "modified": "2023-06-02 14:32:27.276083",
"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 cc2b942..9b03d36 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -19,7 +19,8 @@
reconcile_against_document,
)
from erpnext.controllers.accounts_controller import get_advance_payment_entries
-
+from erpnext.controllers.accounts_controller import make_advance_liability_entry
+from erpnext.accounts.general_ledger import make_gl_entries
class PaymentReconciliation(Document):
def __init__(self, *args, **kwargs):
@@ -56,12 +57,26 @@
self.add_payment_entries(non_reconciled_payments)
def get_payment_entries(self):
+ receivable_payable_account = self.receivable_payable_account
+ default_advances_account = self.default_advances_received_account
+ party_account = [receivable_payable_account, default_advances_account]
order_doctype = "Sales Order" if self.party_type == "Customer" else "Purchase Order"
- condition = self.get_conditions(get_payments=True)
+ condition = frappe._dict(
+ {
+ "company": self.get("company"),
+ "get_payments": True,
+ "cost_center": self.get("cost_center"),
+ "from_payment_date": self.get("from_payment_date"),
+ "to_payment_date": self.get("to_payment_date"),
+ "maximum_payment_amount": self.get("maximum_payment_amount"),
+ "minimum_payment_amount": self.get("minimum_payment_amount")
+ }
+ )
+
payment_entries = get_advance_payment_entries(
- self.party_type,
+ self.party_type,
self.party,
- self.receivable_payable_account,
+ party_account,
order_doctype,
against_all_orders=True,
limit=self.payment_limit,
@@ -319,6 +334,10 @@
for row in self.get("allocation"):
reconciled_entry = []
if row.invoice_number and row.allocated_amount:
+ if row.invoice_type in ["Sales Invoice", "Purchase Invoice"]:
+ gl_entries = []
+ make_advance_liability_entry(gl_entries, row.reference_name, row.allocated_amount, row.invoice_number, self.party_type)
+ make_gl_entries(gl_entries)
if row.reference_type in ["Sales Invoice", "Purchase Invoice"]:
reconciled_entry = dr_or_cr_notes
else:
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 868a150..dfea8e9 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -34,6 +34,7 @@
from erpnext.assets.doctype.asset_category.asset_category import get_asset_category_account
from erpnext.buying.utils import check_on_hold_or_closed_status
from erpnext.controllers.accounts_controller import validate_account_head
+from erpnext.controllers.accounts_controller import make_advance_liability_entry
from erpnext.controllers.buying_controller import BuyingController
from erpnext.stock import get_warehouse_account_map
from erpnext.stock.doctype.purchase_receipt.purchase_receipt import (
@@ -580,6 +581,12 @@
gl_entries = []
self.make_supplier_gl_entry(gl_entries)
+
+ advance_payments_as_liability = frappe.db.get_value("Company", {"company_name": self.company}, "book_advance_payments_as_liability")
+ if advance_payments_as_liability:
+ for advance_entry in self.advances:
+ make_advance_liability_entry(gl_entries, advance_entry.reference_name, advance_entry.allocated_amount, invoice=self.name, party_type="Supplier")
+
self.make_item_gl_entries(gl_entries)
self.make_precision_loss_gl_entry(gl_entries)
diff --git a/erpnext/accounts/doctype/purchase_invoice_advance/purchase_invoice_advance.json b/erpnext/accounts/doctype/purchase_invoice_advance/purchase_invoice_advance.json
index 9fcbf5c..9082115 100644
--- a/erpnext/accounts/doctype/purchase_invoice_advance/purchase_invoice_advance.json
+++ b/erpnext/accounts/doctype/purchase_invoice_advance/purchase_invoice_advance.json
@@ -14,7 +14,8 @@
"advance_amount",
"allocated_amount",
"exchange_gain_loss",
- "ref_exchange_rate"
+ "ref_exchange_rate",
+ "account"
],
"fields": [
{
@@ -111,13 +112,20 @@
"label": "Reference Exchange Rate",
"non_negative": 1,
"read_only": 1
+ },
+ {
+ "fieldname": "account",
+ "fieldtype": "Link",
+ "label": "Account",
+ "options": "Account",
+ "read_only": 1
}
],
"idx": 1,
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2021-09-26 15:47:28.167371",
+ "modified": "2023-06-01 16:56:48.530169",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Purchase Invoice Advance",
@@ -125,5 +133,6 @@
"permissions": [],
"quick_entry": 1,
"sort_field": "modified",
- "sort_order": "DESC"
+ "sort_order": "DESC",
+ "states": []
}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index 7454332..419628e 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -33,6 +33,7 @@
reverse_depreciation_entry_made_after_disposal,
)
from erpnext.controllers.accounts_controller import validate_account_head
+from erpnext.controllers.accounts_controller import make_advance_liability_entry
from erpnext.controllers.selling_controller import SellingController
from erpnext.projects.doctype.timesheet.timesheet import get_projectwise_timesheet_data
from erpnext.setup.doctype.company.company import update_company_current_month_sales
@@ -1064,6 +1065,11 @@
gl_entries = []
self.make_customer_gl_entry(gl_entries)
+
+ advance_payments_as_liability = frappe.db.get_value("Company", {"company_name": self.company}, "book_advance_payments_as_liability")
+ if advance_payments_as_liability:
+ for advance_entry in self.advances:
+ make_advance_liability_entry(gl_entries, advance_entry.reference_name, advance_entry.allocated_amount, invoice=self.name, party_type="Customer")
self.make_tax_gl_entries(gl_entries)
self.make_exchange_gain_loss_gl_entries(gl_entries)
diff --git a/erpnext/accounts/doctype/sales_invoice_advance/sales_invoice_advance.json b/erpnext/accounts/doctype/sales_invoice_advance/sales_invoice_advance.json
index f92b57a..aa52b1c 100644
--- a/erpnext/accounts/doctype/sales_invoice_advance/sales_invoice_advance.json
+++ b/erpnext/accounts/doctype/sales_invoice_advance/sales_invoice_advance.json
@@ -14,7 +14,8 @@
"advance_amount",
"allocated_amount",
"exchange_gain_loss",
- "ref_exchange_rate"
+ "ref_exchange_rate",
+ "account"
],
"fields": [
{
@@ -112,13 +113,20 @@
"label": "Reference Exchange Rate",
"non_negative": 1,
"read_only": 1
+ },
+ {
+ "fieldname": "account",
+ "fieldtype": "Link",
+ "label": "Account",
+ "options": "Account",
+ "read_only": 1
}
],
"idx": 1,
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2021-09-26 15:47:46.911595",
+ "modified": "2023-05-31 11:47:00.191681",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Sales Invoice Advance",
@@ -126,5 +134,6 @@
"permissions": [],
"quick_entry": 1,
"sort_field": "modified",
- "sort_order": "DESC"
+ "sort_order": "DESC",
+ "states": []
}
\ No newline at end of file
diff --git a/erpnext/accounts/party.py b/erpnext/accounts/party.py
index f86dd8f..3be4275 100644
--- a/erpnext/accounts/party.py
+++ b/erpnext/accounts/party.py
@@ -365,7 +365,7 @@
@frappe.whitelist()
-def get_party_account(party_type, party=None, company=None):
+def get_party_account(party_type, party=None, company=None, is_advance=False):
"""Returns the account for the given `party`.
Will first search in party (Customer / Supplier) record, if not found,
will search in group (Customer Group / Supplier Group),
@@ -380,6 +380,11 @@
return frappe.get_cached_value("Company", company, default_account_name)
+ advance_payments_as_liability = frappe.db.get_value("Company", {"company_name": company}, "book_advance_payments_as_liability")
+
+ if is_advance and advance_payments_as_liability and party_type in ["Customer", "Supplier"]:
+ return get_party_advance_account(party_type, party, company)
+
account = frappe.db.get_value(
"Party Account", {"parenttype": party_type, "parent": party, "company": company}, "account"
)
@@ -409,6 +414,26 @@
return account
+def get_party_advance_account(party_type, party, company):
+ account_name = 'advances_received_account' if party_type == 'Customer' else 'advances_paid_account'
+ account = frappe.db.get_value(
+ "Party Account", {"parenttype": party_type, "parent": party, "company": company}, account_name
+ )
+
+ if not account:
+ party_group_doctype = "Customer Group" if party_type == "Customer" else "Supplier Group"
+ group = frappe.get_cached_value(party_type, party, scrub(party_group_doctype))
+ account = frappe.db.get_value(
+ "Party Account",
+ {"parenttype": party_group_doctype, "parent": group, "company": company},
+ account_name,
+ )
+
+ if not account:
+ account = frappe.get_cached_value("Company", company, "default_" + account_name)
+
+ return account
+
@frappe.whitelist()
def get_party_bank_account(party_type, party):
return frappe.db.get_value(
@@ -515,7 +540,10 @@
)
# validate if account is mapped for same company
- validate_account_head(account.idx, account.account, account.company)
+ if account.account:
+ validate_account_head(account.idx, account.account, account.company)
+ if account.advance_account:
+ validate_account_head(account.idx, account.advance_account, account.company)
@frappe.whitelist()
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 0ee06e8..5abc643 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -495,50 +495,51 @@
ret = None
if args.voucher_type == "Journal Entry":
- ret = frappe.db.sql(
- """
- select t2.{dr_or_cr} from `tabJournal Entry` t1, `tabJournal Entry Account` t2
- where t1.name = t2.parent and t2.account = %(account)s
- and t2.party_type = %(party_type)s and t2.party = %(party)s
- and (t2.reference_type is null or t2.reference_type in ('', 'Sales Order', 'Purchase Order'))
- and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
- and t1.docstatus=1 """.format(
- dr_or_cr=args.get("dr_or_cr")
- ),
- args,
+ journal_entry = frappe.qb.DocType("Journal Entry")
+ journal_acc = frappe.qb.DocType("Journal Entry Account")
+
+ q = (frappe.qb.from_(journal_entry)
+ .innerjoin(journal_acc)
+ .on(journal_entry.name == journal_acc.parent)
)
+
+ if args.get("dr_or_cr") == 'debit_in_account_currency':
+ q = q.select(journal_acc.debit_in_account_currency)
+ else:
+ q = q.select(journal_acc.credit_in_account_currency)
+
+ q = q.where((journal_acc.account == args.get("account"))
+ &((journal_acc.party_type == args.get("party_type")))
+ &((journal_acc.party == args.get("party")))
+ &((journal_acc.reference_type == None) | (journal_acc.reference_type.isin(['', 'Sales Order', 'Purchase Order'])))
+ &((journal_entry.name == args.get("voucher_no")))
+ &((journal_acc.name == args.get("voucher_detail_no")))
+ &((journal_entry.docstatus == 1))
+ )
+
else:
- party_account_field = (
- "paid_from" if erpnext.get_party_account_type(args.party_type) == "Receivable" else "paid_to"
+ payment_entry = frappe.qb.DocType("Payment Entry")
+ payment_ref = frappe.qb.DocType("Payment Entry Reference")
+
+ q = (frappe.qb.from_(payment_entry)
+ .select(payment_entry.name)
+ .where(payment_entry.name == args.get("voucher_no"))
+ .where(payment_entry.docstatus == 1)
+ .where(payment_entry.party_type == args.get("party_type"))
+ .where(payment_entry.party == args.get("party"))
)
if args.voucher_detail_no:
- ret = frappe.db.sql(
- """select t1.name
- from `tabPayment Entry` t1, `tabPayment Entry Reference` t2
- where
- t1.name = t2.parent and t1.docstatus = 1
- and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
- and t1.party_type = %(party_type)s and t1.party = %(party)s and t1.{0} = %(account)s
- and t2.reference_doctype in ('', 'Sales Order', 'Purchase Order')
- and t2.allocated_amount = %(unreconciled_amount)s
- """.format(
- party_account_field
- ),
- args,
- )
+ q = ( q.inner_join(payment_ref)
+ .on(payment_entry.name == payment_ref.parent)
+ .where(payment_ref.name == args.get("voucher_detail_no"))
+ .where(payment_ref.reference_doctype.isin(('', 'Sales Order', 'Purchase Order')))
+ .where(payment_ref.allocated_amount == args.get("unreconciled_amount"))
+ )
else:
- ret = frappe.db.sql(
- """select name from `tabPayment Entry`
- where
- name = %(voucher_no)s and docstatus = 1
- and party_type = %(party_type)s and party = %(party)s and {0} = %(account)s
- and unallocated_amount = %(unreconciled_amount)s
- """.format(
- party_account_field
- ),
- args,
- )
+ q = q.where(payment_entry.unallocated_amount == args.get("unreconciled_amount"))
+
+ ret = q.run(as_dict=True)
if not ret:
throw(_("""Payment Entry has been modified after you pulled it. Please pull it again."""))
diff --git a/erpnext/buying/doctype/supplier/supplier.js b/erpnext/buying/doctype/supplier/supplier.js
index 1ae6f03..9e217b5 100644
--- a/erpnext/buying/doctype/supplier/supplier.js
+++ b/erpnext/buying/doctype/supplier/supplier.js
@@ -17,6 +17,29 @@
}
}
});
+
+ frm.set_query('advances_received_account', 'accounts', function (doc, cdt, cdn) {
+ var d = locals[cdt][cdn];
+ return {
+ filters: {
+ "root_type": 'Asset',
+ "company": d.company,
+ "is_group": 0
+ }
+ }
+ });
+
+ frm.set_query('advances_paid_account', 'accounts', function (doc, cdt, cdn) {
+ var d = locals[cdt][cdn];
+ return {
+ filters: {
+ "root_type": 'Liability',
+ "company": d.company,
+ "is_group": 0
+ }
+ }
+ });
+
frm.set_query("default_bank_account", function() {
return {
filters: {
diff --git a/erpnext/buying/doctype/supplier/supplier.json b/erpnext/buying/doctype/supplier/supplier.json
index 1bf7f58..86e5eee 100644
--- a/erpnext/buying/doctype/supplier/supplier.json
+++ b/erpnext/buying/doctype/supplier/supplier.json
@@ -53,6 +53,7 @@
"primary_address",
"accounting_tab",
"payment_terms",
+ "default_accounts_section",
"accounts",
"settings_tab",
"allow_purchase_invoice_creation_without_purchase_order",
@@ -445,6 +446,11 @@
{
"fieldname": "column_break_59",
"fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "default_accounts_section",
+ "fieldtype": "Section Break",
+ "label": "Default Accounts"
}
],
"icon": "fa fa-user",
@@ -457,7 +463,7 @@
"link_fieldname": "party"
}
],
- "modified": "2023-02-18 11:05:50.592270",
+ "modified": "2023-05-29 15:23:11.709415",
"modified_by": "Administrator",
"module": "Buying",
"name": "Supplier",
@@ -489,7 +495,6 @@
"read": 1,
"report": 1,
"role": "Purchase Master Manager",
- "set_user_permissions": 1,
"share": 1,
"write": 1
},
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index 20b332e..ac32fd3 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -8,6 +8,8 @@
from frappe import _, bold, throw
from frappe.model.workflow import get_workflow_name, is_transition_condition_satisfied
from frappe.query_builder.functions import Abs, Sum
+from frappe.query_builder.custom import ConstantColumn
+
from frappe.utils import (
add_days,
add_months,
@@ -871,24 +873,30 @@
"allocated_amount": allocated_amount,
"ref_exchange_rate": flt(d.exchange_rate), # exchange_rate of advance entry
}
+ if d.get("paid_from"):
+ advance_row["account"] = d.paid_from
+ if d.get("paid_to"):
+ advance_row["account"] = d.paid_to
self.append("advances", advance_row)
def get_advance_entries(self, include_unallocated=True):
if self.doctype == "Sales Invoice":
- party_account = self.debit_to
party_type = "Customer"
party = self.customer
amount_field = "credit_in_account_currency"
order_field = "sales_order"
order_doctype = "Sales Order"
+ party_account = frappe.db.get_values("Company", {"company_name": self.company}, ["default_receivable_account", "default_advances_received_account"])
else:
- party_account = self.credit_to
party_type = "Supplier"
party = self.supplier
amount_field = "debit_in_account_currency"
order_field = "purchase_order"
order_doctype = "Purchase Order"
+ party_account = frappe.db.get_values("Company", {"company_name": self.company}, ["default_receivable_account", "default_advances_paid_account"])
+
+ party_account = list(party_account[0])
order_list = list(set(d.get(order_field) for d in self.get("items") if d.get(order_field)))
@@ -2136,45 +2144,41 @@
order_list,
include_unallocated=True,
):
- dr_or_cr = (
- "credit_in_account_currency" if party_type == "Customer" else "debit_in_account_currency"
- )
-
- conditions = []
- if include_unallocated:
- conditions.append("ifnull(t2.reference_name, '')=''")
+ journal_entry = frappe.qb.DocType('Journal Entry')
+ journal_acc = frappe.qb.DocType('Journal Entry Account')
+ q = (frappe.qb.from_(journal_entry)
+ .inner_join(journal_acc)
+ .on(journal_entry.name == journal_acc.parent)
+ .select(
+ ConstantColumn('Journal Entry').as_('reference_type'),
+ (journal_entry.name).as_('reference_name'),
+ (journal_entry.remark).as_('remarks'),
+ (journal_acc.debit_in_account_currency if party_type == 'Supplier' else journal_acc.credit_in_account_currency).as_('amount'),
+ (journal_acc.name).as_('reference_row'),
+ (journal_acc.reference_name).as_('against_order'),
+ (journal_acc.exchange_rate)
+ )
+ .where(journal_acc.account.isin(party_account)
+ & (journal_acc.party_type == party_type)
+ & (journal_acc.party == party)
+ & (journal_acc.is_advance == 'Yes')
+ & (journal_entry.docstatus == 1)
+ )
+ )
+ if party_type == "Customer":
+ q = q.where(journal_acc.credit_in_account_currency > 0)
if order_list:
- order_condition = ", ".join(["%s"] * len(order_list))
- conditions.append(
- " (t2.reference_type = '{0}' and ifnull(t2.reference_name, '') in ({1}))".format(
- order_doctype, order_condition
- )
- )
+ q = q.where(journal_acc.reference_type == order_doctype)
+ if include_unallocated:
+ q = q.where(journal_acc.reference_name.isin(order_list)
+ |(journal_acc.reference_name == ''))
+ else:
+ q = q.where(journal_acc.reference_name.isin(order_list))
- reference_condition = " and (" + " or ".join(conditions) + ")" if conditions else ""
+ q = q.orderby(journal_entry.posting_date)
- # nosemgrep
- journal_entries = frappe.db.sql(
- """
- select
- 'Journal Entry' as reference_type, t1.name as reference_name,
- t1.remark as remarks, t2.{0} as amount, t2.name as reference_row,
- t2.reference_name as against_order, t2.exchange_rate
- from
- `tabJournal Entry` t1, `tabJournal Entry Account` t2
- where
- t1.name = t2.parent and t2.account = %s
- and t2.party_type = %s and t2.party = %s
- and t2.is_advance = 'Yes' and t1.docstatus = 1
- and {1} > 0 {2}
- order by t1.posting_date""".format(
- amount_field, dr_or_cr, reference_condition
- ),
- [party_account, party_type, party] + order_list,
- as_dict=1,
- )
-
+ journal_entries = q.run(as_dict=True)
return list(journal_entries)
@@ -2189,65 +2193,76 @@
limit=None,
condition=None,
):
- party_account_field = "paid_from" if party_type == "Customer" else "paid_to"
- currency_field = (
- "paid_from_account_currency" if party_type == "Customer" else "paid_to_account_currency"
- )
+
+ q = build_query(party_type, party, party_account, order_doctype, order_list, include_unallocated, against_all_orders, limit, condition)
+
+ payment_entries = q.run(as_dict=True)
+
+ return list(payment_entries)
+
+def build_query(party_type, party, party_account, order_doctype, order_list, include_unallocated, against_all_orders, limit, condition):
payment_type = "Receive" if party_type == "Customer" else "Pay"
- exchange_rate_field = (
- "source_exchange_rate" if payment_type == "Receive" else "target_exchange_rate"
- )
+ payment_entry = frappe.qb.DocType('Payment Entry')
+ payment_ref = frappe.qb.DocType('Payment Entry Reference')
- payment_entries_against_order, unallocated_payment_entries = [], []
- limit_cond = "limit %s" % limit if limit else ""
-
- if order_list or against_all_orders:
- if order_list:
- reference_condition = " and t2.reference_name in ({0})".format(
- ", ".join(["%s"] * len(order_list))
- )
- else:
- reference_condition = ""
- order_list = []
-
- payment_entries_against_order = frappe.db.sql(
- """
- select
- 'Payment Entry' as reference_type, t1.name as reference_name,
- t1.remarks, t2.allocated_amount as amount, t2.name as reference_row,
- t2.reference_name as against_order, t1.posting_date,
- t1.{0} as currency, t1.{4} as exchange_rate
- from `tabPayment Entry` t1, `tabPayment Entry Reference` t2
- where
- t1.name = t2.parent and t1.{1} = %s and t1.payment_type = %s
- and t1.party_type = %s and t1.party = %s and t1.docstatus = 1
- and t2.reference_doctype = %s {2}
- order by t1.posting_date {3}
- """.format(
- currency_field, party_account_field, reference_condition, limit_cond, exchange_rate_field
- ),
- [party_account, payment_type, party_type, party, order_doctype] + order_list,
- as_dict=1,
+ q = (frappe.qb.from_(payment_entry)
+ .select(
+ ConstantColumn('Payment Entry').as_('reference_type'),
+ (payment_entry.name).as_('reference_name'),
+ payment_entry.posting_date,
+ (payment_entry.remarks).as_('remarks')
)
+ .where(payment_entry.payment_type == payment_type)
+ .where(payment_entry.party_type == party_type)
+ .where(payment_entry.party == party)
+ .where(payment_entry.docstatus == 1)
+ )
+
+ if party_type == "Customer":
+ q = q.select(payment_entry.paid_from_account_currency)
+ q = q.select(payment_entry.paid_from)
+ q = q.where(payment_entry.paid_from.isin(party_account))
+ else:
+ q = q.select(payment_entry.paid_to_account_currency)
+ q = q.select(payment_entry.paid_to)
+ q = q.where(payment_entry.paid_to.isin(party_account))
+
+ if payment_type == "Receive":
+ q = q.select(payment_entry.source_exchange_rate)
+ else:
+ q.select(payment_entry.target_exchange_rate)
if include_unallocated:
- unallocated_payment_entries = frappe.db.sql(
- """
- select 'Payment Entry' as reference_type, name as reference_name, posting_date,
- remarks, unallocated_amount as amount, {2} as exchange_rate, {3} as currency
- from `tabPayment Entry`
- where
- {0} = %s and party_type = %s and party = %s and payment_type = %s
- and docstatus = 1 and unallocated_amount > 0 {condition}
- order by posting_date {1}
- """.format(
- party_account_field, limit_cond, exchange_rate_field, currency_field, condition=condition or ""
- ),
- (party_account, party_type, party, payment_type),
- as_dict=1,
- )
+ q = q.select((payment_entry.unallocated_amount).as_('amount'))
+ q = q.where(payment_entry.unallocated_amount>0)
- return list(payment_entries_against_order) + list(unallocated_payment_entries)
+ if condition:
+ 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
+ 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
+
+ elif order_list or against_all_orders:
+ q = q.inner_join(payment_ref).on(payment_entry.name == payment_ref.parent)
+ q = q.select(
+ (payment_ref.allocated_amount).as_('amount'),
+ (payment_ref.name).as_('reference_row'),
+ (payment_ref.reference_name).as_('against_order'),
+ payment_ref.reference_doctype == order_doctype
+ )
+
+ if order_list:
+ q = q.where(payment_ref.reference_name.isin(order_list))
+
+ q = q.orderby(payment_entry.posting_date)
+ q = q.limit(limit) if limit else q
+ return q
def update_invoice_status():
@@ -2846,3 +2861,72 @@
@erpnext.allow_regional
def validate_einvoice_fields(doc):
pass
+
+def make_advance_liability_entry(gl_entries, pe, allocated_amount, invoice, party_type):
+ pe = frappe.get_doc("Payment Entry", pe)
+ if party_type=="Customer":
+ invoice = frappe.get_doc("Sales Invoice", invoice)
+ account = pe.paid_from
+ dr_or_cr = "debit"
+ rev = "credit"
+ against = invoice.debit_to
+ party = invoice.customer
+ voucher_type = "Sales Invoice"
+ else:
+ invoice = frappe.get_doc("Purchase Invoice", invoice)
+ account = pe.paid_to
+ dr_or_cr = "credit"
+ rev = "debit"
+ against = invoice.credit_to
+ party = invoice.supplier
+ voucher_type = "Purchase Invoice"
+ gl_entries.append(invoice.get_gl_dict(
+ {
+ "account": account,
+ "party_type": party_type,
+ "party": party,
+ "due_date": invoice.due_date,
+ "against": against,
+ dr_or_cr: allocated_amount,
+ dr_or_cr + "_in_account_currency": allocated_amount,
+ rev: 0,
+ rev + "_in_account_currency": 0,
+ "against_voucher": invoice.return_against
+ if cint(invoice.is_return) and invoice.return_against
+ else invoice.name,
+ "against_voucher_type": invoice.doctype,
+ "cost_center": invoice.cost_center,
+ "project": invoice.project,
+ "voucher_type": voucher_type,
+ "voucher_no": invoice.name
+ },
+ invoice.party_account_currency,
+ item=invoice,
+ ))
+
+ (dr_or_cr, rev) = ("credit", "debit") if party_type=="Customer" else ("debit", "credit")
+ gl_entries.append(invoice.get_gl_dict(
+ {
+ "account": against,
+ "party_type": party_type,
+ "party": party,
+ "due_date": invoice.due_date,
+ "against": account,
+ dr_or_cr: allocated_amount,
+ dr_or_cr + "_in_account_currency": allocated_amount,
+ rev: 0,
+ rev + "_in_account_currency": 0,
+ "against_voucher": invoice.return_against
+ if cint(invoice.is_return) and invoice.return_against
+ else invoice.name,
+ "against_voucher_type": invoice.doctype,
+ "cost_center": invoice.cost_center,
+ "project": invoice.project,
+ "voucher_type": voucher_type,
+ "voucher_no": invoice.name
+ },
+ invoice.party_account_currency,
+ item=invoice,
+ ))
+
+
diff --git a/erpnext/selling/doctype/customer/customer.js b/erpnext/selling/doctype/customer/customer.js
index b53f339..6dac692 100644
--- a/erpnext/selling/doctype/customer/customer.js
+++ b/erpnext/selling/doctype/customer/customer.js
@@ -34,6 +34,28 @@
filters: filters
}
});
+
+ frm.set_query('advances_received_account', 'accounts', function (doc, cdt, cdn) {
+ var d = locals[cdt][cdn];
+ return {
+ filters: {
+ "root_type": 'Liability',
+ "company": d.company,
+ "is_group": 0
+ }
+ }
+ });
+
+ frm.set_query('advances_paid_account', 'accounts', function (doc, cdt, cdn) {
+ var d = locals[cdt][cdn];
+ return {
+ filters: {
+ "root_type": 'Asset',
+ "company": d.company,
+ "is_group": 0
+ }
+ }
+ });
if (frm.doc.__islocal == 1) {
frm.set_value("represents_company", "");
diff --git a/erpnext/selling/doctype/customer/customer.json b/erpnext/selling/doctype/customer/customer.json
index c133cd3..0050279 100644
--- a/erpnext/selling/doctype/customer/customer.json
+++ b/erpnext/selling/doctype/customer/customer.json
@@ -334,13 +334,13 @@
{
"fieldname": "default_receivable_accounts",
"fieldtype": "Section Break",
- "label": "Default Receivable Accounts"
+ "label": "Default Accounts"
},
{
"description": "Mention if a non-standard receivable account",
"fieldname": "accounts",
"fieldtype": "Table",
- "label": "Receivable Accounts",
+ "label": "Accounts",
"options": "Party Account"
},
{
@@ -568,7 +568,7 @@
"link_fieldname": "party"
}
],
- "modified": "2023-02-18 11:04:46.343527",
+ "modified": "2023-05-29 14:29:17.789578",
"modified_by": "Administrator",
"module": "Selling",
"name": "Customer",
@@ -607,7 +607,6 @@
"read": 1,
"report": 1,
"role": "Sales Master Manager",
- "set_user_permissions": 1,
"share": 1,
"write": 1
},
diff --git a/erpnext/setup/doctype/company/company.js b/erpnext/setup/doctype/company/company.js
index e50ce44..81919af 100644
--- a/erpnext/setup/doctype/company/company.js
+++ b/erpnext/setup/doctype/company/company.js
@@ -226,7 +226,9 @@
["capital_work_in_progress_account", {"account_type": "Capital Work in Progress"}],
["asset_received_but_not_billed", {"account_type": "Asset Received But Not Billed"}],
["unrealized_profit_loss_account", {"root_type": ["in", ["Liability", "Asset"]]}],
- ["default_provisional_account", {"root_type": ["in", ["Liability", "Asset"]]}]
+ ["default_provisional_account", {"root_type": ["in", ["Liability", "Asset"]]}],
+ ["default_advances_received_account", {"root_type": "Liability"}],
+ ["default_advances_paid_account", {"root_type": "Asset"}],
], function(i, v) {
erpnext.company.set_custom_query(frm, v);
});
diff --git a/erpnext/setup/doctype/company/company.json b/erpnext/setup/doctype/company/company.json
index f087d99..369e139 100644
--- a/erpnext/setup/doctype/company/company.json
+++ b/erpnext/setup/doctype/company/company.json
@@ -70,6 +70,11 @@
"payment_terms",
"cost_center",
"default_finance_book",
+ "advance_payments_section",
+ "book_advance_payments_as_liability",
+ "default_advances_received_account",
+ "column_break_cui0",
+ "default_advances_paid_account",
"auto_accounting_for_stock_settings",
"enable_perpetual_inventory",
"enable_provisional_accounting_for_non_stock_items",
@@ -694,6 +699,37 @@
"label": "Default Provisional Account",
"no_copy": 1,
"options": "Account"
+ },
+ {
+ "default": "0",
+ "fieldname": "book_advance_payments_as_liability",
+ "fieldtype": "Check",
+ "label": "Book Advance Payments as Liability"
+ },
+ {
+ "fieldname": "advance_payments_section",
+ "fieldtype": "Section Break",
+ "label": "Advance Payments"
+ },
+ {
+ "depends_on": "eval:doc.book_advance_payments_as_liability",
+ "fieldname": "default_advances_received_account",
+ "fieldtype": "Link",
+ "label": "Default Advances Received Account",
+ "mandatory_depends_on": "book_advance_payments_as_liability",
+ "options": "Account"
+ },
+ {
+ "depends_on": "eval:doc.book_advance_payments_as_liability",
+ "fieldname": "default_advances_paid_account",
+ "fieldtype": "Link",
+ "label": "Default Advances Paid Account",
+ "mandatory_depends_on": "book_advance_payments_as_liability",
+ "options": "Account"
+ },
+ {
+ "fieldname": "column_break_cui0",
+ "fieldtype": "Column Break"
}
],
"icon": "fa fa-building",
@@ -701,7 +737,7 @@
"image_field": "company_logo",
"is_tree": 1,
"links": [],
- "modified": "2022-08-16 16:09:02.327724",
+ "modified": "2023-06-02 13:11:41.939016",
"modified_by": "Administrator",
"module": "Setup",
"name": "Company",
diff --git a/erpnext/setup/doctype/customer_group/customer_group.js b/erpnext/setup/doctype/customer_group/customer_group.js
index 44a5019..ef556c7 100644
--- a/erpnext/setup/doctype/customer_group/customer_group.js
+++ b/erpnext/setup/doctype/customer_group/customer_group.js
@@ -30,9 +30,31 @@
var d = locals[cdt][cdn];
return {
filters: {
- 'account_type': 'Receivable',
- 'company': d.company,
+ "account_type": 'Receivable',
+ "company": d.company,
"is_group": 0
}
}
}
+
+cur_frm.fields_dict['accounts'].grid.get_field('advances_received_account').get_query = function(doc, cdt, cdn) {
+ var d = locals[cdt][cdn];
+ return {
+ filters: {
+ "root_type": 'Liability',
+ "company": d.company,
+ "is_group": 0
+ }
+ }
+}
+
+cur_frm.fields_dict['accounts'].grid.get_field('advances_paid_account').get_query = function(doc, cdt, cdn) {
+ var d = locals[cdt][cdn];
+ return {
+ filters: {
+ "root_type": 'Asset',
+ "company": d.company,
+ "is_group": 0
+ }
+ }
+}
\ No newline at end of file
diff --git a/erpnext/setup/doctype/customer_group/customer_group.json b/erpnext/setup/doctype/customer_group/customer_group.json
index d6a431e..4c36bc7 100644
--- a/erpnext/setup/doctype/customer_group/customer_group.json
+++ b/erpnext/setup/doctype/customer_group/customer_group.json
@@ -113,7 +113,7 @@
{
"fieldname": "default_receivable_account",
"fieldtype": "Section Break",
- "label": "Default Receivable Account"
+ "label": "Default Accounts"
},
{
"depends_on": "eval:!doc.__islocal",
@@ -139,7 +139,7 @@
"idx": 1,
"is_tree": 1,
"links": [],
- "modified": "2022-12-24 11:15:17.142746",
+ "modified": "2023-06-02 13:40:34.435822",
"modified_by": "Administrator",
"module": "Setup",
"name": "Customer Group",
@@ -171,7 +171,6 @@
"read": 1,
"report": 1,
"role": "Sales Master Manager",
- "set_user_permissions": 1,
"share": 1,
"write": 1
},