SQL Script To Copy Tax Setup To A New Company

Microsoft Dynamics GPWhile helping a client configure some new companies recently, we needed to create the tax schedules in each of the companies; unfortunately, there were about 30 tax schedules and 4 companies which needed them. To do manually, or even with a macro, was going to take a while, so I took a few minutes out to see if it was going to be possible to run the update using a SQL script.

The below is what I came up with and appeared to work fine for the companies we tested it in. One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

There are two variables at the top which will need to be configured before running the script: @SourceCompany and @DestinationCompany.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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 @Statement AS VARCHAR(2000)
DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(2000)

SET @SourceCompany = 'TWO'
SET @DestinationCompany = 'PI'

-- Sales/Purchases Tax Schedule Header Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
	(TAXSCHID,TXSCHDSC)
--VALUES
	(SELECT 
		TAXSCHID,TXSCHDSC
	FROM
		' + @SourceCompany + '..TX00101 AS TX
	WHERE
		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Schedule Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
	(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
--VALUES
	(SELECT
		TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
	FROM
		' + @SourceCompany + '..TX00102 AS TX
	WHERE
		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
	(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
	TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
	NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
	TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
--VALUES
	(SELECT
		TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
		TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
		NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
		TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
	FROM
		' + @SourceCompany + '..TX00201 AS TX 
	WHERE
		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Summary Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
	(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
--VALUES
	(SELECT
		TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
	FROM
		' + @SourceCompany + '..TX00202 AS TX
	WHERE
		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

If you do use this script, then please make sure you have a good backup before running it and also test afterwards.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.