Merge pull request #33444 from s-aga-r/fix/github-issue/28766
fix: consider child nodes while getting bin details
diff --git a/erpnext/controllers/selling_controller.py b/erpnext/controllers/selling_controller.py
index 8b073a4..cd1168d 100644
--- a/erpnext/controllers/selling_controller.py
+++ b/erpnext/controllers/selling_controller.py
@@ -23,7 +23,7 @@
super(SellingController, self).onload()
if self.doctype in ("Sales Order", "Delivery Note", "Sales Invoice"):
for item in self.get("items"):
- item.update(get_bin_details(item.item_code, item.warehouse))
+ item.update(get_bin_details(item.item_code, item.warehouse, include_child_warehouses=True))
def validate(self):
super(SellingController, self).validate()
diff --git a/erpnext/public/js/controllers/buying.js b/erpnext/public/js/controllers/buying.js
index 09779d8..b0e08cc 100644
--- a/erpnext/public/js/controllers/buying.js
+++ b/erpnext/public/js/controllers/buying.js
@@ -225,7 +225,8 @@
args: {
item_code: item.item_code,
warehouse: item.warehouse,
- company: doc.company
+ company: doc.company,
+ include_child_warehouses: true
}
});
}
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index 02456f3..8561dc2 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -102,9 +102,11 @@
elif out.get("warehouse"):
if doc and doc.get("doctype") == "Purchase Order":
# calculate company_total_stock only for po
- bin_details = get_bin_details(args.item_code, out.warehouse, args.company)
+ bin_details = get_bin_details(
+ args.item_code, out.warehouse, args.company, include_child_warehouses=True
+ )
else:
- bin_details = get_bin_details(args.item_code, out.warehouse)
+ bin_details = get_bin_details(args.item_code, out.warehouse, include_child_warehouses=True)
out.update(bin_details)
@@ -1060,7 +1062,9 @@
res[fieldname] = pos_profile.get(fieldname)
if res.get("warehouse"):
- res.actual_qty = get_bin_details(args.item_code, res.warehouse).get("actual_qty")
+ res.actual_qty = get_bin_details(
+ args.item_code, res.warehouse, include_child_warehouses=True
+ ).get("actual_qty")
return res
@@ -1171,16 +1175,31 @@
@frappe.whitelist()
-def get_bin_details(item_code, warehouse, company=None):
- bin_details = frappe.db.get_value(
- "Bin",
- {"item_code": item_code, "warehouse": warehouse},
- ["projected_qty", "actual_qty", "reserved_qty", "ordered_qty"],
- as_dict=True,
- cache=True,
- ) or {"projected_qty": 0, "actual_qty": 0, "reserved_qty": 0, "ordered_qty": 0}
+def get_bin_details(item_code, warehouse, company=None, include_child_warehouses=False):
+ bin_details = {"projected_qty": 0, "actual_qty": 0, "reserved_qty": 0, "ordered_qty": 0}
+
+ if warehouse:
+ from frappe.query_builder.functions import Coalesce, Sum
+
+ from erpnext.stock.doctype.warehouse.warehouse import get_child_warehouses
+
+ warehouses = get_child_warehouses(warehouse) if include_child_warehouses else [warehouse]
+
+ bin = frappe.qb.DocType("Bin")
+ bin_details = (
+ frappe.qb.from_(bin)
+ .select(
+ Coalesce(Sum(bin.projected_qty), 0).as_("projected_qty"),
+ Coalesce(Sum(bin.actual_qty), 0).as_("actual_qty"),
+ Coalesce(Sum(bin.reserved_qty), 0).as_("reserved_qty"),
+ Coalesce(Sum(bin.ordered_qty), 0).as_("ordered_qty"),
+ )
+ .where((bin.item_code == item_code) & (bin.warehouse.isin(warehouses)))
+ ).run(as_dict=True)[0]
+
if company:
bin_details["company_total_stock"] = get_company_total_stock(item_code, company)
+
return bin_details