Update Taxes In Fabrikam Sample Company to Add Current UK VAT Rates

While the Fabrikam sample company is very US-centric, it is still somewhat useful to use as it has data spread across lots of different modules. The sample data hasn’t been updated for years and so still has the UK VAT rate set at 17.5%; VAT was raised to 20% in the 2010 budget and came into force in January 2011.

I created a script a while ago which could be used to create VAT rates for use in both Purchasing and Sales.

In Sales only a Standard 20% rate is created, but for purchasing rates are created for the Standard 20%, reduced 5%, Exempt and Zero Rated.

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
-- Sales/Purchases Tax Schedule Header Master (TX00101)
INSERT INTO TX00101
	(TAXSCHID,TXSCHDSC,NOTEINDX)
VALUES
	-- Purchasing
	('UKVATP','UK VAT All Purchasing',0)
	,('UKVATPS','UK VAT Purchasing Standard',0)
	,('UKVATPR','UK VAT Purchasing Reduced',0)
	,('UKVATPE','UK VAT Purchasing Exempt',0)
	,('UKVATPZ','UK VAT Purchasing Zero',0)
	-- Sales
	,('UKVATS','UK VAT All Sales',0)
	,('UKVATSS','UK VAT Sales Standard',0)
GO

-- Sales/Purchases Tax Schedule Master (TX00102)
INSERT INTO TX00102
	(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
VALUES
	-- Purchasing
	('ALL DETAILS','UKVATPS',3,0,0)
	,('ALL DETAILS','UKVATPR',3,0,0)
	,('ALL DETAILS','UKVATPE',3,0,0)
	,('ALL DETAILS','UKVATPZ',3,0,0)
	,('UKVATP','UKVATPS',3,0,0)
	,('UKVATP','UKVATPR',3,0,0)
	,('UKVATP','UKVATPE',3,0,0)
	,('UKVATP','UKVATPZ',3,0,0)
	,('UKVATPS','UKVATPS',3,0,0)
	,('UKVATPR','UKVATPR',3,0,0)
	,('UKVATPE','UKVATPE',3,0,0)
	,('UKVATPZ','UKVATPZ',3,0,0)
	--Sales
	,('ALL DETAILS','UKVATSS',3,0,0)
	,('UKVATS','UKVATSS',3,0,0)
	,('UKVATSS','UKVATSS',3,0,0)
GO

--Sales/Purchases Tax Master (TX00201)
INSERT INTO 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
	-- Purchasing
	('UKVATPS','UK VAT Purchasing Standard',2,90,'',3,20.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPR','UK VAT Purchasing Reduced',2,90,'',3,5.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPE','UK VAT Purchasing Exempt',2,90,'',3,0.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPZ','UK VAT Purchasing Zero',2,90,'',3,0.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	-- Sales
	,('UKVATSS','UK VAT Sales Standard',1,89,'',3,20.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
GO

--Sales/Purchases Tax Summary Master (TX00202)
INSERT INTO TX00202
	(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
VALUES
	-- Purchases
	('UKVATPS',0,0,0,0,0,0,1,1)
	,('UKVATPR',0,0,0,0,0,0,1,1)
	,('UKVATPE',0,0,0,0,0,0,1,1)
	,('UKVATPZ',0,0,0,0,0,0,1,1)
	-- Sales
	,('UKVATSS',0,0,0,0,0,0,1,1)
GO

This script has only ever been tested in the Fabrikam sample company; the script will need to be run again any time you redeploy the sample company using GP Utilities.