Category: GP (Great Plains)

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script updates items with an Item Number starting 0 to a status of Discontinued.

As with any script, before running on production test the script to make sure it works and have a good backup of the database.

/*
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
	IV
SET
	IV.ITEMTYPE = 2
FROM
	IV00101 IV WHERE IV.ITEMNMBR LIKE '0%' AND IV.ITEMTYPE <> 2
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will take a CSV file and update the three phone numbers and fax numbers on the vendor address supplied.

/*
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).
*/
CREATE TABLE #PM00200_IMPORT
	(
		VENDORID VARCHAR(100)
		,ADRSCODE VARCHAR(100)
		,PHNUMBR1 VARCHAR(100)
		,PHNUMBR2 VARCHAR(100)
		,PHONE3 VARCHAR(100)
		,FAXNUMBR VARCHAR(100)
	)
GO

BULK INSERT
	#PM00200_IMPORT
FROM
	'C:\Temp\Supplier Phone and Fax.csv'
WITH
	(
		FIELDTERMINATOR = ','
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 2
	)
GO

select * from #PM00200_IMPORT

-- VENDOR MASTER
UPDATE
	PM
SET
	PM.PHNUMBR1 = ISNULL(PM_I.PHNUMBR1,'')
	,PM.PHNUMBR2 = ISNULL(PM_I.PHNUMBR2,'')
	,PM.PHONE3 = ISNULL(PM_I.PHONE3,'')
	,PM.FAXNUMBR = ISNULL(PM_I.FAXNUMBR,'')
FROM
	PM00200 PM
INNER JOIN
	#PM00200_IMPORT As PM_I
		ON UPPER(PM_I.ADRSCODE) = PM.VENDORID
	AND
		PM_I.ADRSCODE = "MAIN"
GO

-- VENDOR ADDRESS MASTER
UPDATE
	PM
SET
	PM.PHNUMBR1 = ISNULL(PM_I.PHNUMBR1,'')
	,PM.PHNUMBR2 = ISNULL(PM_I.PHNUMBR2,'')
	,PM.PHONE3 = ISNULL(PM_I.PHONE3,'')
	,PM.FAXNUMBR = ISNULL(PM_I.FAXNUMBR,'')
FROM
	PM00300 PM
INNER JOIN
	#PM00200_IMPORT As PM_I
		ON PM_I.VENDORID = PM.VENDORID AND PM.ADRSCODE = UPPER(PM_I.ADRSCODE)
GO

DROP TABLE #PM00200_IMPORT
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns payables transactions at a status of open or history after a specified date.

/*
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).
*/
SELECT
	PMOPEN.VCHRNMBR AS [Voucher Number],
	PMOPEN.DOCNUMBR AS [Document Number],
	CASE
		WHEN PMOPEN.DOCTYPE = 1 THEN 'Invoice'
		WHEN PMOPEN.DOCTYPE = 2 THEN 'Invoice'
		WHEN PMOPEN.DOCTYPE = 3 THEN 'Invoice'
		WHEN PMOPEN.DOCTYPE = 4 THEN 'Credit'
		WHEN PMOPEN.DOCTYPE = 5 THEN 'Credit'
		ELSE ''
	END 'Document Type',
	PMOPEN.DOCDATE AS [Document Date],
	PMOPEN.POSTEDDT AS [Posted Date],
	PMOPEN.VENDORID,
	VM.VENDNAME AS [Vendor Name],
	PMOPEN.PRCHAMNT AS [Net Amount],
	PMOPEN.TAXAMNT AS [Tax Amount],
	PMOPEN.DOCAMNT AS [Gross Amount],
	PMOPEN.CURTRXAM AS [Outstanding Amount]
FROM PM20000 PMOPEN --PM Transaction OPEN File (PM20000)
INNER JOIN PM00200 VM --PM Vendor Master File (PM00200)
	ON PMOPEN.VENDORID = VM.VENDORID
WHERE DOCTYPE IN (1, 2, 3, 4, 5)
AND DOCDATE > '2016-04-30 00:00:00.000'
AND VOIDED = 0
AND CURTRXAM > 0
AND CURTRXAM = 0

UNION ALL

SELECT
	PMHIST.VCHRNMBR AS [Voucher Number],
	PMHIST.DOCNUMBR AS [Document Number],
	CASE
		WHEN PMHIST.DOCTYPE = 1 THEN 'Invoice'
		WHEN PMHIST.DOCTYPE = 2 THEN 'Invoice'
		WHEN PMHIST.DOCTYPE = 3 THEN 'Invoice'
		WHEN PMHIST.DOCTYPE = 4 THEN 'Credit'
		WHEN PMHIST.DOCTYPE = 5 THEN 'Credit'
		ELSE ''
	END 'Document Type',
	PMHIST.DOCDATE AS [Document Date],
	PMHIST.POSTEDDT AS [Posted Date],
	PMHIST.VENDORID,
	VM.VENDNAME AS [Vendor Name],
	PMHIST.PRCHAMNT AS [Net Amount],
	PMHIST.TAXAMNT AS [Tax Amount],
	PMHIST.DOCAMNT AS [Gross Amount],
	PMHIST.CURTRXAM AS [Outstanding Amount]
FROM PM30200 PMHIST --PM Paid Transaction History File (PM30200)
INNER JOIN PM00200 VM --PM Vendor Master File (PM00200)
	ON PMHIST.VENDORID = VM.VENDORID
WHERE DOCTYPE IN (1, 2, 3, 4, 5)
AND DOCDATE > '2016-04-30 00:00:00.000'
AND VOIDED = 0
ORDER BY DOCDATE

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script selects all primary keys for all tables in a database and creates the ALTER script to reapply them. It seems I created this in 2015 to allow primary keys to be exported from one Dynamics GP database and then applied against another.

/*
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).
*/
-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT
	so.name,
	si.name,
	si.type_desc
FROM sys.indexes si
JOIN sys.objects so
	ON si.object_id = so.object_id
	AND so.type = 'U'
WHERE si.type_desc <> 'HEAP'
AND si.is_Primary_Key = 1
ORDER BY so.Name

DECLARE @PkTable sysname
DECLARE @PkName sysname
DECLARE @KeyType nvarchar(50)

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName, @KeyType
WHILE (@@FETCH_STATUS = 0)
BEGIN
	DECLARE @PKSQL nvarchar(4000)
	SET @PKSQL = ''
	SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY ' + @KeyType + ' ('

	-- Get all columns for the current primary key
	DECLARE cPKColumn CURSOR FOR
	SELECT
		COLUMN_NAME
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE TABLE_NAME = @PkTable
	AND CONSTRAINT_NAME = @PkName
	ORDER BY ORDINAL_POSITION
	OPEN cPKColumn

	DECLARE @PkColumn sysname
	DECLARE @PkFirstColumn bit
	SET @PkFirstColumn = 1
	-- Loop through all columns and append the sql statement
	FETCH NEXT FROM cPKColumn INTO @PkColumn
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		IF (@PkFirstColumn = 1)
			SET @PkFirstColumn = 0
		ELSE
			SET @PKSQL = @PKSQL + ', '

		SET @PKSQL = @PKSQL + @PkColumn

		FETCH NEXT FROM cPKColumn INTO @PkColumn
	END
	CLOSE cPKColumn
	DEALLOCATE cPKColumn

	SET @PKSQL = @PKSQL + ')'
	-- Print the primary key statement
	PRINT @PKSQL

	FETCH NEXT FROM cPK INTO @PkTable, @PkName, @KeyType
END
CLOSE cPK
DEALLOCATE cPK

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created to allow a client to rename sites in Dynamics GP from a CSV file.

If the name provided was only DO NOT USE then this was added to the start of the existing site description (which was then truncated to the maximum length of 30 characters) otherwise the site description was replaced with the one from the file.

/*
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).
*/
CREATE TABLE #Sites
	(
		LOCNCODE VARCHAR(10)
		,LOCNDESC VARCHAR(30)
	)
GO

BULK INSERT
	#Sites
FROM
	'C:\Temp\Site Descriptions.csv'
WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n'
	)
GO

UPDATE
	IV
SET
	IV.LOCNDSCR = (
					CASE WHEN Sites.LOCNDESC = 'DO NOT USE' THEN
						LEFT(RTRIM(Sites.LOCNDESC) + ' - ' + RTRIM(IV.LOCNDSCR),30)
					ELSE 
						RTRIM(Sites.LOCNDESC)
					END
				)
FROM
	IV40700 IV
INNER JOIN
	#Sites As Sites
		ON
			Sites.LOCNCODE = IV.LOCNCODE
GO


DROP TABLE #Sites
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client who was closing some sites and wanted to get a list of all Work status sales transactions against a specific site.

/*
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 @LOCNCODE VARCHAR(10) = 'Z%'

SELECT
	SOP101.CUSTNMBR
	,SOP101.CUSTNAME
	,SOP102.SOPNUMBE, SOP102.LNITMSEQ, SOP102.LOCNCODE, SOP102.ITEMNMBR
FROM
	SOP10200 AS SOP102 -- Sales Transaction Amounts Work (SOP10200)
INNER JOIN
	SOP10100 AS SOP101 -- Sales Transaction Work (SOP10100)
		ON SOP101.SOPNUMBE = SOP102.SOPNUMBE
WHERE
	SOP102.LOCNCODE LIKE @LOCNCODE
ORDER BY
	SOP101.CUSTNAME,SOP102.SOPNUMBE

Long time readers of this site will be aware that I changed jobs in September this year and no longer work with Microsoft Dynamics GP.

I worked with Dynamics GP for just over 19 years and in that time I wrote a lot of SQL scripts for a variety of purposes. I’ve decided to publish almost all of the scripts I have written an will use this series as the container to hold them. I’ll filter out scripts which are duplicates of each other or what I have already posted, but otherwise will post them all.

Some may not be that useful to others or require tweaking before they can be used. If a script looks like it might be useful and you have questions, ask in the comments for that script and I’ll do what I can too help.

Continue reading “SQL Scripts for Microsoft Dynamics GP: Series Index”

I’m a big fan of Post Master Enterprise from Envisage Software Solutions and implemented it for a few clients.

For most clients we’ve installed it, configured it and it just runs without issue.

One client though had a few issues where it would suddenly stop processing. This client though was using more than the standard posting; they also had the modules for transferring sales order to invoice and emailing the sales invoice to a customer and saving a copy of the invoice to a network share. They also process quite a lot of transactions.

I flagged this up to Envisage and they pointed me in the direction of one of the Advanced Settings:

This setting makes Post Master check if the application is running correctly and if it isn;t, it will initiate a restart. We set this to 300 seconds (5 minutes)and asked the client to monitor. this was several weeks before I left ISC and, as of when I left at the end of August, the issue had not recurred; or if it had, the auto restart had resolved it without the client being aware.

Four years ago I wrote a script which changed server and database names embedded within SQL views after a live Microsoft Dynamics GP company had been copied into a test one. This script is used by a client as one of several which are all run when they refresh live into test, but they also sometimes use it when copying databases between a live server and a test one.

They recently ran it when creating a new test server, but found afterwards that it hadn’t worked correctly. Or more accurately, it had worked correctly as it was created to do. However, the new SQL Server had a different Instance name than the original.

The script below is an update of the original which adds in a change of Instance alongside the existing server and company.

The three highlighted sections are for the old values which need to be changed; the new values are all determined automatically based on the database in which the script is run.

/*
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 @ViewDefinition AS NVARCHAR(max)

DECLARE @OldServer AS VARCHAR(50) = '2018SQL1'
DECLARE @NewServer AS VARCHAR(50) = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50))

DECLARE @OldDB AS VARCHAR(50) = 'FINANCE'
DECLARE @NewDB AS VARCHAR(50) = CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(50))

DECLARE @OldCompany AS VARCHAR(5) = 'TWO'
DECLARE @NewCompany AS VARCHAR(5) = DB_NAME()

CREATE TABLE #ViewDefinitions(
	ViewDefinition NVARCHAR(MAX)
	,ROW_ID INT IDENTITY
)

INSERT INTO #ViewDefinitions
	(ViewDefinition)
--VALUES
	(SELECT
		REPLACE(
			REPLACE(['SQL Modules'].definition, 'CREATE VIEW', 'ALTER VIEW')
		,'Db=' + @OldDB + '&Srv=' + @OldServer + '&Cmp=' + @OldCompany,'Db=' + @NewDB + '&Srv=' + @NewServer + '&Cmp=' + @NewCompany)
	FROM
		sys.all_views AS ['All Views']
	JOIN
		sys.sql_modules AS ['SQL Modules']
			ON
				['SQL Modules'].object_id = ['All Views'].object_id
	and
		['SQL Modules'].definition LIKE '%Db=' + @OldDB + '&Srv=' + @OldServer + '&Cmp=' + @OldCompany + '%')

DECLARE
	cursor_Views Cursor
FOR
	SELECT
		ViewDefinition
	FROM
		#ViewDefinitions
	
	Open cursor_Views

	FETCH NEXT FROM
		cursor_Views
	INTO
		@ViewDefinition
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			EXEC (@ViewDefinition)
			FETCH NEXT FROM
				cursor_Views
			INTO
				@ViewDefinition
		END
	CLOSE cursor_Views
DEALLOCATE cursor_Views

DROP TABLE #ViewDefinitions
GO

As always before using a script, make sure you understand wat it is going to do and also ensure you have a good backup.

A little while ago I did some work with a client who was migrating their Microsoft Dynamics GP VBA modifications into C# so that they could make more use of the web client. We had the C# code created, and the VBA-mess modified forms and reports loaded into Dynamics GP. The next step is to use dag.exe to create the managed code assembly which provides access to resources in an application dictionary.

On one of the machines we installed Dexterity in order to get access to dag.exe and then ran the command required to create the managed code assembly. Unfortunately, the following error was produced:

It was only seeing the error message that I remembered having been told about this by the developer. The Microsoft.Dynamics.GP.BusinessObjects.dll file needs to be copied from the Dynamics GP install folder (typically %ProgramFiles(x86)%\Microsoft Dynamics GP) into the Dexterity install folder (typically %ProgramFiles(x86)%\Microsoft Dexterity\Dex 18.0).

Once this has been done, the dag.exe command can be run successfully.