Day: January 6, 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 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']