SQL Script To Copy Tax Setup To All Companies Using Cursor

November 18th, 2015 by

Microsoft Dynamics GPFollowing on from the creation of the script to copy tax schedules between companies, I was on a fairly long train journey and spent a small part of the time wrapping a cursor round the original script to allow tax schedules to be configured in one company and rolled through the others.

There is one setting at the top of the script to set the SourceCompany.

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.

/*
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 @SQLStatement AS VARCHAR(2000)
DECLARE @SourceCompany VARCHAR(5)
DECLARE @DestinationCompany VARCHAR(5)

SET @SourceCompany = 'TWO'

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@DestinationCompany
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)

			-- Sales/Purchases Tax Schedule Header Master
			SET @SQLStatement = '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 (@SQLStatement)

			-- Sales/Purchases Tax Schedule Master
			SET @SQLStatement = '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 (@SQLStatement)

			-- Sales Purchases Tax Master
			SET @SQLStatement = '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 (@SQLStatement)

			-- Sales/Purchases Tax Summary Master
			SET @SQLStatement = '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 (@SQLStatement)

			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationCompany
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

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

Source: azurecurve

Leave a Reply