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