While 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.
4 thoughts on “SQL Script To Copy Tax Setup To A New Company”