The best way to record an advance payment to a supplier / customer is that it is recorded in a separate ledger (e.g. Advance to Suppliers) and not in the regular payable ledger (e.g. Trade Payables). The reason for this is that Notes to the financial statements need to separately show advances to suppliers. These should not be net-off.
Now, the issue is that ERPNext only allows to use Trade Payable ledger for recording advance payment. If any other ledger account is used (I tried with both Payment Entry and Journal Entry), then the advance is not pulled in the Purchase Invoice.
In my humble opinion this issue needs to be urgently addressed as it is hampering the reporting.
I have setup a seperate ledger; thats not an issue at all. In order to meet system requirements i had choosen account type as payable. Payment is booked in this account.
But issue is that when you call advance payments in subsequent purchase invoices; payments recorded in this ledger do not show up. Only advance payments booked in trade payables ledger show up in purchase invoice.
As for report, in a trial balance this ledger would separately appear. Similarly on balance sheet this ledger would separate appear like all other ledgers. As i mentioned earlier financial statements prepared under accounting standards require advances to suppliers/customers to appear separately and not net off from trade payable/ receivable. This requirement is easily met by using a seperate ledger. No issues here.
The problem arises when advances booked in this separate ledger (other than trade payable/receivable standard ledger) are not pulled in the purchase / sales invoice.
tested, the party_account filter condition in erpnext.controllers.accounts_controller.get_advance_payment_entries method can be / need to be removed as below
def get_advance_payment_entries(party_type, party, party_account, order_doctype,
order_list=None, include_unallocated=True, against_all_orders=False, limit=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"
payment_type = "Receive" if party_type == "Customer" else "Pay"
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
from `tabPayment Entry` t1, `tabPayment Entry Reference` t2
where
t1.name = t2.parent and t1.payment_type = %s
and t1.party_type = %s and t1.party = %s and t1.docstatus = 1
and t2.reference_doctype = %s {1}
order by t1.posting_date {2}
""".format(currency_field, reference_condition, limit_cond),
[payment_type, party_type, party,
order_doctype] + order_list, as_dict=1)
if include_unallocated:
unallocated_payment_entries = frappe.db.sql("""
select "Payment Entry" as reference_type, name as reference_name,
remarks, unallocated_amount as amount
from `tabPayment Entry`
where
{0} = %s and party_type = %s and party = %s and payment_type = %s
and docstatus = 1 and unallocated_amount > 0
order by posting_date {1}
""".format(party_account_field, limit_cond), (party_account, party_type, party, payment_type), as_dict=1)
return list(payment_entries_against_order) + list(unallocated_payment_entries)