The 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' DELETE FROM TX00301 WHERE TAX_RETURN_ID = @TAX_RETURN_ID GO DELETE FROM TX00303 WHERE TAX_RETURN_ID = @TAX_RETURN_ID GO DELETE FROM TX00304 WHERE TAX_RETURN_ID = @TAX_RETURN_ID GO UPDATE TX30000 SET Included_On_Return = 0 ,Tax_Return_ID = '' WHERE TAX_RETURN_ID = @TAX_RETURN_ID GO
Before running the script make sure you have a good backup of the database and test afterwards to make sure everything is OK.
UPDATE VAT10001 SET CCodeDesc = 'United Kingdom' WHERE CCode = 'GB' GO
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):
UPDATE VAT10001 SET ECFLAG = 1 WHERE CCode IN ('HR' --Croatia ,'FI' --Finland ,'HU') --Hungary GO
As always have a good backup of your databases before running the script and check afterwards that everything is good.
One of the problems with the standard Tax Reports ( ) 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:
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.
After 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:
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.
I 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 (), set Product to VAT Daybook and restrict the Type to 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.