"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.
- 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 asServer Action)
- Action To Do =
- 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 = False
# fix mismatched reserved quantity on quants? (suggest: True)
DO_FIX_MISMATCH_RESERVED_QTY = False
# choose some products to filter (product.template IDs) - leave blank to include all products
FILTER_PRODUCT_IDS = []
def get_version_data():
odoo_version_raw = env['ir.module.module'].search([('name', '=', 'base')]).installed_version
if '~' in odoo_version_raw:
odoo_version_raw = odoo_version_raw.split('~')[1]
odoo_version = int(odoo_version_raw.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",
})
elif odoo_version in (18,):
data.update({
'move_line_reserved_qty': "quantity",
'product_type': "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()
Updated to support Odoo
18.0