Merge branch 'develop' into quo-status-fix
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 765f911..fca9047 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -668,4 +668,5 @@
erpnext.patches.v12_0.set_total_batch_quantity
erpnext.patches.v12_0.rename_mws_settings_fields
erpnext.patches.v12_0.set_updated_purpose_in_pick_list
+erpnext.patches.v12_0.fix_quotation_expired_status
erpnext.patches.v12_0.repost_stock_ledger_entries_for_target_warehouse
diff --git a/erpnext/patches/v12_0/fix_quotation_expired_status.py b/erpnext/patches/v12_0/fix_quotation_expired_status.py
new file mode 100644
index 0000000..0e4419a
--- /dev/null
+++ b/erpnext/patches/v12_0/fix_quotation_expired_status.py
@@ -0,0 +1,37 @@
+import frappe
+from frappe.utils import nowdate
+
+def execute():
+ # fixes status of quotations which have status 'Expired' despite having valid sales order created
+
+ # filter out submitted expired quotations which has sales order created
+ cond = "qo.docstatus = 1 and qo.status = 'Expired'"
+ invalid_so_against_quo = """
+ SELECT
+ so.name FROM `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so_item.docstatus = 1 and so.docstatus = 1
+ and so_item.parent = so.name
+ and so_item.prevdoc_docname = qo.name
+ and qo.valid_till < so.transaction_date""" # check if SO was created after quotation expired
+
+ frappe.db.sql(
+ """UPDATE `tabQuotation` qo SET qo.status = 'Expired' WHERE {cond} and not exists({invalid_so_against_quo})"""
+ .format(cond=cond, invalid_so_against_quo=invalid_so_against_quo),
+ (nowdate())
+ )
+
+ valid_so_against_quo = """
+ SELECT
+ so.name FROM `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so_item.docstatus = 1 and so.docstatus = 1
+ and so_item.parent = so.name
+ and so_item.prevdoc_docname = qo.name
+ and qo.valid_till >= so.transaction_date""" # check if SO was created before quotation expired
+
+ frappe.db.sql(
+ """UPDATE `tabQuotation` qo SET qo.status = 'Closed' WHERE {cond} and not exists({valid_so_against_quo})"""
+ .format(cond=cond, valid_so_against_quo=valid_so_against_quo),
+ (nowdate())
+ )
diff --git a/erpnext/selling/doctype/quotation/quotation.py b/erpnext/selling/doctype/quotation/quotation.py
index 7c47b8a..7cfec5a 100644
--- a/erpnext/selling/doctype/quotation/quotation.py
+++ b/erpnext/selling/doctype/quotation/quotation.py
@@ -193,12 +193,23 @@
return doclist
def set_expired_status():
- frappe.db.sql("""
- UPDATE
- `tabQuotation` SET `status` = 'Expired'
- WHERE
- `status` not in ('Ordered', 'Expired', 'Lost', 'Cancelled') AND `valid_till` < %s
- """, (nowdate()))
+ # filter out submitted non expired quotations whose validity has been ended
+ cond = "qo.docstatus = 1 and qo.status != 'Expired' and qo.valid_till < %s"
+ # check if those QUO have SO against it
+ so_against_quo = """
+ SELECT
+ so.name FROM `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so_item.docstatus = 1 and so.docstatus = 1
+ and so_item.parent = so.name
+ and so_item.prevdoc_docname = qo.name"""
+
+ # if not exists any SO, set status as Expired
+ frappe.db.sql(
+ """UPDATE `tabQuotation` qo SET qo.status = 'Expired' WHERE {cond} and not exists({so_against_quo})"""
+ .format(cond=cond, so_against_quo=so_against_quo),
+ (nowdate())
+ )
@frappe.whitelist()
def make_sales_invoice(source_name, target_doc=None):