More Changes for VAT in Microsoft Dynamics GP April 2019 Hotfix

Microsoft Dynamics GPSnuck out just before the Easter bank holidays was a new hotfix with some fixes in for the following in UK VAT:

  • To submit the file to HMRC you had to be logged into Microsoft Dynamics GP as user SA.
  • You may incorrectly receive an error in VAT Daybook Obligation period dates do not match with the VAT Daybook Calendar.
  • VAT submission changes required by HMRC.

The post on the Dynamics GP Support and Services Blog is here.

The hotfix is inclusive of the February hotfix changes and can be downloaded via these links:

Microsoft Dynamics GP: Information about the VAT 100 Return

Microsoft Dynamics GPSr. Technical Support Engineer Cheryl Waswick did a post yesterday on the Dynamics GP Support and Services Blog about the upcoming Making Tax Digital changes for VAT submission in the UK.

Microsoft have been fielding a lot of questions from partners about the VAT 100 Report in relation to Making Tax Digital and have put together some information on VAT 100 in a Q&A format.

As Cheyrl notes, that post should not be your sole source of information, but the information there may be useful.

Cheryl also says that a post is due soon with details on how Microsoft Dynamics GP will be meting the requirements of Making Tax Digital.

Microsoft Dynamics GP 2018 U.S. Year End Update Released (Includes Critical VAT Daybook Fixes)

Microsoft Dynamics GPDon't be fooled by the name; the Microsoft Dynamics GP 2018 U.S. Year End Update includes other fixes, some of which are critical to clients running the Vat Daybook module.

This fix has all the usual stuff for the Payroll (US) module which you can read about here.

The other good stuff in this version is:

  • System
  • VAT Daybook
    • Purchasing tax amounts from GL entries are subtracting from boxes 2 and 7, instead of adding to boxes 4 and 7 on the VAT Summary.
    • Sales returns and credit memos are not deducting total taxable amount from box 6 on VAT return, and voids from box 1 and box 6.
    • Purchasing returns & credit memo's, voids are not deducting total taxable amount from box 7 on VAT return, and invoices not updating box 4.

The Microsoft Dynamics GP hot fix is available from: Customer Source lock or PartnerSource lock.

The VAT Daybook fixes have also been rolled back into Microsoft Dynamics GP 2016 (but no earlier): Customer Source lock or PartnerSource lock.

Update Taxes In Fabrikam Sample Company to Add Current UK VAT Rates

Microsoft Dynamics GPWhile the Fabrikam sample company is very US-centric, it is still somewhat useful to use as it has data spread across lots of different modules. The sample data hasn't been updated for years and so still has the UK VAT rate set at 17.5%; VAT was raised to 20% in the 2010 budget and came into force in January 2011.

I created a script a while ago which could be used to create VAT rates for use in both Purchasing and Sales.

In Sales only a Standard 20% rate is created, but for purchasing rates are created for the Standard 20%, reduced 5%, Exempt and Zero Rated.

Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
-- Sales/Purchases Tax Schedule Header Master (TX00101)
	-- Purchasing
	('UKVATP','UK VAT All Purchasing',0)
	,('UKVATPS','UK VAT Purchasing Standard',0)
	,('UKVATPR','UK VAT Purchasing Reduced',0)
	,('UKVATPE','UK VAT Purchasing Exempt',0)
	,('UKVATPZ','UK VAT Purchasing Zero',0)
	-- Sales
	,('UKVATS','UK VAT All Sales',0)
	,('UKVATSS','UK VAT Sales Standard',0)

-- Sales/Purchases Tax Schedule Master (TX00102)
	-- Purchasing

--Sales/Purchases Tax Master (TX00201)
	-- Purchasing
	('UKVATPS','UK VAT Purchasing Standard',2,90,'',3,20.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPR','UK VAT Purchasing Reduced',2,90,'',3,5.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPE','UK VAT Purchasing Exempt',2,90,'',3,0.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPZ','UK VAT Purchasing Zero',2,90,'',3,0.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	-- Sales
	,('UKVATSS','UK VAT Sales Standard',1,89,'',3,20.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)

--Sales/Purchases Tax Summary Master (TX00202)
	-- Purchases
	-- Sales

This script has only ever been tested in the Fabrikam sample company; the script will need to be run again any time you redeploy the sample company using GP Utilities.

Microsoft Dynamics GP January Hotfix Released

Microsoft Dynamics GPI don't usually post about releases, except for major version releases. However, the January Hotfix release caught my eye with a few of the included features in the 2016 R2 one.

As well as having the US Payroll updates there are updates to the following which would be of benefit to clients in the UK:

  • VAT Daybook

    • VAT Daybook Summary reports may not show correct data in boxes 1, 6 and 7 if you voided a transaction.
    • VAT detail report may show incorrect figures if you have 2 or more tax codes on a transaction.
  • General Ledger

    • The Excel copy and paste function may not calculate the credit/debit column correctly when using an allocation account.
  • Fixed Asset Management

    • Tax values may not update properly if a user marks to create multiple fixed assets in the Fixed Asset Purchase Order additional information window.
  • Bank Reconciliation

    • You may experience performance issues with the Checkbook Register Inquiry window after you install GP 2016 R2.
  • Project Accounting

    • Ability to change line distributions for time and material projects in revenue recognition.
    • Fee Accounts not available in line distributions button.
    • Purchase Receiving's not reflecting WIP account edits from Purchase Order for non-inventory items.
  • System Manager

    • Fixed issues with Uncollated printing.

The hotfixes for Dynamics GP 2013 R2 and 2015 R2 only contain US Payroll fixes.

The post from the Dynamics GP Support and Services blog contains the download links.

SQL Script To Log Into Each Company

Microsoft Dynamics GPI have a few of clients who have a large number of companies. This generally isn't a problem as Microsoft Dynamics GP supports this very well.

However, after doing an upgrade of a system which has the VAT Daybook module installed, you need to log into each and every database using the sa account.

Doesn't sound so bad on the surface, but when you have two hundred companies, this soon adds up to a substantial length of time.

I did a little looking around a while ago for ways to automate this process and found a post by Aaron Berquist from 2011 where he had done exactly this.

However, when we tried his script we got a few errors, so I made a few changes and improvements; my script automatically adds OK button clicks for Test, Historic and Fabrikam sample company databases.

The Test and Historic databases are identified by having or at the end of their names; this is what Dynamics GP uses to display the message.

I removed the temporary table from Aaron's script as well; when my version of the script is run, output the results to text (make sure you have increased the query results length).

You can copy and paste the output into a Notepad document which can be saved with a .mac extension.

Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (
Based on idea and code from
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
	'# DEXVERSION=16.00.0033.000 2 2
	  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company'' 
		NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' 
		  ClickHit field ''(L) Company Names'' item ' + CONVERT(VARCHAR(3), ROW_NUMBER() OVER (ORDER BY CMPANYID)) + '  # ' + LTRIM(RTRIM(CMPNYNAM)) + ' 
		  MoveTo field ''OK Button'' 
		  ClickHit field ''OK Button''
		NewActiveWin dictionary ''default''  form sheLL window sheLL 
		NewActiveWin dictionary ''default''  form sheLL window sheLL ' +
			  # This company is set up for testing only. Do not use this company when processing live data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
			  # This company is used for storing historical data only. Do not use this company when processing current-year data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
			  # You have chosen to use the sample company, which provides data that you can use to practice procedures or learn more about the product. When you use this sample company, the date is automatically set to April 12, 2017.
			NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
			  ClickHit field OK 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '

SQL Script To Fix A Corrupt VAT 100 Return

Microsoft Dynamics GPThe process of creating a Tax Return in Microsoft Dynamics GP can sometimes take a while when there are a large number of transactions to process. While this usually isn't a problem, on occasion a network connectivity issue (or a Citrix server cutting the user off after a certain length of time) can cut GP off from the database resulting in corrupt information in the Tax Return tables.

The following script will remove the corrupt data from the Tax tables and reset the flag on the transactions which shows which tax Return they're on.

Change the highlighted section to the Tax Return ID to be reset:

Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
DECLARE @TAX_RETURN_ID AS VARCHAR(100) = 'taxreturnid'




	Included_On_Return = 0
	,Tax_Return_ID = ''

Before running the script make sure you have a good backup of the database and test afterwards to make sure everything is OK.

Update EU Member Country Code Information

Microsoft Dynamics GPThey say there are only two certainties in life; death and taxes. Well, one other certainty is that time passes and with the passage of time comes change.

One of the changes is that the European Union has become larger, but the VAT10001 (VAT Country Code MSTR) in Microsoft Dynamics GP which holds the country code information has not been updated.

As the information in this table has a direct impact on clients, I created a script to flag the missing current EU Member states (Croatia, Finland and Hungary):

	CCode IN ('HR' --Croatia
	,'FI' --Finland
	,'HU') --Hungary

As always have a good backup of your databases before running the script and check afterwards that everything is good.

SmartList To Show Transactions Included On VAT 100 Return

Microsoft Dynamics GPOne of the problems with the standard Tax Reports (Administration area page » Reports » Company » Taxes) in Microsoft Dynamics GP is that they will include only items requested for the date range, but the VAT 100 Return itself will select everything in the specified date range and also anything dated before this period which has not been included in previous VAT Returns.

This is a problem as it means you can not run a report before generating the VAT Return to see what would be picked up. However, a little thought and consideration of how the SmartList wildcards work and a workable solution presented itself.

In SmartList select the Tax Detail Transactions SmartList Favourite under Company and click on the Search button:

Search Tax Detail Transactions

Under Search Definition 1 enter Tax Return ID in the Column Name. Set the Filter to begins with and enter [^2] in the Value.

This search definition will return all transactions which do not have a Tax Return ID beginning with a 2; this assumes you name your VAT Returns along the lines of 2014-08 to have them sorted by date. If, as some clients I have seen, you create your VAT Returns with the month's long, or short, name first then replace the 2 with JFMASOND and all items not starting with one of those letters will be returned.