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