Payment Reconciliation changes for Payment Entry
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index a1df702..0e5d8e7 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -21,13 +21,17 @@
self.party_account_field = None
self.party_account = None
+ self.party_account_currency = None
if self.payment_type == "Receive":
self.party_account_field = "paid_from"
self.party_account = self.paid_from
+ self.party_account_currency = self.paid_from_account_currency
+
elif self.payment_type == "Pay":
self.party_account_field = "paid_to"
self.party_account = self.paid_to
+ self.party_account_currency = self.paid_to_account_currency
def validate(self):
self.set_missing_values()
@@ -80,7 +84,25 @@
acc = get_account_currency_and_balance(self.paid_to, self.posting_date)
self.paid_to_account_currency = acc.account_currency
self.paid_to_account_balance = acc.account_balance
-
+
+ self.party_account_currency = self.paid_from_account_currency \
+ if self.payment_type=="Receive" else self.paid_to_account_currency
+
+ for d in self.get("references"):
+ if d.allocated_amount and d.reference_doctype in ("Sales Invoice", "Purchase Invoice"):
+ ref_doc = frappe.db.get_value(d.reference_doctype, d.reference_name, ["grand_total",
+ "base_grand_total", "outstanding_amount", "conversion_rate", "due_date"], as_dict=1)
+
+ d.outstanding_amount = ref_doc.outstanding_amount
+ d.due_date = ref_doc.due_date
+
+ if self.party_account_currency == self.company_currency:
+ d.total_amount = ref_doc.base_grand_total
+ d.exchange_rate = 1
+ else:
+ d.total_amount = ref_doc.grand_total
+ d.exchange_rate = ref_doc.conversion_rate
+
def validate_party_details(self):
if self.party:
if not frappe.db.exists(self.party_type, self.party):
@@ -165,6 +187,7 @@
self.total_allocated_amount, self.base_total_allocated_amount = 0, 0
for d in self.get("references"):
if d.allocated_amount:
+ print d.reference_name, d.outstanding_amount, d.allocated_amount
if d.allocated_amount > d.outstanding_amount:
frappe.throw(_("Row #{0}: Allocated amount cannot be greater than outstanding amount")
.format(d.idx))
@@ -221,10 +244,8 @@
def add_party_gl_entries(self, gl_entries):
if self.party_account:
if self.payment_type=="Receive":
- party_account_currency = self.paid_from_account_currency
against_account = self.paid_to
else:
- party_account_currency = self.paid_to_account_currency
against_account = self.paid_from
@@ -233,7 +254,7 @@
"party_type": self.party_type,
"party": self.party,
"against": against_account,
- "account_currency": party_account_currency
+ "account_currency": self.party_account_currency
})
for d in self.get("references"):
@@ -277,7 +298,6 @@
gl_entries.append(gle)
-
def add_bank_gl_entries(self, gl_entries):
if self.payment_type in ("Pay", "Internal Transfer"):
gl_entries.append(
@@ -341,10 +361,10 @@
args.get("party_account"))
for d in outstanding_invoices:
- d.exchange_rate = 1
+ d["exchange_rate"] = 1
if party_account_currency != company_currency \
and d.voucher_type in ("Sales Invoice", "Purchase Invoice"):
- d.exchange_rate = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
+ d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
# Get all SO / PO which are not fully billed or aginst which full advance not paid
orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party"),
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
index 5075f15..a50ff19 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
@@ -4,7 +4,15 @@
frappe.provide("erpnext.accounts");
erpnext.accounts.PaymentReconciliationController = frappe.ui.form.Controller.extend({
-
+ setup: function() {
+ this.frm.get_field('payments').grid.editable_fields = [
+ {fieldname: 'reference_name', columns: 3},
+ {fieldname: 'amount', columns: 2},
+ {fieldname: 'invoice_number', columns: 3},
+ {fieldname: 'allocated_amount', columns: 3}
+ ];
+ },
+
onload: function() {
var me = this
this.frm.set_query('party_type', function() {
@@ -105,14 +113,16 @@
if (row.invoice_number && !inList(invoices, row.invoice_number))
invoices.push(row.invoice_type + " | " + row.invoice_number);
});
+
+ if (invoices) {
+ frappe.meta.get_docfield("Payment Reconciliation Payment", "invoice_number",
+ me.frm.doc.name).options = "\n" + invoices.join("\n");
- frappe.meta.get_docfield("Payment Reconciliation Payment", "invoice_number",
- me.frm.doc.name).options = invoices.join("\n");
-
- $.each(me.frm.doc.payments || [], function(i, p) {
- if(!inList(invoices, cstr(p.invoice_number))) p.invoice_number = null;
- });
-
+ $.each(me.frm.doc.payments || [], function(i, p) {
+ if(!inList(invoices, cstr(p.invoice_number))) p.invoice_number = null;
+ });
+ }
+
refresh_field("payments");
},
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index 6868a48..5910a24 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -7,31 +7,49 @@
from frappe import msgprint, _
from frappe.model.document import Document
from erpnext.accounts.utils import get_outstanding_invoices
+from erpnext.controllers.accounts_controller import get_advance_payment_entries
class PaymentReconciliation(Document):
def get_unreconciled_entries(self):
- self.get_jv_entries()
+ self.get_nonreconciled_payment_entries()
self.get_invoice_entries()
+
+ def get_nonreconciled_payment_entries(self):
+ self.check_mandatory_to_fetch()
+
+ payment_entries = self.get_payment_entries()
+ journal_entries = self.get_jv_entries()
+
+ self.add_payment_entries(payment_entries + journal_entries)
+
+ def get_payment_entries(self):
+ order_doctype = "Sales Order" if self.party_type=="Customer" else "Purchase Order"
+ payment_entries = get_advance_payment_entries(self.party_type, self.party,
+ self.receivable_payable_account, order_doctype, against_all_orders=True)
+
+ return payment_entries
def get_jv_entries(self):
- self.check_mandatory_to_fetch()
dr_or_cr = "credit_in_account_currency" if self.party_type == "Customer" \
else "debit_in_account_currency"
bank_account_condition = "t2.against_account like %(bank_cash_account)s" \
if self.bank_cash_account else "1=1"
- jv_entries = frappe.db.sql("""
+ journal_entries = frappe.db.sql("""
select
- t1.name as voucher_no, t1.posting_date, t1.remark,
- t2.name as voucher_detail_no, {dr_or_cr} as payment_amount, t2.is_advance
+ "Journal Entry" as reference_type, t1.name as reference_name,
+ t1.posting_date, t1.remark as remarks, t2.name as reference_row,
+ {dr_or_cr} as amount, t2.is_advance
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
and t2.party_type = %(party_type)s and t2.party = %(party)s
and t2.account = %(account)s and {dr_or_cr} > 0
- and (t2.reference_type is null or t2.reference_type in ('', 'Sales Order', 'Purchase Order'))
+ and (t2.reference_type is null or t2.reference_type = '' or
+ (t2.reference_type in ('Sales Order', 'Purchase Order')
+ and t2.reference_name is not null and t2.reference_name != ''))
and (CASE
WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
THEN 1=1
@@ -47,18 +65,13 @@
"bank_cash_account": "%%%s%%" % self.bank_cash_account
}, as_dict=1)
- self.add_payment_entries(jv_entries)
+ return list(journal_entries)
- def add_payment_entries(self, jv_entries):
+ def add_payment_entries(self, entries):
self.set('payments', [])
- for e in jv_entries:
- ent = self.append('payments', {})
- ent.journal_entry = e.get('voucher_no')
- ent.posting_date = e.get('posting_date')
- ent.amount = flt(e.get('payment_amount'))
- ent.remark = e.get('remark')
- ent.voucher_detail_number = e.get('voucher_detail_no')
- ent.is_advance = e.get('is_advance')
+ for e in entries:
+ row = self.append('payments', {})
+ row.update(e)
def get_invoice_entries(self):
#Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against
@@ -90,14 +103,16 @@
self.get_invoice_entries()
self.validate_invoice()
- dr_or_cr = "credit_in_account_currency" if self.party_type == "Customer" \
- else "debit_in_account_currency"
+ dr_or_cr = "credit_in_account_currency" \
+ if self.party_type == "Customer" else "debit_in_account_currency"
+
lst = []
for e in self.get('payments'):
if e.invoice_number and e.allocated_amount:
- lst.append({
- 'voucher_no' : e.journal_entry,
- 'voucher_detail_no' : e.voucher_detail_number,
+ lst.append(frappe._dict({
+ 'voucher_type': e.reference_type,
+ 'voucher_no' : e.reference_name,
+ 'voucher_detail_no' : e.reference_row,
'against_voucher_type' : e.invoice_type,
'against_voucher' : e.invoice_number,
'account' : self.receivable_payable_account,
@@ -107,11 +122,12 @@
'dr_or_cr' : dr_or_cr,
'unadjusted_amount' : flt(e.amount),
'allocated_amount' : flt(e.allocated_amount)
- })
-
+ }))
+
if lst:
from erpnext.accounts.utils import reconcile_against_document
reconcile_against_document(lst)
+
msgprint(_("Successfully Reconciled"))
self.get_unreconciled_entries()
@@ -142,7 +158,7 @@
.format(p.invoice_type, p.invoice_number))
if flt(p.allocated_amount) > flt(p.amount):
- frappe.throw(_("Row {0}: Allocated amount {1} must be less than or equals to JV amount {2}")
+ frappe.throw(_("Row {0}: Allocated amount {1} must be less than or equals to Payment Entry amount {2}")
.format(p.idx, p.allocated_amount, p.amount))
invoice_outstanding = unreconciled_invoices.get(p.invoice_type, {}).get(p.invoice_number)
diff --git a/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
index f9b6039..401d234 100644
--- a/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
+++ b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
@@ -2,6 +2,7 @@
"allow_copy": 0,
"allow_import": 0,
"allow_rename": 0,
+ "beta": 0,
"creation": "2014-07-09 16:13:35.452759",
"custom": 0,
"docstatus": 0,
@@ -12,18 +13,46 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "journal_entry",
+ "fieldname": "reference_type",
"fieldtype": "Link",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
- "in_list_view": 1,
- "label": "Journal Entry",
+ "in_list_view": 0,
+ "label": "Reference Type",
"length": 0,
"no_copy": 0,
- "options": "Journal Entry",
+ "options": "DocType",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "fieldname": "reference_name",
+ "fieldtype": "Dynamic Link",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Reference_name",
+ "length": 0,
+ "no_copy": 0,
+ "options": "reference_type",
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -39,6 +68,7 @@
"fieldtype": "Date",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Posting Date",
@@ -46,6 +76,7 @@
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -61,6 +92,7 @@
"fieldtype": "Currency",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Amount",
@@ -68,6 +100,7 @@
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -83,6 +116,7 @@
"fieldtype": "Data",
"hidden": 1,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"label": "Is Advance",
@@ -90,6 +124,7 @@
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -101,17 +136,19 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "voucher_detail_number",
+ "fieldname": "reference_row",
"fieldtype": "Data",
"hidden": 1,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
- "label": "Voucher Detail Number",
+ "label": "Reference Row",
"length": 0,
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -127,6 +164,7 @@
"fieldtype": "Column Break",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"label": "",
@@ -134,6 +172,7 @@
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -149,6 +188,7 @@
"fieldtype": "Select",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Invoice Number",
@@ -157,6 +197,7 @@
"options": "",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 1,
@@ -172,6 +213,7 @@
"fieldtype": "Currency",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Allocated amount",
@@ -180,6 +222,7 @@
"permlevel": 0,
"precision": "",
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 1,
@@ -195,6 +238,7 @@
"fieldtype": "Section Break",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"label": "",
@@ -202,6 +246,7 @@
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -217,6 +262,7 @@
"fieldtype": "Small Text",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Remark",
@@ -224,6 +270,7 @@
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -234,21 +281,27 @@
],
"hide_heading": 0,
"hide_toolbar": 0,
+ "idx": 0,
+ "image_view": 0,
"in_create": 0,
"in_dialog": 0,
"is_submittable": 0,
"issingle": 0,
"istable": 1,
"max_attachments": 0,
- "modified": "2015-11-16 06:29:51.563989",
+ "menu_index": 0,
+ "modified": "2016-06-27 18:27:15.663498",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Reconciliation Payment",
"name_case": "",
"owner": "Administrator",
"permissions": [],
+ "quick_entry": 1,
"read_only": 0,
"read_only_onload": 0,
"sort_field": "modified",
- "sort_order": "DESC"
+ "sort_order": "DESC",
+ "track_seen": 0,
+ "version": 0
}
\ No newline at end of file
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 9574603..56a485e 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -316,6 +316,7 @@
new_row.update(reference_details)
payment_entry.flags.ignore_validate_update_after_submit = True
+ payment_entry.set_missing_values()
payment_entry.set_amounts()
payment_entry.save(ignore_permissions=True)
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index 58c6bb1..5185614 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -313,77 +313,15 @@
order_list = list(set([d.get(order_field)
for d in self.get("items") if d.get(order_field)]))
- journal_entries = self.get_advance_journal_entries(party_type, party, party_account,
+ journal_entries = get_advance_journal_entries(party_type, party, party_account,
amount_field, order_doctype, order_list, include_unallocated)
- payment_entries = self.get_advance_payment_entries(party_type, party, party_account,
+ payment_entries = get_advance_payment_entries(party_type, party, party_account,
order_doctype, order_list, include_unallocated)
res = journal_entries + payment_entries
return res
-
- def get_advance_journal_entries(self, party_type, party, party_account, amount_field,
- order_doctype, order_list, include_unallocated=True):
- conditions = []
- if include_unallocated:
- conditions.append("ifnull(t2.reference_name, '')=''")
-
- 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))
-
- reference_condition = " and (" + " or ".join(conditions) + ")" if conditions else ""
-
- 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
- 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 (ifnull(t2.reference_name, '')='' {1})
- order by t1.posting_date""".format(amount_field, reference_condition),
- [party_account, party_type, party] + order_list, as_dict=1)
-
- return list(journal_entries)
-
- def get_advance_payment_entries(self, party_type, party, party_account,
- order_doctype, order_list, include_unallocated=True):
- party_account_field = "paid_from" if party_type == "Customer" else "paid_to"
- payment_type = "Receive" if party_type == "Customer" else "Pay"
- payment_entries_against_order, unallocated_payment_entries = [], []
-
- if 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
- from `tabPayment Entry` t1, `tabPayment Entry Reference` t2
- where
- t1.name = t2.parent and t1.{0} = %s and t1.payment_type = %s
- and t1.party_type = %s and t1.party = %s and t1.docstatus = 1
- and t2.reference_doctype = %s and t2.reference_name in ({1})
- """.format(party_account_field, ', '.join(['%s'] * len(order_list))),
- [party_account, 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
- """.format(party_account_field), (party_account, party_type, party, payment_type), as_dict=1)
-
- return list(payment_entries_against_order) + list(unallocated_payment_entries)
def validate_advance_entries(self):
advance_entries = self.get_advance_entries(include_unallocated=False)
@@ -698,3 +636,77 @@
if flt(gl_dict.credit) and not flt(gl_dict.credit_in_account_currency):
gl_dict.credit_in_account_currency = gl_dict.credit if account_currency==company_currency \
else flt(gl_dict.credit / conversion_rate, 2)
+
+
+def get_advance_journal_entries(party_type, party, party_account, amount_field,
+ order_doctype, 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, '')=''")
+
+ 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))
+
+ reference_condition = " and (" + " or ".join(conditions) + ")" if conditions else ""
+
+ 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
+ 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
+ and (ifnull(t2.reference_name, '')='' {2})
+ order by t1.posting_date""".format(amount_field, dr_or_cr, reference_condition),
+ [party_account, party_type, party] + order_list, as_dict=1)
+
+ return list(journal_entries)
+
+def get_advance_payment_entries(party_type, party, party_account,
+ order_doctype, order_list=None, include_unallocated=True, against_all_orders=False):
+ party_account_field = "paid_from" if party_type == "Customer" else "paid_to"
+ payment_type = "Receive" if party_type == "Customer" else "Pay"
+ payment_entries_against_order, unallocated_payment_entries = [], []
+
+ if order_list or against_all_orders:
+ if order_list:
+ reference_condition = " and t2.reference_name in ({1})"\
+ .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
+ from `tabPayment Entry` t1, `tabPayment Entry Reference` t2
+ where
+ t1.name = t2.parent and t1.{0} = %s and t1.payment_type = %s
+ and t1.party_type = %s and t1.party = %s and t1.docstatus = 1
+ and t2.reference_doctype = %s {1}
+ """.format(party_account_field, reference_condition),
+ [party_account, 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
+ """.format(party_account_field), (party_account, party_type, party, payment_type), as_dict=1)
+
+ return list(payment_entries_against_order) + list(unallocated_payment_entries)
\ No newline at end of file
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 66c7a7e..c4bae85 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -284,3 +284,4 @@
erpnext.patches.v7_0.set_is_group_for_warehouse
erpnext.patches.v7_0.update_maintenance_module_in_doctype
erpnext.patches.v7_0.update_prevdoc_values_for_supplier_quotation_item
+erpnext.patches.v7_0.rename_advance_table_fields
\ No newline at end of file