Oh! No. Not Again!
This was my first reaction when I discovered an open Support Ticket was awaiting me since three days. It was raised by Aditya Duggal, one of our early customers and the most active member of the ERPNext community. Since I am the resident expert on this issue, and I was on leave, the ticket remained open.
Looking at the subject of the ticket, a guilty feeling came into my mind. I was frustrated with the issue, as it has bugged me several times even after fixing it at a regular intervals. But this time I was determined to fix the root cause, with the power of our new "Test Suite".
The Issue:
Reserved quantity for the item CSE07000060-411 showing as 5 Nos in BGH655 warehouse whereas we don't have any pending orders for this particular item.
Reserved Quantity is the quantity, which is reserved for delivery to the customer. It increases on submission of a Sales Order, and reduces when a Delivery against that Sales Order is made. The value is maintained in a table called "Bin", a unique combination of item and warehouse.
Unlike past, this time I did not dive into the code directly. I started with writing automated test cases.
Case 1: Create Sales Order (SO) with stock items. Submit and Cancel it, check reserved qty after each step.
Case 2: Make Delivery Note (DN) against an SO, with partial delivery and submit it. Stop and Unstop the SO, Cancel DN and Cancel the SO. Check reserved qty in each step.
Case 3: Follow same steps as case 2, for over-delivery against sales order
All the testcases passed without any error. I was not able to think of any other test case. But the error in Aditya's account confirms that there are some cases where it is going wrong. Then I realized that I totally forgot about packing items. Immediately I looked into it and tried to cover those test cases.
Case 4: Create packing item and Sales BOM for that item. Follow same steps as case 2 with packing items.
Case 5: Follow same steps as case 3 with packing items for over-delivery.
And here it goes!
The Case 4 failed while stopping SO after partial delivery. I was very excited but wanted to make sure that I wrote the right test cases. After digging into deep, I found that the quantity being updated while stopping was wrong. I completed test cases for over-delivery of packing items as well before moving to fix the issue.
While fixing the issue, I realized that the code should be refactored, as it was very difficult to read. Test cases gave me the license to go ahead with rewritiing the code.
After refactoring the code, I was very glad, all the test cases passed smoothly. I realized the power of writing automated test cases, which helped me to catch a bug that was eluding me for almost 2 years. Now I try to write every piece of code with atleast minimal test cases.
After fixing the issue, I was traced back my step to understand how this issue bugged me in the past. I am pasting here full email transcript between Aditya and me. If you are curious to know, please click here [Warning: It's a long conversation]
Aditya [18-04-2011]:
I would like to inform you that the projected quantities have gone all wrong in the BIN doc type and these are very important for us to know what to manufacture. Now the projected quantities are getting messed up due to the RESERVED QUANTITY being wrong in the doc type. I think the problem is somewhat with stopping SOs as it should reduce the reserved quantity but I think it is not doing that, I am not sure about it but honestly speaking most of my items are showing reserved quantities but they are not on orders.
ERPNext [18-04-2011]:
It is working fine in our side. So we need to track the exact scenario for such an error to occur. For now, we are recalculating the reserved qty for all the items.
Aditya [09-05-2011]:
Please check this as it is creating a lot of problems for us, the reserved quantities in BIN DOCTYPE is not equal to the sum of quantities to deliver for that particular item code. Please let me know why this error is coming again and again, there something that we are doing is making this error come again, since I could recall Saumil had earlier rectified this error.
ERPNext [09-05-2011]:
I investigated the first case - for item CSE08000100-411 1) Total SO (booked) qty as per Sales Order Detail is 124 2) Total delivered as per Delivery Note Detail is 115. Hence reserved should be 124-115 = 9, but in stock level report, it is showing 11. This is because in SO111200230 - you have delivered 4 against 2, hence you will still have reserved some more against some other SO. Hence the number of 11 is correct because you have over-delivered in another Sales Order. This is also because you are allowing over delivery by 100% as set in your Settings -> Global Defaults. To stop over-delivery, make this number smaller!
Aditya [10-05-2011]:
Thanks for the detailed analysis. I would like to know the best possible way to move forward, I have for a start changed the global defaults and disallowed any over-delivery but the thing is that now how should I change the reserved quantities to match up with the actual qty to deliver. Now the only thing which comes to my mind is that I manually change the values of reserved quantities in the Bin doc type, is that thing fine or there is a better way to do it. Please let me know.
ERPNext [11-05-2011]:
Finally, we had identified the exact issue in reserved qty. It was not related to logic of reserved qty calculation. Basically, there was a bug in the system related to attachments. When you attach any file in a submitted document, the document converted to Draft. Thats why it allow to re-submit the document, hence double addition in reserved qty. The problem has been fixed. And I had submitted sales order SO111201132, please amend if required.
Aditya [16-06-2011]:
This problem is still persisting, kindly let me know how to see the root cause of this problem.
ERPNext [24-06-2011]:
Investigated almost 1 full day but did not find the exact reason of getting reserved qty wrong except over delivery, may be due to old bug. I have recalculated quantity(ordered, reserved, projected, actual etc) for all item. Now hopefully, the system will give correct projected qty for all items.
Aditya [30-06-2011]:
Please check the items mentioned below, they all have their reserved quantities different from the actual quantities to be delivered. This bug is really creating problems for us. Did you check the code for reserved quantities when we "STOP" or "CANCEL" a Sales Order? I think the problem should be in that.
ERPNext [05-07-2011]:
You are right, I did not considered Stopped document while recalculating reserved qty. Now reserved qty is sync with qty pending to deliver. Thanks for pointing out the exact mistake.
Aditya [05-07-2011]:
I have checked the items and they are indeed in sync as per expectations, let’s hope no errors creep in this time.
Aditya [03-08-2011]:
The problem has re-occurred, but this time I think the problem is not wide spread but just related to one or two SOs. The below mentioned items were booked under 2 SOs whose ID is mentioned, now 891 was a repeat of 890 so I had cancelled it. Now before canceling 891 the reserved quantities were 18,18,18,28,30,15 respectively. Now as per my analysis it seems that the system is considering SO890 twice for reasons unknown to me. Please let me know when this is resolved. Now this is something which is still not going good.
ERPNext [03-08-2011]:
Did not find any problem in reserved qty for any of the following items. Can you please tell how you get/calculated this reserved qty.
Aditya [03-08-2011]:
Checked it myself, now the difference is not showing even in my report but for only one item CSE12000150-411where the bin table also shows the reserved quantity as 1 but I cannot find a single order in which this item is pending to be delivered.
ERPNext [04-08-2011]:
There is 1 sales order SO111200657 where this item is pending to deliver. Probably in the custom report you did not considered null value. We have put a validation in the system. It will check if reserved qty gets wrong at any scenario. It will give a message and do not allow to submit the transaction. It will help us to find out the exact scenario where it is getting wrong.
Aditya [01-08-2012]:
This problem seems to have crept into the system again. Or this is not a problem, please elaborate and also let me know how could I check how this error crept into the system.
ERPNext [06-08-2012]:
We have fixed reserved qty for item HSQ09530200-040, but could not fix for other item JCNO-01 because that needs manual operation. In Sales Order SO121300304-1, packing list table did not populated properly due to one old issue, that resulted wrong reserved qty for that item. Please amend the mentioned sales order, it will fix reserved qty.
Aditya [27-09-2012]:
Reserved Quantities of the following seems to be wrong to me: HSQ31750150-040 and HSQ03180150-040. Please let me know how this error creeps all the time.
ERPNext [01-10-2012]:
We did not find any issue in reserved qty for the mentioned items, seems they are correct. Sales Order: SO121301538-1 is not showing in PENDING ORDERS report because company is different in that sales order.
Aditya [19-02-2013]:
I have just noticed that in RIGPL account, erp.rigpl.com there is a problem with the posting of reserved quantity for one item. The item is CSE07000060-411 which is showing as reserved quantity as 5 Nos in BGH655 warehouse whereas we don't have any pending orders for this particular item. Kindly let me know why this error is occurring, you can access the account for admin purpose for resolving this particular issue.
ERPNext [22-02-2013]:
I am very glad to inform you that finally "Reserved Qty" bug has been identified and fixed. We have written extensive test cases for reserved qty calculation, so minimum chances of introduction of new bugs in the future. The issue was related to "STOP" functionality. Reserved qty also recalculated for all items, it was wrong for 4 items.
Aditya [22-02-2013]:
Glad to know that this bug has finally been caught, I can still remember it took something like a year or so for getting hold of this bug (hope all the cases are now covered in the new script).
