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 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 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)
})
