Using SQL UNPIVOT operator to reconcile Dynamics GP inventory items

Working with the Dynamics GP inventory tables you will encounter the field named QTYTYPE a lot. This is usually seen with its partner LOCNCODE.

Inventory in GP can reside in different locations (for example depots New York, London, Sidney). That location then is broken down further into five item types. Item types can be though of condition/state of the item where the item states are an indexed as followed:

1 = On Hand
2 = Returned
3 = In Use
4 = In Service
5 = Damaged

This is a way we can categorise where and what state/status the inventory is in.

The item stock levels for each item is stored in the table IV00102, keyed by ITEMNMBR and LOCNCODE. To avoid creating four times more rows, the table architect decided to pivot the table, giving each quantity type its own field column in the table. The fields names are as follows:

This presents a problem as the other inventory tables have a QTYTYPE field and have separate rows for different quantity types. If we need to join to the IV00102 table this becomes troublesome for us. One solution (I know there are others) is to use the UNPIVOT operator in TSQL to unpivot the IV00102 table, causing the columns to present themselves as extra rows.

SELECT ITEMNMBR
    ,LOCNCODE
    ,CASE valuename
        WHEN 'QTYONHND'
            THEN 1
        WHEN 'QTYRTRND'
            THEN 2
        WHEN 'QTYINUSE'
            THEN 3
        WHEN 'QTYINSVC'
            THEN 4
        WHEN 'QTYDMGED'
            THEN 5
        END AS QTYTYPE
    ,QtyValue
FROM iv00102
UNPIVOT(QtyValue FOR valuename IN (
            QTYONHND
            ,QTYRTRND
            ,QTYINUSE
            ,QTYINSVC
            ,QTYDMGED
            )) UnPiv
WHERE ITEMNMBR = '100XLG'

This gives rise to the following result set.

See how each quantity type now has its own row rather  than being named columns? We also used a CASE statement to alias the names of the columns back to index numbers.

Example, reconciling inventory values using SQL

In the blog post by Mahmood M. Alsaadi  Reconciling Quantity on Hand – SQL Script, he shows a script to reconcile inventory, so let us not reinvent the wheel and start with that script. The example provided in his post, at the time of writing did not take into account the quantity types. Unfortunately I ended up debugging the SQL to work this out, only to then return to the original post comments to find someone else had also done the same and pointed this out. However the solution they proposed was to tie the QTYTYPE=1 so that only on hand quantities are reconciled.  I felt I could do better than!

I build on the original script adding in the UNPIVOT introduced above:

SELECT TRX_BALANCE.ITEMNMBR AS ItemNumber
    ,TRXLOCTN AS Location
    ,Master_Balance.QTYTYPE AS QTYTYPE
    ,BALANCE AS TRX_BALNACE
    ,QtyValue AS Master_Balance
    ,ATYALLOC AS Master_AllocatedQuantity
    ,QtyAvailable
    ,BALANCE - QtyValue AS Variance
FROM (
    SELECT ITEMNMBR
        ,TRXLOCTN
        ,QTYTYPE
        ,SUM(QTYRECVD) - SUM(QTYSOLD) AS BALANCE
    FROM dbo.IV10200
    --WHERE IV10200.ITEMNMBR='40-322' 
    GROUP BY ITEMNMBR
        ,TRXLOCTN
        ,QTYTYPE
    ) AS TRX_BALANCE
LEFT OUTER JOIN (
    SELECT ITEMNMBR
        ,LOCNCODE
        ,CASE valuename
            WHEN 'QTYONHND'
                THEN 1
            WHEN 'QTYRTRND'
                THEN 2
            WHEN 'QTYINUSE'
                THEN 3
            WHEN 'QTYINSVC'
                THEN 4
            WHEN 'QTYDMGED'
                THEN 5
            END AS QTYTYPE
        ,QtyValue
        ,CASE valuename
            WHEN 'QTYONHND'
                THEN ATYALLOC
            ELSE 0
            END AS ATYALLOC
        ,CASE valuename
            WHEN 'QTYONHND'
                THEN QtyValue - ATYALLOC
            ELSE 0
            END AS QtyAvailable
    FROM IV00102
    UNPIVOT(QtyValue FOR valuename IN (
                QTYONHND
                ,QTYRTRND
                ,QTYINUSE
                ,QTYINSVC
                ,QTYDMGED
                )) IV00102Pivot
    ) AS Master_Balance ON TRX_BALANCE.ITEMNMBR = Master_Balance.ITEMNMBR
    AND TRX_BALANCE.TRXLOCTN = Master_Balance.LOCNCODE
    AND TRX_BALANCE.QTYTYPE = Master_Balance.QTYTYPE
WHERE BALANCE - QtyValue <> 0

This will output the item, location and quantity type of the items that have an incorrect inventory level in the IV00102 table (this is the table viewed when looking at the Dynamics GP item enquiry form).

This can be a useful  script to set as a scheduled SQL Job to notify your GP admin that inventory needs reconciling, even listing the items that need reconciling. This could also lead to generation of a macro to do the reconcile… one for another day…