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

Microsoft Dynamics GPWhile 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 (http://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 Exempt',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.

Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

The thirteenth Feature of the Day I am going “hands on” with is POP to FA Link to Include Taxes.

This feature will be popular with some of our clients who deal with non-recoeverable VAT (VAT is a UK sales tax charged at a standard rate of 20%) as non-rec VAT means they would need to depreciate the gross value of the asset including VAT where in previous versions the asset would be created net and need to be amended.

To use this feature, the Include Tax in Acquisition Cost checkbox on the Fixed Assets Company Setup window (Financial >> Setup >> Fixed Assets >> Company) under the Purchasing Options:

Fixed Assets Company 	Setup

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes”

Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

In this post, I’m going “hands on” with the sixth of the Microsoft Dynamics GP 2016 R2 Feature of the Day posts, Display Tax Percent for Historical Sales Transactions.

To test this feature, I created a new Tax Detail in Tax Detail Maintenance (Administration >> Setup >> Company >> Tax Details) for a Sales series Standard Rated UK VAT at 20% and created a related Tax Schedule:

Tax Detail Maintenance

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions”

VAT 100 Is Missing

Microsoft Dynamics GPI ran into an issue recently when installing Microsoft Dynamics GP 2010 R2 where the VAT Return was not available. I was configuring the system in preparation for training a new client and was running through the Company options (Microsoft Dynamics GP menu ¯ Tools ¯ Routines ¯ Company ¯ Company ¯ Options button) where I was sure the Enable VAT 100 option was but in this case wasn’t;

Company Options - No Enable VAT 100 Option

Continue reading “VAT 100 Is Missing”