Phantom stock allocations and Dynamics GP

Users find instances of an item in inventory that claims to be allocated, but no transaction document with the same total allocations for that item exist (allocation enquiry). This happens very frequently and support will be quick to blame 3rd party products, Here I show it happening with base product. So for the question from the user is how could this happen? it is easy to show a sequence of events to replicate it.

Create phantom allocation from sales document

There are many modules that can allocate stock, I’d recommend reading this post !item allocation quantity does not match sum of allocated orders in microsoft dynamics gp and downloading the show allocations script to see just how many places an inventory allocation can occur.

Lets look at the example of a sales order transaction, this could equally be another type of transaction such as an inventory movement..

A new sales order is created, enter an item code for the first item, it needs to be an item that has available inventory. Use the scrolling grid window on SOP entry. Tabbing off item number field to the quantity field, check the database to see what is happening. The database shows that the sales order row is not yet inserted in the sales order table. It also shows nothing is allocated in inventory for this item.

Next tab through the quantity field, check the database and see what has happened. See that now the item has an allocation in inventory, the item allocate script runs when a user leaves the quantity field. Note that a row representing the sales order line has not yet been created in the database

.

Next tab through the prices and onto the next sales order line. Check the database, it can be seen that the sale order line row has been be inserted into the database at this point.

For a short time the sales order line only existed in the local (in memory) table buffer within the Dynamics GP sales order entry form, However there is stock allocated to this virtual sales line. It is important to appreciate that the allocation of the stock in the database and the creation of the order line in the database are not inside one SQL transaction, so it is vulnerable to corruption.

Corruption could happen if the GP client application got disconnected after it had allocated the inventory stock and before the user saves the sales line or causes the line to save by moving to a new line in the order. The quantity allocated could also be corrupted by a user adjusting the quantity on an order, thus changing the allocation relative to the database sales order line but then never updating that database quantity by crashing the GP client before the line is saved. This would result in an allocation amount that differs to the line.

Some examples of how this may happen:

• an administrator kills off user’s GP client on terminal server/citrix, while GP session is in use, in a sales order line or other inventory transaction
• a user kills off the GP process or the GP process dies (crashes on the client)
• network vanishes -say network infrastructure problem or VPN dropping on a direct connection to the database (not recommended configuration for this reason)
• Power outage
• Computer sleeps and disconnects from network
• SQL server is force restarted or has failure  of some nature

I’m certain there will be other ways for this to happen too, feel free to comment below if you can think of any.

I see a constant trickle of these phantom allocations issues. Reconciling stock or using a SQL script to do the same thing quickly puts things right again but it is frustrating that it happens at all. If GP were written today I am certain these sorts of operation would be performed inside a single SQL transaction that would protect the integrity of the data. GP comes from an era before modern databases and lacks both in built database referential integrity and full use of SQL transactions. Allocation of the stock has to be real time, the nature of stock allocation makes it a multiuser real time system, but the creation of the sales line could be inside a transaction with the allocation, I wish Microsoft would sort this one.

To manage the problem it is possible to write SQL script to run on SQL job to watch for phantom allocations and notify administrators, or even let the script fix the allocations say when the system is idle, overnight.