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 (http://www.azurecurve.co.uk)
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 (http://www.azurecurve.co.uk)
Based on idea and code from http://www.aaronberquist.com/2011/07/use-dynamic-sql-to-generate-a-dynamics-gp-login-macro/
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 (http://www.azurecurve.co.uk)
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.

Please Enter A Valid Tax Registration Number

Microsoft Dynamics GPAfter upgrading a client from Microsoft Dynamics GP 10 to 2013 they started getting a problem when creating, or editing, creditors. The error only came up when they had loaded the options window:

Microsoft Dynamics GP - Please enter a valid tax registration number.Microsoft Dynamics GP – Please enter a valid tax registration number.

I did some testing and found that this error was produced when the Country Code field was set. Microsoft Dynamics GP 10 didn’t exhibit this behaviour, but there is a difference in the installation of version 10 and 2010. And that difference is the VAT Daybook.

In Microsoft Dynamics GP 2010, and later, the VAT Daybook module needs to be installed to use the VAT 100 report which I had installed during the upgrade process.

In this case, the client operates only in the UK and doesn’t need to have Tax Registrations recorded in GP, so they were able to just stop setting the Country Code field on the addresses.

SOP Blank Invoice Form Word Template Not Working

Microsoft Dynamics GPI was configuring up a test system a short time ago and found that the Sales Documents (specifically the SOP Blank Invoice Form) were producing into IE rather than Word when I printed them.

The problem was caused by the VAT Daybook being installed. VAT Daybook provides alternative versions of the standard SOP reports which do not work with the Word Template Email functionality. To have the invoices send via Word, I had two choices; make a Word Template version of the SOP Blank Invoice Form or, and as this was only a test system my preferred choice, to change a setting to use the standard SOP template rather than the VAT Daybook one.

To do this, open Alternative/Modified Forms and Reports (Microsoft Dynamics GP menu » Tools » Setup » System), set Product to VAT Daybook and restrict the Type to Reports;

Alternative/Modified Forms and Reports

In the scrolling window, expand SOP Blank Invoice Form and SOP Blank History Invoice Form and mark the Microsoft Dynamics GP option under both.

After saving these changes, the SOP Blank Invoice Form successfully produced to Word.

Collation Conflict Printing The Tax Detail Report

Microsoft Dynamics GPWe upgraded a client from Microsoft Dynamics GP 9 to Microsoft Dynamics GP 2010 recently, and the first time they tried to run the VAT 100 report they received a number of errors:

Microsoft Dynamics GP - An error occurred executing SQL statements.Microsoft Dynamics GP – An error occurred executing SQL statements.

Continue reading “Collation Conflict Printing The Tax Detail Report”