PS 1.7.6 ASM incorrect "reserved_quantity" values

[omar2886]

Hi,
I installed a new module for advanced stock management, but I can use it because there is a problem with the stock levels in my PS installation. I've got plenty of products with positive stock reserved quantity that I can't correct, I can only change values regarding "quantity" and "physical_quantity" values.
I've tried to modify them directly using PhpMyAdmin by changing the values at ps_stock_available. As soon as I change the values, I update the view of ASM at BO and then the previous values come back, there is something modifying them right after me.
Any help? how can I reset the values? knowing what causes this issue may help prevent and fix this in the future.
Thanks

[khouloudbelguith]

Hi @omar2886,

Reserved stock represents the quantity of products that are currently in an open customer order that has not been shipped yet. In other terms, these products are physically in your storage but are not available for sale anymore. You cannot change directly the reserved stock. The reserved stock only depends on customer orders.
From the BO, you cannot change directly the reserved stock.
But you can access the ps_stock_available table & try to change it directly.
In the ps_stock_available table the physical_quantity = quantity + reserved_quantity
But the reserved_quantity represents the quantity of products that are currently in an open customer order => you need to update the "product_quatity" column in the "ps_order_detail" table.

Thanks to check & feedback.

[omar2886]

Thanks @khouloudbelguith I didn't know about this info being stored that way. For example, for a unique product product I have as much entries as orders have been placed for that item, right? What I don't know is how to do in order to fix it.
For the item in this example, the ASM system shows 23 reserved & -23 Available. In this table you told me "ps_order_detail" "product_quatity" I have 29 entries (I guess 1 for each order ever processed on the website, successful ones and canceled as well).
The thing is that now we are talking just about an example, but in order for me to fix the entire system to be able to use my new module "WK Gestion Entrepots" I'd need something more practical as I have +9.000 products

[khouloudbelguith]

@omar2886, Please be aware that this bug tracker should be used to report issues ONLY. If you need help, you can ask on the Forum (https://www.prestashop.com/forums/), Gitter (https://gitter.im/PrestaShop/General) or Stack Overflow (https://stackoverflow.com/questions/tagged/prestashop).
Thanks for your understanding!

[omar2886]

isn't it a bug then? I mean, if all orders containing these products are either shipped or canceled there shouldn't be any reserved products on the ASM system

[khouloudbelguith]

@omar2886,

unique product product I have as much entries as orders have been placed for that item

How did you reproduce the issue? the quantity of your product is <0 but in your case, "Allow ordering of out-of-stock products " is disabled?

Thanks!

[omar2886]

"Allow ordering of out-of-stock products " enabled, that must be the reason that caused all that mess I guess. Because we accept orders of products not available, so we can order them from our providers on demand and then serve our customers.
We add stock using the stock tab on the product page when we receive stock for our store.
I guess that this way, what we've been doing is adding "Available" stock, while "Reserved" stock was getting bigger and bigger everytime we an order was placed on the system while we didn't actually have availability. Then even if the order was shipped/delivered, the "reserved" stock never changed, only the "available" and "physical" stock levels compensate each other. Am I wrong?

[khouloudbelguith]

@omar2886,

Can you confirm these steps:

1. Allow ordering of out-of-stock products " enabled
2. A product has a quantity=5
3. Order this product with quantity=10 (order status= On backorder)
4. change the status to shipped
5. add stock using the stock tab on the product page

Thanks!

[omar2886]

After doing so the result is:
5 Stock, 10 Reserved & -5 Available
Now I think where could be the problem. Over the last years we had reconfigured the order statuses and as I can see now there is no status that considers the order as "shipped". So even when marked as delivered, the ASM system won't change a thing as it only seems to react to the "shipped" characteristic of an order status.
Even if I do it now, if I change this characteristic for the "Shipped" status to be considered as shipped, it won't change anything on the past orders. So, what would you recommend me to do in order to fix this on my system? shall I execute an SQL query to find delivered orders on table "ps_order_detail" and, somehow, mark them as shipped or anything like that?. It's an idea, but don't know if that's how it works neither how to do it

[omar2886]

Now I remember why I don't have order statuses with the "shipped" option assigned, because that condition closes automatically the payments on open orders and generates the invoice, which I don't want, because many orders are sent with credit or COD, so we prefer entering manually the payments so we can control when & how they were done.

[khouloudbelguith]

@omar2886,

we prefer entering manually the payments

How manually? edit from the database manually?

Thanks!

[omar2886]

There is a payment block in the orders where you can add payments with this info:
date - payment method - transaction ID - amount paid

[khouloudbelguith]

@omar2886, and the status of those orders?
Thanks!

[omar2886]

The non paid orders? they could just normal statuses like "shipped" (but not considered on PS system as really shipped) or "unpaid" so we can keep track of non paid orders that have been delivered.
Anything else, if the order has been shipped, paid and delivered, the final status would be "Delivered", but this status wouldn't be considered for the PS system as delivered as well, we can from now on do it I guess, but won't change anything on all previous orders that kept all that stock reserved

[khouloudbelguith]

@omar2886,

They could just normal statuses like "shipped" (but not considered on PS system as really shipped)

What do you think to create a new order status similar to shipped or just edit this status & uncheck this option
Send an email to the customer when his/her order status has changed. & check this option Hide this status in all customer orders. then change all your old orders with this option.

It could be a solution in your case to consider that your order are really shipped.

Thanks to check & feedback.

[omar2886]

Thanks! that was a smart solution, works! I now have to deal with some Error 500 I'm getting when filtering orders_list for orders from past years. I'll contact my programmer to check on the logs to see what is happening

[khouloudbelguith]

@omar2886, Great!
Feel free to open a new one when needed.

Thanks!

[omar2886]

Hi @khouloudbelguith , after doing what you suggested in +12.000 orders, I've noticed that by only checking those options you said, now there is a lil problem as this new status does not consider the order as validated + allow pdf download + consider order as paid... so now it has affected many other statistics as for example if I open a customer account that may have many orders on already delivered it won't show any on the historic.
Is there a way to fix this? maybe running an SQL query on PhpMyAdmin so it can look for all orders having this new status ID as last status update and mark those orders as: validated, paid, allow PDF download and show the delivery slip?
This may be a clean solution I guess, so I don't have to create again a new status and change again all +12.000 orders.
Thanks

[khouloudbelguith]

Hi @omar2886,

Please be aware that this bug should be used to report issues ONLY. If you need help, you can ask on the Forum (https://www.prestashop.com/forums/), Gitter (https://gitter.im/PrestaShop/General) or Stack Overflow (https://stackoverflow.com/questions/tagged/prestashop).

Thanks!

[omar2886]

Thanks! that was a smart solution, works! I now have to deal with some Error 500 I'm getting when filtering orders_list for orders from past years. I'll contact my programmer to check on the logs to see what is happening

Fix: #14595 (comment)

[wewew85]

Hello @omar2886 ,
I have the same problem. Could you pls help me how to fix this bug efficient for 550 orders?
thx a lot!

[omar2886]

@wewew85 as I mentioned on my last post, I found the solution here: #14595 (comment)
You just have to go to your back office -> International -> Localization -> Currencies and then add a new currency (you can add it and have it disabled, it doesn't matter). But this fix is very specific for cases like mine, so not sure if that is going to be the same case for you.
In order for you to understand your issue, you have to enable mode debug on your BO and then replicate the error so you can get the error report. If the error report points to a file related to $currencyCode, then it may also be your case and fortunately the fix is just an easy thing to do, no coding at all

[wewew85]

@omar2886 thanks for your answer, but what I mean is the reserved quantity. How to reset this reserved quantity? I tried to do it directly in database but without any success.

[omar2886]

@wewew85 no body seem to answer that question, I've done a lot of research and still and nothing. If I get to fix if I'll let you know, please do the same if you find it first

[wewew85]

@omar2886 ok, I will let you know. thanks.

[PassionX1]

Solutions:

1. Problem is that the order is not marked as shipped in the order_id table. Solution: Mark as shipped
2. If the orders have a order state ID that is not used anymore or erased, you need to configurate the order status in the database as shipped.

regards
Simon

[ibrahimfsasmaz]

yes there is clear bug here

• when you refund an order the products are added to stock and at the same time to reserved

[fmozzati]

Hi! I bring the solution! You should look at the status of all your orders and review your settings. They must have the option "Set the order as shipped" checked.

Once that is done, you can go to see your Stock and you will see that the reserved and therefore physical quantities of each product were discounted.