Reserved Stock? PrestaShop 1.7.8.8

[sampsonzak]

Hi, I have noticed some items in my store show 'Reserved Stock' over 0, even though all orders have been complete, and there's no pending/awaiting processing.

 

I can't figure out how to find where this "reserved stock" is hidden? It is affecting my stock buying and starting to annoy me now.

 

How can I find the orders/issue with Reserved Stock easily?? 

 

Thank you

[idnovate.com]

Can you upload a screenshot where "Reserved stock" appear?

[sampsonzak]

1 hour ago, idnovate.com said:

Can you upload a screenshot where "Reserved stock" appear?

Hi, it's on the Catalogue -> Stocks page.

 

Thank you

[idnovate.com]

Execute this query and let me know if the result matches with what you see in the BO:

SELECT id_product, sa.reserved_quantity = (
    SELECT SUM(od.product_quantity - od.product_quantity_refunded)
    FROM ps_orders o
    INNER JOIN ps_order_detail od ON od.id_order = o.id_order
    INNER JOIN ps_order_state os ON os.id_order_state = o.current_state
    WHERE o.id_shop = 1 AND
        os.shipped != 1 AND (
            o.valid = 1 OR (
                os.id_order_state != 8 AND
                os.id_order_state != 6
            )
        ) AND sa.id_product = od.product_id AND
        sa.id_product_attribute = od.product_attribute_id
        GROUP BY od.product_id, od.product_attribute_id
    )
FROM ps_stock_available sa
WHERE sa.id_shop = 1

 

[sampsonzak]

5 hours ago, idnovate.com said:

Execute this query and let me know if the result matches with what you see in the BO:

SELECT id_product, sa.reserved_quantity = (
    SELECT SUM(od.product_quantity - od.product_quantity_refunded)
    FROM ps_orders o
    INNER JOIN ps_order_detail od ON od.id_order = o.id_order
    INNER JOIN ps_order_state os ON os.id_order_state = o.current_state
    WHERE o.id_shop = 1 AND
        os.shipped != 1 AND (
            o.valid = 1 OR (
                os.id_order_state != 8 AND
                os.id_order_state != 6
            )
        ) AND sa.id_product = od.product_id AND
        sa.id_product_attribute = od.product_attribute_id
        GROUP BY od.product_id, od.product_attribute_id
    )
FROM ps_stock_available sa
WHERE sa.id_shop = 1

 

 

Hi, I've ran this in phpmyadmin and it's showing this:

 

[idnovate.com]

If you check a product ID with some reserved stock in the BO, does it appear the same value in the query?

[sampsonzak]

1 hour ago, idnovate.com said:

If you check a product ID with some reserved stock in the BO, does it appear the same value in the query?

I forgot which product it was showing on, and I tried sorting by the sa.reserved_quantity in that script but it wasn't working. I don't think the script worked correctly else it should have let me sort by this column right?

 

[idnovate.com]

Add at the end:

ORDER BY sa.reserved_quantity DESC

 

[radu_xc]

Hello,

I tried running the script, but the result is not as expected. I have attached a CSV file with the output I received. Could someone please assist me in troubleshooting this issue and help me resolve it?

Thank you in advance for your support!

request_sql_13 (1).csv

[idnovate.com]

On 1/19/2025 at 3:32 PM, radu_xc said:

Hello,

I tried running the script, but the result is not as expected. I have attached a CSV file with the output I received. Could someone please assist me in troubleshooting this issue and help me resolve it?

Thank you in advance for your support!

request_sql_13 (1).csv 698 B · 0 downloads

Have you finally fixed the issue?

[radu_xc]

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;

 

Edited May 8 by radu_xc
add more info and code (see edit history)