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”

MDGP 2016 R2 Feature of the Day: Display Tax Percent for Historical Sales Transactions

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here

The sixth Feature of the Day is Display Tax Percent for Historical Sales Transactions.

Sales Tax Summary Enquiry

When drilling into the Sales Tax Summary Enquiry, the tax percent used at time of transaction displays instead of the percent that is setup on the tax detail maintenance window.

Click to show/hide the MDGP 2016 R2 Feature of the Day Series Index

Fixing Typo in Fabrikam’s Tax Schedule Description

Microsoft Dynamics GPThe script in this post fixes a typo, in the Fabrikam sample database, in the Tax Schedule Description whereby the h in Purchases was missing. Not a big thing, but I was working on a report which was configured to return the Description as well as the Tax Schedule ID and it was bugging me.

Hence a script to fix it, so I can easily fix it again in future.

/*
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).
*/UPDATE
	['Tax Schedule Header Master']
SET
	TXSCHDSC = REPLACE(TXSCHDSC, 'Purcase', 'Purchase')
FROM
	TX00101 AS ['Tax Schedule Header Master']
WHERE
	TXSCHDSC LIKE '%Purcase%'

SQL Script To Copy Tax Setup To All Companies Using Cursor

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.

Feature Explained: Date Effective Tax Rates

Microsoft Dynamics GPOver the last two or three years the VAT rate in the UK has changed three times; temporarily down to 15% from 17.5% and then back up a year later before a permanent rise in January 2011. On each occasion we issued advice to customers on how to handle these changes within Microsoft Dynamics GP; there were two ways for them to make the change; either create a new Tax Detail or amend the existing one. Personally I recommended the former as it is somewhat cleaner; you can tell at a glance what the VAT rate will be without the need to check the date the invocie was registered. If the latter option was taken users needed to remember which rate is effective on what date during the cross over period which means lots of scope for errors. Continue reading “Feature Explained: Date Effective Tax Rates”