fix: incorrect status being set in Invoices (#28019)
Co-authored-by: Pruthvi Patel <pruthvipatel145@gmail.com>
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 1c9943f..508f728 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -15,6 +15,7 @@
from erpnext.accounts.doctype.gl_entry.gl_entry import update_outstanding_amt
from erpnext.accounts.doctype.sales_invoice.sales_invoice import (
check_if_return_invoice_linked_with_payment_entry,
+ get_total_in_party_account_currency,
is_overdue,
unlink_inter_company_doc,
update_linked_doc,
@@ -1183,6 +1184,7 @@
return
outstanding_amount = flt(self.outstanding_amount, self.precision("outstanding_amount"))
+ total = get_total_in_party_account_currency(self)
if not status:
if self.docstatus == 2:
@@ -1190,9 +1192,9 @@
elif self.docstatus == 1:
if self.is_internal_transfer():
self.status = 'Internal Transfer'
- elif is_overdue(self):
+ elif is_overdue(self, total):
self.status = "Overdue"
- elif 0 < outstanding_amount < flt(self.grand_total, self.precision("grand_total")):
+ elif 0 < outstanding_amount < total:
self.status = "Partly Paid"
elif outstanding_amount > 0 and getdate(self.due_date) >= getdate():
self.status = "Unpaid"
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index dafae31..40ad7b7 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -1427,6 +1427,7 @@
return
outstanding_amount = flt(self.outstanding_amount, self.precision("outstanding_amount"))
+ total = get_total_in_party_account_currency(self)
if not status:
if self.docstatus == 2:
@@ -1434,9 +1435,9 @@
elif self.docstatus == 1:
if self.is_internal_transfer():
self.status = 'Internal Transfer'
- elif is_overdue(self):
+ elif is_overdue(self, total):
self.status = "Overdue"
- elif 0 < outstanding_amount < flt(self.grand_total, self.precision("grand_total")):
+ elif 0 < outstanding_amount < total:
self.status = "Partly Paid"
elif outstanding_amount > 0 and getdate(self.due_date) >= getdate():
self.status = "Unpaid"
@@ -1463,27 +1464,42 @@
if update:
self.db_set('status', self.status, update_modified = update_modified)
-def is_overdue(doc):
- outstanding_amount = flt(doc.outstanding_amount, doc.precision("outstanding_amount"))
+def get_total_in_party_account_currency(doc):
+ total_fieldname = (
+ "grand_total"
+ if doc.disable_rounded_total
+ else "rounded_total"
+ )
+ if doc.party_account_currency != doc.currency:
+ total_fieldname = "base_" + total_fieldname
+
+ return flt(doc.get(total_fieldname), doc.precision(total_fieldname))
+
+def is_overdue(doc, total):
+ outstanding_amount = flt(doc.outstanding_amount, doc.precision("outstanding_amount"))
if outstanding_amount <= 0:
return
- grand_total = flt(doc.grand_total, doc.precision("grand_total"))
- nowdate = getdate()
- if doc.payment_schedule:
- # calculate payable amount till date
- payable_amount = sum(
- payment.payment_amount
- for payment in doc.payment_schedule
- if getdate(payment.due_date) < nowdate
- )
+ today = getdate()
+ if doc.get('is_pos') or not doc.get('payment_schedule'):
+ return getdate(doc.due_date) < today
- if (grand_total - outstanding_amount) < payable_amount:
- return True
+ # calculate payable amount till date
+ payment_amount_field = (
+ "base_payment_amount"
+ if doc.party_account_currency != doc.currency
+ else "payment_amount"
+ )
- elif getdate(doc.due_date) < nowdate:
- return True
+ payable_amount = sum(
+ payment.get(payment_amount_field)
+ for payment in doc.payment_schedule
+ if getdate(payment.due_date) < today
+ )
+
+ return (total - outstanding_amount) < payable_amount
+
def get_discounting_status(sales_invoice):
status = None
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index e9b531e..88c439b 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -1686,17 +1686,58 @@
def update_invoice_status():
"""Updates status as Overdue for applicable invoices. Runs daily."""
+ today = getdate()
for doctype in ("Sales Invoice", "Purchase Invoice"):
frappe.db.sql("""
- update `tab{}` as dt set dt.status = 'Overdue'
- where dt.docstatus = 1
- and dt.status != 'Overdue'
- and dt.outstanding_amount > 0
- and (dt.grand_total - dt.outstanding_amount) <
- (select sum(payment_amount) from `tabPayment Schedule` as ps
- where ps.parent = dt.name and ps.due_date < %s)
- """.format(doctype), getdate())
+ UPDATE `tab{doctype}` invoice SET invoice.status = 'Overdue'
+ WHERE invoice.docstatus = 1
+ AND invoice.status REGEXP '^Unpaid|^Partly Paid'
+ AND invoice.outstanding_amount > 0
+ AND (
+ {or_condition}
+ (
+ (
+ CASE
+ WHEN invoice.party_account_currency = invoice.currency
+ THEN (
+ CASE
+ WHEN invoice.disable_rounded_total
+ THEN invoice.grand_total
+ ELSE invoice.rounded_total
+ END
+ )
+ ELSE (
+ CASE
+ WHEN invoice.disable_rounded_total
+ THEN invoice.base_grand_total
+ ELSE invoice.base_rounded_total
+ END
+ )
+ END
+ ) - invoice.outstanding_amount
+ ) < (
+ SELECT SUM(
+ CASE
+ WHEN invoice.party_account_currency = invoice.currency
+ THEN ps.payment_amount
+ ELSE ps.base_payment_amount
+ END
+ )
+ FROM `tabPayment Schedule` ps
+ WHERE ps.parent = invoice.name
+ AND ps.due_date < %(today)s
+ )
+ )
+ """.format(
+ doctype=doctype,
+ or_condition=(
+ "invoice.is_pos AND invoice.due_date < %(today)s OR"
+ if doctype == "Sales Invoice"
+ else ""
+ )
+ ), {"today": today}
+ )
@frappe.whitelist()
def get_payment_terms(terms_template, posting_date=None, grand_total=None, base_grand_total=None, bill_date=None):
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 351d729..e446d6b 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -294,6 +294,7 @@
erpnext.patches.v13_0.validate_options_for_data_field
erpnext.patches.v13_0.create_gst_payment_entry_fields
erpnext.patches.v14_0.delete_shopify_doctypes
+erpnext.patches.v13_0.fix_invoice_statuses
erpnext.patches.v13_0.replace_supplier_item_group_with_party_specific_item
erpnext.patches.v13_0.update_dates_in_tax_withholding_category
erpnext.patches.v14_0.update_opportunity_currency_fields
diff --git a/erpnext/patches/v13_0/fix_invoice_statuses.py b/erpnext/patches/v13_0/fix_invoice_statuses.py
new file mode 100644
index 0000000..4395757
--- /dev/null
+++ b/erpnext/patches/v13_0/fix_invoice_statuses.py
@@ -0,0 +1,113 @@
+import frappe
+from frappe.utils import flt, getdate
+
+from erpnext.accounts.doctype.sales_invoice.sales_invoice import (
+ get_total_in_party_account_currency,
+ is_overdue,
+)
+
+TODAY = getdate()
+
+def execute():
+ # This fix is not related to Party Specific Item,
+ # but it is needed for code introduced after Party Specific Item was
+ # If your DB doesn't have this doctype yet, you should be fine
+ if not frappe.db.exists("DocType", "Party Specific Item"):
+ return
+
+ for doctype in ("Purchase Invoice", "Sales Invoice"):
+ fields = [
+ "name",
+ "status",
+ "due_date",
+ "outstanding_amount",
+ "grand_total",
+ "base_grand_total",
+ "rounded_total",
+ "base_rounded_total",
+ "disable_rounded_total",
+ ]
+ if doctype == "Sales Invoice":
+ fields.append("is_pos")
+
+ invoices_to_update = frappe.get_all(
+ doctype,
+ fields=fields,
+ filters={
+ "docstatus": 1,
+ "status": ("in", (
+ "Overdue",
+ "Overdue and Discounted",
+ "Partly Paid",
+ "Partly Paid and Discounted"
+ )),
+ "outstanding_amount": (">", 0),
+ "modified": (">", "2021-01-01")
+ # an assumption is being made that only invoices modified
+ # after 2021 got affected as incorrectly overdue.
+ # required for performance reasons.
+ }
+ )
+
+ invoices_to_update = {
+ invoice.name: invoice for invoice in invoices_to_update
+ }
+
+ payment_schedule_items = frappe.get_all(
+ "Payment Schedule",
+ fields=(
+ "due_date",
+ "payment_amount",
+ "base_payment_amount",
+ "parent"
+ ),
+ filters={"parent": ("in", invoices_to_update)}
+ )
+
+ for item in payment_schedule_items:
+ invoices_to_update[item.parent].setdefault(
+ "payment_schedule", []
+ ).append(item)
+
+ status_map = {}
+
+ for invoice in invoices_to_update.values():
+ invoice.doctype = doctype
+ doc = frappe.get_doc(invoice)
+ correct_status = get_correct_status(doc)
+ if not correct_status or doc.status == correct_status:
+ continue
+
+ status_map.setdefault(correct_status, []).append(doc.name)
+
+ for status, docs in status_map.items():
+ frappe.db.set_value(
+ doctype, {"name": ("in", docs)},
+ "status",
+ status,
+ update_modified=False
+ )
+
+
+
+def get_correct_status(doc):
+ outstanding_amount = flt(
+ doc.outstanding_amount, doc.precision("outstanding_amount")
+ )
+ total = get_total_in_party_account_currency(doc)
+
+ status = ""
+ if is_overdue(doc, total):
+ status = "Overdue"
+ elif 0 < outstanding_amount < total:
+ status = "Partly Paid"
+ elif outstanding_amount > 0 and getdate(doc.due_date) >= TODAY:
+ status = "Unpaid"
+
+ if not status:
+ return
+
+ if doc.status.endswith(" and Discounted"):
+ status += " and Discounted"
+
+ return status