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!
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.