"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 = 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()
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).