fix: do not consider submitted Work Orders in the Production Plan Res… (backport #37343) (#37347)
fix: do not consider submitted Work Orders in the Production Plan Res… (#37343)
fix: do not consider submitted Work Orders in the Production Plan Reserve qty
(cherry picked from commit c3aeb2dec58190d16a18e2609fd57054bda54e43)
Co-authored-by: rohitwaghchaure <rohitw1991@gmail.com>
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index fabdafc..deef020 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -8,6 +8,7 @@
import frappe
from frappe import _, msgprint
from frappe.model.document import Document
+from frappe.query_builder import Case
from frappe.query_builder.functions import IfNull, Sum
from frappe.utils import (
add_days,
@@ -1617,18 +1618,33 @@
table = frappe.qb.DocType("Production Plan")
child = frappe.qb.DocType("Material Request Plan Item")
+ completed_production_plans = get_completed_production_plans()
+
+ case = Case()
query = (
frappe.qb.from_(table)
.inner_join(child)
.on(table.name == child.parent)
- .select(Sum(child.quantity * IfNull(child.conversion_factor, 1.0)))
+ .select(
+ Sum(
+ child.quantity
+ * IfNull(
+ case.when(child.material_request_type == "Purchase", child.conversion_factor).else_(1.0), 1.0
+ )
+ )
+ )
.where(
(table.docstatus == 1)
& (child.item_code == item_code)
& (child.warehouse == warehouse)
& (table.status.notin(["Completed", "Closed"]))
)
- ).run()
+ )
+
+ if completed_production_plans:
+ query = query.where(table.name.notin(completed_production_plans))
+
+ query = query.run()
if not query:
return 0.0
@@ -1636,7 +1652,9 @@
reserved_qty_for_production_plan = flt(query[0][0])
reserved_qty_for_production = flt(
- get_reserved_qty_for_production(item_code, warehouse, check_production_plan=True)
+ get_reserved_qty_for_production(
+ item_code, warehouse, completed_production_plans, check_production_plan=True
+ )
)
if reserved_qty_for_production > reserved_qty_for_production_plan:
@@ -1645,6 +1663,25 @@
return reserved_qty_for_production_plan - reserved_qty_for_production
+def get_completed_production_plans():
+ table = frappe.qb.DocType("Production Plan")
+ child = frappe.qb.DocType("Production Plan Item")
+
+ query = (
+ frappe.qb.from_(table)
+ .inner_join(child)
+ .on(table.name == child.parent)
+ .select(table.name)
+ .where(
+ (table.docstatus == 1)
+ & (table.status.notin(["Completed", "Closed"]))
+ & (child.ordered_qty >= child.planned_qty)
+ )
+ ).run(as_dict=True)
+
+ return list(set([d.name for d in query]))
+
+
def get_raw_materials_of_sub_assembly_items(
item_details, company, bom_no, include_non_stock_items, sub_assembly_items, planned_qty=1
):
diff --git a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
index 5d54c41..47a89aa 100644
--- a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
@@ -6,6 +6,7 @@
from erpnext.controllers.item_variant import create_variant
from erpnext.manufacturing.doctype.production_plan.production_plan import (
+ get_completed_production_plans,
get_items_for_material_requests,
get_sales_orders,
get_warehouse_list,
@@ -1103,6 +1104,50 @@
self.assertEqual(after_qty, before_qty)
+ def test_resered_qty_for_production_plan_for_less_rm_qty(self):
+ from erpnext.stock.utils import get_or_make_bin
+
+ bin_name = get_or_make_bin("Raw Material Item 1", "_Test Warehouse - _TC")
+ before_qty = flt(frappe.db.get_value("Bin", bin_name, "reserved_qty_for_production_plan"))
+
+ pln = create_production_plan(item_code="Test Production Item 1", planned_qty=10)
+
+ bin_name = get_or_make_bin("Raw Material Item 1", "_Test Warehouse - _TC")
+ after_qty = flt(frappe.db.get_value("Bin", bin_name, "reserved_qty_for_production_plan"))
+
+ self.assertEqual(after_qty - before_qty, 10)
+
+ pln.make_work_order()
+
+ plans = []
+ for row in frappe.get_all("Work Order", filters={"production_plan": pln.name}, fields=["name"]):
+ wo_doc = frappe.get_doc("Work Order", row.name)
+ wo_doc.source_warehouse = "_Test Warehouse - _TC"
+ wo_doc.wip_warehouse = "_Test Warehouse 1 - _TC"
+ wo_doc.fg_warehouse = "_Test Warehouse - _TC"
+ for d in wo_doc.required_items:
+ d.source_warehouse = "_Test Warehouse - _TC"
+ print(d.required_qty, "before")
+ d.required_qty -= 5
+ make_stock_entry(
+ item_code=d.item_code,
+ qty=d.required_qty,
+ rate=100,
+ target="_Test Warehouse - _TC",
+ )
+
+ wo_doc.submit()
+ plans.append(pln.name)
+
+ bin_name = get_or_make_bin("Raw Material Item 1", "_Test Warehouse - _TC")
+ after_qty = flt(frappe.db.get_value("Bin", bin_name, "reserved_qty_for_production_plan"))
+
+ self.assertEqual(after_qty, before_qty)
+
+ completed_plans = get_completed_production_plans()
+ for plan in plans:
+ self.assertTrue(plan in completed_plans)
+
def test_resered_qty_for_production_plan_for_material_requests_with_multi_UOM(self):
from erpnext.stock.utils import get_or_make_bin
diff --git a/erpnext/manufacturing/doctype/work_order/work_order.py b/erpnext/manufacturing/doctype/work_order/work_order.py
index d8fc220..3dc33ac 100644
--- a/erpnext/manufacturing/doctype/work_order/work_order.py
+++ b/erpnext/manufacturing/doctype/work_order/work_order.py
@@ -358,10 +358,10 @@
else:
self.update_work_order_qty_in_so()
+ self.update_ordered_qty()
self.update_reserved_qty_for_production()
self.update_completed_qty_in_material_request()
self.update_planned_qty()
- self.update_ordered_qty()
self.create_job_card()
def on_cancel(self):
@@ -1513,7 +1513,10 @@
def get_reserved_qty_for_production(
- item_code: str, warehouse: str, check_production_plan: bool = False
+ item_code: str,
+ warehouse: str,
+ completed_production_plans: list = None,
+ check_production_plan: bool = False,
) -> float:
"""Get total reserved quantity for any item in specified warehouse"""
wo = frappe.qb.DocType("Work Order")
@@ -1546,6 +1549,9 @@
if check_production_plan:
query = query.where(wo.production_plan.isnotnull())
+ if completed_production_plans:
+ query = query.where(wo.production_plan.notin(completed_production_plans))
+
return query.run()[0][0] or 0.0