Merge pull request #34313 from s-aga-r/fix-22-23-05690
perf: Stock Entry (Material Transfer)
diff --git a/erpnext/manufacturing/doctype/job_card/job_card.py b/erpnext/manufacturing/doctype/job_card/job_card.py
index 3133628..e82f379 100644
--- a/erpnext/manufacturing/doctype/job_card/job_card.py
+++ b/erpnext/manufacturing/doctype/job_card/job_card.py
@@ -561,7 +561,34 @@
)
def set_transferred_qty_in_job_card_item(self, ste_doc):
- from frappe.query_builder.functions import Sum
+ def _get_job_card_items_transferred_qty(ste_doc):
+ from frappe.query_builder.functions import Sum
+
+ job_card_items_transferred_qty = {}
+ job_card_items = [
+ x.get("job_card_item") for x in ste_doc.get("items") if x.get("job_card_item")
+ ]
+
+ if job_card_items:
+ se = frappe.qb.DocType("Stock Entry")
+ sed = frappe.qb.DocType("Stock Entry Detail")
+
+ query = (
+ frappe.qb.from_(sed)
+ .join(se)
+ .on(sed.parent == se.name)
+ .select(sed.job_card_item, Sum(sed.qty))
+ .where(
+ (sed.job_card_item.isin(job_card_items))
+ & (se.docstatus == 1)
+ & (se.purpose == "Material Transfer for Manufacture")
+ )
+ .groupby(sed.job_card_item)
+ )
+
+ job_card_items_transferred_qty = frappe._dict(query.run(as_list=True))
+
+ return job_card_items_transferred_qty
def _validate_over_transfer(row, transferred_qty):
"Block over transfer of items if not allowed in settings."
@@ -578,29 +605,23 @@
exc=JobCardOverTransferError,
)
- for row in ste_doc.items:
- if not row.job_card_item:
- continue
+ job_card_items_transferred_qty = _get_job_card_items_transferred_qty(ste_doc)
- sed = frappe.qb.DocType("Stock Entry Detail")
- se = frappe.qb.DocType("Stock Entry")
- transferred_qty = (
- frappe.qb.from_(sed)
- .join(se)
- .on(sed.parent == se.name)
- .select(Sum(sed.qty))
- .where(
- (sed.job_card_item == row.job_card_item)
- & (se.docstatus == 1)
- & (se.purpose == "Material Transfer for Manufacture")
- )
- ).run()[0][0]
-
+ if job_card_items_transferred_qty:
allow_excess = frappe.db.get_single_value("Manufacturing Settings", "job_card_excess_transfer")
- if not allow_excess:
- _validate_over_transfer(row, transferred_qty)
- frappe.db.set_value("Job Card Item", row.job_card_item, "transferred_qty", flt(transferred_qty))
+ for row in ste_doc.items:
+ if not row.job_card_item:
+ continue
+
+ transferred_qty = flt(job_card_items_transferred_qty.get(row.job_card_item))
+
+ if not allow_excess:
+ _validate_over_transfer(row, transferred_qty)
+
+ frappe.db.set_value(
+ "Job Card Item", row.job_card_item, "transferred_qty", flt(transferred_qty)
+ )
def set_transferred_qty(self, update_status=False):
"Set total FG Qty in Job Card for which RM was transferred."
diff --git a/erpnext/stock/doctype/material_request/material_request.py b/erpnext/stock/doctype/material_request/material_request.py
index dcbc460..8aeb751 100644
--- a/erpnext/stock/doctype/material_request/material_request.py
+++ b/erpnext/stock/doctype/material_request/material_request.py
@@ -10,6 +10,7 @@
import frappe
from frappe import _, msgprint
from frappe.model.mapper import get_mapped_doc
+from frappe.query_builder.functions import Sum
from frappe.utils import cint, cstr, flt, get_link_to_form, getdate, new_line_sep, nowdate
from erpnext.buying.utils import check_on_hold_or_closed_status, validate_for_items
@@ -180,6 +181,34 @@
self.update_requested_qty()
self.update_requested_qty_in_production_plan()
+ def get_mr_items_ordered_qty(self, mr_items):
+ mr_items_ordered_qty = {}
+ mr_items = [d.name for d in self.get("items") if d.name in mr_items]
+
+ doctype = qty_field = None
+ if self.material_request_type in ("Material Issue", "Material Transfer", "Customer Provided"):
+ doctype = frappe.qb.DocType("Stock Entry Detail")
+ qty_field = doctype.transfer_qty
+ elif self.material_request_type == "Manufacture":
+ doctype = frappe.qb.DocType("Work Order")
+ qty_field = doctype.qty
+
+ if doctype and qty_field:
+ query = (
+ frappe.qb.from_(doctype)
+ .select(doctype.material_request_item, Sum(qty_field))
+ .where(
+ (doctype.material_request == self.name)
+ & (doctype.material_request_item.isin(mr_items))
+ & (doctype.docstatus == 1)
+ )
+ .groupby(doctype.material_request_item)
+ )
+
+ mr_items_ordered_qty = frappe._dict(query.run())
+
+ return mr_items_ordered_qty
+
def update_completed_qty(self, mr_items=None, update_modified=True):
if self.material_request_type == "Purchase":
return
@@ -187,18 +216,13 @@
if not mr_items:
mr_items = [d.name for d in self.get("items")]
+ mr_items_ordered_qty = self.get_mr_items_ordered_qty(mr_items)
+ mr_qty_allowance = frappe.db.get_single_value("Stock Settings", "mr_qty_allowance")
+
for d in self.get("items"):
if d.name in mr_items:
if self.material_request_type in ("Material Issue", "Material Transfer", "Customer Provided"):
- d.ordered_qty = flt(
- frappe.db.sql(
- """select sum(transfer_qty)
- from `tabStock Entry Detail` where material_request = %s
- and material_request_item = %s and docstatus = 1""",
- (self.name, d.name),
- )[0][0]
- )
- mr_qty_allowance = frappe.db.get_single_value("Stock Settings", "mr_qty_allowance")
+ d.ordered_qty = flt(mr_items_ordered_qty.get(d.name))
if mr_qty_allowance:
allowed_qty = d.qty + (d.qty * (mr_qty_allowance / 100))
@@ -217,14 +241,7 @@
)
elif self.material_request_type == "Manufacture":
- d.ordered_qty = flt(
- frappe.db.sql(
- """select sum(qty)
- from `tabWork Order` where material_request = %s
- and material_request_item = %s and docstatus = 1""",
- (self.name, d.name),
- )[0][0]
- )
+ d.ordered_qty = flt(mr_items_ordered_qty.get(d.name))
frappe.db.set_value(d.doctype, d.name, "ordered_qty", d.ordered_qty)