Update Quant Reservation

"It is not possible to unreserve more products of <product name> than you have in stock"

Sometimes, the reserved_quantity on the stock.quant record for a product becomes out-of-sync with the sum of the reserved quantity in stock.move.lines for that product.

For example, consider:

• Product A (quant): on-hand = 50, reserved = 10
• Picking A (move): reserved = 10
• Picking B (move): reserved = 5

As we can see, Product A thinks that 10 units are reserved, when in reality, 15 units are actually reserved in pickings. If we try to validate/cancel/unreserve Move A and Move B, we'll get an error like above, because un-reserving 15 units would leave us with -5 reserved! Note that this prevents cancel, unreserve, AND validate.

This seems to be an edge-case bug, that may have been fixed in a recent (circa 2021-05-01) patch to Odoo (for v14, at least).

Note: This script was inspired by amoyaux's gist, which will show any discrepancies, but won't update any DB tables. Link here: https://gist.github.com/amoyaux/279aee13eaddacbddb435dafbc0a6295

Fixing this issue is easy: We just need to update the reserved_quantity field on stock.quant to match all open stock.move.line records.

How do I fix it?

• Ensure you're logged in as superuser (like this).
• Settings -> Technical -> Server Actions -> Create
  • Action To Do = Execute Python Code
  • Model = ir.actions.server (copy/paste into the box, it will show up as Server Action)
• Untick Active
• Click Run

Logs can be found at: Settings -> Technical -> Database Structure -> Logging.

Note: This script should work with any Odoo version from 12.0 up to 17.0. If you run into any issues, please log them on the Gist.

# Available variables:
#  - env: Odoo Environment on which the action is triggered
#  - model: Odoo Model of the record on which the action is triggered; is a void recordset
#  - record: record on which the action is triggered; may be void
#  - records: recordset of all records on which the action is triggered in multi-mode; may be void
#  - time, datetime, dateutil, timezone: useful Python libraries
#  - log: log(message, level='info'): logging function to record debug information in ir.logging table
#  - Warning: Warning Exception to use with raise
# To return an action, assign: action = {...}
#
# Written by Ryan Cole (github.com/ryanc-me)
# Source Gist: https://gist.github.com/ryanc-me/632fd59639a8a68e041c876abe87168f/
#
# Setup:
# - Settings -> Technical -> Actions -> Server Actions
# - Create new
#   * Name: Update Quant Reservation
#   * Model: ir.actions.server (Server Action)
#   * Execute Python Code
#   * Add this code
# - Ensure you're logged in as user #1 (Superuser)
# - Click Run
#
# Disabling the 3 vars below will cause the script to run in "dry-run" mode


# "fix" reservation on locations who are "bypass reservation"? (suggest: False)
DO_FIX_BYPASS_RESERVATION_LOCATION = False

# create quants if one doesn't exist, but we have move lines with reserved quantity? (suggest: True)
DO_FIX_MISSING_QUANT = True

# fix mismatched reserved quantity on quants? (suggest: True)
DO_FIX_MISMATCH_RESERVED_QTY = True

# choose some products to filter (product.template IDs) - leave blank to include all products
FILTER_PRODUCT_IDS = []


def get_version_data():
    odoo_version = int(env['ir.module.module'].search([('name', '=', 'base')]).installed_version.split('.')[0])
    data = {
        'odoo_version': odoo_version,
    }
    if odoo_version < 12:
        raise Warning("This script is only for Odoo 12.0 and later")
    elif odoo_version in (12, 13, 14):
        data.update({
            'move_line_reserved_qty': "product_qty",
            'product_type': "type",
            'stock_lot': "stock.production.lot",
        })
    elif odoo_version in (15,):
        data.update({
            'move_line_reserved_qty': "product_qty",
            'product_type': "detailed_type",
            'stock_lot': "stock.production.lot",
        })
    elif odoo_version in (16,):
        data.update({
            'move_line_reserved_qty': "reserved_qty",
            'product_type': "detailed_type",
            'stock_lot': "stock.lot",
        })
    elif odoo_version in (17,):
        data.update({
            'move_line_reserved_qty': "quantity_product_uom",
            'product_type': "detailed_type",
            'stock_lot': "stock.lot",
        })
    return data

def build_data():
    vdata = get_version_data()

    quants_domain = [
        ('product_id.%s' % vdata['product_type'], '=', 'product')
    ]
    move_lines_domain = [
        (vdata['move_line_reserved_qty'], '!=', 0),
        ('state', 'in', ('waiting', 'confirmed', 'partially_available', 'assigned')),
        ('product_id.%s' % vdata['product_type'], '=', 'product'),
    ]
    if FILTER_PRODUCT_IDS:
        quants_domain += [
            ('product_id.product_tmpl_id', 'in', FILTER_PRODUCT_IDS),
        ]
        move_lines_domain += [
            ('product_id.product_tmpl_id', 'in', FILTER_PRODUCT_IDS),
        ]

    quants = env['stock.quant'].search(quants_domain)
    move_lines = env["stock.move.line"].search(move_lines_domain)

    data_by_key = {}
    for quant in quants:
        key = (quant.product_id.id, quant.location_id.id, quant.lot_id.id, quant.package_id.id, quant.owner_id.id, quant.company_id.id)
        if key not in data_by_key:
            data_by_key[key] = {
                'quant': env['stock.quant'],
                'move_lines': env['stock.move.line']
            }
        data_by_key[key]['quant'] |= quant
    for move_line in move_lines:
        key = (move_line.product_id.id, move_line.location_id.id, move_line.lot_id.id, move_line.package_id.id, move_line.owner_id.id, move_line.company_id.id)
        if key not in data_by_key:
            data_by_key[key] = {
                'quant': env['stock.quant'],
                'move_lines': env['stock.move.line']
            }
        data_by_key[key]['move_lines'] |= move_line
    return data_by_key

def build_logline(messages, key, quants, moves, extra_lines, header):
    vdata = get_version_data()
    location = env['stock.location'].browse([key[1]]) if key[1] else env['stock.location']
    product = env['product.product'].browse([key[0]]) if key[0] else env['product.product']
    lot = env[vdata['stock_lot']].browse([key[2]]) if key[2] else env[vdata['stock_lot']]
    package = env['stock.quant.package'].browse([key[3]]) if key[3] else env['stock.quant.package']
    owner = env['res.partner'].browse([key[4]]) if key[4] else env['res.partner']
    company = env['res.company'].browse([key[5]]) if key[5] else env['res.company']
    adjust_msg = header
    adjust_msg += "\n - Company (%s): %s" % (str(company.id), company.display_name)
    adjust_msg += "\n - Location (%s): %s" % (str(location.id), location.display_name)
    adjust_msg += "\n - Product (%s): %s" % (str(product.id), product.display_name)
    adjust_msg += "\n - Lot: %s" % (lot.display_name if lot else "N/A")
    adjust_msg += "\n - Package: %s" % (package.display_name if package else "N/A")
    adjust_msg += "\n - Owner: %s" % (owner.display_name if owner else "N/A")
    adjust_msg += "\n - Quants: %s" % (', '.join([str(q.id) for q in quants]))
    adjust_msg += "\n - Moves: %s" % (', '.join([str(l.id) for l in moves]))
    if extra_lines:
        adjust_msg += extra_lines
    messages.append(adjust_msg)

def run():
    vdata = get_version_data()
    data_by_key = build_data()
    messages = []
    for key, data in data_by_key.items():
        try:
            quant = data['quant']
            move_lines = data['move_lines']
            location = env['stock.location'].browse([key[1]]) if key[1] else env['stock.location']
            product = env['product.product'].browse([key[0]]) if key[0] else env['product.product']
            lot = env[vdata['stock_lot']].browse([key[2]]) if key[2] else env[vdata['stock_lot']]
            package = env['stock.quant.package'].browse([key[3]]) if key[3] else env['stock.quant.package']
            owner = env['res.partner'].browse([key[4]]) if key[4] else env['res.partner']
            company = env['res.company'].browse([key[5]]) if key[5] else env['res.company']

            # CASE #1: more than one quant - shouldn't be possible!
            if len(quant) > 1:
                build_logline(messages, key, quant, move_lines, '', 'Multiple Quants Found:')
                continue

            # CASE #2: location is not stockable - quant reservation should be 0
            if location.should_bypass_reservation():
                # the location should bypass reservation; if there is some reserved quantity there,
                # then we can zero it. (note that this is just a housekeeping task - this case will
                # not cause any direct errors in Odoo.)

                # only update if the non-stocked location actually has some reserved qty
                reserved_quant = sum([0] + [q.reserved_quantity for q in quant])
                if reserved_quant != 0:
                    if DO_FIX_BYPASS_RESERVATION_LOCATION:
                        quant.write({'reserved_quantity': 0})
                    extra = "\n - Quantity: (%s)" % str(reserved_quant)
                    build_logline(messages, key, quant, move_lines, extra, 'Adjusted bypass-reservation location:')
                continue

            # CASE #3: no quant, but reserved quantity - create a new quant
            reserved_moves = 0
            for move in move_lines:
                reserved_moves += move[vdata['move_line_reserved_qty']]
            if not quant and reserved_moves != 0.0:
                # the quant doesn't exist, but we have some move lines with reserved quantity
                # we need to create a new quant so we can assign the reserved quantity to it

                if DO_FIX_MISSING_QUANT:
                    quant = env['stock.quant'].create({
                        'product_id': key[0],
                        'location_id': key[1],
                        'lot_id': key[2],
                        'package_id': key[3],
                        'owner_id': key[4],
                        'company_id': key[5],
                        'quantity': 0,
                        'reserved_quantity': 0,
                    })
                build_logline(messages, key, quant, move_lines, '', 'Created a new quant:')

            # we need to round qty after summing, but we can't import float_round
            # instead, use the _compute_qty() function. it's designed to convert from
            # one UoM to another, and will round to the UoM's precision. we can
            # provide the product UoM twice to 'convert' to/from the same UoM,
            # resulting in the same qty, but rounded!
            raw_reserved_qty = sum(move_lines.mapped(vdata['move_line_reserved_qty']))
            move_reserved_qty = move_lines.product_id.uom_id._compute_quantity(raw_reserved_qty, move_lines.product_id.uom_id)
            quant_reserved_qty = move_lines.product_id.uom_id._compute_quantity(quant.reserved_quantity, move_lines.product_id.uom_id)

            # CASE #4: quant reservation doesn't match move reservation - correct it
            if quant_reserved_qty != move_reserved_qty:
                # the quant reservation doesn't match the move reservation (either up or down)
                # we can adjust the quant to match
                
                if DO_FIX_MISMATCH_RESERVED_QTY:
                    quant.write({'reserved_quantity': move_reserved_qty})

                moves_formatted = ""
                for ml in move_lines:
                    # can't square-bracket access inside a lambda, so we do it the old fashioned way
                    moves_formatted += "\n    * "
                    moves_formatted += '%s: %s (origin: %s)' % (str(ml.id), ml[vdata['move_line_reserved_qty']], ml.origin)
                extra = "\n - Quantity: (quant=%s) (move=%s)" % (str(quant_reserved_qty), str(move_reserved_qty))
                extra += "\n - Details: " + moves_formatted
                build_logline(messages, key, quant, move_lines, extra, 'Adjusted reservation discrepancy:')
                continue

        except Exception as e:
            raise e
            try:
                extra = "\n - Exception: %s" % str(e)
                build_logline(messages, key, quant, move_lines, extra, 'Exception:')
            except Exception as e:
                pass

    log('Reservation fix done!\n\n' + '\n\n'.join(messages), level='info')

run()