payment to invoice matching
diff --git a/erpnext/accounts/doctype/journal_voucher/journal_voucher.json b/erpnext/accounts/doctype/journal_voucher/journal_voucher.json
index 44d6971..6a70c69 100644
--- a/erpnext/accounts/doctype/journal_voucher/journal_voucher.json
+++ b/erpnext/accounts/doctype/journal_voucher/journal_voucher.json
@@ -132,7 +132,7 @@
{
"fieldname": "difference",
"fieldtype": "Currency",
- "label": "Difference",
+ "label": "Difference (Dr - Cr)",
"no_copy": 1,
"oldfieldname": "difference",
"oldfieldtype": "Currency",
@@ -440,7 +440,7 @@
"icon": "icon-file-text",
"idx": 1,
"is_submittable": 1,
- "modified": "2014-04-29 14:55:19.872882",
+ "modified": "2014-05-01 11:24:52.313364",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Journal Voucher",
diff --git a/erpnext/accounts/doctype/journal_voucher/journal_voucher.py b/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
index 1314c85..62758c6 100644
--- a/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
+++ b/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
@@ -21,22 +21,21 @@
def validate(self):
if not self.is_opening:
self.is_opening='No'
-
self.clearance_date = None
super(JournalVoucher, self).validate_date_with_fiscal_year()
- self.validate_debit_credit()
self.validate_cheque_info()
self.validate_entries_for_advance()
+ self.validate_debit_and_credit()
self.validate_against_jv()
-
+ self.validate_against_sales_invoice()
+ self.validate_against_purchase_invoice()
self.set_against_account()
self.create_remarks()
self.set_aging_date()
self.set_print_format_fields()
-
def on_submit(self):
if self.voucher_type in ['Bank Voucher', 'Contra Voucher', 'Journal Entry']:
self.check_credit_days()
@@ -49,16 +48,6 @@
self.make_gl_entries(1)
- def on_trash(self):
- pass
- #if self.amended_from:
- # frappe.delete_doc("Journal Voucher", self.amended_from)
-
- def validate_debit_credit(self):
- for d in self.get('entries'):
- if d.debit and d.credit:
- msgprint(_("You cannot credit and debit same account at the same time."), raise_exception=1)
-
def validate_cheque_info(self):
if self.voucher_type in ['Bank Voucher']:
if not self.cheque_no or not self.cheque_date:
@@ -81,33 +70,68 @@
for d in self.get('entries'):
if d.against_jv:
if d.against_jv == self.name:
- msgprint(_("You can not enter current voucher in 'Against Journal Voucher' column"), raise_exception=1)
- elif not frappe.db.sql("""select name from `tabJournal Voucher Detail`
- where account = %s and docstatus = 1 and parent = %s""",
- (d.account, d.against_jv)):
- msgprint(_("Journal Voucher {0} does not have account {1}.").format(d.against_jv, d.account), raise_exception=1)
+ frappe.throw(_("You can not enter current voucher in 'Against Journal Voucher' column"))
+
+ against_entries = frappe.db.sql("""select * from `tabJournal Voucher Detail`
+ where account = %s and docstatus = 1 and parent = %s
+ and ifnull(against_jv, '') = ''""", (d.account, d.against_jv), as_dict=True)
+
+ if not against_entries:
+ frappe.throw(_("Journal Voucher {0} does not have account {1} or already matched")
+ .format(d.against_jv, d.account))
+ else:
+ dr_or_cr = "debit" if d.credit > 0 else "credit"
+ valid = False
+ for jvd in against_entries:
+ if flt(jvd[dr_or_cr]) > 0:
+ valid = True
+ if not valid:
+ frappe.throw(_("Against Journal Voucher {0} does not have any unmatched {1} entry")
+ .format(d.against_jv, dr_or_cr))
+
+ def validate_against_sales_invoice(self):
+ for d in self.get("entries"):
+ if d.against_invoice:
+ if d.debit > 0:
+ frappe.throw(_("Row {0}: Debit entry can not be linked with a Sales Invoice")
+ .format(d.idx))
+ if frappe.db.get_value("Sales Invoice", d.against_invoice, "debit_to") != d.account:
+ frappe.throw(_("Row {0}: Account does not match with \
+ Sales Invoice Debit To account").format(d.idx, d.account))
+
+ def validate_against_purchase_invoice(self):
+ for d in self.get("entries"):
+ if d.against_voucher:
+ if flt(d.credit) > 0:
+ frappe.throw(_("Row {0}: Credit entry can not be linked with a Purchase Invoice")
+ .format(d.idx))
+ if frappe.db.get_value("Purchase Invoice", d.against_voucher, "credit_to") != d.account:
+ frappe.throw(_("Row {0}: Account does not match with \
+ Purchase Invoice Credit To account").format(d.idx, d.account))
def set_against_account(self):
- # Debit = Credit
- debit, credit = 0.0, 0.0
- debit_list, credit_list = [], []
- for d in self.get('entries'):
- debit += flt(d.debit, 2)
- credit += flt(d.credit, 2)
- if flt(d.debit)>0 and (d.account not in debit_list): debit_list.append(d.account)
- if flt(d.credit)>0 and (d.account not in credit_list): credit_list.append(d.account)
+ accounts_debited, accounts_credited = [], []
+ for d in self.get("entries"):
+ if flt(d.debit > 0): accounts_debited.append(d.account)
+ if flt(d.credit) > 0: accounts_credited.append(d.account)
- self.total_debit = debit
- self.total_credit = credit
+ for d in self.get("entries"):
+ if flt(d.debit > 0): d.against_account = ", ".join(list(set(accounts_credited)))
+ if flt(d.credit > 0): d.against_account = ", ".join(list(set(accounts_debited)))
+
+ def validate_debit_and_credit(self):
+ self.total_debit, self.total_credit = 0, 0
+
+ for d in self.get("entries"):
+ if d.debit and d.credit:
+ frappe.throw(_("You cannot credit and debit same account at the same time"))
+
+ self.total_debit = flt(self.total_debit) + flt(d.debit)
+ self.total_credit = flt(self.total_credit) + flt(d.credit)
if abs(self.total_debit-self.total_credit) > 0.001:
- msgprint(_("Debit must equal Credit. The difference is {0}").format(self.total_debit-self.total_credit),
- raise_exception=1)
-
- # update against account
- for d in self.get('entries'):
- if flt(d.debit) > 0: d.against_account = ', '.join(credit_list)
- if flt(d.credit) > 0: d.against_account = ', '.join(debit_list)
+ frappe.throw(_("Total Debit must be equal to Total Credit. The difference is {0}")
+ .format(self.total_debit - self.total_credit))
def create_remarks(self):
r = []
@@ -220,22 +244,9 @@
return self.is_approving_authority
- def check_account_against_entries(self):
- for d in self.get("entries"):
- if d.against_invoice and frappe.db.get_value("Sales Invoice",
- d.against_invoice, "debit_to") != d.account:
- frappe.throw(_("Account {0} must be sames as Debit To Account in Sales Invoice in row {0}").format(d.account, d.idx))
-
- if d.against_voucher and frappe.db.get_value("Purchase Invoice",
- d.against_voucher, "credit_to") != d.account:
- frappe.throw(_("Account {0} must be sames as Credit To Account in Purchase Invoice in row {0}").format(d.account, d.idx))
-
def make_gl_entries(self, cancel=0, adv_adj=0):
from erpnext.accounts.general_ledger import make_gl_entries
- if not cancel:
- self.check_account_against_entries()
-
gl_map = []
for d in self.get("entries"):
if d.debit or d.credit:
diff --git a/erpnext/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py b/erpnext/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
index 6ec28d9..300d25e 100644
--- a/erpnext/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
+++ b/erpnext/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
@@ -15,25 +15,21 @@
total_amount = frappe.db.sql("""select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry`
where voucher_type = %s and voucher_no = %s
- and account = %s""", (self.voucher_type, self.voucher_no, self.account))
+ and account = %s and ifnull(against_voucher, '') != voucher_no""",
+ (self.voucher_type, self.voucher_no, self.account))
self.total_amount = total_amount and flt(total_amount[0][0]) or 0
reconciled_payment = frappe.db.sql("""
select abs(sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)))
from `tabGL Entry`
- where against_voucher = %s and account = %s
- """, (self.voucher_no, self.account))
+ where against_voucher_type = %s and against_voucher = %s and account = %s
+ """, (self.voucher_type, self.voucher_no, self.account))
reconciled_payment = reconciled_payment and flt(reconciled_payment[0][0]) or 0
self.unmatched_amount = self.total_amount - reconciled_payment
def get_against_entries(self):
- """
- Get payment entries for the account and period
- Payment entry will be decided based on account type (Dr/Cr)
- """
-
self.set('against_entries', [])
gle = self.get_gl_entries()
self.create_against_entries_table(gle)
@@ -56,14 +52,16 @@
t1.name as voucher_no, t1.posting_date, t1.total_debit as total_amt,
abs(ifnull(t2.debit, 0) - ifnull(t2.credit, 0)) as unmatched_amount, t1.remark,
t2.against_account, t2.name as voucher_detail_no, t2.is_advance
- from `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
- where t1.name = t2.parent and t1.docstatus = 1 and t2.account = %s
- and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
- and ifnull(t2.against_jv, '')='' and t2.%s > 0 and t1.name != %s
- and not exists (select * from `tabJournal Voucher Detail`
- where parent=%s and against_jv = t1.name) %s
- group by t1.name, t2.name """ %
- ('%s', dr_or_cr, '%s', '%s', cond), (self.account, self.voucher_no, self.voucher_no), as_dict=1)
+ from
+ `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
+ where
+ t1.name = t2.parent and t1.docstatus = 1 and t2.account = %s
+ and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
+ and ifnull(t2.against_jv, '')='' and t2.%s > 0 and t1.name != %s
+ and not exists (select * from `tabJournal Voucher Detail`
+ where parent=%s and against_jv = t1.name) %s
+ group by t1.name, t2.name """ % ('%s', dr_or_cr, '%s', '%s', cond),
+ (self.account, self.voucher_no, self.voucher_no), as_dict=1)
return gle
@@ -112,23 +110,15 @@
frappe.throw(_("Voucher No is not valid"))
def reconcile(self):
- """
- Links booking and payment voucher
- 1. cancel payment voucher
- 2. split into multiple rows if partially adjusted, assign against voucher
- 3. submit payment voucher
- """
self.validate_mandatory()
-
- if not self.total_allocated_amount:
- frappe.throw(_("You must allocate amount before reconcile"))
+ self.validate_allocated_amount()
dr_or_cr = "credit" if self.total_amount > 0 else "debit"
lst = []
for d in self.get('against_entries'):
if flt(d.allocated_amount) > 0:
- args = {
+ lst.append({
'voucher_no' : d.voucher_no,
'voucher_detail_no' : d.voucher_detail_no,
'against_voucher_type' : self.voucher_type,
@@ -138,16 +128,21 @@
'dr_or_cr' : dr_or_cr,
'unadjusted_amt' : flt(d.original_amount),
'allocated_amt' : flt(d.allocated_amount)
- }
-
- lst.append(args)
+ })
if lst:
from erpnext.accounts.utils import reconcile_against_document
reconcile_against_document(lst)
+ self.get_voucher_details()
self.get_against_entries()
msgprint(_("Successfully allocated"))
+ def validate_allocated_amount(self):
+ if not self.total_allocated_amount:
+ frappe.throw(_("You must allocate amount before reconcile"))
+ elif self.total_allocated_amount > self.unmatched_amount:
+ frappe.throw(_("Total Allocated Amount can not be greater than unmatched amount"))
+
def get_voucher_nos(doctype, txt, searchfield, start, page_len, filters):
non_reconclied_entries = []
entries = frappe.db.sql("""
@@ -172,7 +167,7 @@
""", (filters["account"], filters["voucher_type"], d.voucher_no))
adjusted_amount = adjusted_amount[0][0] if adjusted_amount else 0
- if adjusted_amount != d.amount:
+ if d.amount > adjusted_amount:
non_reconclied_entries.append([d.voucher_no, d.posting_date, d.amount])
return non_reconclied_entries
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index bbc6e53..3abbd03 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -369,14 +369,17 @@
and ifnull(allocated_amount, 0) = 0""" % (childtype, '%s', '%s'), (parentfield, self.name))
def get_advances(self, account_head, child_doctype, parentfield, dr_or_cr):
- res = frappe.db.sql("""select t1.name as jv_no, t1.remark,
- t2.%s as amount, t2.name as jv_detail_no
- from `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
- where t1.name = t2.parent and t2.account = %s and t2.is_advance = 'Yes'
- and (t2.against_voucher is null or t2.against_voucher = '')
- and (t2.against_invoice is null or t2.against_invoice = '')
- and (t2.against_jv is null or t2.against_jv = '')
- and t1.docstatus = 1 order by t1.posting_date""" %
+ res = frappe.db.sql("""
+ select
+ t1.name as jv_no, t1.remark, t2.%s as amount, t2.name as jv_detail_no
+ from
+ `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
+ where
+ t1.name = t2.parent and t2.account = %s and t2.is_advance = 'Yes' and t1.docstatus = 1
+ and ifnull(t2.against_voucher, '') = ''
+ and ifnull(t2.against_invoice, '') = ''
+ and ifnull(t2.against_jv, '') = ''
+ order by t1.posting_date""" %
(dr_or_cr, '%s'), account_head, as_dict=1)
self.set(parentfield, [])