def execute():
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():
cancelled_invoices = frappe.db.sql_list(
"""select name from `tabSales Invoice`
where docstatus = 2 and ifnull(update_stock, 0) = 1""")
if cancelled_invoices:
repost_for = frappe.db.sql(
"""select distinct item_code, warehouse from `tabStock Ledger Entry`
where voucher_type = 'Sales Invoice' and voucher_no in (%s)""" %
(', '.join(['%s'] * len(cancelled_invoices))),
tuple(cancelled_invoices))
frappe.db.sql(
"""delete from `tabStock Ledger Entry`
where voucher_type = 'Sales Invoice' and voucher_no in (%s)""" %
(', '.join(['%s'] * len(cancelled_invoices))),
tuple(cancelled_invoices))
for item_code, warehouse in repost_for:
repost_actual_qty(item_code, warehouse)
for item_code, warehouse in frappe.db.sql(
"""select distinct item_code, warehouse
from `tabPacked Item` where parenttype = 'Sales Invoice' and docstatus = 1"""
):
update_bin_qty(
item_code, warehouse,
{"reserved_qty": get_reserved_qty(item_code, warehouse)})
def execute():
cancelled_invoices = frappe.db.sql_list("""select name from `tabSales Invoice`
where docstatus = 2 and ifnull(update_stock, 0) = 1""")
if cancelled_invoices:
repost_for = frappe.db.sql("""select distinct item_code, warehouse from `tabStock Ledger Entry`
where voucher_type = 'Sales Invoice' and voucher_no in (%s)"""
% (', '.join(['%s']*len(cancelled_invoices))), tuple(cancelled_invoices))
frappe.db.sql("""delete from `tabStock Ledger Entry`
where voucher_type = 'Sales Invoice' and voucher_no in (%s)"""
% (', '.join(['%s']*len(cancelled_invoices))), tuple(cancelled_invoices))
for item_code, warehouse in repost_for:
repost_actual_qty(item_code, warehouse)
for item_code, warehouse in frappe.db.sql("""select distinct item_code, warehouse
from `tabPacked Item` where parenttype = 'Sales Invoice' and docstatus = 1"""):
update_bin_qty(item_code, warehouse, {
"reserved_qty": get_reserved_qty(item_code, warehouse)
})
def execute():
for item_code, warehouse in frappe.db.sql("select item_code, warehouse from tabBin where ifnull(reserved_qty, 0) < 0"):
update_bin_qty(item_code, warehouse, {
"reserved_qty": get_reserved_qty(item_code, warehouse)
})
