Merge branch 'develop' into fix-reserve-qty
diff --git a/erpnext/selling/doctype/selling_settings/selling_settings.json b/erpnext/selling/doctype/selling_settings/selling_settings.json
index 2abb169..73bfbd3 100644
--- a/erpnext/selling/doctype/selling_settings/selling_settings.json
+++ b/erpnext/selling/doctype/selling_settings/selling_settings.json
@@ -27,6 +27,7 @@
"column_break_5",
"allow_multiple_items",
"allow_against_multiple_purchase_orders",
+ "dont_reserve_sales_order_qty_on_sales_return",
"hide_tax_id",
"enable_discount_accounting"
],
@@ -172,6 +173,12 @@
"fieldname": "enable_discount_accounting",
"fieldtype": "Check",
"label": "Enable Discount Accounting for Selling"
+ },
+ {
+ "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",
@@ -179,7 +186,7 @@
"index_web_pages_for_search": 1,
"issingle": 1,
"links": [],
- "modified": "2022-05-31 19:39:48.398738",
+ "modified": "2022-06-17 15:50:43.968334",
"modified_by": "Administrator",
"module": "Selling",
"name": "Selling Settings",
diff --git a/erpnext/stock/doctype/delivery_note/test_delivery_note.py b/erpnext/stock/doctype/delivery_note/test_delivery_note.py
index 6bcab73..3611d0b 100644
--- a/erpnext/stock/doctype/delivery_note/test_delivery_note.py
+++ b/erpnext/stock/doctype/delivery_note/test_delivery_note.py
@@ -1064,32 +1064,79 @@
self.assertEqual(dn.items[0].rate, rate)
- def test_internal_transfer_precision_gle(self):
- from erpnext.selling.doctype.customer.test_customer import create_internal_customer
+ def test_internal_transfer_precision_gle(self):
+ from erpnext.selling.doctype.customer.test_customer import create_internal_customer
+
+ item = make_item(properties={"valuation_method": "Moving Average"}).name
+ company = "_Test Company with perpetual inventory"
+ warehouse = "Stores - TCP1"
+ target = "Finished Goods - TCP1"
+ customer = create_internal_customer(represents_company=company)
+
+ # average rate = 128.015
+ rates = [101.45, 150.46, 138.25, 121.9]
+
+ for rate in rates:
+ make_stock_entry(item_code=item, target=warehouse, qty=1, rate=rate)
+
+ dn = create_delivery_note(
+ item_code=item,
+ company=company,
+ customer=customer,
+ qty=4,
+ warehouse=warehouse,
+ target_warehouse=target,
+ )
+ self.assertFalse(
+ frappe.db.exists("GL Entry", {"voucher_no": dn.name, "voucher_type": dn.doctype})
+ )
- item = make_item(properties={"valuation_method": "Moving Average"}).name
- company = "_Test Company with perpetual inventory"
- warehouse = "Stores - TCP1"
- target = "Finished Goods - TCP1"
- customer = create_internal_customer(represents_company=company)
+ 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()
- # average rate = 128.015
- rates = [101.45, 150.46, 138.25, 121.9]
+ 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()
- for rate in rates:
- make_stock_entry(item_code=item, target=warehouse, qty=1, rate=rate)
+ 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
- dn = create_delivery_note(
- item_code=item,
- company=company,
- customer=customer,
- qty=4,
- warehouse=warehouse,
- target_warehouse=target,
+ dont_reserve_qty = frappe.db.get_single_value(
+ "Selling Settings", "dont_reserve_sales_order_qty_on_sales_return"
)
- self.assertFalse(
- frappe.db.exists("GL Entry", {"voucher_no": dn.name, "voucher_type": dn.doctype})
- )
+
+ 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):
diff --git a/erpnext/stock/stock_balance.py b/erpnext/stock/stock_balance.py
index 14cedd2..a8e056c 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 != '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 != '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 != "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