Disable Enhanced Intrastat Dynamics GP

I have fallen out of friends with the GP Intrastat module, it has caused me problems with side effects in different parts of GP over the years. After another such incident, I decided it had to go, but I found it was not so obvious how to switch it off…

Microsoft Dynamics GP>> Tools>>Setup>> Company>>Company

Select the options button

Dynamics GP Company Options Window

The company setup options window will allow access to the “Enable Intrastat Tracking” option. Try unchecking the checkbox. You will get the following dialog.

Please detach all debtors, creditors and sites from all declarnts before you unmark Intratat Tracking

Please detach all debtors creditors and sites from all declarants before you unmark Intrastat Tracking

Microsoft Dynamics GP>> Tools>>Setup>> Company>>Enhanced Intrastat>>Setup

Intrastat Setup Window

You must ensure all users are out of the system before taking the next steps, as this changes the behaviour of the UI and back end. In the Intrastat Requirement Setup window, select unmark all then ok the window.

Go back to the company options window, you may now be able to deselect the intrastat module.

You must then log out of GP and back in again, this will remove the buttons etc that are related to the Intrastat module.

Please be aware that this will stop recording of Intrastat figures, make certain that your intrastat returns can be completed in some other way before disabling this module.

I used the word “may” above, as this worked for our test copy of our production company, but when it came to applying this to production over the weekend, it behaved differently, still throwing the above dialog.

Detaching declarants using SQL

As there does not seem to be any documentation around removing the module and it is holding up some other critical projects, I ran the following SQL script to essentially do what it was asking me to, remove the declarants.

Using DML SQL to query to search for tables containing the field DECLID, we find (for this version of GP and our modules), the following tables:

EDCEI016
VAT10302
*RM00102
*RM00101
*PM00200
*IV40700
EDCEI021
*PM00300

The tables marked with a * are the ones linking the sites, debtors and vendors to declarants. So lets remove the references to the declarant, backing up the data, just in case…

SELECT CUSTNMBR, ADRSCODE, DECLID INTO RM00101_IntrastatBackup 
FROM RM00101 WHERE DECLID!=''
UPDATE RM00101 SET DECLID='' WHERE DECLID!=''

SELECT CUSTNMBR, ADRSCODE, DECLID INTO RM00102_IntrastatBackup
FROM RM00102 WHERE DECLID!=''
UPDATE RM00102 SET DECLID='' WHERE DECLID!=''

SELECT VENDORID, DECLID INTO PM00200_IntrastatBackup
FROM PM00200 WHERE DECLID!=''
UPDATE PM00200 SET DECLID='' WHERE DECLID!=''

SELECT VENDORID, ADRSCODE, DECLID INTO PM00300_IntrastatBackup
FROM PM00300 WHERE DECLID!=''
UPDATE PM00300 SET DECLID='' WHERE DECLID!=''

SELECT LOCNCODE, DECLID INTO IV40700_IntrastatBackup
FROM IV40700 WHERE DECLID!=''
UPDATE IV40700 SET DECLID='' WHERE DECLID!=''
 
The above script worked for me, but I can not recommend you use it unless you have fully tested and know your install of GP does not have any other quirks that need addressing (sensible disclaimer!). Certainly something like this should involve your support partner…
 
Remember to put those backup tables somewhere safe, so they don’t have any danger of upsetting a future upgrades or sitting there forever (probably another database or export them then delete).
 
Now you will be able to uncheck the intrastat check box.
Remember to log out of GP and back in to see the changes.
 
This will break and switch off your intrastat module installation and historical reporting, so make certain you are not going to need anything from it before you do this!

You may want to remove the intrastat history too using the GP remove history menu before turning off the module. I’ve chosen to remove leave it there for now, I’ll remove it at a later point in time. I also cleared VAT10201 and VAT10101 tables to back up tables, just to highlight sooner rather than later where we might have integrations (I know we do) and jobs (I know we do) interacting with those tables.

After effects

After switching off the module some flaky bits of GP started working again, such as product suggestions and rolling down address changes to lines, after they have been changed in SOP.

rolldown address prompt

Bizarrely some mods I did a long time ago, but didn’t work and I left in place, started working, even though I’d moved on and used a work around since. Luckily the after effects were all positive, I’m now happy I’ve turned off this pesky module!

Also please note that if you use the country of origin and tax commodity code fields in item maintenance, these are turned off and on by the intrastat module being disabled. I don’t see why even those who do not use intrastat should be prevented from using these fields.

Item Maint window in GP showing optional content

I used a visual studio addin to re-enable those fields on the form.

ItemMaint.TaxCommodityCode.Show()
ItemMaint.TaxCommCodeDesc.Show()

ItemMaint.CountryOrigin.Show()
ItemMaint.CountryOriginDescription.Show()

ItemMaint.LookupButton5.Show()
ItemMaint.LookupButton7.Show()

Do comment on your experiences too, motivates me to write more!

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

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.
 
intrastat iv posting sql trace showing calls to ZDP_VAT stored proc
 
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.
 
 
 
 

Uppercase SQL script using SSMS

I just realised I use this functionality a lot and thought that others might not know it was possible.

Highlight a lower case bit of text in the SQL editor window of SSMS and press CTRL+SHIFT+U and the text will go to upper case (L for lower case).

SQL Upper case

This is handy when someone else has written a SQL script with GP field names in lower case as I’m so used to them in upper, it speeds up my reading to make them upper.

This is just an example. You may also highlight the whole script and make it upper case or individual lines, the above GIF is just showing the principle. In this particular case it would be quicker to have made the whole lot upper case then go back and lower case the table aliases.

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:

ITEMTYPE Field Name Description
1 QTYONHND On Hand
2 QTYRTRND Returned
3 QTYINUSE In Use
4 QTYINSVC In Service
5 QTYDMGED Damaged

 

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.

SQL results

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