Sometimes items in Dynamics GP inventory have a fake inventory allocation showing on them, where drilling into that allocation shows no documents are actually allocated to the item (using allocation enquiry window).
from my experience, phantom inventory allocations in Dynamics GP are quite common.
This happens quite frequently and support will be quick to blame 3rd party products. Here I show it happening with base GP product.
How could this happen you ask? 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 from there, 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 enforced database referential integrity at the schema level and use of SQL transactions everywhere, to protect transaction integrity.
Allocation of inventory has to be real time, the nature of stock allocation requires it to be 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 out.
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.
This script will vary depending on the modules you have running, so do not run this example without testing fully first.
--- Script will fix site allocations in Dynamics GP
--- It should be tested against specific sites using test database as it updates data
--- If the GP company uses other modules, such as manufacturing that allocate stock, those need incorporating into the script too
DECLARE @Results TABLE
ivAlloc decimal(19, 5),
SalesAlloc decimal(19, 5),
-- find Sales SOP allocations and IV transaction Allocations clashing with site inventory allocations
AS (SELECT ITEMNMBR,
SUM(ATYALLOC) as TotAlloc,
ON SOP10100.SOPTYPE = SOP10200.SOPTYPE
and SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
WHERE (SOP10100.SOPTYPE != 1)
AND VOIDSTTS = 0
GROUP BY ITEMNMBR,
SUM(Temp_Allocated_Quantity) as TotAlloc,
group by ITEMNMBR,
HAVING SUM(temp_allocated_Quantity) != 0
-SUM(TRXQTY) as TotAlloc,
ON IV10000.IVDOCNBR = IV10001.IVDOCNBR
AND IV10000.IVDOCTYP = IV10001.IVDOCTYP
AND PSTGSTUS < 2
group by ITEMNMBR,
HAVING SUM(TRXQTY) != 0
AS (SELECT ITEMNMBR,
SUM(TotAlloc) as TotAlloc,
GROUP BY ITEMNMBR,
INSERT INTO @Results
iv.ATYALLOC as ivAlloc,
sop.TotAlloc as SalesAlloc,
'Sales Alloc does not match Inventory alloc' as comment
FROM iv00102 iv
LEFT JOIN CTE2 sop
ON iv.ITEMNMBR = sop.ITEMNMBR
AND iv.LOCNCODE = sop.LOCNCODE
where iv.ATYALLOC != sop.TotAlloc
sop.TotAlloc is null
AND iv.ATYALLOC != 0
and iv.LOCNCODE != ''
ISNULL(SalesAlloc, 0) SalesAlloc,
ORDER BY 1;
SET ATYALLOC = ISNULL(r.SalesAlloc, 0)
FROM IV00102 iv
JOIN @Results r
ON iv.ITEMNMBR = r.ITEMNMBR
AND iv.LOCNCODE = r.LOCNCODE
-- Correct global site allocations, after the site specific ones were addressed above
as (select itemnmbr,
where locncode != ''
group by itemnmbr
--select t1.ITEMNMBR,t1.tot, t2.itemnmbr, t2.ATYALLOC
set t2.atyalloc = t1.tot
from cte t1
join iv00102 t2
on t1.itemnmbr = t2.itemnmbr
and t2.LOCNCODE = ''
where t1.tot != t2.ATYALLOC