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…

SELECT [ITEMNMBR]
,[LOCNCODE]
,[BIN]
,[QTYTYPE]
,[QUANTITY]
,[ATYALLOC]
,[DEX_ROW_ID]
FROM [dbo].[IV00112]
WHERE QUANTITY < 0
 
Running this SQL against the production database reveals where bins have negative stock, it returns this:
(19 row(s) affected)
 
So it seems 19 bins/item/location/ItemType combinations exist with a negative quantity in them, not something I would expect to see.
 
Many SQL scripts running as SQL jobs are employed as a strategy to detect anomalies in GP as fast as possible. The scripts email when problems in various areas of GP occur. One of these various integrity scripts has been emailing when things go out of whack with Bins values. So why did this script not detect a negative inventory issue? Below is the guts of the script that detects where the total stock held in bins does not match the stock held in that location.
;
WITH CTE_BinStock
AS (
SELECT ITEMNMBR
,LOCNCODE
,SUM(QUANTITY) SumQty
FROM IV00112
WHERE QTYTYPE = 1
GROUP BY ITEMNMBR
,LOCNCODE
)
SELECT iv.ITEMNMBR
,iv.LOCNCODE
,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 != ''
 
Notice that it is summing up the values. The only way this could not return rows  would be if the negative stock in each location perfectly had a matching positive stock that cancelled it out, hence the net value is correct for the location.

Time to dig into data

Hmmm…
Time to dig in to the data for one item.
 
negative bin qtys shown from table query

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.
 
Bin and Locations mostly the same

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. 

Entering inventory transfer, overriding bin with location

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.

bin problem3

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!

binpost6

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!

Lookup Dynamics GP install location from registry key

You should never assume that Dynamics GP has been installed in the default location. Almost always it will be, but sooner or later you will find a site or development machine where it is different. It is a good idea to check the machine registry for the application folder(s).

You should also not assume there is only one instance of GP! There may be multiple instances installed, see the note later on in this post about checking for instances. The format of the registry keys has evolved through the versions, to support new features (instances) and versioning.

GP2015
64-Bit Windows Operating System

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Business Solutions\Great Plains\v14.0\1033\DEFAULT\SETUP
32-Bit Windows Operating System
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Business Solutions\Great Plains\v12.0\1033\Default\SETUP

The keys found under this registry location are as follows (on my machine):

Registry Keys GP2015 Listing

Note for GP 10 version

The path omits the version number:
32-bit
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Business Solutions\Great Plains\1033\Default
64-bit
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Business Solutions\Great Plains\1033\Default

Note for GP 9 version

Drop the 1033:
32-bit
HKEY_LOCAL_MACHINE\Software\Microsoft\Business Solutions\Great Plains
64-bit
HKEY_LOCAL_MACHINE\Software\wow6432Node\Microsoft\Business Solutions\Great Plains

For GP2010 onward

The convention is to embed the GP version number, v14.0 in this example, into the path. The “v14.0” part of the path is dependant on the version of GP that you are running or targeting, Victoria Yudin has a good lookup for these on her site:
Dynamics GP build numbers and service packs

Victoria Versions GP

Check for multiple instances

In the registry path, “DEFAULT” refers to the instance of GP, so if there are multiple instances the path would look like this:

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Business Solutions\Great Plains\v14.0\1033\Inst0X\SETUP

Where “Inst0X” is the instance name, note in the image below that there is an instance name folder too! What at first glance is a simple task just became so much more difficult, didn’t it?!

Registry Keys GP2015 folder structure showing notde for Instance Name

Then it all goes wrong…

But you may notice that in the earlier values, the version of GP was listed as 14.00.0726 and yet if I look at the version from the application I get listed 14.00.0952. This is because we copy deploy GP, one of the pitfalls of this approach is that this means the registry is not maintained by the setup installer correctly when new versions are rolled out.

GP Version

Although this does not change the location of the application, I thought it worth pointing out before the reader runs away with using the registry as the source of the truth for other facts, it can be stale information!

Let me know with a comment if you found this useful –helps motivate me to write more!

Old SQL habits

With each new version of SQL server some old SQL habits have to die.

In Dynamics GP we often find document numbering sequences that are padded with zeros to facilitate sorting/ordering and to “look normal” on printed documentation.

For example website orders may be imported via econnect into GP in the range:

W00000001 to W99999999

Now sometimes we need to generate sequences of those numbers or join with tables holding just the number and no prefix. There are very many examples of this, originating from various reasons all now in my TSQL code, embedded in stored procedures.

DECLARE @WebOrderID int
SET @WebOrderID=22
SELECT 'W' + REPLACE(STR(@WebOrderID, 7), SPACE(1), '0')

The above shows a contrived example, to give us the result: W0000022 by overlaying on a template.er

The above was the way we did things in the 90s, new kids these days (starting with SQL 2012), can use the much simpler to read and use  FORMAT command . A command that supports standard .NET format strings.

DECLARE @WebOrderID int
SET @WebOrderID=22
SELECT FORMAT(@WebOrderID,'W#00000000')

This produces  much more easily read SQL and assuming you don’t need to be backward compatible, it seems the best way to go forward.
 
Old habits die hard, I need to start thinking FORMAT for these kinds of problem where pre-SQL 2012 support is not required. 

Financial Elementz export to Excel crashing Dynamics GP

Receivables Transaction Analysis window

User reported that Microsoft GP crashed with “Microsoft Dynamics GP has stopped working”. This occurred when attempting to export the Financial Elementz window Receivables Transaction Analysis to Excel.

GPCrash

By closing all instances of Excel on the machine and then examining the user’s Windows Task Manager, it was revealed that there was a hidden (Crashed?) copy of Excel sitting in process. The orphan process was “end tasked” using Task Manager.

After removing all instances of Excel the export to Excel started working again. It turned out to be a quick and easy fix.

Drop me a comment if this turned out helpful!