Microsoft Dynamics GP

Writing off stock from Qty In Service from Dynamics GP using SQL

Tim Wappat

Jan 5, 2017 • 5 min read

I have 9,791 items with QTY in service >0 that I want to "adjust" out of stock.

All these items have been financially accounted for, written off over the years, the IV account manually cleared by journal each month. However the items have been left in stock (why we do this is for another day).

My challenge is to remove the items from stock so that I can then delete all the inventory master records for items as they are all historical items that have been discontinued for over 10 years and I'm sick of seeing them in reports and enquiries.

To do this

I need to firstly create a stock transfer document for all the items and qtys, including bins and serial numbers
I need create a stock adjustment for the same items bin qtys and serial numbers to adjust the items out of stock
I need to post a reversing journal to undo the effect on the already adjusted accounts

I really don’t like the idea of trying to write a GP macro to do this, it will take forever to run and is complicated by having to select serial numbers and bin numbers for each item.

I could go for programming solution using eConnect, but looking at it, I don’t think the business rules are that difficult and the problem is quite constrained so just building the transactions in SQL will do me. I feel confident I won’t screw anything up here.

I will show you the scripts I’ve used but be aware that I know this system well, you may have other modules installed that would affect things, or have GP set up in different ways that will cause issues. So TEST! TEST! TEST! first, I can take to responsibility for what happens on your system with this script!

To generate a transfer, create an empty inventory transfer with no lines, take a note of the document number and set it as the variable in the script. Save the GP document and come out of it.

Run the first half of the SQL script to create transfers for ALL the items in inventory that have a type of “In Service”. Obviously this could be narrowed down to your own needs with some more where clauses.

Note that I have made some safe assumptions for the dataset I’m working with, some of the field values are tied down rather than looked up. For example, you would need to check the account indexes for your inventory accounts before running the script, but it provides a starting point.

Once the SQL has generated the transfer, post it. Once posted run the second half of the SQL script, this will use the posted transfer to generate the inventory adjustment.

Now post the inventory adjustment.

Note: When I tried this it took over 19 hours on the test environment, after switching off the Intrastat Module it only took a few mins, see more on this at the bottom of this post.

Finally in our case we needed some reversing journals to put the accounts back to where they should have been, but that was only as we were not using the system correctly in the first place!

The SQL…

DECLARE @IVDOCNMBR CHAR(17)

-- Create a new Inventory Transfer Document, Save it then enter its number here
-- this will be the document that will be loaded with items
SELECT @IVDOCNMBR = '00017213'

-- Load all items that have an "in service" value to a stock transfer to change to "on hand"
INSERT INTO IV10001
SELECT @IVDOCNMBR
    ,3
    ,IV00101.ITEMNMBR
    ,(
        ROW_NUMBER() OVER (
            ORDER BY IV00101.ITEMNMBR
            ) - 1
        ) * 16384 + 16384
    ,IV40201.BASEUOFM
    ,IV40202.QTYBSUOM
    ,IV00102.QTYINSVC
    ,CURRCOST
    ,STNDCOST * IV00102.QTYINSVC
    ,IV00102.LOCNCODE
    ,IV00102.LOCNCODE
    ,4
    ,1
    ,2011
    ,523
    ,0x00000000
    ,DECPLCUR
    ,IV00101.DECPLQTY
    ,0
    ,''
FROM IV00101
JOIN IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
    AND IV00102.RCRDTYPE = 2
JOIN IV40201 ON IV40201.UOMSCHDL = IV00101.UOMSCHDL
JOIN IV40202 ON IV40202.UOMSCHDL = IV40201.UOMSCHDL
    AND IV40202.UOFM = IV40201.BASEUOFM
WHERE QTYINSVC > 0

INSERT INTO IV10003
SELECT IVDOCNBR
    ,IVDOCTYP
    ,LNSEQNBR
    ,(
        ROW_NUMBER() OVER (
            ORDER BY IV10001.ITEMNMBR
            ) - 1
        ) * 16384 + 16384
    ,IV10001.ITEMNMBR
    ,TRXLOCTN
    ,IV00112.BIN
    ,IV00112.BIN
    ,TRFQTYTY
    ,IV00112.QUANTITY
    ,0
FROM IV10001
JOIN IV00112 ON IV10001.ITEMNMBR = IV00112.ITEMNMBR
    AND IV10001.TRXLOCTN = IV00112.LOCNCODE
    AND IV10001.TRFQTYTY = IV00112.QTYTYPE
WHERE IVDOCNBR = @IVDOCNMBR
    AND IV00112.QUANTITY - IV00112.ATYALLOC > 0

INSERT INTO IV10002
SELECT IVDOCNBR
    ,IVDOCTYP
    ,IV10001.ITEMNMBR
    ,IV00200.SERLNMBR
    ,1
    ,IV10001.LNSEQNBR
    ,(
        ROW_NUMBER() OVER (
            ORDER BY IV10001.ITEMNMBR
            ) - 1
        ) * 16384 + 16384
    ,IV00200.DATERECD
    ,IV00200.DTSEQNUM
    ,0
    ,0
    ,IV00200.BIN
    ,IV00200.BIN
    ,'1900-01-01'
    ,'1900-01-01'
FROM IV10001
JOIN IV00200 ON IV10001.ITEMNMBR = IV00200.ITEMNMBR
    AND IV10001.TRXLOCTN = IV00200.LOCNCODE
    AND IV10001.TRFQTYTY = IV00200.QTYTYPE
WHERE IVDOCNBR = @IVDOCNMBR
    AND IV00200.SERLNSLD = 0

UPDATE IV00200
SET SERLNSLD = 1
FROM IV10001
JOIN IV10002 ON IV10001.IVDOCNBR = IV10002.IVDOCNBR
    AND IV10001.IVDOCTYP = IV10002.IVDOCTYP
    AND IV10001.LNSEQNBR = IV10002.LNSEQNBR
JOIN IV00200 ON IV10002.ITEMNMBR = IV00200.ITEMNMBR
    AND IV10001.TRXLOCTN = IV00200.LOCNCODE
    AND IV10002.DATERECD = IV00200.DATERECD
    AND IV10002.DTSEQNUM = IV00200.DTSEQNUM
    AND IV10002.SERLTNUM = IV00200.SERLNMBR
    AND IV10001.TRFQTYTY = IV00200.QTYTYPE
WHERE IV10002.IVDOCNBR = @IVDOCNMBR

-----------------------------------------------------------------------------------
-- Post the generated document, once sucessfully posted run the next part that follows
---- prepare the inventory adjustment by creating a new inventory adjustment and saving it (empty)
-- enter the previous document number for the stock transfer, and the empty adjustment number - an inventory adjustment 
-- will be created from the transfer to adjust all that stock out.
DECLARE @IVDOCNMBR CHAR(17)

SELECT @IVDOCNMBR = '00017213' -- posted (in history) inventory transfer 

DECLARE @IVDOCNMBR2 CHAR(17)

SELECT @IVDOCNMBR2 = '00054595' -- empty inventory adjustment

INSERT INTO IV10001
SELECT @IVDOCNMBR2
    ,1
    ,IV30300.ITEMNMBR
    ,(
        ROW_NUMBER() OVER (
            ORDER BY IV30300.ITEMNMBR
            ) - 1
        ) * 16384 + 16384
    ,IV30300.UOFM
    ,IV30300.QTYBSUOM
    ,- 1 * TRXQTY
    ,UNITCOST
    ,0
    ,IV30300.TRNSTLOC
    ,''
    ,0
    ,0
    ,525
    ,2008
    ,0x00000000
    ,DECPLCUR
    ,IV30300.DECPLQTY
    ,0
    ,''
FROM IV30300
WHERE IV30300.DOCNUMBR = @IVDOCNMBR
    AND IV30300.DOCTYPE = 3

INSERT INTO IV10003
SELECT @IVDOCNMBR2
    ,1
    ,IV30300.LNSEQNBR
    ,IV30302.SEQNUMBR
    ,IV30302.ITEMNMBR
    ,IV30302.LOCNCODE
    ,TOBIN
    ,''
    ,IV30302.QTYTYPE
    ,IV30302.QTYSLCTD
    ,0
FROM IV30302
JOIN IV30300 ON IV30302.DOCNUMBR = IV30300.DOCNUMBR
    AND IV30302.DOCTYPE = IV30300.DOCTYPE
    AND IV30302.LNSEQNBR = IV30300.LNSEQNBR
WHERE IV30300.DOCNUMBR = @IVDOCNMBR

INSERT INTO IV10002
SELECT @IVDOCNMBR2
    ,1
    ,IV30400.ITEMNMBR
    ,[SERLTNUM]
    ,1
    ,LNSEQNBR
    ,SLTSQNUM
    ,DATERECD
    ,DTSEQNUM
    ,0
    ,0
    ,TOBIN
    ,''
    ,'1900-01-01'
    ,'1900-01-01'
FROM [IV30400]
JOIN IV00200 ON IV30400.ITEMNMBR = IV00200.ITEMNMBR
    AND IV30400.SERLTNUM = IV00200.SERLNMBR
    AND IV00200.SERLNSLD = 0
WHERE DOCNUMBR = @IVDOCNMBR

UPDATE IV00200
SET SERLNSLD = 1
FROM IV10001
JOIN IV10002 ON IV10001.IVDOCNBR = IV10002.IVDOCNBR
    AND IV10001.IVDOCTYP = IV10002.IVDOCTYP
    AND IV10001.LNSEQNBR = IV10002.LNSEQNBR
JOIN IV00200 ON IV10002.ITEMNMBR = IV00200.ITEMNMBR
    AND IV10001.TRXLOCTN = IV00200.LOCNCODE
    AND IV10002.DATERECD = IV00200.DATERECD
    AND IV10002.DTSEQNUM = IV00200.DTSEQNUM
    AND IV10002.SERLTNUM = IV00200.SERLNMBR
    AND IV00200.QTYTYPE = 1
WHERE IV10002.IVDOCNBR = @IVDOCNMBR

ROLLBACK TRANSACTION

UPDATE IV00102
SET ATYALLOC = ATYALLOC + ABS(TRXQTY)
--SELECT * 
FROM IV10001
JOIN IV00102 ON IV10001.ITEMNMBR = IV00102.ITEMNMBR
    AND IV10001.TRXLOCTN = IV00102.LOCNCODE
    AND TRFQTYTY = 0
WHERE IV10001.IVDOCNBR = @IVDOCNMBR2
    AND IV10001.IVDOCTYP = 1

UPDATE IV00102
SET ATYALLOC = ATYALLOC + ABS(TRXQTY)
--SELECT * 
FROM IV10001
JOIN IV00102 ON IV10001.ITEMNMBR = IV00102.ITEMNMBR
    AND RCRDTYPE = 1
    AND TRFQTYTY = 0
WHERE IV10001.IVDOCNBR = @IVDOCNMBR2
    AND IV10001.IVDOCTYP = 1

Dynamics GP Intrastat module and slow posting of inventory transfer

When posting this large 3,000 line transaction with the Intrastat module switched on, it was still posting 18hrs later, at that point I killed the GP client process.

Looking at the SQL trace for what the GP client was doing before I killed it, it seemed to be that GP was iterating through each line of the transaction looking up something in the Intrastat VAT table but for every other line of the transaction too. You could see the occasional call to IV00101 where by the item number was moving to the next number, but then it would sit for ages calling out to the VAT tables for all the items on the order before moving on again.

After disabling the Intrastat module, the transaction posted in a few minutes, so there is some interaction with that module causing a serious degradation in performance on our system. In one of my previous posts I mentioned that the Intrastat module also causes the Sales order processing auto suggestion functionality to not work. I am now looking to see if we can report Intrastat without the need for the module so that ultimately I can switch it off for good.