Delivery by Serial No (#15030)
* fields added for delivery by Serial No
* SO - validate item for delivery by Serial No
* Stock Entry - add Serial No on production, validate reserved consumption
* add item by reservation to transaction if delivery by Serial No
* SLE - validate reserved Serial No by SO in Delivery Note, Sale Invoice
* Sales Order - validate Ensure Delivery by Serial No
* Serial No - remove SO ref on cancel
diff --git a/erpnext/selling/doctype/sales_order/sales_order.py b/erpnext/selling/doctype/sales_order/sales_order.py
index 3ec4a23..b34089d 100755
--- a/erpnext/selling/doctype/sales_order/sales_order.py
+++ b/erpnext/selling/doctype/sales_order/sales_order.py
@@ -40,6 +40,7 @@
self.validate_for_items()
self.validate_warehouse()
self.validate_drop_ship()
+ self.validate_serial_no_based_delivery()
from erpnext.stock.doctype.packed_item.packed_item import make_packing_list
make_packing_list(self)
@@ -398,6 +399,32 @@
d.set("delivery_date", _get_delivery_date(reference_delivery_date,
reference_doc.transaction_date, self.transaction_date))
+ def validate_serial_no_based_delivery(self):
+ reserved_items = []
+ normal_items = []
+ for item in self.items:
+ if item.ensure_delivery_based_on_produced_serial_no:
+ if item.item_code in normal_items:
+ frappe.throw(_("Cannot ensure delivery by Serial No as \
+ Item {0} is added with and without Ensure Delivery by \
+ Serial No.").format(item.item_code))
+ if item.item_code not in reserved_items:
+ if not frappe.db.get_value("Item", item.item_code, "has_serial_no"):
+ frappe.throw(_("Item {0} has no Serial No. Only serilialized items \
+ can have delivery based on Serial No").format(item.item_code))
+ if not frappe.db.exists("BOM", {"item": item.item_code, "is_active": 1}):
+ frappe.throw(_("No active BOM found for item {0}. Delivery by \
+ Serial No cannot be ensured").format(item.item_code))
+ reserved_items.append(item.item_code)
+ else:
+ normal_items.append(item.item_code)
+
+ if not item.ensure_delivery_based_on_produced_serial_no and \
+ item.item_code in reserved_items:
+ frappe.throw(_("Cannot ensure delivery by Serial No as \
+ Item {0} is added with and without Ensure Delivery by \
+ Serial No.").format(item.item_code))
+
def get_list_context(context=None):
from erpnext.controllers.website_list_for_contact import get_list_context
list_context = get_list_context(context)
diff --git a/erpnext/selling/doctype/sales_order_item/sales_order_item.json b/erpnext/selling/doctype/sales_order_item/sales_order_item.json
index 904d8fa..7795bec 100644
--- a/erpnext/selling/doctype/sales_order_item/sales_order_item.json
+++ b/erpnext/selling/doctype/sales_order_item/sales_order_item.json
@@ -79,6 +79,38 @@
"unique": 0
},
{
+ "allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "ensure_delivery_based_on_produced_serial_no",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Ensure Delivery Based on Produced Serial No",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
"allow_bulk_edit": 0,
"allow_on_submit": 0,
"bold": 0,
@@ -2338,7 +2370,7 @@
"istable": 1,
"max_attachments": 0,
"menu_index": 0,
- "modified": "2018-05-28 05:52:36.908884",
+ "modified": "2018-07-26 05:52:36.908884",
"modified_by": "Administrator",
"module": "Selling",
"name": "Sales Order Item",
@@ -2352,4 +2384,4 @@
"sort_order": "DESC",
"track_changes": 1,
"track_seen": 0
-}
\ No newline at end of file
+}
diff --git a/erpnext/stock/doctype/serial_no/serial_no.json b/erpnext/stock/doctype/serial_no/serial_no.json
index 1420156..ec3c851 100644
--- a/erpnext/stock/doctype/serial_no/serial_no.json
+++ b/erpnext/stock/doctype/serial_no/serial_no.json
@@ -374,6 +374,39 @@
"unique": 0
},
{
+ "allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "sales_order",
+ "fieldtype": "Link",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Sales Order",
+ "length": 0,
+ "no_copy": 0,
+ "options": "Sales Order",
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
"allow_bulk_edit": 0,
"allow_in_quick_entry": 0,
"allow_on_submit": 0,
@@ -1580,7 +1613,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 0,
- "modified": "2018-06-26 15:26:54.476202",
+ "modified": "2018-07-26 15:26:54.476202",
"modified_by": "Administrator",
"module": "Stock",
"name": "Serial No",
@@ -1653,4 +1686,4 @@
"sort_order": "DESC",
"track_changes": 0,
"track_seen": 0
-}
\ No newline at end of file
+}
diff --git a/erpnext/stock/doctype/serial_no/serial_no.py b/erpnext/stock/doctype/serial_no/serial_no.py
index 04c1f1e..dcf7675 100644
--- a/erpnext/stock/doctype/serial_no/serial_no.py
+++ b/erpnext/stock/doctype/serial_no/serial_no.py
@@ -6,6 +6,8 @@
from frappe.model.naming import make_autoname
from frappe.utils import cint, cstr, flt, add_days, nowdate, getdate
+from erpnext.stock.get_item_details import get_reserved_qty_for_so
+
from frappe import _, ValidationError
from erpnext.controllers.stock_controller import StockController
@@ -241,6 +243,42 @@
frappe.throw(_("Serial No {0} does not belong to any Warehouse")
.format(serial_no), SerialNoWarehouseError)
+ # if Sales Order reference in Serial No validate the Delivery Note or Invoice is against the same
+ if sr.sales_order:
+ if sle.voucher_type == "Sales Invoice":
+ if not frappe.db.exists("Sales Invoice Item", {"parent": sle.voucher_no,
+ "item_code": sle.item_code, "sales_order": sr.sales_order}):
+ frappe.throw(_("Cannot deliver Serial No {0} of item {1} as it is reserved \
+ to fullfill Sales Order {2}").format(sr.name, sle.item_code, sr.sales_order))
+ elif sle.voucher_type == "Delivery Note":
+ if not frappe.db.exists("Delivery Note Item", {"parent": sle.voucher_no,
+ "item_code": sle.item_code, "against_sales_order": sr.sales_order}):
+ invoice = frappe.db.get_value("Delivery Note Item", {"parent": sle.voucher_no,
+ "item_code": sle.item_code}, "against_sales_invoice")
+ if not invoice or frappe.db.exists("Sales Invoice Item",
+ {"parent": invoice, "item_code": sle.item_code,
+ "sales_order": sr.sales_order}):
+ frappe.throw(_("Cannot deliver Serial No {0} of item {1} as it is reserved to \
+ fullfill Sales Order {2}").format(sr.name, sle.item_code, sr.sales_order))
+ # if Sales Order reference in Delivery Note or Invoice validate SO reservations for item
+ if sle.voucher_type == "Sales Invoice":
+ sales_order = frappe.db.get_value("Sales Invoice Item", {"parent": sle.voucher_no,
+ "item_code": sle.item_code}, "sales_order")
+ if sales_order and get_reserved_qty_for_so(sales_order, sle.item_code):
+ validate_so_serial_no(sr, sales_order)
+ elif sle.voucher_type == "Delivery Note":
+ sales_order = frappe.get_value("Delivery Note Item", {"parent": sle.voucher_no,
+ "item_code": sle.item_code}, "against_sales_order")
+ if sales_order and get_reserved_qty_for_so(sales_order, sle.item_code):
+ validate_so_serial_no(sr, sales_order)
+ else:
+ sales_invoice = frappe.get_value("Delivery Note Item", {"parent": sle.voucher_no,
+ "item_code": sle.item_code}, "against_sales_invoice")
+ if sales_invoice:
+ sales_order = frappe.db.get_value("Sales Invoice Item", {
+ "parent": sales_invoice, "item_code": sle.item_code}, "sales_order")
+ if sales_order and get_reserved_qty_for_so(sales_order, sle.item_code):
+ validate_so_serial_no(sr, sales_order)
elif sle.actual_qty < 0:
# transfer out
frappe.throw(_("Serial No {0} not in stock").format(serial_no), SerialNoNotExistsError)
@@ -248,6 +286,12 @@
frappe.throw(_("Serial Nos Required for Serialized Item {0}").format(sle.item_code),
SerialNoRequiredError)
+def validate_so_serial_no(sr, sales_order,):
+ if not sr.sales_order or sr.sales_order!= sales_order:
+ frappe.throw(_("""Sales Order {0} has reservation for item {1}, you can
+ only deliver reserved {1} against {0}. Serial No {2} cannot
+ be delivered""").format(sales_order, sr.item_code, sr.name))
+
def has_duplicate_serial_no(sn, sle):
if sn.warehouse:
return True
@@ -287,7 +331,6 @@
serial_nos = get_auto_serial_nos(item_det.serial_no_series, sle.actual_qty)
frappe.db.set(sle, "serial_no", serial_nos)
validate_serial_no(sle, item_det)
-
if sle.serial_no:
auto_make_serial_nos(sle)
@@ -308,6 +351,8 @@
sr.warehouse = args.get('warehouse') if args.get('actual_qty', 0) > 0 else None
sr.batch_no = args.get('batch_no')
sr.location = args.get('location')
+ if sr.sales_order and not args.get('actual_qty', 0) > 0:
+ sr.sales_order = None
sr.save(ignore_permissions=True)
elif args.get('actual_qty', 0) > 0:
make_serial_no(serial_no, args)
@@ -354,7 +399,7 @@
if not stock_ledger_entries: return
for d in controller.get(parentfield):
- update_rejected_serial_nos = True if (controller.doctype in ("Purchase Receipt", "Purchase Invoice")
+ update_rejected_serial_nos = True if (controller.doctype in ("Purchase Receipt", "Purchase Invoice")
and d.rejected_qty) else False
accepted_serial_nos_updated = False
if controller.doctype == "Stock Entry":
@@ -402,4 +447,4 @@
if dn_serial_nos and len(dn_serial_nos)>0:
serial_nos = '\n'.join(dn_serial_nos)
- return serial_nos
\ No newline at end of file
+ return serial_nos
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.py b/erpnext/stock/doctype/stock_entry/stock_entry.py
index 89f90bf..bf12793 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.py
@@ -8,12 +8,14 @@
from frappe.utils import cstr, cint, flt, comma_or, getdate, nowdate, formatdate, format_time
from erpnext.stock.utils import get_incoming_rate
from erpnext.stock.stock_ledger import get_previous_sle, NegativeStockError, get_valuation_rate
-from erpnext.stock.get_item_details import get_bin_details, get_default_cost_center, get_conversion_factor
+from erpnext.stock.get_item_details import get_bin_details, get_default_cost_center, get_conversion_factor, get_reserved_qty_for_so
from erpnext.setup.doctype.item_group.item_group import get_item_group_defaults
from erpnext.stock.doctype.batch.batch import get_batch_no, set_batch_nos, get_batch_qty
from erpnext.stock.doctype.item.item import get_item_defaults
from erpnext.manufacturing.doctype.bom.bom import validate_bom_no, add_additional_cost
from erpnext.stock.utils import get_bin
+from erpnext.stock.doctype.serial_no.serial_no import update_serial_nos_after_submit, get_serial_nos
+
import json
from six import string_types, itervalues, iteritems
@@ -73,7 +75,6 @@
self.update_stock_ledger()
- from erpnext.stock.doctype.serial_no.serial_no import update_serial_nos_after_submit
update_serial_nos_after_submit(self, "items")
self.update_work_order()
self.validate_purchase_order()
@@ -81,6 +82,10 @@
self.update_purchase_order_supplied_items()
self.make_gl_entries()
self.update_cost_in_project()
+ self.validate_reserved_serial_no_consumption()
+ if self.work_order and self.purpose == "Manufacture":
+ self.update_so_in_serial_number()
+
def on_cancel(self):
@@ -1046,6 +1051,33 @@
stock_bin = get_bin(item_code, reserve_warehouse)
stock_bin.update_reserved_qty_for_sub_contracting()
+ def update_so_in_serial_number(self):
+ so_name, item_code = frappe.db.get_value("Work Order", self.work_order, ["sales_order", "production_item"])
+ if so_name and item_code:
+ qty_to_reserve = get_reserved_qty_for_so(so_name, item_code)
+ if qty_to_reserve:
+ reserved_qty = frappe.db.sql("""select count(name) from `tabSerial No` where item_code=%s and
+ sales_order=%s""", (item_code, so_name))
+ if reserved_qty and reserved_qty[0][0]:
+ qty_to_reserve -= reserved_qty[0][0]
+ if qty_to_reserve > 0:
+ for item in self.items:
+ if item.item_code == item_code:
+ serial_nos = (item.serial_no).split("\n")
+ for serial_no in serial_nos:
+ if qty_to_reserve > 0:
+ frappe.db.set_value("Serial No", serial_no, "sales_order", so_name)
+ qty_to_reserve -=1
+
+ def validate_reserved_serial_no_consumption(self):
+ for item in self.items:
+ if item.s_warehouse and not item.t_warehouse and item.serial_no:
+ for sr in get_serial_nos(item.serial_no):
+ sales_order = frappe.db.get_value("Serial No", sr, "sales_order")
+ if sales_order:
+ frappe.throw(_("Item {0} (Serial No: {1}) cannot be consumed as is reserverd\
+ to fullfill Sales Order {2}.").format(item.item_code, sr, sales_order))
+
@frappe.whitelist()
def move_sample_to_retention_warehouse(company, items):
if isinstance(items, string_types):
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index 82579a5..da92608 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -91,11 +91,13 @@
out.update(actual_batch_qty)
if out.has_serial_no and args.get('batch_no'):
+ reserved_so = get_so_reservation_for_item(args)
out.batch_no = args.get('batch_no')
- out.serial_no = get_serial_no(out, args.serial_no)
+ out.serial_no = get_serial_no(out, args.serial_no, sales_order=reserved_so)
elif out.has_serial_no:
- out.serial_no = get_serial_no(out, args.serial_no)
+ reserved_so = get_so_reservation_for_item(args)
+ out.serial_no = get_serial_no(out, args.serial_no, sales_order=reserved_so)
if args.transaction_date and item.lead_time_days:
out.schedule_date = out.lead_time_date = add_days(args.transaction_date,
@@ -586,25 +588,32 @@
return pos_profile and pos_profile[0] or None
-def get_serial_nos_by_fifo(args):
+def get_serial_nos_by_fifo(args, sales_order=None):
if frappe.db.get_single_value("Stock Settings", "automatically_set_serial_nos_based_on_fifo"):
return "\n".join(frappe.db.sql_list("""select name from `tabSerial No`
- where item_code=%(item_code)s and warehouse=%(warehouse)s
- order by timestamp(purchase_date, purchase_time) asc limit %(qty)s""", {
+ where item_code=%(item_code)s and warehouse=%(warehouse)s and
+ sales_order=IF(%(sales_order)s IS NULL, sales_order, %(sales_order)s)
+ order by timestamp(purchase_date, purchase_time)
+ asc limit %(qty)s""",
+ {
"item_code": args.item_code,
"warehouse": args.warehouse,
- "qty": abs(cint(args.stock_qty))
+ "qty": abs(cint(args.stock_qty)),
+ "sales_order": sales_order
}))
-def get_serial_no_batchwise(args):
+def get_serial_no_batchwise(args, sales_order=None):
if frappe.db.get_single_value("Stock Settings", "automatically_set_serial_nos_based_on_fifo"):
return "\n".join(frappe.db.sql_list("""select name from `tabSerial No`
- where item_code=%(item_code)s and warehouse=%(warehouse)s and (batch_no=%(batch_no)s or batch_no is NULL)
- order by timestamp(purchase_date, purchase_time) asc limit %(qty)s""", {
+ where item_code=%(item_code)s and warehouse=%(warehouse)s and
+ sales_order=IF(%(sales_order)s IS NULL, sales_order, %(sales_order)s)
+ and batch_no=IF(%(batch_no)s IS NULL, batch_no, %(batch_no)s) order
+ by timestamp(purchase_date, purchase_time) asc limit %(qty)s""", {
"item_code": args.item_code,
"warehouse": args.warehouse,
"batch_no": args.batch_no,
- "qty": abs(cint(args.stock_qty))
+ "qty": abs(cint(args.stock_qty)),
+ "sales_order": sales_order
}))
@frappe.whitelist()
@@ -815,23 +824,21 @@
return out
@frappe.whitelist()
-def get_serial_no(args, serial_nos=None):
+def get_serial_no(args, serial_nos=None, sales_order=None):
serial_no = None
if isinstance(args, string_types):
args = json.loads(args)
args = frappe._dict(args)
-
if args.get('doctype') == 'Sales Invoice' and not args.get('update_stock'):
return ""
-
if args.get('warehouse') and args.get('stock_qty') and args.get('item_code'):
has_serial_no = frappe.get_value('Item', {'item_code': args.item_code}, "has_serial_no")
if args.get('batch_no') and has_serial_no == 1:
- return get_serial_no_batchwise(args)
+ return get_serial_no_batchwise(args, sales_order)
elif has_serial_no == 1:
args = json.dumps({"item_code": args.get('item_code'),"warehouse": args.get('warehouse'),"stock_qty": args.get('stock_qty')})
args = process_args(args)
- serial_no = get_serial_nos_by_fifo(args)
+ serial_no = get_serial_nos_by_fifo(args, sales_order)
if not serial_no and serial_nos:
# For POS
@@ -871,3 +878,28 @@
blanket_order_details = blanket_order_details[0] if blanket_order_details else ''
return blanket_order_details
+
+def get_so_reservation_for_item(args):
+ reserved_so = None
+ if args.get('against_sales_order'):
+ if get_reserved_qty_for_so(args.get('against_sales_order'), args.get('item_code')):
+ reserved_so = args.get('against_sales_order')
+ elif args.get('against_sales_invoice'):
+ sales_order = frappe.db.sql("""select sales_order from `tabSales Invoice Item` where
+ parent=%s and item_code=%s""", (args.get('against_sales_invoice'), args.get('item_code')))
+ if sales_order and sales_order[0]:
+ if get_reserved_qty_for_so(sales_order[0][0], args.get('item_code')):
+ reserved_so = sales_order[0]
+ elif args.get("sales_order"):
+ if get_reserved_qty_for_so(args.get('sales_order'), args.get('item_code')):
+ reserved_so = args.get('sales_order')
+ return reserved_so
+
+def get_reserved_qty_for_so(sales_order, item_code):
+ reserved_qty = frappe.db.sql("""select sum(qty) from `tabSales Order Item`
+ where parent=%s and item_code=%s and ensure_delivery_based_on_produced_serial_no=1
+ """, (sales_order, item_code))
+ if reserved_qty and reserved_qty[0][0]:
+ return reserved_qty[0][0]
+ else:
+ return 0