[fix] Item Re-order if Bin does not exists and fixed Stock Projected Qty report to show per Warehouse Re-order level
diff --git a/erpnext/stock/reorder_item.py b/erpnext/stock/reorder_item.py
index 51dd8c1..d4b0a20 100644
--- a/erpnext/stock/reorder_item.py
+++ b/erpnext/stock/reorder_item.py
@@ -15,21 +15,37 @@
def _reorder_item():
material_requests = {"Purchase": {}, "Transfer": {}}
-
- item_warehouse_projected_qty = get_item_warehouse_projected_qty()
-
- warehouse_company = frappe._dict(frappe.db.sql("""select name, company from `tabWarehouse`"""))
+ warehouse_company = frappe._dict(frappe.db.sql("""select name, company from `tabWarehouse`
+ where disabled=0"""))
default_company = (frappe.defaults.get_defaults().get("company") or
frappe.db.sql("""select name from tabCompany limit 1""")[0][0])
+ items_to_consider = frappe.db.sql_list("""select name from `tabItem` item
+ where is_stock_item=1 and has_variants=0
+ and (is_purchase_item=1 or is_sub_contracted_item=1)
+ and (end_of_life is null or end_of_life='0000-00-00' or end_of_life > %(today)s)
+ and ((re_order_level is not null and re_order_level > 0)
+ or exists (select name from `tabItem Reorder` ir where ir.parent=item.name)
+ or (variant_of is not null and variant_of != ''
+ and exists (select name from `tabItem Reorder` ir where ir.parent=item.variant_of))
+ )""",
+ {"today": nowdate()})
+
+ if not items_to_consider:
+ return
+
+ item_warehouse_projected_qty = get_item_warehouse_projected_qty(items_to_consider)
+
def add_to_material_request(item_code, warehouse, reorder_level, reorder_qty, material_request_type):
- if warehouse not in item_warehouse_projected_qty[item_code]:
- # likely a disabled warehouse or a warehouse where BIN does not exist
+ if warehouse not in warehouse_company:
+ # a disabled warehouse
return
reorder_level = flt(reorder_level)
reorder_qty = flt(reorder_qty)
- projected_qty = item_warehouse_projected_qty[item_code][warehouse]
+
+ # projected_qty will be 0 if Bin does not exist
+ projected_qty = flt(item_warehouse_projected_qty.get(item_code, {}).get(warehouse))
if reorder_level and projected_qty < reorder_level:
deficiency = reorder_level - projected_qty
@@ -44,7 +60,7 @@
"reorder_qty": reorder_qty
})
- for item_code in item_warehouse_projected_qty:
+ for item_code in items_to_consider:
item = frappe.get_doc("Item", item_code)
if item.variant_of and not item.get("reorder_levels"):
@@ -62,18 +78,13 @@
if material_requests:
return create_material_request(material_requests)
-def get_item_warehouse_projected_qty():
+def get_item_warehouse_projected_qty(items_to_consider):
item_warehouse_projected_qty = {}
for item_code, warehouse, projected_qty in frappe.db.sql("""select item_code, warehouse, projected_qty
- from tabBin where ifnull(item_code, '') != '' and ifnull(warehouse, '') != ''
- and exists (select name from `tabItem`
- where `tabItem`.name = `tabBin`.item_code and
- is_stock_item=1 and (is_purchase_item=1 or is_sub_contracted_item=1) and
- (ifnull(end_of_life, '0000-00-00')='0000-00-00' or end_of_life > %s))
- and exists (select name from `tabWarehouse`
- where `tabWarehouse`.name = `tabBin`.warehouse
- and ifnull(disabled, 0)=0)""", nowdate()):
+ from tabBin where item_code in ({0})
+ and (warehouse != "" and warehouse is not null)"""\
+ .format(", ".join(["%s"] * len(items_to_consider))), items_to_consider):
item_warehouse_projected_qty.setdefault(item_code, {})[warehouse] = flt(projected_qty)