def execute(): from erpnext.stock.stock_balance import get_indented_qty, get_ordered_qty, update_bin_qty count = 0 for item_code, warehouse in frappe.db.sql( """select distinct item_code, warehouse from (select item_code, warehouse from tabBin union select item_code, warehouse from `tabStock Ledger Entry`) a"""): try: if not (item_code and warehouse): continue count += 1 update_bin_qty( item_code, warehouse, { "indented_qty": get_indented_qty(item_code, warehouse), "ordered_qty": get_ordered_qty(item_code, warehouse), }, ) if count % 200 == 0: frappe.db.commit() except Exception: frappe.db.rollback()
def execute(): frappe.reload_doctype("Sales Order Item") repost_for = frappe.db.sql(""" select distinct item_code, warehouse from ( ( select distinct item_code, warehouse from `tabSales Order Item` where docstatus=1 ) UNION ( select distinct item_code, warehouse from `tabPacked Item` where docstatus=1 and parenttype='Sales Order' ) ) so_item where exists(select name from tabItem where name=so_item.item_code and ifnull(is_stock_item, 0)=1) """) for item_code, warehouse in repost_for: update_bin_qty( item_code, warehouse, {"reserved_qty": get_reserved_qty(item_code, warehouse)}) frappe.db.sql("""delete from tabBin where exists( select name from tabItem where name=tabBin.item_code and ifnull(is_stock_item, 0) = 0 ) """)
def execute(): for item_code, warehouse in frappe.db.sql("""select distinct production_item, fg_warehouse from `tabWork Order`"""): if frappe.db.exists("Item", item_code) and frappe.db.exists("Warehouse", warehouse): update_bin_qty(item_code, warehouse, { "planned_qty": get_planned_qty(item_code, warehouse) })
def execute(): frappe.reload_doctype("Sales Order Item") repost_for = frappe.db.sql(""" select distinct item_code, warehouse from ( ( select distinct item_code, warehouse from `tabSales Order Item` where docstatus=1 ) UNION ( select distinct item_code, warehouse from `tabPacked Item` where docstatus=1 and parenttype='Sales Order' ) ) so_item where exists(select name from tabItem where name=so_item.item_code and ifnull(is_stock_item, 0)=1) """) for item_code, warehouse in repost_for: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) }) frappe.db.sql("""delete from tabBin where exists( select name from tabItem where name=tabBin.item_code and ifnull(is_stock_item, 0) = 0 ) """)
def update_planned_qty(self): update_bin_qty(self.production_item, self.fg_warehouse, { "planned_qty": get_planned_qty(self.production_item, self.fg_warehouse) }) if self.material_request: mr_obj = frappe.get_doc("Material Request", self.material_request) mr_obj.update_requested_qty([self.material_request_item])
def recalculate_reserved_qty(): items = frappe.get_all("Bin", fields=['item_code', 'warehouse', 'reserved_qty']) for item in items: reserved_qty = get_reserved_qty(item.item_code, item.warehouse) if reserved_qty != item.reserved_qty: update_bin_qty(item.item_code, item.warehouse, {"reserved_qty": reserved_qty})
def execute(): bin_details = frappe.db.sql(""" SELECT item_code, warehouse FROM `tabBin`""",as_dict=1) for entry in bin_details: update_bin_qty(entry.get("item_code"), entry.get("warehouse"), { "indented_qty": get_indented_qty(entry.get("item_code"), entry.get("warehouse")) })
def execute(): for item_code, warehouse in frappe.db.sql( """select distinct production_item, fg_warehouse from `tabProduction Order`"""): if frappe.db.exists("Item", item_code) and frappe.db.exists( "Warehouse", warehouse): update_bin_qty( item_code, warehouse, {"planned_qty": get_planned_qty(item_code, warehouse)})
def update_ordered_qty(self, po_item_rows=None): """update requested qty (before ordered_qty is updated)""" item_wh_list = [] for d in self.get("items"): if (not po_item_rows or d.name in po_item_rows) and [d.item_code, d.warehouse] not in item_wh_list \ and frappe.db.get_value("Item", d.item_code, "is_stock_item") and d.warehouse: item_wh_list.append([d.item_code, d.warehouse]) for item_code, warehouse in item_wh_list: update_bin_qty(item_code, warehouse, { "ordered_qty": get_ordered_qty(item_code, warehouse) })
def execute(): for doctype in ("Sales Order Item", "Bin"): frappe.reload_doctype(doctype) repost_for = frappe.db.sql("""select distinct item_code, warehouse from `tabSales Order Item` where docstatus=1 and uom != stock_uom and exists(select name from tabItem where name=`tabSales Order Item`.item_code and ifnull(is_stock_item, 0)=1)""") for item_code, warehouse in repost_for: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) })
def update_requested_qty(self, mr_item_rows=None): """update requested qty (before ordered_qty is updated)""" item_wh_list = [] for d in self.get("items"): if (not mr_item_rows or d.name in mr_item_rows) and [d.item_code, d.warehouse] not in item_wh_list \ and frappe.db.get_value("Item", d.item_code, "is_stock_item") == 1 and d.warehouse: item_wh_list.append([d.item_code, d.warehouse]) for item_code, warehouse in item_wh_list: update_bin_qty(item_code, warehouse, { "indented_qty": get_indented_qty(item_code, warehouse) })
def execute(): for doctype in ("Sales Order Item", "Bin"): frappe.reload_doctype(doctype) repost_for = frappe.db.sql("""select distinct item_code, warehouse from `tabSales Order Item` where docstatus=1 and uom != stock_uom and exists(select name from tabItem where name=`tabSales Order Item`.item_code and ifnull(is_stock_item, 0)=1)""" ) for item_code, warehouse in repost_for: update_bin_qty( item_code, warehouse, {"reserved_qty": get_reserved_qty(item_code, warehouse)})
def update_ordered_qty(self, po_item_rows=None): """update requested qty (before ordered_qty is updated)""" item_wh_list = [] for d in self.get("items"): if (not po_item_rows or d.name in po_item_rows) \ and [d.item_code, d.warehouse] not in item_wh_list \ and frappe.db.get_value("Item", d.item_code, "is_stock_item") \ and d.warehouse and not d.delivered_by_supplier: item_wh_list.append([d.item_code, d.warehouse]) for item_code, warehouse in item_wh_list: update_bin_qty( item_code, warehouse, {"ordered_qty": get_ordered_qty(item_code, warehouse)})
def execute(): from erpnext.stock.stock_balance import update_bin_qty, get_indented_qty count=0 for item_code, warehouse in frappe.db.sql("""select distinct item_code, warehouse from `tabMaterial Request Item` where docstatus = 1"""): try: count += 1 update_bin_qty(item_code, warehouse, { "indented_qty": get_indented_qty(item_code, warehouse), }) if count % 200 == 0: frappe.db.commit() except: frappe.db.rollback()
def execute(): from erpnext.stock.stock_balance import update_bin_qty, get_indented_qty, get_ordered_qty count=0 for item_code, warehouse in frappe.db.sql("""select distinct item_code, warehouse from (select item_code, warehouse from tabBin union select item_code, warehouse from `tabStock Ledger Entry`) a"""): try: count += 1 update_bin_qty(item_code, warehouse, { "indented_qty": get_indented_qty(item_code, warehouse), "ordered_qty": get_ordered_qty(item_code, warehouse) }) if count % 200 == 0: frappe.db.commit() except: frappe.db.rollback()
def validate_and_delete_children(parent, data): deleted_children = [] updated_item_names = [d.get("docname") for d in data] for item in parent.items: if item.name not in updated_item_names: deleted_children.append(item) for d in deleted_children: if parent.doctype == "Sales Order": if flt(d.delivered_qty): frappe.throw( _("Row #{0}: Cannot delete item {1} which has already been delivered" ).format(d.idx, d.item_code)) if flt(d.work_order_qty): frappe.throw( _("Row #{0}: Cannot delete item {1} which has work order assigned to it." ).format(d.idx, d.item_code)) if flt(d.ordered_qty): frappe.throw( _("Row #{0}: Cannot delete item {1} which is assigned to customer's purchase order." ).format(d.idx, d.item_code)) if parent.doctype == "Purchase Order" and flt(d.received_qty): frappe.throw( _("Row #{0}: Cannot delete item {1} which has already been received" ).format(d.idx, d.item_code)) if flt(d.billed_amt): frappe.throw( _("Row #{0}: Cannot delete item {1} which has already been billed." ).format(d.idx, d.item_code)) d.cancel() if d.doctype == "Sales Order Item": """Delete document.""" frappe.delete_doc(d.doctype, d.name, ignore_permissions=False, flags=d.flags) update_bin_qty( d.item_code, d.warehouse, {"reserved_qty": get_reserved_qty(d.item_code, d.warehouse)}) else: d.delete()
def update_reserved_qty(self, so_item_rows=None): """update requested qty (before ordered_qty is updated)""" item_wh_list = [] def _valid_for_reserve(item_code, warehouse): if item_code and warehouse and [item_code, warehouse] not in item_wh_list \ and frappe.get_cached_value("Item", item_code, "is_stock_item"): item_wh_list.append([item_code, warehouse]) for d in self.get("items"): if (not so_item_rows or d.name in so_item_rows) and not d.delivered_by_supplier: if self.has_product_bundle(d.item_code): for p in self.get("packed_items"): if p.parent_detail_docname == d.name and p.parent_item == d.item_code: _valid_for_reserve(p.item_code, p.warehouse) else: _valid_for_reserve(d.item_code, d.warehouse) for item_code, warehouse in item_wh_list: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) })
def update_reserved_qty(self, so_item_rows=None): """update requested qty (before ordered_qty is updated)""" item_wh_list = [] def _valid_for_reserve(item_code, warehouse): if item_code and warehouse and [item_code, warehouse] not in item_wh_list \ and frappe.db.get_value("Item", item_code, "is_stock_item"): item_wh_list.append([item_code, warehouse]) for d in self.get("items"): if (not so_item_rows or d.name in so_item_rows): _valid_for_reserve(d.item_code, d.warehouse) if self.has_product_bundle(d.item_code): for p in self.get("packed_items"): if p.parent_detail_docname == d.name and p.parent_item == d.item_code: _valid_for_reserve(p.item_code, p.warehouse) for item_code, warehouse in item_wh_list: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) })
def test_check_stock_uom_with_bin_no_sle(self): from erpnext.stock.stock_balance import update_bin_qty item = create_item("_Item with bin qty") item.stock_uom = "Gram" item.save() update_bin_qty(item.item_code, "_Test Warehouse - _TC", {"reserved_qty": 10}) item.stock_uom = "Kilometer" self.assertRaises(frappe.ValidationError, item.save) update_bin_qty(item.item_code, "_Test Warehouse - _TC", {"reserved_qty": 0}) item.load_from_db() item.stock_uom = "Kilometer" try: item.save() except frappe.ValidationError as e: self.fail( f"UoM change not allowed even though no SLE / BIN with positive qty exists: {e}" )
def update_planned_qty(self): update_bin_qty( self.production_item, self.fg_warehouse, { "planned_qty": get_planned_qty(self.production_item, self.fg_warehouse) })
def update_planned_qty(self): update_bin_qty(self.production_item, self.fg_warehouse, { "planned_qty": get_planned_qty(self.production_item, self.fg_warehouse) })