Puzzle of the negative bin quantities in Dynamics GP
Since going live with multiple binning there have been niggles with with bin quantity errors. Until today I had assumed it was our bespoke fulfilment or other bespoke processes causing the irregularities in bin quantities. Today I had the time to analyse it and find the root cause which was an interesting detective story to share.
Negative Bin Quantities
The story starts with yet again finding Bins with negative values for their inventory quantities. We can’t have a negative number of items on a shelf, let’s investigate…
WHERE QUANTITY < 0
(19 row(s) affected)
WHERE QTYTYPE = 1
GROUP BY ITEMNMBR
,QTYONHND [Location Qty]
,ISNULL(SumQty, 0) AS [Total Bins Qty]
FROM IV00102 iv
LEFT JOIN CTE_BinStock ivb ON iv.ITEMNMBR = ivb.ITEMNMBR
AND iv.LOCNCODE = ivb.LOCNCODE
WHERE ISNULL(SumQty, 0) != QTYONHND
AND iv.LOCNCODE != ''
Time to dig into data
See how in this example the 1573 is shadowed by the –1573, I know 1 is not a real existing bin number, it is not in the correct format convention we use. Looking back at the first query we ran on this post, the one showing bin qty<0, it can be seen that all the affected items are in bins that have names that also correspond to the location they are in.
This correlation was the clue I needed to guess how this might be occurring, my gut instinct from experience with GP was on to something. Next I went looking for the more unusual quantities involved, such as 1573, as they are distinguished, easy to spot among many transactions. It turns out that all the items and quantities affected were involved in an inventory transfer to another site. The inventory transaction history showed they were mostly all performed by one particular user too.
Recreating the issue
I had a theory by this point as to what was causing the negative bin values, so it is time to test it out. In development/test, create a new inventory transfer in the Item Transfer Entry window. I chose an item and tried to move some to another location, moving some from location 1 to 26.
As shown below the Bin Quantity Transfer Entry window opens prompting for which bin the stock should be drawn from. Enter the source site ID, not the Bin, in the “From Bin” field, then tab to enter a Qty.
Correctly GP asked me to create a bin I was able to say ok (subsequent errors like this would not even prompt for the bin as it is now created this first time). Dynamics GP correctly points out that there is not enough stock in that bin (as it is empty, we just created it). Look at what it asks though!
Do you want to continue, or cancel?
Continue, are you crazy..?!.. of course we don’t want to continue there is no stock, goodness knows what might happen! -but I suspect some other users may answer the opposite. So against my natural judgement I click continue, and complete the rest of the transaction entry and post it.
By this point it will come as no surprise to the reader that on checking the inventory bins, -25 was found against bin “1” for the item and the stock is untouched in the other bins in the "from" location. Correctly GP had a new quantity of 25 in the destination bin at the destination location. This is the behavior we that we were trying to replicate - jackpot!
I’ve not gotten as far as thinking hard about if this is a feature or a bug, perhaps some sites using GP have need for this functionality in some way, for us it is a problem and confusing, some user training and monitoring needs to go into place to prevent this happening again in production.
If you too were puzzling over negative bin quantities and this helped, then please do comment it motivates me to keep blogging!