fix: mismatch between warehouse tree value and warehouse based stock balance report value (#18879)
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index 6ea3228..aec37d4 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -15,7 +15,7 @@
values = {}
conditions = ""
if warehouse:
- conditions += """ and warehouse in (
+ conditions += """ and `tabBin`.warehouse in (
select w2.name from `tabWarehouse` w1
join `tabWarehouse` w2 on
w1.name = %(warehouse)s
@@ -25,11 +25,12 @@
values['warehouse'] = warehouse
if item_code:
- conditions += " and item_code = %(item_code)s"
+ conditions += " and `tabBin`.item_code = %(item_code)s"
values['item_code'] = item_code
- query = "select sum(stock_value) from `tabBin` where 1 = 1 %s" % conditions
+ query = """select sum(stock_value) from `tabBin`, `tabItem` where 1 = 1
+ and `tabItem`.name = `tabBin`.item_code and ifnull(`tabItem`.disabled, 0) = 0 %s""" % conditions
stock_value = frappe.db.sql(query, values)