Edit History

Hello,

I needed to track down the cause of some phantom reserved stock, so I wrote a quick query that lists every order still holding stock for a given product.
Below is the version for product ID 127; adapt it as needed.

I eventually discovered that one of my order statuses did not have the “Shipped” flag ticked, so PrestaShop kept the stock in a reserved state.

Note: make sure you set the correct language ID in the osl.id_lang filter.
In my shop the default language is 2 - adjust that value for your installation.

Run the query, check any orders where shipped = 0, and either change their status to one that is marked “Shipped” or edit the status itself to enable the flag. Once that’s done, the product’s reserved stock disappears.

Hope it helps!

SELECT 
  o.id_order,
  o.date_add,
  osl.name AS current_status,
  os.paid AS paid,
  os.shipped AS shipped,
  os.delivery AS delivered
FROM ps_orders o
JOIN ps_order_detail od ON od.id_order = o.id_order
JOIN ps_order_state os ON o.current_state = os.id_order_state
JOIN ps_order_state_lang osl ON os.id_order_state = osl.id_order_state
WHERE osl.id_lang = 2
  AND od.product_id = 127
GROUP BY o.id_order;

Below you’ll find option #2, which shows even more details: the order ID/reference, the quantity still reserved, and whether the “Shipped” flag is set (1 = yes / 0 = no).

SELECT
       o.id_order                       AS id_comanda,
       o.reference                      AS cod_comanda,
       DATE(o.date_add)                 AS data,
       osl.name                         AS status_curent,
       os.shipped                       AS shipped_flag,
       SUM(od.product_quantity
           - od.product_quantity_refunded
           - od.product_quantity_reinjected)  AS cantitate_blocata
FROM   ps_order_detail od
JOIN   ps_orders o              ON o.id_order        = od.id_order
JOIN   ps_order_state os        ON os.id_order_state = o.current_state
JOIN   ps_order_state_lang osl  ON osl.id_order_state = os.id_order_state
                                 AND osl.id_lang      = (
                                     SELECT value
                                     FROM   ps_configuration
                                     WHERE  name = 'PS_LANG_DEFAULT')
WHERE  od.product_id           = 127
  AND  od.product_attribute_id = 0
GROUP  BY o.id_order
HAVING cantitate_blocata > 0
ORDER  BY o.date_add DESC;