SQL Script To Copy Tax Setup To A New Company

Microsoft Dynamics GPWhile 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 (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 @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.

Management Reporter Link To Financial Dimension Range Problem

Microsoft Dynamics GPManagement Reporter again, folks, after a short delay; I finally completed on a house purchase, in rural Northumberland, two years after I started work at Perfect Image.

I received another call that data on a report was not coming out correctly a short time ago (I’ve had the screenshots stored for a while until I had time to write this post); the numbers didn’t match those in Microsoft Dynamics GP or those on the old FRx report (which did match the numbers in GP.

I started by checking the Row Definition and the Link to Financial Dimension column to see what information was being brought through. The setup with ranges for the excludes struck me as a little odd with the ranges of 1:1, 10:10 and 100:100:

Dimensions

Continue reading “Management Reporter Link To Financial Dimension Range Problem”

Area Page Error – Value Cannot be Null

Microsoft Dynamics GPA client who upgraded to Microsoft Dynamics GP 2013 a few weeks ago began encountering a problem when switching between modules. In particular when they loaded the Purchasing area page they received the following message;

Value cannot be null. Parameter  name: contentValueValue cannot be null. Parameter name: contentValue

Fortunately, there are two solutions to this problem outlined in the Microsoft Support KB Article 2843273. The first is to upgrade to Microsoft Dynamics GP 2013 which at very short notice isn’t really an option, or to use the script in the KB article to both remove the corrupt entry in SY07140 and add a trigger to the table to prevent another corrupt entry being created.

As we needed the client up and running without the error quickly, we opted for the second approach and will look to schedule in the upgrade from 2013 RTM to SP1.

SSRS GL Trial Balance Returning No Data

Microsoft Dynamics GPI was doing some testing to compare the SSRS Trial Balance against the standard report version but ran into a problem straight away when I found the SSRS version returned no data.

A quick search online found me this post SSRS: GL Trial Balance Summary report returns no data – Follow Up by Mariano Gomez where he discussed the exact problem I had encountered.

As Mariano says this has been fixed in the upcoming SP3 but for now there is a Knowledge Base article covering a workaround which is to alter the stored procedure (seeglPrintSRSTrialBalance) which is used for returning the data: GL Trial Balance SRS Reports return no data using Microsoft Dynamics GP.

Update Debtor Addresses From CSV To Work Around An Integration Manager Bug

Microsoft Dynamics GPEarlier this week I posted about a bug in Integration Manager where it added extra zeros into the phone and fax number fields when importing creditors. Well, the same bug also affects debtors but a similar script to update debtors via a CSV is also possible.

Continue reading “Update Debtor Addresses From CSV To Work Around An Integration Manager Bug”

Update Creditor Addresses From CSV To Work Around An Integration Manager Bug

Microsoft Dynamics GPI was doing some work for a client recently where we were loading lists of new creditors into Microsoft Dynamics GP from CSV files via Integration Manager. There is, however, a bug in IM 10 where it adds extra zeros to the phone and fax number fields of both creditors and debtors. These extra characters are, rather bizarrely, not always at the end but can be interspersed in the phone number.

The easiest work around was to put together a SQL script for loading the phone and fax numbers from CSV file which I did while on site which updated only a single address on the PM Creditor Master (PM00200) and PM Address Master (PM00300) tables.

Continue reading “Update Creditor Addresses From CSV To Work Around An Integration Manager Bug”

Updating Item Purchasing Unit Of Measure From A CSV

Microsoft Dynamics GPAfter a slight mid-week diversion, here is another script I produced a while ago which updates the Item’s Default Purchasing Unit of Measure. It was produced when a client wanted to bulk update a large number of records which would have taken a long time manually but by script only minutes to write and run.

Continue reading “Updating Item Purchasing Unit Of Measure From A CSV”

Update Sales Transactions Distributions From Item

Microsoft Dynamics GPThis is a script I’ve had a few months now and, like the one I posted yesterday, thought it might be useful to others.

This script was created for a client who changed some of their account codes on some Item Classes and had rolled them down to the Items themselves but there were many Sales Transactions already on the system which had the old code on and needed to be updated to the new one.

Continue reading “Update Sales Transactions Distributions From Item”

Update Inventory Item Accounts From CSV

Microsoft Dynamics GPThis script was produced for a client who wanted to bulk update the accounts defined against selected Inventory Items in Microsoft Dynamics GP 2010 R2. This particular client did not have Integration Manager so I needed an alternative approach to doing the update.

I could have used a GP Macro to do the job (doing one while recording the macro to create a template to be populated from a CSV using Mail Merge) but it was easier to create a SQL script to do the job directly from the CSV (this is the same view I took for updating the Account Segment Master).

Continue reading “Update Inventory Item Accounts From CSV”

Restoring A Database Without Encountering A Locking Issue

Microsoft SQL ServerI periodically have problems when trying to restore a GP company database over the Test database as SQL reports that the database is currently in use.

One way of resolving is to restart the SQL Server but this is only possible on a stand alone test system which is not being used by other people but this approach is overkill.

The better solution is to change the database to single user, restore the database and change the database back to multiple user. This can be done manually through SQL Server Management Studio but is far easier done via a SQL script.

Continue reading “Restoring A Database Without Encountering A Locking Issue”