Archives
Contributors
Re: stock: Retroactively removing lots from incompatible products in stock move history
Re: stock: Retroactively removing lots from incompatible products in stock move history
Re: stock: Retroactively removing lots from incompatible products in
stock move history
Personally if they are meant to have lots I wouldn't null them. Just create them new ones and set.
Now as for quants there are 2 choices (these are v14) - first backport the Odoo way with a server action like this (if database is small) or the second is the same thing just specific to products.
Model Server Action
quants = env['stock.quant'].sudo().search([])
move_line_ids = []
move_line_to_recompute_ids = []
logging = ''
for quant in quants:
move_lines = env['stock.move.line'].search([
('product_id', '=', quant.product_id.id),
('location_id', '=', quant.location_id.id),
('lot_id', '=', quant.lot_id.id),
('package_id', '=', quant.package_id.id),
('owner_id', '=', quant.owner_id.id),
('product_qty', '!=', 0),
])
move_line_ids += move_lines.ids
reserved_on_move_lines = sum(move_lines.mapped('product_qty'))
move_line_str = str.join(', ', [str(move_line_id) for move_line_id in move_lines.ids])
if quant.location_id.should_bypass_reservation():
# If a quant is in a location that should bypass the reservation, its `reserved_quantity` field
# should be 0.
if quant.reserved_quantity != 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is not 0 while its location should bypass the reservation\n"
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
else:
logging += "no move lines are reserved on it, you can safely reset its `reserved_quantity` to 0\n"
logging += '******************\n'
quant.write({'reserved_quantity': 0})
else:
# If a quant is in a reservable location, its `reserved_quantity` should be exactly the sum
# of the `product_qty` of all the partially_available / assigned move lines with the same
# characteristics.
if quant.reserved_quantity == 0:
if move_lines:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is 0 while these move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
elif quant.reserved_quantity < 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is negative while it should not happen\n"
quant.write({'reserved_quantity': 0})
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
logging += '******************\n'
else:
if reserved_on_move_lines != quant.reserved_quantity:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` does not reflect the move lines reservation\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
else:
if any(move_line.product_qty < 0 for move_line in
move_lines):
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` correctly reflects the move lines reservation but some are negatives\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
move_lines = env['stock.move.line'].search([('product_id.type', '=',
'product'), ('product_qty', '!=', 0), ('id', 'not in',
move_line_ids)])
move_lines_to_unreserve = []
for move_line in move_lines:
if not move_line.location_id.should_bypass_reservation():
logging += "Problematic move line found: %s (reserved_quantity: %s)\n" % (move_line.id, move_line.product_qty)
logging += "There is no exiting quants despite its `reserved_quantity`\n"
logging += '******************\n'
move_lines_to_unreserve.append(move_line.id)
move_line_to_recompute_ids.append(move_line.id)
if len(move_lines_to_unreserve) > 0:
env.cr.execute("""
UPDATE stock_move_line SET product_uom_qty = 0, product_qty = 0 WHERE id in %s ;
"""
% (tuple(move_lines_to_unreserve), ))
if logging:
env['ir.logging'].sudo().create({
'name': 'Unreserve stock.quant and stock.move.line',
'type': 'server',
'level': 'DEBUG',
'dbname': env.cr.dbname,
'message': logging,
'func': '_update_reserved_quantity',
'path': 'addons/stock/models/stock_quant.py',
'line': '0',
})
if move_line_to_recompute_ids:
env['stock.move.line'].browse(move_line_to_recompute_ids).move_id._recompute_state()
move_line_ids = []
move_line_to_recompute_ids = []
logging = ''
for quant in quants:
move_lines = env['stock.move.line'].search([
('product_id', '=', quant.product_id.id),
('location_id', '=', quant.location_id.id),
('lot_id', '=', quant.lot_id.id),
('package_id', '=', quant.package_id.id),
('owner_id', '=', quant.owner_id.id),
('product_qty', '!=', 0),
])
move_line_ids += move_lines.ids
reserved_on_move_lines = sum(move_lines.mapped('product_qty'))
move_line_str = str.join(', ', [str(move_line_id) for move_line_id in move_lines.ids])
if quant.location_id.should_bypass_reservation():
# If a quant is in a location that should bypass the reservation, its `reserved_quantity` field
# should be 0.
if quant.reserved_quantity != 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is not 0 while its location should bypass the reservation\n"
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
else:
logging += "no move lines are reserved on it, you can safely reset its `reserved_quantity` to 0\n"
logging += '******************\n'
quant.write({'reserved_quantity': 0})
else:
# If a quant is in a reservable location, its `reserved_quantity` should be exactly the sum
# of the `product_qty` of all the partially_available / assigned move lines with the same
# characteristics.
if quant.reserved_quantity == 0:
if move_lines:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is 0 while these move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
elif quant.reserved_quantity < 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is negative while it should not happen\n"
quant.write({'reserved_quantity': 0})
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
logging += '******************\n'
else:
if reserved_on_move_lines != quant.reserved_quantity:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` does not reflect the move lines reservation\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
else:
if any(move_line.product_qty < 0 for move_line in
move_lines):
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` correctly reflects the move lines reservation but some are negatives\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
move_lines = env['stock.move.line'].search([('product_id.type', '=',
'product'), ('product_qty', '!=', 0), ('id', 'not in',
move_line_ids)])
move_lines_to_unreserve = []
for move_line in move_lines:
if not move_line.location_id.should_bypass_reservation():
logging += "Problematic move line found: %s (reserved_quantity: %s)\n" % (move_line.id, move_line.product_qty)
logging += "There is no exiting quants despite its `reserved_quantity`\n"
logging += '******************\n'
move_lines_to_unreserve.append(move_line.id)
move_line_to_recompute_ids.append(move_line.id)
if len(move_lines_to_unreserve) > 0:
env.cr.execute("""
UPDATE stock_move_line SET product_uom_qty = 0, product_qty = 0 WHERE id in %s ;
"""
% (tuple(move_lines_to_unreserve), ))
if logging:
env['ir.logging'].sudo().create({
'name': 'Unreserve stock.quant and stock.move.line',
'type': 'server',
'level': 'DEBUG',
'dbname': env.cr.dbname,
'message': logging,
'func': '_update_reserved_quantity',
'path': 'addons/stock/models/stock_quant.py',
'line': '0',
})
if move_line_to_recompute_ids:
env['stock.move.line'].browse(move_line_to_recompute_ids).move_id._recompute_state()
Or if database is large/many users I create a Server Action on Product secured to inventory manager with an action
if records:
quants = env['stock.quant'].sudo().search([('product_id', 'in', records.ids)])
move_line_ids = []
move_line_to_recompute_ids = []
logging = ''
for quant in quants:
move_lines = env['stock.move.line'].search([
('product_id', '=', quant.product_id.id),
('location_id', '=', quant.location_id.id),
('lot_id', '=', quant.lot_id.id),
('package_id', '=', quant.package_id.id),
('owner_id', '=', quant.owner_id.id),
('product_qty', '!=', 0),
])
move_line_ids += move_lines.ids
reserved_on_move_lines = sum(move_lines.mapped('product_qty'))
move_line_str = str.join(', ', [str(move_line_id) for move_line_id in move_lines.ids])
if quant.location_id.should_bypass_reservation():
# If a quant is in a location that should bypass the reservation, its `reserved_quantity` field
# should be 0.
if quant.reserved_quantity != 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is not 0 while its location should bypass the reservation\n"
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
else:
logging += "no move lines are reserved on it, you can safely reset its `reserved_quantity` to 0\n"
logging += '******************\n'
quant.write({'reserved_quantity': 0})
else:
# If a quant is in a reservable location, its `reserved_quantity` should be exactly the sum
# of the `product_qty` of all the partially_available / assigned move lines with the same
# characteristics.
if quant.reserved_quantity == 0:
if move_lines:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is 0 while these move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
elif quant.reserved_quantity < 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is negative while it should not happen\n"
quant.write({'reserved_quantity': 0})
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
logging += '******************\n'
else:
if reserved_on_move_lines != quant.reserved_quantity:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` does not reflect the move lines reservation\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
else:
if any(move_line.product_qty < 0 for move_line in
move_lines):
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` correctly reflects the move lines reservation but some are negatives\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
move_lines = env['stock.move.line'].search([('product_id.type', '=',
'product'), ('product_qty', '!=', 0), ('id', 'not in',
move_line_ids), ('product_id', 'in', records.ids)])
move_lines_to_unreserve = []
for move_line in move_lines:
if not move_line.location_id.should_bypass_reservation():
logging += "Problematic move line found: %s (reserved_quantity: %s)\n" % (move_line.id, move_line.product_qty)
logging += "There is no exiting quants despite its `reserved_quantity`\n"
logging += '******************\n'
move_lines_to_unreserve.append(move_line.id)
move_line_to_recompute_ids.append(move_line.id)
if len(move_lines_to_unreserve) > 0:
env.cr.execute("""
UPDATE stock_move_line SET product_uom_qty = 0, product_qty = 0 WHERE id in %s ;
"""
% (tuple(move_lines_to_unreserve), ))
if logging:
env['ir.logging'].sudo().create({
'name': 'Unreserve stock.quant and stock.move.line',
'type': 'server',
'level': 'INFO',
'dbname': env.cr.dbname,
'message': logging,
'func': '_update_reserved_quantity',
'path': 'addons/stock/models/stock_quant.py',
'line': '0',
})
if move_line_to_recompute_ids:
env['stock.move.line'].browse(move_line_to_recompute_ids).move_id._recompute_state()
quants = env['stock.quant'].sudo().search([('product_id', 'in', records.ids)])
move_line_ids = []
move_line_to_recompute_ids = []
logging = ''
for quant in quants:
move_lines = env['stock.move.line'].search([
('product_id', '=', quant.product_id.id),
('location_id', '=', quant.location_id.id),
('lot_id', '=', quant.lot_id.id),
('package_id', '=', quant.package_id.id),
('owner_id', '=', quant.owner_id.id),
('product_qty', '!=', 0),
])
move_line_ids += move_lines.ids
reserved_on_move_lines = sum(move_lines.mapped('product_qty'))
move_line_str = str.join(', ', [str(move_line_id) for move_line_id in move_lines.ids])
if quant.location_id.should_bypass_reservation():
# If a quant is in a location that should bypass the reservation, its `reserved_quantity` field
# should be 0.
if quant.reserved_quantity != 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is not 0 while its location should bypass the reservation\n"
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
else:
logging += "no move lines are reserved on it, you can safely reset its `reserved_quantity` to 0\n"
logging += '******************\n'
quant.write({'reserved_quantity': 0})
else:
# If a quant is in a reservable location, its `reserved_quantity` should be exactly the sum
# of the `product_qty` of all the partially_available / assigned move lines with the same
# characteristics.
if quant.reserved_quantity == 0:
if move_lines:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is 0 while these move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
elif quant.reserved_quantity < 0:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` field is negative while it should not happen\n"
quant.write({'reserved_quantity': 0})
if move_lines:
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
logging += '******************\n'
else:
if reserved_on_move_lines != quant.reserved_quantity:
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` does not reflect the move lines reservation\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
else:
if any(move_line.product_qty < 0 for move_line in
move_lines):
logging += "Problematic quant found: %s (quantity: %s, reserved_quantity: %s)\n" % (quant.id, quant.quantity, quant.reserved_quantity)
logging += "its `reserved_quantity` correctly reflects the move lines reservation but some are negatives\n"
logging += "These move lines are reserved on it: %s (sum of the reservation: %s)\n" % (move_line_str, reserved_on_move_lines)
logging += '******************\n'
move_lines.with_context(bypass_reservation_update=True).sudo().write({'product_uom_qty': 0})
move_line_to_recompute_ids += move_lines.ids
quant.write({'reserved_quantity': 0})
move_lines = env['stock.move.line'].search([('product_id.type', '=',
'product'), ('product_qty', '!=', 0), ('id', 'not in',
move_line_ids), ('product_id', 'in', records.ids)])
move_lines_to_unreserve = []
for move_line in move_lines:
if not move_line.location_id.should_bypass_reservation():
logging += "Problematic move line found: %s (reserved_quantity: %s)\n" % (move_line.id, move_line.product_qty)
logging += "There is no exiting quants despite its `reserved_quantity`\n"
logging += '******************\n'
move_lines_to_unreserve.append(move_line.id)
move_line_to_recompute_ids.append(move_line.id)
if len(move_lines_to_unreserve) > 0:
env.cr.execute("""
UPDATE stock_move_line SET product_uom_qty = 0, product_qty = 0 WHERE id in %s ;
"""
% (tuple(move_lines_to_unreserve), ))
if logging:
env['ir.logging'].sudo().create({
'name': 'Unreserve stock.quant and stock.move.line',
'type': 'server',
'level': 'INFO',
'dbname': env.cr.dbname,
'message': logging,
'func': '_update_reserved_quantity',
'path': 'addons/stock/models/stock_quant.py',
'line': '0',
})
if move_line_to_recompute_ids:
env['stock.move.line'].browse(move_line_to_recompute_ids).move_id._recompute_state()
On Fri, 7 Jan 2022, 11:27 pm Carmen Bianca Bakker, <carmen@coopiteasy.be> wrote:
Hello, Bedankt Tom, you are right. I have exclusively updated stock_quant from a previous backup, and I get effectively the same behaviour as I did when I updated both. That helps narrow down my research a lot---thank you. With kindness, Carmen_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe
by Graeme Gellatly - 11:46 - 7 Jan 2022
Reference
-
stock: Retroactively removing lots from incompatible products in
stock move history
Hi all, I'm a little bit new to Odoo, so please bear with me if I don't get everything right, but I've been told that I have struck a difficult bug. TL:DR: Having removed the lot_id from certain rows (products that are incompatible with that lot) in stock_move_line and stock_quant, how do I know I haven't broken something? I have a database that was migrated from Odoo 9 to Odoo 12. Before the migration, there was a bug that assigned lots to by-products of a dismantling operation (in this case, empty bottles were assigned the lots of full bottles of wine). As a result, during inventory on Odoo 12, the error message from StockMoveLine._check_lot_product() shows up, correctly identifying that these various lots are incompatible with empty bottles, then aborting the validation. Because these lots were added in error, I want to remove them from all past stock moves. This cannot be done through the UI, so I've made these SQL statements to get it done: ``` update stock_move_line set lot_id = null, lot_name = null where product_id in (67, 38, 29) and lot_id is not null; update stock_quant set lot_id = null where product_id in (67, 38, 29) and lot_id is not null; ``` (the product_ids here are empty bottles) Having run the above on a test/staging database, I can now do the inventory without troubles. I have also verified that the quantities in the inventory have correctly added up (the quantities of bottles that previously had lots are added to the lot-less bottles). The quantities on the `product.product` view have remained the same, which makes sense to me. I have also triggered `_compute_quantities()` for 'product.product', and nothing has changed. Although everything works and my problems seem to have disappeared, the problem is that I do not know whether I have inadvertently corrupted the (test!) database. I've been reading the source code of the stock module to find interactions with lot_id and lot_name, but I've not really found anything troubling. Does my approach make sense? Is there anything I can do to verify the integrity of what I've done? Many thanks and best regards, Carmen Bianca Bakker Coop IT Easy
by Carmen Bianca Bakker - 10:46 - 7 Jan 2022