odoo issue #62139: addon code to fix the issue on the fly

odoo-issue-62139_stock_quant.py

# Copyright 2020 ForgeFlow, S.L. (https://www.forgeflow.com)
# Copyright 2020 Camptocamp

import logging

from odoo import _, models
from odoo.exceptions import UserError
from odoo.tools import float_compare

_logger = logging.getLogger(__name__)


class StockQuant(models.Model):
    _inherit = "stock.quant"

    def fix_reserved_quantity_in_quants(self):
        self.env.cr.execute(
            """
        WITH q1 AS (
            SELECT
                sml.PRODUCT_ID,
                sml.LOCATION_ID,
                sml.package_id,
                SUM(product_qty) AS RESERVED_QUANTITY
            FROM STOCK_MOVE_LINE SML
            LEFT JOIN stock_location sl ON sml.location_id = sl.id
            LEFT JOIN product_product pp ON sml.product_id = pp.id
            LEFT JOIN product_template pt ON pp.product_tmpl_id = pt.id
            WHERE
                SML.STATE NOT IN ('done', 'cancel')
                AND pt.type = 'product'
                AND sl.USAGE NOT IN (
                'customer', 'inventory', 'production', 'supplier', 'view')
                AND sl.complete_name NOT ILIKE 'Stores%'
                AND sl.complete_name NOT ILIKE 'Return%'
                AND sl.complete_name NOT ILIKE '%Input%'
            GROUP BY sml.PRODUCT_ID, sml.LOCATION_ID, sml.package_id
        )
        SELECT
            COALESCE (sq.product_id, q1.product_id) AS product_id,
            COALESCE (sq.location_id, q1.location_id) AS location_id,
            COALESCE (sq.RESERVED_QUANTITY, 0) AS quant_qty,
            COALESCE (q1.RESERVED_QUANTITY) AS ml_reserved_qty,
            COALESCE (sq.package_id, q1.package_id) as package_id
        FROM q1
        FULL OUTER JOIN stock_quant sq
            ON q1.product_id = sq.PRODUCT_ID
            AND q1.location_id = sq.location_id
            AND coalesce(q1.package_id, 0)  = coalesce(sq.package_id, 0)
        WHERE (
            sq.RESERVED_QUANTITY <> q1.RESERVED_QUANTITY OR
            sq.reserved_quantity IS NULL);
        """
        )
        data = self.env.cr.dictfetchall()
        for line in data:
            product = self.env['product.product'].browse(line['product_id'])
            location = self.env['stock.location'].browse(line['location_id'])
            lot = self.env['stock.production_lot'].browse(line['lot_id'])
            package = self.env['stock.quant.package'].browse(
                line['package_id']
            )
            quants = self._gather(
                product, location, lot_id=lot, package_id=package, strict=True
            )
            available_qty = sum(quants.mapped('quantity'))
            ml_reserved_qty = line['ml_reserved_qty']
            _logger.info(
                'available_qty: %s, ml_reserved_qty: %s',
                available_qty,
                ml_reserved_qty,
            )
            rounding = product.uom_id.rounding
            if (
                float_compare(
                    available_qty, ml_reserved_qty, precision_rounding=rounding
                )
                < 0
            ):
                self._update_available_quantity(
                    product,
                    location,
                    ml_reserved_qty - available_qty,
                    lot,
                    package,
                )
            # update available quantity can have created a new quant, so gather again
            quants = self._gather(
                product, location, lot_id=lot, package_id=package, strict=True
            )
            quant_reserved_qty = sum(quants.mapped('reserved_quantity'))
            _logger.info(
                'quant reserved qty: %s, ml_reserved_qty: %s',
                quant_reserved_qty,
                ml_reserved_qty,
            )
            if (
                float_compare(
                    quant_reserved_qty,
                    ml_reserved_qty,
                    precision_rounding=rounding,
                )
                < 0
            ):
                # the reserved qty on the quants will cause a problem -> update the first matching quant
                to_reserve = ml_reserved_qty - sum(
                    quants[1:].mapped('reserved_quantity')
                )
                quants[0].write({'reserved_quantity': to_reserve})

    def _update_reserved_quantity(
        self,
        product_id,
        location_id,
        quantity,
        lot_id=None,
        package_id=None,
        owner_id=None,
        strict=False,
    ):
        try:
            return super()._update_reserved_quantity(
                product_id,
                location_id,
                quantity,
                lot_id=lot_id,
                package_id=package_id,
                owner_id=owner_id,
                strict=strict,
            )
        except UserError as e:
            if (
                e.name
                == _(
                    'It is not possible to unreserve more products of %s than you have in stock.'
                )
                % product_id.display_name
            ):
                _logger.warning(
                    'You have unreserved more products of %s than you have in stock in location %s.'
                    % (product_id.display_name, location_id.display_name)
                )
                # if we are here means that the quantity is greater than
                # available_qty, so in order to unreserve without getting an
                # error we need to decrease the quantity to unreserve to
                # match the quantity actually reserved on the quant.
                # we are assuming there is only one quant by location/product
                rounding = product_id.uom_id.rounding
                quants = self._gather(
                    product_id,
                    location_id,
                    lot_id=lot_id,
                    package_id=package_id,
                    owner_id=owner_id,
                    strict=strict,
                )
                available_quantity = sum(quants.mapped('quantity'))
                available_reserved_quantity = sum(
                    quants.mapped('reserved_quantity')
                )
                # the current qty is less than what we want to unreserve: update it
                if (
                    float_compare(
                        available_quantity,
                        -quantity,
                        precision_rounding=rounding,
                    )
                    < 0
                ):
                    self._update_available_quantity(
                        product_id,
                        location_id,
                        -quantity - available_quantity,
                        lot_id=lot_id,
                        package_id=package_id,
                        owner_id=owner_id,
                    )
                # only unreserve what we have reserved, this a pathologic case...
                return super()._update_reserved_quantity(
                    product_id,
                    location_id,
                    -available_reserved_quantity,
                    lot_id=lot_id,
                    package_id=package_id,
                    owner_id=owner_id,
                    strict=strict,
                )
            else:
                raise