SQL Script To Fix A Corrupt VAT 100 Return

● Ian Grieve ●  ● 1 Comment   ● 

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'

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.

● Categories: Dynamics, GP, Microsoft, VAT Daybook ● Tags: , , , , ,  ● Permalink ● Shortlink ●

Correcting Spelling Of United Kingdom

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPWhen you deploy Microsoft Dynamics GP, the country codes are populated automatically. Unfortunately, the United Kingdom’s name is misspelled. This script corrects the spelling mistake.

UPDATE
	VAT10001
SET
	CCodeDesc = 'United Kingdom'
WHERE
	CCode = 'GB'
GO
● Categories: Dynamics, GP, Microsoft, VAT Daybook ● Tags: , , , ,  ● Permalink ● Shortlink ●

Update EU Member Country Code Information

● Ian Grieve ●  ● 2 Comments   ● 

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):

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.

● Categories: Dynamics, GP, Microsoft, VAT Daybook ● Tags: , , , ,  ● Permalink ● Shortlink ●

SmartList To Show Transactions Included On VAT 100 Return

● Ian Grieve ●  ● 1 Comment   ● 

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.

● Categories: Dynamics, GP, Microsoft, SmartList, VAT Daybook ● Tags: , , , ,  ● Permalink ● Shortlink ●

Please Enter A Valid Tax Registration Number

● Ian Grieve ●  ● 1 Comment   ● 

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:

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.

● Categories: Dynamics, GP, Microsoft, VAT Daybook ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

SOP Blank Invoice Form Word Template Not Working

● Ian Grieve ●  ● 0 Comments   ● 

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

● Categories: Dynamics, GP, Microsoft, VAT Daybook ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●