Merge pull request #36221 from s-aga-r/FIX-ISS-23-24-02079
perf: use `LEFT JOIN` instead of `NOT EXISTS`
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.js b/erpnext/manufacturing/doctype/production_plan/production_plan.js
index 4898691..46c554c 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.js
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.js
@@ -9,19 +9,25 @@
item.temporary_name = item.name;
});
},
+
setup(frm) {
+ frm.trigger("setup_queries");
+
frm.custom_make_buttons = {
'Work Order': 'Work Order / Subcontract PO',
'Material Request': 'Material Request',
};
+ },
- frm.fields_dict['po_items'].grid.get_field('warehouse').get_query = function(doc) {
+ setup_queries(frm) {
+ frm.set_query("sales_order", "sales_orders", () => {
return {
+ query: "erpnext.manufacturing.doctype.production_plan.production_plan.sales_order_query",
filters: {
- company: doc.company
+ company: frm.doc.company,
}
}
- }
+ });
frm.set_query('for_warehouse', function(doc) {
return {
@@ -42,32 +48,40 @@
};
});
- frm.fields_dict['po_items'].grid.get_field('item_code').get_query = function(doc) {
+ frm.set_query("item_code", "po_items", (doc, cdt, cdn) => {
return {
query: "erpnext.controllers.queries.item_query",
filters:{
'is_stock_item': 1,
}
}
- }
+ });
- frm.fields_dict['po_items'].grid.get_field('bom_no').get_query = function(doc, cdt, cdn) {
+ frm.set_query("bom_no", "po_items", (doc, cdt, cdn) => {
var d = locals[cdt][cdn];
if (d.item_code) {
return {
query: "erpnext.controllers.queries.bom",
- filters:{'item': cstr(d.item_code), 'docstatus': 1}
+ filters:{'item': d.item_code, 'docstatus': 1}
}
} else frappe.msgprint(__("Please enter Item first"));
- }
+ });
- frm.fields_dict['mr_items'].grid.get_field('warehouse').get_query = function(doc) {
+ frm.set_query("warehouse", "mr_items", (doc) => {
return {
filters: {
company: doc.company
}
}
- }
+ });
+
+ frm.set_query("warehouse", "po_items", (doc) => {
+ return {
+ filters: {
+ company: doc.company
+ }
+ }
+ });
},
refresh(frm) {
@@ -436,7 +450,7 @@
}
});
}
- }
+ },
});
frappe.ui.form.on("Material Request Plan Item", {
@@ -467,31 +481,36 @@
frappe.ui.form.on("Production Plan Sales Order", {
sales_order(frm, cdt, cdn) {
- const { sales_order } = locals[cdt][cdn];
+ let row = locals[cdt][cdn];
+ const sales_order = row.sales_order;
if (!sales_order) {
return;
}
- frappe.call({
- method: "erpnext.manufacturing.doctype.production_plan.production_plan.get_so_details",
- args: { sales_order },
- callback(r) {
- const {transaction_date, customer, grand_total} = r.message;
- frappe.model.set_value(cdt, cdn, 'sales_order_date', transaction_date);
- frappe.model.set_value(cdt, cdn, 'customer', customer);
- frappe.model.set_value(cdt, cdn, 'grand_total', grand_total);
- }
- });
+
+ if (row.sales_order) {
+ frm.call({
+ method: "validate_sales_orders",
+ doc: frm.doc,
+ args: {
+ sales_order: row.sales_order,
+ },
+ callback(r) {
+ frappe.call({
+ method: "erpnext.manufacturing.doctype.production_plan.production_plan.get_so_details",
+ args: { sales_order },
+ callback(r) {
+ const {transaction_date, customer, grand_total} = r.message;
+ frappe.model.set_value(cdt, cdn, 'sales_order_date', transaction_date);
+ frappe.model.set_value(cdt, cdn, 'customer', customer);
+ frappe.model.set_value(cdt, cdn, 'grand_total', grand_total);
+ }
+ });
+ }
+ });
+ }
}
});
-cur_frm.fields_dict['sales_orders'].grid.get_field("sales_order").get_query = function() {
- return{
- filters: [
- ['Sales Order','docstatus', '=' ,1]
- ]
- }
-};
-
frappe.tour['Production Plan'] = [
{
fieldname: "get_items_from",
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.json b/erpnext/manufacturing/doctype/production_plan/production_plan.json
index 232f1cb..0d0fd5e 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.json
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.json
@@ -228,10 +228,10 @@
},
{
"default": "0",
- "description": "To know more about projected quantity, <a href=\"https://erpnext.com/docs/user/manual/en/stock/projected-quantity\" style=\"text-decoration: underline;\" target=\"_blank\">click here</a>.",
+ "description": "If enabled, the system won't create material requests for the available items.",
"fieldname": "ignore_existing_ordered_qty",
"fieldtype": "Check",
- "label": "Ignore Existing Projected Quantity"
+ "label": "Ignore Available Stock"
},
{
"fieldname": "column_break_25",
@@ -339,7 +339,7 @@
"depends_on": "eval:doc.get_items_from == 'Sales Order'",
"fieldname": "combine_items",
"fieldtype": "Check",
- "label": "Consolidate Items"
+ "label": "Consolidate Sales Order Items"
},
{
"fieldname": "section_break_25",
@@ -399,7 +399,7 @@
},
{
"default": "0",
- "description": "System consider the projected quantity to check available or will be available sub-assembly items ",
+ "description": "If this checkbox is enabled, then the system won\u2019t run the MRP for the available sub-assembly items.",
"fieldname": "skip_available_sub_assembly_item",
"fieldtype": "Check",
"label": "Skip Available Sub Assembly Items"
@@ -422,7 +422,7 @@
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2023-05-22 23:36:31.770517",
+ "modified": "2023-07-28 13:37:43.926686",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "Production Plan",
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index d8cc8f6..261aa76 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -39,6 +39,36 @@
self.set_status()
self._rename_temporary_references()
validate_uom_is_integer(self, "stock_uom", "planned_qty")
+ self.validate_sales_orders()
+
+ @frappe.whitelist()
+ def validate_sales_orders(self, sales_order=None):
+ sales_orders = []
+
+ if sales_order:
+ sales_orders.append(sales_order)
+ else:
+ sales_orders = [row.sales_order for row in self.sales_orders if row.sales_order]
+
+ data = sales_order_query(filters={"company": self.company, "sales_orders": sales_orders})
+
+ title = _("Production Plan Already Submitted")
+ if not data:
+ msg = _("No items are available in the sales order {0} for production").format(sales_orders[0])
+ if len(sales_orders) > 1:
+ sales_orders = ", ".join(sales_orders)
+ msg = _("No items are available in sales orders {0} for production").format(sales_orders)
+
+ frappe.throw(msg, title=title)
+
+ data = [d[0] for d in data]
+
+ for sales_order in sales_orders:
+ if sales_order not in data:
+ frappe.throw(
+ _("No items are available in the sales order {0} for production").format(sales_order),
+ title=title,
+ )
def set_pending_qty_in_row_without_reference(self):
"Set Pending Qty in independent rows (not from SO or MR)."
@@ -205,6 +235,7 @@
).as_("pending_qty"),
so_item.description,
so_item.name,
+ so_item.bom_no,
)
.distinct()
.where(
@@ -342,7 +373,7 @@
"item_code": data.item_code,
"description": data.description or item_details.description,
"stock_uom": item_details and item_details.stock_uom or "",
- "bom_no": item_details and item_details.bom_no or "",
+ "bom_no": data.bom_no or item_details and item_details.bom_no or "",
"planned_qty": data.pending_qty,
"pending_qty": data.pending_qty,
"planned_start_date": now_datetime(),
@@ -401,11 +432,50 @@
def on_submit(self):
self.update_bin_qty()
+ self.update_sales_order()
def on_cancel(self):
self.db_set("status", "Cancelled")
self.delete_draft_work_order()
self.update_bin_qty()
+ self.update_sales_order()
+
+ def update_sales_order(self):
+ sales_orders = [row.sales_order for row in self.po_items if row.sales_order]
+ if sales_orders:
+ so_wise_planned_qty = self.get_so_wise_planned_qty(sales_orders)
+
+ for row in self.po_items:
+ if not row.sales_order and not row.sales_order_item:
+ continue
+
+ key = (row.sales_order, row.sales_order_item)
+ frappe.db.set_value(
+ "Sales Order Item",
+ row.sales_order_item,
+ "production_plan_qty",
+ flt(so_wise_planned_qty.get(key)),
+ )
+
+ @staticmethod
+ def get_so_wise_planned_qty(sales_orders):
+ so_wise_planned_qty = frappe._dict()
+ data = frappe.get_all(
+ "Production Plan Item",
+ fields=["sales_order", "sales_order_item", "SUM(planned_qty) as qty"],
+ filters={
+ "sales_order": ("in", sales_orders),
+ "docstatus": 1,
+ "sales_order_item": ("is", "set"),
+ },
+ group_by="sales_order, sales_order_item",
+ )
+
+ for row in data:
+ key = (row.sales_order, row.sales_order_item)
+ so_wise_planned_qty[key] = row.qty
+
+ return so_wise_planned_qty
def update_bin_qty(self):
for d in self.mr_items:
@@ -719,6 +789,9 @@
sub_assembly_items_store = [] # temporary store to process all subassembly items
for row in self.po_items:
+ if self.skip_available_sub_assembly_item and not row.warehouse:
+ frappe.throw(_("Row #{0}: Please select the FG Warehouse in Assembly Items").format(row.idx))
+
if not row.item_code:
frappe.throw(_("Row #{0}: Please select Item Code in Assembly Items").format(row.idx))
@@ -1142,7 +1215,7 @@
& (so.docstatus == 1)
& (so.status.notin(["Stopped", "Closed"]))
& (so.company == self.company)
- & (so_item.qty > so_item.work_order_qty)
+ & (so_item.qty > so_item.production_plan_qty)
)
)
@@ -1566,7 +1639,6 @@
def get_raw_materials_of_sub_assembly_items(
item_details, company, bom_no, include_non_stock_items, sub_assembly_items, planned_qty=1
):
-
bei = frappe.qb.DocType("BOM Item")
bom = frappe.qb.DocType("BOM")
item = frappe.qb.DocType("Item")
@@ -1609,7 +1681,10 @@
for item in items:
key = (item.item_code, item.bom_no)
- if item.bom_no and key in sub_assembly_items:
+ if item.bom_no and key not in sub_assembly_items:
+ continue
+
+ if item.bom_no:
planned_qty = flt(sub_assembly_items[key])
get_raw_materials_of_sub_assembly_items(
item_details,
@@ -1626,3 +1701,42 @@
item_details.setdefault(item.get("item_code"), item)
return item_details
+
+
+@frappe.whitelist()
+def sales_order_query(
+ doctype=None, txt=None, searchfield=None, start=None, page_len=None, filters=None
+):
+ frappe.has_permission("Production Plan", throw=True)
+
+ if not filters:
+ filters = {}
+
+ so_table = frappe.qb.DocType("Sales Order")
+ table = frappe.qb.DocType("Sales Order Item")
+
+ query = (
+ frappe.qb.from_(so_table)
+ .join(table)
+ .on(table.parent == so_table.name)
+ .select(table.parent)
+ .distinct()
+ .where((table.qty > table.production_plan_qty) & (table.docstatus == 1))
+ )
+
+ if filters.get("company"):
+ query = query.where(so_table.company == filters.get("company"))
+
+ if filters.get("sales_orders"):
+ query = query.where(so_table.name.isin(filters.get("sales_orders")))
+
+ if txt:
+ query = query.where(table.item_code.like(f"{txt}%"))
+
+ if page_len:
+ query = query.limit(page_len)
+
+ if start:
+ query = query.offset(start)
+
+ return query.run()
diff --git a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
index f60dbfc..2871a29 100644
--- a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
@@ -225,6 +225,102 @@
self.assertEqual(sales_orders, [])
+ def test_donot_allow_to_make_multiple_pp_against_same_so(self):
+ item = "Test SO Production Item 1"
+ create_item(item)
+
+ raw_material = "Test SO RM Production Item 1"
+ create_item(raw_material)
+
+ if not frappe.db.get_value("BOM", {"item": item}):
+ make_bom(item=item, raw_materials=[raw_material])
+
+ so = make_sales_order(item_code=item, qty=4)
+ pln = frappe.new_doc("Production Plan")
+ pln.company = so.company
+ pln.get_items_from = "Sales Order"
+
+ pln.append(
+ "sales_orders",
+ {
+ "sales_order": so.name,
+ "sales_order_date": so.transaction_date,
+ "customer": so.customer,
+ "grand_total": so.grand_total,
+ },
+ )
+
+ pln.get_so_items()
+ pln.submit()
+
+ pln = frappe.new_doc("Production Plan")
+ pln.company = so.company
+ pln.get_items_from = "Sales Order"
+
+ pln.append(
+ "sales_orders",
+ {
+ "sales_order": so.name,
+ "sales_order_date": so.transaction_date,
+ "customer": so.customer,
+ "grand_total": so.grand_total,
+ },
+ )
+
+ pln.get_so_items()
+ self.assertRaises(frappe.ValidationError, pln.save)
+
+ def test_so_based_bill_of_material(self):
+ item = "Test SO Production Item 1"
+ create_item(item)
+
+ raw_material = "Test SO RM Production Item 1"
+ create_item(raw_material)
+
+ bom1 = make_bom(item=item, raw_materials=[raw_material])
+
+ so = make_sales_order(item_code=item, qty=4)
+
+ # Create new BOM and assign to new sales order
+ bom2 = make_bom(item=item, raw_materials=[raw_material])
+ so2 = make_sales_order(item_code=item, qty=4)
+
+ pln1 = frappe.new_doc("Production Plan")
+ pln1.company = so.company
+ pln1.get_items_from = "Sales Order"
+
+ pln1.append(
+ "sales_orders",
+ {
+ "sales_order": so.name,
+ "sales_order_date": so.transaction_date,
+ "customer": so.customer,
+ "grand_total": so.grand_total,
+ },
+ )
+
+ pln1.get_so_items()
+
+ self.assertEqual(pln1.po_items[0].bom_no, bom1.name)
+
+ pln2 = frappe.new_doc("Production Plan")
+ pln2.company = so2.company
+ pln2.get_items_from = "Sales Order"
+
+ pln2.append(
+ "sales_orders",
+ {
+ "sales_order": so2.name,
+ "sales_order_date": so2.transaction_date,
+ "customer": so2.customer,
+ "grand_total": so2.grand_total,
+ },
+ )
+
+ pln2.get_so_items()
+
+ self.assertEqual(pln2.po_items[0].bom_no, bom2.name)
+
def test_production_plan_combine_items(self):
"Test combining FG items in Production Plan."
item = "Test Production Item 1"
diff --git a/erpnext/selling/doctype/sales_order_item/sales_order_item.json b/erpnext/selling/doctype/sales_order_item/sales_order_item.json
index 5c7e10a..07565c3 100644
--- a/erpnext/selling/doctype/sales_order_item/sales_order_item.json
+++ b/erpnext/selling/doctype/sales_order_item/sales_order_item.json
@@ -84,6 +84,7 @@
"actual_qty",
"ordered_qty",
"planned_qty",
+ "production_plan_qty",
"column_break_69",
"work_order_qty",
"delivered_qty",
@@ -882,12 +883,19 @@
"print_hide": 1,
"read_only": 1,
"report_hide": 1
+ },
+ {
+ "fieldname": "production_plan_qty",
+ "fieldtype": "Float",
+ "label": "Production Plan Qty",
+ "no_copy": 1,
+ "read_only": 1
}
],
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2023-04-04 10:44:05.707488",
+ "modified": "2023-07-28 14:56:42.031636",
"modified_by": "Administrator",
"module": "Selling",
"name": "Sales Order Item",