Month: December 2022

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”