Category: GP (Great Plains)

A few years ago I posted about securing the Web Services for Microsoft Dynamics GP which is something I would recommend that everyone does, most especially if they are exposed externally.

However, when the certificate expires and needs to be replaced, there are a few steps you need to follow to do this and they are mainly done at the command line as it is the certificate binding which needs to be updated.

First, you need to delete the certificate (the highlighted section is the port used when the certificate was originally bound):

netsh http delete sslcert ipport=0.0.0.0:48666

Second, you need to add the new certificate; the certhash is the certificate thumbprint:

netsh http add sslcert ipport=0.0.0.0:48666 certhash=7c69255774b41dd0f4edc3a34514e5f08a526443 appid={8EFC5047-33C1-438D-B76B-3634DDCF585F}

Once the above steps have been completed, the Microsoft Dynamics GP Service Host service should be restarted.

I’m a little late, but there was a good article fromAngela Eckman Ebensteiner on the Dynamics GP Support and Services Blog on the differences between the Historical Inventory Trial Balance and the Historical Stock Status Report and why the HITB (Historical Inventory Trial Balance) is the one you should be using to determine the value of your stock on a given day and to reconcile with the GL.

If you’re a user of the Inventory Control module, then the article is definitely worth a few minutes of your time.

Back in Microsoft Dynamics GP 10 and before, the ability to produce EFT files was not available in UK language installs. This meant a lot of clients used other methods for producing the EFT files and many of them are still using those alternative methods. That said, I’ve done work with a few recently where they had changed banks or otherwise needed to mke changes to the EFT format, so we hve switched them to using the standard functionality so the format can be maintained esily through the EFT File Format window ((Financial area page » Cards » EFT File Format)).

This largely works, but as the clients had been running EFT payment runs, there was a multi-year backlog of entries in the Generate EFT File window ((Financial area page » Transactions » Generate EFT File)) which needed to be cleared. While it is possible to hit the generte button and have them cleared by creating the files, for some cients we were looking at hundreds of files which would need to be created.

Instead I explored the database and found two tables holding entries which would need to be cleared down to remove the pending batches:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
DELETE FROM CM20202 --Checkbook Transaction Electronic Funds Transfer (CM20202)
DELETE FROM CM20203 --Checkbook EFT Transaction Batch (CM20203)

This script removes all entries from the Generate EFT File window; before running the script make sure you don’t need any of the entries, test the script on a test company or system and make sure you have a good backup before running the script on live.

While I am a big advocate of making changes to data via the front end or using an officially supported integration tool, there are times when a SQL script is the quicker way of making a small update.

I recently needed to update the purchasing unit of measure on 60,000 inventory items. The below SQL script was created to make this update:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
UPDATE
	['Inventory Item Master']
SET
	['Inventory Item Master'].PRCHSUOM = ['Inventory U of M Schedule Detail Setup'].UOFM
FROM
	IV00101 AS ['Inventory Item Master'] --Item Master (IV00101)
INNER JOIN
	IV40202 AS[/sqlgrey] ['Inventory U of M Schedule Detail Setup'] --Inventory U of M Schedule Detail Setup (IV40202)
		ON	[/sqlgrey]
			['Inventory U of M Schedule Detail Setup'].UOMSCHDL = ['Inventory Item Master'].UOMSCHDL

This script was suitable for the client’s data against which it was run, but may not be suitable for all data configurations.

As always before running a script, make sure you understand what the script does, test it on a test company/system and have a good backup before running on live and verify the results.

I did some work for a client recently where we created a new check remittance layout and showed them how to enable emails in Microsoft Dynamics GP. When we deployed ive however, we encountered a problem whereby they client could not enable, en masse, the creditors for emails as the button was missing fromthe action pane in the Creditors navigation list.

On the standalone test system the E-mail Settings option on the Modify section of the navigation pane was present:

But on live, this option is missing:

While comparing the two systems, I realised that the Vendors Not Submitted and Vendors Pending Approval navigation ist favourites were missing from live, but present on test (just like hte E-mail Settings option.

We had two small projects running in parallel; one for the remittance and emails and the other for workflow approvals. We had identified the navigation list favourites had been missing from the test system and had planned fixing them when workflow was deployed to live, but the remittance project was signed off first.

The navigation list favourites is a known issue after upgrade, but hadn’t been fixed as the client had not been using the navigation lists and so hadn’t noticed they were missing. We fixed the navigation ists on live and were then abe to use the email settings option to enable email on creditors en masse.

I’m not sure how, but I stumbled across an article on the ERP Software Blog by Michael Krasivsky of The Resource Group where he has posted a more efficient version of the Grant.sql script.

For those who don’t know, the Grant script is used to apply the correct permissions to SQL objects (tables, views and stored procedures) used by Microsoft Dynamics GP; this script can take a while to run as it utilises a cursor to update the permissions on every single SQL objects in the database regardless of the current permissions.

What Michael has done is create a script which only selects the SQL objects which do not have the correct permissions and grant them on those objects only; this makes the script much faster to run, as in most cases the majority of the SQL objects will already have the correct permissions so it is only a minority of objects which need to be updated.

I recently needed to find which databases in SQL Server had a specific view deployed to them. I’ve created scripts in the past to find SQL objects in all databases (table,trigger and functions), but not one for SQL views.

Some of the previous scripts looked in all databases and others were limited to only the Microsoft Dynamics GP databases; this script is one of the latter, using the SY015000 table to only search for a SQL view in the Dynamics GP databases:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 @command NVARCHAR(MAX)

DECLARE @SystemDatabase VARCHAR(15) = 'D20'
DECLARE @View VARCHAR(50) = 'uv_AZRCRV'
CREATE TABLE #ReturnedData(
	DBNAME VARCHAR(15)
	,VIEWNAME VARCHAR(100)
)

SELECT @command = 'IF EXISTS (SELECT 1 FROM sys.databases AS dbs LEFT JOIN ' + @SystemDatabase + '..SY01500 SY ON SY.INTERID = dbs.name WHERE dbs.name = ''?'' AND (dbs.name = ''' + @SystemDatabase + ''' OR SY.INTERID IS NOT NULL))
						BEGIN
							USE [?];
							INSERT INTO #ReturnedData (dbname, VIEWNAME) (SELECT DB_NAME() AS ''DB_NAME'', o.name FROM sys.objects AS o WHERE o.type = ''V'' AND o.name LIKE ''' + @View + '%'')
						END'

EXEC sp_MSforeachdb @command

SELECT * FROM #ReturnedData

DROP TABLE #ReturnedData

After assisting a client with a server migration, an issue was reported with a fixed asset integration:

The first thing we checked was to see if the stored procedure did exist, which it did. The next option was a suspicion of permissions. After checking in SQL I found that the service account did not have access to all of the company databases; we added the DYNGRP database role to the service account and tried the integration again and found that it now ran successfully.

I was doing some work with a client recently where we needed to output two different formats of EFT file for the same bank account. This is possible using the EFT Transfer Method on the bank on the creditors. However, with a large number of creditors we needed a way to update the records in bulk. While it would be possible to do this with a macro, it would be fiddly and take a while.

Instead, I created a SQL script which can be run to update the Address Electronic Funds Transfer Master (SY06000) table:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
UPDATE
	['Address Electronic Funds Transfer Master']
SET
	EFTTransferMethod = 2
		/*
			1=Not Specified
			2=Business Account
			3=Corporate Account
			4=Personal Account
			5=Foreign Account
		*/
FROM
	SY06000 AS ['Address Electronic Funds Transfer Master']
WHERE
	SERIES = 4
AND
	EFTBankType = 3 
		/*
			2 = Ireland
			3 = United Kingdom
			26 = Other 1
			27 = Other 2
			30 = Canada
			31 = United States
		*/
GO

The script is currently set up to update only UK banks and to set the EFT Transfer Method to Business Account. The green comments show some of the other values which you can use.

If you use the script, make sure you have a good backup before.

Working on a recent project for a client we needed to extract the chart of accounts from one Microsoft Dynamics GP company and import it into a couple of others (altering the first segment during the process. The below script was created to extract all of the chart of account data in the format required for import through Integration Manager:

SELECT
	['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,CASE WHEN ['Account Master'].ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS 'Active'
	,['Account Master'].ACCTENTR AS 'Allow Account Entry'
	,['Account Master'].ACTALIAS AS 'Account Alias'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,CASE WHEN ['Account Master'].PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS 'Posting Type'
	,CASE WHEN ['Account Master'].TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS 'Typical Balance'
	,['Account Master'].USERDEF1 AS 'User Defined 1'
	,['Account Master'].USERDEF2 AS 'User Defined 2'
	,['Account Master'].USRDEFS1 AS 'User Defined 3'
	,['Account Master'].USRDEFS2 AS 'User Defined 4'
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM[