chore: add `Stock Reservation Qty` column in `Stock Balance Report`
diff --git a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py
index 73cda72..06e14da 100644
--- a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py
+++ b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py
@@ -181,3 +181,28 @@
sre_details = {d.name: d for d in sre_data}
return sre_details
+
+
+def get_sre_reserved_qty_details(item_code_list: list, warehouse_list: list) -> dict:
+ sre_details = {}
+
+ if item_code_list and warehouse_list:
+ sre = frappe.qb.DocType("Stock Reservation Entry")
+ sre_data = (
+ frappe.qb.from_(sre)
+ .select(
+ sre.item_code, sre.warehouse, Sum(sre.reserved_qty - sre.delivered_qty).as_("reserved_qty")
+ )
+ .where(
+ (sre.docstatus == 1)
+ & (sre.item_code.isin(item_code_list))
+ & (sre.warehouse.isin(warehouse_list))
+ & (sre.status.notin(["Delivered", "Cancelled"]))
+ )
+ .groupby(sre.item_code, sre.warehouse)
+ ).run(as_dict=True)
+
+ if sre_data:
+ sre_details = {(d["item_code"], d["warehouse"]): d["reserved_qty"] for d in sre_data}
+
+ return sre_details
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index 0fc642e..b8d6b6c 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -58,6 +58,7 @@
return columns, []
iwb_map = get_item_warehouse_map(filters, sle)
+ sre_details = get_sre_reserved_qty_details(iwb_map)
item_map = get_item_details(items, sle, filters)
item_reorder_detail_map = get_item_reorder_details(item_map.keys())
@@ -88,6 +89,7 @@
"company": company,
"reorder_level": item_reorder_level,
"reorder_qty": item_reorder_qty,
+ "stock_reservation_qty": sre_details.get((item, warehouse), 0.0),
}
report_data.update(item_map[item])
report_data.update(qty_dict)
@@ -230,6 +232,13 @@
"convertible": "qty",
},
{
+ "label": _("Stock Reservation Qty"),
+ "fieldname": "stock_reservation_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {
"label": _("Company"),
"fieldname": "company",
"fieldtype": "Link",
@@ -388,6 +397,19 @@
return iwb_map
+def get_sre_reserved_qty_details(iwb_map: list) -> dict:
+ from erpnext.stock.doctype.stock_reservation_entry.stock_reservation_entry import (
+ get_sre_reserved_qty_details as get_reserved_qty_details,
+ )
+
+ item_code_list, warehouse_list = [], []
+ for d in iwb_map:
+ item_code_list.append(d[1])
+ warehouse_list.append(d[2])
+
+ return get_reserved_qty_details(item_code_list, warehouse_list)
+
+
def get_group_by_key(row, filters, inventory_dimension_fields) -> tuple:
group_by_key = [row.company, row.item_code, row.warehouse]
diff --git a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
index 8640710..d3046d2 100644
--- a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
+++ b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
@@ -4,7 +4,6 @@
import frappe
from frappe import _
-from frappe.query_builder.functions import Sum
from frappe.utils import flt, today
from pypika.terms import ExistsCriterion
@@ -275,33 +274,16 @@
def get_sre_reserved_qty_details(bin_list: list) -> dict:
- sre_details = {}
+ from erpnext.stock.doctype.stock_reservation_entry.stock_reservation_entry import (
+ get_sre_reserved_qty_details as get_reserved_qty_details,
+ )
- if bin_list:
- item_code_list, warehouse_list = [], []
- for bin in bin_list:
- item_code_list.append(bin["item_code"])
- warehouse_list.append(bin["warehouse"])
+ item_code_list, warehouse_list = [], []
+ for bin in bin_list:
+ item_code_list.append(bin["item_code"])
+ warehouse_list.append(bin["warehouse"])
- sre = frappe.qb.DocType("Stock Reservation Entry")
- sre_data = (
- frappe.qb.from_(sre)
- .select(
- sre.item_code, sre.warehouse, Sum(sre.reserved_qty - sre.delivered_qty).as_("reserved_qty")
- )
- .where(
- (sre.docstatus == 1)
- & (sre.item_code.isin(item_code_list))
- & (sre.warehouse.isin(warehouse_list))
- & (sre.status.notin(["Delivered", "Cancelled"]))
- )
- .groupby(sre.item_code, sre.warehouse)
- ).run(as_dict=True)
-
- if sre_data:
- sre_details = {(d["item_code"], d["warehouse"]): d["reserved_qty"] for d in sre_data}
-
- return sre_details
+ return get_reserved_qty_details(item_code_list, warehouse_list)
def get_item_map(item_code, include_uom):