Month: January 2023

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 copy vendor emails from the Active Documents table to the standard Dynamics GP email fields where they weren’t already present. This script was written for a client who was replacing the sending of remittances using Active Docs to using the standard functionality.

The script returns mutiple emai address from the Active Docs tables as a semi-colon delimited ist to insert into one field in the standard tables.

/*
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).
*/
INSERT INTO SY01200 --Internet Addresses (SY01200)
	(
		Master_Type,Master_ID,ADRSCODE,INET1,INET2,INET3,INET4,INET5,INET6,INET7,INET8,Messenger_Address,INETINFO,EmailToAddress,EmailCcAddress,EmailBccAddress
	)

SELECT DISTINCT
	'VEN' as Master_Type
	,['Active Docs'].VENDORID AS Master_ID
	,['PM Creditor Master'].VADCDTRO AS ADRSCODE
	,'' AS INET1
	,'' AS INET2
	,'' AS INET3
	,'' AS INET4
	,'' AS INET5
	,'' AS INET6
	,'' AS INET7
	,'' AS INET8
	,'' AS Messenger_Address
	,'' AS INETINFO
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 1
		AND
			['INNER'].VENDORID = ['Active Docs'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ),'') AS EmailToAddress
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 2
		AND
			['INNER'].VENDORID = ['Active Docs'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ), '') AS EmailCcAddress
	,'' AS EmailBccAddress
FROM
	EMA00101 AS ['Active Docs']
INNER JOIN
	PM00200 AS ['PM Creditor Master'] --PM Vendor Master File (PM00200)
		ON
			['PM Creditor Master'].VENDORID = ['Active Docs'].VENDORID
WHERE
	(
		SELECT
			COUNT(*)
		FROM
			SY01200 AS ['Internet Addresses'] --Internet Addresses (SY01200)
		WHERE
			['Internet Addresses'].Master_Type = 'VEN'
		AND
			['Internet Addresses'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
	) = 0

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 a list of email addresses for vendors from both the standard Dynamics GP fields as well as Active Docs. The email addresses from Active Docs are returned as a semi colon delimited list.

They were returned this way to allow users to check the returned email addresses and correct any incorrect ones, through the Active Docs window, before another script (which I’ll post soon) was run to migrate the emails from the Active Docs fields to the standard ones.

/*
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
	['PM Creditor Master'].VENDORID AS 'Creditor ID'
	,['PM Creditor Master'].VENDNAME AS 'Creditor Name'
	,['PM Creditor Master'].VADCDTRO AS 'Address Code'
	,['Internet Addresses'].EmailToAddress AS 'GP Email To Address'
	,['Internet Addresses'].EmailCcAddress AS 'GP Email Cc Address'
	,['Internet Addresses'].EmailBccAddress AS 'GP Email Bcc Address'
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 1
		AND
			['INNER'].VENDORID = ['PM Creditor Master'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ),'') AS 'Active Docs Email To Address'
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 2
		AND
			['INNER'].VENDORID = ['PM Creditor Master'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ), '') AS 'Active Docs Email Cc Address'
FROM
	PM00200 AS ['PM Creditor Master']
LEFT JOIN
	SY01200 AS ['Internet Addresses']
		ON
			['Internet Addresses'].Master_Type = 'VEN'
		AND
			['Internet Addresses'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses'].ADRSCODE = ['PM Creditor Master'].VADCDTRO

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 return all unposted batches and the workflow approval comments which have been entered; it was used within a customisation of workflow in Dynamics GP which allowed the returned comment to be added to the workflow approval notification emails.

/*
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).
*/
WITH COMMENTS AS
	(
		SELECT
			['Posting Definitions Master'].BACHNUMB
			,['Posting Definitions Master'].BCHSOURC
			,CASE WHEN LEFT(CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)), 54) = 'The workflow was automatically rejected by the system.' THEN
					'SYSTEM'
				ELSE
					LTRIM(RTRIM(SUBSTRING(REPLACE(['Workflow History'].Workflow_History_User, '\', REPLICATE(' ', LEN(['Workflow History'].Workflow_History_User))), (1) * LEN(['Workflow History'].Workflow_History_User)+1, LEN(['Workflow History'].Workflow_History_User))))
				END + 
				', ' +
				FORMAT(['Workflow History'].Workflow_Completion_Date, 'dd/MM/yyyy') + 
				' ' + 
				FORMAT(['Workflow History'].Workflow_Completion_Time, 'T', 'en-US') + 
				': ' + 
				CASE ['Workflow History'].Workflow_Action
				WHEN 1 THEN
					'SUBMITTED'
				WHEN 2 THEN
					'RESUBMITTED'
				WHEN 3  THEN
					'APPROVED'
				WHEN 4 THEN
					'TASK COMPLETED'
				WHEN 5 THEN
					'REJECTED'
				WHEN 6 THEN
					'DELEGATED'
				WHEN 7 THEN
					'RECALLED'
				WHEN 8 THEN
					'ESCALATED'
				WHEN 9 THEN
					'EDITED'
				WHEN 10 THEN
					'FINAL APPROVED'
			END + ' ' + RTRIM(['Workflow Instance Master'].WfBusObjKey)  + '. ' +
				RTRIM(CAST(ISNULL(['Workflow History'].Workflow_Comments,'') AS VARCHAR(8000))) AS Comment

			,ROW_NUMBER() OVER (PARTITION BY ['Posting Definitions Master'].BACHNUMB, ['Posting Definitions Master'].BCHSOURC ORDER BY Workflow_Completion_Date DESC,Workflow_Completion_Time DESC) AS RowNumber

		FROM
			SY00500 AS ['Posting Definitions Master']
		INNER JOIN
			WFI10002 AS ['Workflow Instance Master']
				ON
					['Workflow Instance Master'].WfBusObjKey = RTRIM(['Posting Definitions Master'].BACHNUMB) + '~' + RTRIM(['Posting Definitions Master'].BCHSOURC) + '~' + FORMAT(['Posting Definitions Master'].CREATDDT, 'yyyy/M/dd') + '~' + FORMAT(['Posting Definitions Master'].TIME1, 'HH:mm:ss')
		LEFT JOIN
			WF30100 AS ['Workflow History']
				ON
					['Workflow History'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID
		WHERE
			['Workflow Instance Master'].WfBusObjKey = RTRIM(BACHNUMB) + '~' + RTRIM(BCHSOURC) + '~' + FORMAT(CREATDDT, 'yyyy/M/dd') + '~' + FORMAT(TIME1, 'HH:mm:ss')
		AND
			CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> 'No approval is required for this step.'
		AND
			CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> 'No action is required for this step.'
		AND
			CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> ''
	)

SELECT
    ['Posting Definitions Master'].BCHSOURC
	,['Posting Definitions Master'].BACHNUMB
    ,(STUFF((
        SELECT
			CHAR(10) + 
				COMMENTS.Comment
        FROM
			COMMENTS
		WHERE
			COMMENTS.BACHNUMB = ['Posting Definitions Master'].BACHNUMB
		AND
			COMMENTS.BCHSOURC = ['Posting Definitions Master'].BCHSOURC
		ORDER BY
			COMMENTS.RowNumber DESC
        FOR XML PATH('')
        ), 1, 1, '')
	) AS Comments
    ,'' AS CMMTTEXT
FROM
	SY00500 AS ['Posting Definitions Master']

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 wanted to be able to see how much of the annual PO commitment budget was remaining. It takes into account postings to the GL, PO commitments and entered purchase requisitions. This particular client wasn;t interested in seeing having a period check, just a check against the overall annual budget amount.

/*
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
	ACTINDX
	,SUM(VALUE) AS VALUE
	,YEAR1
FROM
	(SELECT
		GLB.ACTINDX AS ACTINDX
		,SUM(GLB.BUDGETAMT) AS VALUE
		,GLB.YEAR1 AS YEAR1
	FROM
		GL00201 AS GLB --[gpt=GL00201]
	INNER JOIN
		CPO40002 AS CPOS --[gpt=CPO40002]
			ON
				CPOS.BUDGETID = GLB.BUDGETID WHERE PERIODID NOT IN (0,13)
	GROUP BY
		GLB.ACTINDX
		,GLB.YEAR1
	UNION ALL
		SELECT
			GLPB.ACTINDX AS ACTINDX
			,SUM(ISNULL(GLPB.PERDBLNC,0)) * -1 AS VALUE
			,GLPB.YEAR1 AS YEAR1
		FROM
			GL10110 AS GLPB --[gpt=GL10110]
		WHERE
			GLPB.PERIODID NOT IN (0,13)
		GROUP BY
			GLPB.ACTINDX
			,GLPB.YEAR1
		UNION ALL
			SELECT
				CPO.ACTINDX AS ACTINDX
				,SUM(ISNULL(CPO.Committed_Amount,0)) * -1 AS VALUE
				,SYY.YEAR1 AS YEAR1
			FROM
				CPO10110 AS CPO --[gpt=CPO10110]
			INNER JOIN
				SY40100 AS SYY --[gpt=SY40100]
					ON
						SYY.ODESCTN = 'Budget Transaction Entry'
					AND
						PERIODDT <= CPO.REQDATE
					AND
						PERDENDT >= CPO.REQDATE
						AND
							PERIODID NOT IN (0,13)
			GROUP BY
				CPO.ACTINDX
				,SYY.YEAR1
			UNION ALL
				SELECT
					POP.INVINDX AS ACTINDX
					,SUM(ISNULL(POP.EXTDCOST,0)) * -1 AS VALUE
					,SYY.YEAR1 AS YEAR1
				FROM
					POP10210 AS POP --[gpt=POP10210]
				INNER JOIN
					SY40100 AS SYY --[gpt=SY40100]
						ON
							SYY.ODESCTN = 'Budget Transaction Entry'
						AND
							PERIODDT <= POP.REQDATE
						AND
							PERDENDT >= POP.REQDATE
						AND
							PERIODID  NOT IN (0,13)
				GROUP BY
					POP.INVINDX
					,SYY.YEAR1
	) AS REMBUDGT
GROUP BY
	ACTINDX
	,YEAR1

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 a list of items at each location and the different quantities on each quantity type (such as on hold, allocation and on different types of open transactions).

/*
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
	['Item Quantity Master'].ITEMNMBR
	,['Item Quantity Master'].LOCNCODE
	,SUM(['Item Quantity Master'].QTYONHND - ['Item Quantity Master'].ATYALLOC - ISNULL(['Sales Transaction Amounts Work'].QTYALLOC,0)) AS 'QTYAVAIL'
	,SUM(['Item Quantity Master'].QTYONHND) AS QTYONHND
	,SUM(['Item Quantity Master'].ATYALLOC)  AS 'QTYALLOC'
	,SUM(['Sales Transaction Amounts Work'].ORDQTYALLOC) AS 'OPENORDERS'
	,SUM(['Item Quantity Master'].QTYBKORD) AS 'OPENBACKORDERS'
	,SUM(['Sales Transaction Amounts Work'].RTNQTYALLOC) AS 'OPENRETURNS'
	,SUM(['Sales Transaction Amounts Work'].INVQTYALLOC) AS 'OPENINVOICES'
	,SUM(['Sales Transaction Amounts Work'].QTYONPO) AS 'QTYONORD'
	,SUM(['Purchase Order Line'].QTYONPO) AS QTYONPO
	,SUM(['Item Quantity Master'].QTYDMGED) AS QTYDAMAGED
	,['Item Quantity Master'].RCRDTYPE AS RCRDTYPE
FROM
	IV00102 AS ['Item Quantity Master'] --Item Quantity Master (IV00102)
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM(CASE WHEN SOPTYPE NOT IN (1,4,5) THEN (QTYREMAI - ATYALLOC) * QTYBSUOM ELSE 0 END) AS QTYALLOC
			,SUM(CASE WHEN SOPTYPE = 2 THEN (QTYREMAI - ATYALLOC) * QTYBSUOM ELSE 0 END) AS ORDQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 4 THEN (ATYALLOC) * QTYBSUOM ELSE 0 END) AS RTNQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 3 THEN (ATYALLOC) * QTYBSUOM ELSE 0 END) AS INVQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 5 THEN QTYONPO ELSE 0 END) AS QTYONPO
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		GROUP BY
			ITEMNMBR
			,LOCNCODE
	) AS ['Sales Transaction Amounts Work']
		ON
			['Sales Transaction Amounts Work'].ITEMNMBR = ['Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work'].LOCNCODE = ['Item Quantity Master'].LOCNCODE
LEFT JOIN
	(
		SELECT
			(['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) - ISNULL(['Purchasing Receipt Line Quantities'].QTYSHPPD, 0) AS QTYONPO
			,['Purchase Order Line'].ITEMNMBR
			,['Purchase Order Line'].LOCNCODE
		FROM
			POP10110 AS ['Purchase Order Line'] --Purchase Order Line (POP10110)
		LEFT JOIN
			POP10500 AS ['Purchasing Receipt Line Quantities'] --Purchasing Receipt Line Quantities (POP10500)
				ON
					['Purchasing Receipt Line Quantities'].PONUMBER = ['Purchase Order Line'].PONUMBER
				AND
					['Purchasing Receipt Line Quantities'].POLNENUM = ['Purchase Order Line'].ORD
	) AS ['Purchase Order Line']
		ON
			['Purchase Order Line'].ITEMNMBR = ['Item Quantity Master'].ITEMNMBR
		AND
			['Purchase Order Line'].LOCNCODE = ['Item Quantity Master'].LOCNCODE
WHERE
	['Item Quantity Master'].RCRDTYPE = 2
GROUP BY
	['Item Quantity Master'].ITEMNMBR
	,['Item Quantity Master'].LOCNCODE
	,['Item Quantity Master'].RCRDTYPE