Improve logging (previously "Lot: 0000001" would show, even if no lot was set)

update_quant_reservation.md

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

@yediel Thanks for letting me know - part of the logging code was using the wrong field name. Fixed now :)

Hi! I've just tried running the script on Odoo V17 EE and got the same
ValueError: Invalid field stock.move.line.reserved_qty in leaf ('reserved_qty', '!=', 0)
error. Looking in the model for stock.move.line, the field does indeed not seem to exist.
Edit:
Changing reserved_qty to quantity_product_uom seemed to have worked.
My problem wasn't the one described here, but we had a bunch of negative reservations and "phantom" reservations that we were not able to get rid of, and this seemed to have fixed it. Thank you!

Thanks for the ping @cparadis777, and glad to hear it worked for you!

I've updated the script with the new field name.

I'm interested to see how effective this is for actual Odoo 17.0 deployments. It looks like the quant-syncing code has been improve a lot, so maybe this script won't be necessary from now (at least for positive reservations).