Microsoft Dynamics GP

Phantom stock allocations and Dynamics GP

Tim Wappat

Sep 5, 2015 • 5 min read

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
(
    ITEMNMBR char(31),
    ivAlloc decimal(19, 5),
    SalesAlloc decimal(19, 5),
    LOCNCODE char(11),
    Comment varchar(2000)
)

-- find Sales SOP allocations and IV transaction Allocations clashing with site inventory allocations
;
WITH CTE
AS (SELECT ITEMNMBR,
           SUM(ATYALLOC) as TotAlloc,
           SOP10200.LOCNCODE
    FROM SOP10200
        JOIN SOP10100
            ON SOP10100.SOPTYPE = SOP10200.SOPTYPE
               and SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
    WHERE (SOP10100.SOPTYPE != 1)
          AND VOIDSTTS = 0
    GROUP BY ITEMNMBR,
             SOP10200.LOCNCODE
    UNION
    SELECT ITEMNMBR,
           SUM(Temp_Allocated_Quantity) as TotAlloc,
           LOCNCODE
    FROM IV10301
    group by ITEMNMBR,
             LOCNCODE
    HAVING SUM(temp_allocated_Quantity) != 0
    UNION
    SELECT ITEMNMBR,
           -SUM(TRXQTY) as TotAlloc,
           TRXLOCTN
    FROM IV10001
        JOIN IV10000
            ON IV10000.IVDOCNBR = IV10001.IVDOCNBR
               AND IV10000.IVDOCTYP = IV10001.IVDOCTYP
               AND PSTGSTUS < 2
    group by ITEMNMBR,
             TRXLOCTN
    HAVING SUM(TRXQTY) != 0
   ),
     CTE2
AS (SELECT ITEMNMBR,
           SUM(TotAlloc) as TotAlloc,
           LOCNCODE
    FROM CTE
    GROUP BY ITEMNMBR,
             LOCNCODE
   )
INSERT INTO @Results
(
    ITEMNMBR,
    ivAlloc,
    SalesAlloc,
    LOCNCODE,
    Comment
)
select iv.ITEMNMBR,
       iv.ATYALLOC as ivAlloc,
       sop.TotAlloc as SalesAlloc,
       iv.LOCNCODE,
       '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
      OR (
             sop.TotAlloc is null
             AND iv.ATYALLOC != 0
             and iv.LOCNCODE != ''
         );


SELECT ITEMNMBR,
       ivAlloc,
       ISNULL(SalesAlloc, 0) SalesAlloc,
       LOCNCODE,
       Comment
FROM @Results
ORDER BY 1;

UPDATE iv
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
;
with cte
as (select itemnmbr,
           sum(atyalloc) tot
    from iv00102
    where locncode != ''
    group by itemnmbr
   )
--select t1.ITEMNMBR,t1.tot, t2.itemnmbr, t2.ATYALLOC
update t2
set t2.atyalloc = t1.tot
from cte t1
    join iv00102 t2
        on t1.itemnmbr = t2.itemnmbr
           and t2.LOCNCODE = ''
where t1.tot != t2.ATYALLOC