Merge branch 'develop' into fix-reserve-qty
diff --git a/erpnext/selling/doctype/sales_order/sales_order.json b/erpnext/selling/doctype/sales_order/sales_order.json
index ccea840..9559f13 100644
--- a/erpnext/selling/doctype/sales_order/sales_order.json
+++ b/erpnext/selling/doctype/sales_order/sales_order.json
@@ -17,6 +17,8 @@
"customer_name",
"tax_id",
"order_type",
+ "col_breaktest123",
+ "test_my_field",
"column_break_7",
"transaction_date",
"delivery_date",
@@ -249,6 +251,15 @@
"reqd": 1
},
{
+ "fieldname": "col_breaktest123",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "test_my_field",
+ "fieldtype": "Data",
+ "label": "Test My Field"
+ },
+ {
"fieldname": "column_break1",
"fieldtype": "Column Break",
"hide_days": 1,
@@ -1643,7 +1654,7 @@
"idx": 105,
"is_submittable": 1,
"links": [],
- "modified": "2022-12-12 18:34:00.681780",
+ "modified": "2023-02-13 11:59:00.681780",
"modified_by": "Administrator",
"module": "Selling",
"name": "Sales Order",
@@ -1722,4 +1733,4 @@
"title_field": "customer_name",
"track_changes": 1,
"track_seen": 1
-}
\ No newline at end of file
+}
diff --git a/erpnext/selling/doctype/selling_settings/selling_settings.json b/erpnext/selling/doctype/selling_settings/selling_settings.json
index 6ea66a0..50c30e6 100644
--- a/erpnext/selling/doctype/selling_settings/selling_settings.json
+++ b/erpnext/selling/doctype/selling_settings/selling_settings.json
@@ -28,6 +28,7 @@
"allow_multiple_items",
"allow_against_multiple_purchase_orders",
"allow_sales_order_creation_for_expired_quotation",
+ "dont_reserve_sales_order_qty_on_sales_return",
"hide_tax_id",
"enable_discount_accounting"
],
@@ -179,6 +180,12 @@
"fieldname": "allow_sales_order_creation_for_expired_quotation",
"fieldtype": "Check",
"label": "Allow Sales Order Creation For Expired Quotation"
+ },
+ {
+ "default": "0",
+ "fieldname": "dont_reserve_sales_order_qty_on_sales_return",
+ "fieldtype": "Check",
+ "label": "Don't Reserve Sales Order Qty on Sales Return"
}
],
"icon": "fa fa-cog",
@@ -215,4 +222,4 @@
"sort_order": "DESC",
"states": [],
"track_changes": 1
-}
\ No newline at end of file
+}
diff --git a/erpnext/stock/doctype/delivery_note/test_delivery_note.py b/erpnext/stock/doctype/delivery_note/test_delivery_note.py
index 903e2af..22d8135 100644
--- a/erpnext/stock/doctype/delivery_note/test_delivery_note.py
+++ b/erpnext/stock/doctype/delivery_note/test_delivery_note.py
@@ -1180,6 +1180,53 @@
self.assertTrue(return_dn.docstatus == 1)
+ def test_reserve_qty_on_sales_return(self):
+ frappe.db.set_single_value("Selling Settings", "dont_reserve_sales_order_qty_on_sales_return", 0)
+ self.reserved_qty_check()
+
+ def test_dont_reserve_qty_on_sales_return(self):
+ frappe.db.set_single_value("Selling Settings", "dont_reserve_sales_order_qty_on_sales_return", 1)
+ self.reserved_qty_check()
+
+ def reserved_qty_check(self):
+ from erpnext.controllers.sales_and_purchase_return import make_return_doc
+ from erpnext.selling.doctype.sales_order.sales_order import make_delivery_note
+ from erpnext.stock.stock_balance import get_reserved_qty
+
+ dont_reserve_qty = frappe.db.get_single_value(
+ "Selling Settings", "dont_reserve_sales_order_qty_on_sales_return"
+ )
+
+ item = make_item().name
+ warehouse = "_Test Warehouse - _TC"
+ qty_to_reserve = 5
+
+ so = make_sales_order(item_code=item, qty=qty_to_reserve)
+
+ # Make qty avl for test.
+ make_stock_entry(item_code=item, to_warehouse=warehouse, qty=10, basic_rate=100)
+
+ # Test that item qty has been reserved on submit of sales order.
+ self.assertEqual(get_reserved_qty(item, warehouse), qty_to_reserve)
+
+ dn = make_delivery_note(so.name)
+ dn.save().submit()
+
+ # Test that item qty is no longer reserved since qty has been delivered.
+ self.assertEqual(get_reserved_qty(item, warehouse), 0)
+
+ dn_return = make_return_doc("Delivery Note", dn.name)
+ dn_return.save().submit()
+
+ returned = frappe.get_doc("Delivery Note", dn_return.name)
+ returned.update_prevdoc_status()
+
+ # Test that item qty is not reserved on sales return, if selling setting don't reserve qty is checked.
+ self.assertEqual(get_reserved_qty(item, warehouse), 0 if dont_reserve_qty else qty_to_reserve)
+
+ def tearDown(self):
+ frappe.db.set_single_value("Selling Settings", "dont_reserve_sales_order_qty_on_sales_return", 0)
+
def create_delivery_note(**args):
dn = frappe.new_doc("Delivery Note")
diff --git a/erpnext/stock/stock_balance.py b/erpnext/stock/stock_balance.py
index 439ed7a..ab6e18e 100644
--- a/erpnext/stock/stock_balance.py
+++ b/erpnext/stock/stock_balance.py
@@ -3,7 +3,11 @@
import frappe
+from frappe.query_builder import DocType
+from frappe.query_builder.functions import Sum
+from frappe.query_builder.utils import Table
from frappe.utils import cstr, flt, now, nowdate, nowtime
+from pypika.queries import QueryBuilder
from erpnext.controllers.stock_controller import create_repost_item_valuation_entry
@@ -94,51 +98,85 @@
def get_reserved_qty(item_code, warehouse):
- reserved_qty = frappe.db.sql(
- """
- select
- sum(dnpi_qty * ((so_item_qty - so_item_delivered_qty) / so_item_qty))
- from
- (
- (select
- qty as dnpi_qty,
- (
- select qty from `tabSales Order Item`
- where name = dnpi.parent_detail_docname
- and (delivered_by_supplier is null or delivered_by_supplier = 0)
- ) as so_item_qty,
- (
- select delivered_qty from `tabSales Order Item`
- where name = dnpi.parent_detail_docname
- and delivered_by_supplier = 0
- ) as so_item_delivered_qty,
- parent, name
- from
- (
- select qty, parent_detail_docname, parent, name
- from `tabPacked Item` dnpi_in
- where item_code = %s and warehouse = %s
- and parenttype='Sales Order'
- and item_code != parent_item
- and exists (select * from `tabSales Order` so
- where name = dnpi_in.parent and docstatus = 1 and status not in ('On Hold', 'Closed'))
- ) dnpi)
- union
- (select stock_qty as dnpi_qty, qty as so_item_qty,
- delivered_qty as so_item_delivered_qty, parent, name
- from `tabSales Order Item` so_item
- where item_code = %s and warehouse = %s
- and (so_item.delivered_by_supplier is null or so_item.delivered_by_supplier = 0)
- and exists(select * from `tabSales Order` so
- where so.name = so_item.parent and so.docstatus = 1
- and so.status not in ('On Hold', 'Closed')))
- ) tab
- where
- so_item_qty >= so_item_delivered_qty
- """,
- (item_code, warehouse, item_code, warehouse),
+ def append_open_so_query(q: QueryBuilder, child_table: Table) -> QueryBuilder:
+ return (
+ q.inner_join(SalesOrder)
+ .on(SalesOrder.name == child_table.parent)
+ .where(SalesOrder.docstatus == 1)
+ .where(SalesOrder.status.notin(["On Hold", "Closed"]))
+ )
+
+ SalesOrder = DocType("Sales Order")
+ SalesOrderItem = DocType("Sales Order Item")
+ PackedItem = DocType("Packed Item")
+
+ dont_reserve_qty_on_sales_return = frappe.db.get_single_value(
+ "Selling Settings", "dont_reserve_sales_order_qty_on_sales_return"
)
+ tab = (
+ frappe.qb.from_(SalesOrderItem)
+ .where(SalesOrderItem.item_code == item_code)
+ .where(SalesOrderItem.warehouse == warehouse)
+ )
+ for field, cond in [
+ (SalesOrderItem.stock_qty.as_("dnpi_qty"), 1),
+ (SalesOrderItem.qty.as_("so_item_qty"), 1),
+ (SalesOrderItem.delivered_qty.as_("so_item_delivered_qty"), 1),
+ (SalesOrderItem.returned_qty.as_("so_item_returned_qty"), dont_reserve_qty_on_sales_return),
+ (SalesOrderItem.parent, 1),
+ (SalesOrderItem.name, 1),
+ ]:
+ if cond:
+ tab = tab.select(field)
+ tab = append_open_so_query(tab, SalesOrderItem)
+
+ dnpi = (
+ frappe.qb.from_(PackedItem)
+ .select(PackedItem.qty, PackedItem.parent_detail_docname, PackedItem.parent, PackedItem.name)
+ .where(PackedItem.item_code == item_code)
+ .where(PackedItem.warehouse == warehouse)
+ )
+ dnpi = append_open_so_query(dnpi, PackedItem)
+
+ dnpi_parent = frappe.qb.from_(dnpi).select(dnpi.qty.as_("dnpi_qty"))
+ for key, so_item_field, cond in [
+ ("so_item_qty", "qty", 1),
+ ("so_item_delivered_qty", "delivered_qty", 1),
+ ("so_item_returned_qty", "returned_qty", dont_reserve_qty_on_sales_return),
+ ]:
+ if cond:
+ dnpi_parent = dnpi_parent.select(
+ (
+ frappe.qb.from_(SalesOrderItem)
+ .select(SalesOrderItem[so_item_field])
+ .where(SalesOrderItem.name == dnpi.parent_detail_docname)
+ .where(SalesOrderItem.delivered_by_supplier == 0)
+ ).as_(key)
+ )
+ dnpi_parent = dnpi_parent.select(dnpi.parent, dnpi.name)
+
+ dnpi_parent = dnpi_parent + tab
+
+ q = (
+ frappe.qb.from_(dnpi_parent)
+ .select(
+ Sum(
+ dnpi_parent.dnpi_qty
+ * (
+ (
+ dnpi_parent.so_item_qty
+ - dnpi_parent.so_item_delivered_qty
+ - (dnpi_parent.so_item_returned_qty if dont_reserve_qty_on_sales_return else 0)
+ )
+ / dnpi_parent.so_item_qty
+ )
+ )
+ )
+ .where(dnpi_parent.so_item_qty >= dnpi_parent.so_item_delivered_qty)
+ )
+
+ reserved_qty = q.run()
return flt(reserved_qty[0][0]) if reserved_qty else 0