SQL Scripts for Microsoft Dynamics GP: Custom Purchase Order Email Notification to Originator on Workflow Final Approval

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 creates a custom notification to the originator of a workflow process when the document is final approved; this was created due to a bug in Dynamics GP whereby the notifications were being sent incorrectly. This bug may have been corrected in subsequent versions, but I am sharing the script in case it is useful to anyone else.

Due to needing the email address, which may not be accessible through any of the tables in the Dynamics GP database, a custom table is created in the system database (called DYNAMICS by default) and will need to be populated with the ADLogin and email address for all users who will be the originator for purchase orders.

/*
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).
*/USE DYNAMICS

GO

CREATE TABLE ut_ARCCRV_WorflowOriginatorEmails

	(

		ADLogin VARCHAR(200)

		,EMail VARCHAR(200)

	)

GO

The main part of the script needs to be created in each database in which PO approval workflows are being used.

The email sent includes the lines of the PO in a table; you can customise these lines or the format of the email further, as shown in this series I did in 2017.

USE [TWO]
CREATE TRIGGER utr_AZRCRV_POP10100_WorkflowFinalApproved ON POP10100 AFTER UPDATE AS

/*
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 @EmailRecipient VARCHAR(100)

	DECLARE @EmailSubject VARCHAR(1000)

	DECLARE @EmailBody VARCHAR(MAX)

	DECLARE @EmailBody_POLines VARCHAR(MAX)

	DECLARE @POLines VARCHAR(MAX)
	IF (SELECT CASE WHEN i.Workflow_Status = 6 AND d.Workflow_Status <> 6 THEN 1 ELSE 0 END FROM inserted as i INNER JOIN deleted AS d on d.PONUMBER = i.PONUMBER) = 1

		BEGIN

			WITH ['Workflow Status'] AS

				(

					SELECT

						i.PONUMBER

						,['Workflow History'].Workflow_History_User

						,['Originator Email Address'].EMail

						,['Workflow History'].Workflow_Action

						,['Workfow Users'].ADDisplayName

						,ROW_NUMBER() OVER(PARTITION BY i.PONUMBER ORDER BY i.PONUMBER, ['Workflow History'].DEX_ROW_ID DESC) AS RowNumber

					FROM

						inserted AS i

					INNER JOIN

						WFI10002 AS ['Workflow Instance Master'] --Workflow Instance Master (WFI10002)
							ON

								['Workflow Instance Master'].WfBusObjKey = i.PONUMBER

					INNER JOIN

						WF30100 AS ['Workflow History'] --Workflow History (WF30100)
							ON

								['Workflow History'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID

					INNER JOIN

						ut_ARCCRV_WorflowOriginatorEmails AS ['Originator Email Address']

							ON

								['Originator Email Address'].ADLogin = ['Workflow Instance Master'].Workflow_Originator

					INNER JOIN

						WF40200 AS ['Workfow Users'] --Workflow Users (WF40200)
							ON

								['Workfow Users'].UsersListGuid = ['Workflow History'].Workflow_Step_Assign_To

				)
			SELECT

					@EmailRecipient = RTRIM(CAST(['Workflow Status'].EMail AS VARCHAR(100)))

					,@EmailSubject = 'Purchase Order ' + RTRIM(CAST(['Workflow Status'].PONUMBER AS VARCHAR(100))) + ' has been Final Approved'

					,@EmailBody = 'Purchase Order ' + RTRIM(CAST(['Workflow Status'].PONUMBER AS VARCHAR(100))) + ' has been Final Approved by ' + RTRIM(CAST(['Workflow Status'].ADDisplayName AS VARCHAR(100))) + '.'

									+ CHAR(10) + CHAR(10)

			FROM

				['Workflow Status']

			WHERE

				['Workflow Status'].RowNumber = 1
		-- add PO lines

		SELECT @POLines = COALESCE(@POLines + '', '') +

			'<tr><td width="91" style="width:68.25pt;border:solid #3B5E87 1.0pt;background:#C6D4E8;padding:1.5pt 1.5pt 1.5pt 1.5pt">

		<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;color:black;letter-spacing:-1.0pt"></span><b><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;letter-spacing:-1.0pt;mso-fareast-language:EN-GB"><o:p></o:p></span></b></p>

		<p class="MsoNormal"><b><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;color:black;letter-spacing:-1.0pt">'

				+ 'Item Number'

				+ '</span></b><b><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;letter-spacing:-1.0pt"><o:p></o:p></span></b></p></td><td width="188" style="width:141.0pt;border:solid #3B5E87 1.0pt;border-left:none;background:#C6D4E8;padding:1.5pt 1.5pt 1.5pt 1.5pt">

		<p class="MsoNormal"><b><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;color:black;letter-spacing:-1.0pt">'

				+ 'Item Description'

				+ '</span></b><b><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;letter-spacing:-1.0pt"><o:p></o:p></span></b></p></td><td width="84" style="width:63.0pt;border:solid #3B5E87 1.0pt;border-left:none;background:#C6D4E8;padding:1.5pt 1.5pt 1.5pt 1.5pt">

		<p class="MsoNormal"><b><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;color:black;letter-spacing:-1.0pt">'

				+ 'Extended Cost'

				+ '</span></b><b><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;letter-spacing:-1.0pt"><o:p></o:p></span></b></p></td></tr>'

		SELECT @POLines = COALESCE(@POLines + '', '') +

				'<tr><td width="91" style="width:68.25pt;border:solid #3B5E87 1.0pt;border-top:none;padding:1.5pt 1.5pt 1.5pt 1.5pt">

		<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;letter-spacing:-1.0pt">'

					+ CAST(RTRIM(['Purchase Order Lines'].ITEMNMBR) AS VARCHAR(100))

					+ '<o:p></o:p></span></p></td><td width="188" style="width:141.0pt;border-top:none;border-left:none;border-bottom:solid #3B5E87 1.0pt;border-right:solid #3B5E87 1.0pt;padding:1.5pt 1.5pt 1.5pt 1.5pt">

		<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;letter-spacing:-1.0pt">'

					+ CAST(RTRIM(['Purchase Order Lines'].ITEMDESC) AS VARCHAR(100))

					+ '<o:p></o:p></span></p></td><td width="84" style="width:63.0pt;border-top:none;border-left:none;border-bottom:solid #3B5E87 1.0pt;border-right:solid #3B5E87 1.0pt;padding:1.5pt 1.5pt 1.5pt 1.5pt">

		<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Verdana",sans-serif;letter-spacing:-1.0pt">'

					+ CAST(RTRIM(['Purchase Order Lines'].EXTDCOST) AS VARCHAR(100))

					+ '<o:p></o:p></span></p></td></tr>'

			FROM

				inserted AS i

			INNER JOIN

				POP10110 AS ['Purchase Order Lines'] --Purchase Order Line (POP10110)
					ON

						['Purchase Order Lines'].PONUMBER = i.PONUMBER
		SELECT @EmailBody = @EmailBody + '<table class="MsoNormalTable" border="0" cellpadding="0">

		<tbody>

		<tr>

		<td style="padding:.75pt .75pt .75pt .75pt">

		<pre><span style="letter-spacing:-1.0pt"><o:p> </o:p></span></pre>

		<pre><span style="letter-spacing:-1.0pt">PO0002448<o:p></o:p></span></pre>

		<pre><span style="letter-spacing:-1.0pt"><o:p> </o:p></span></pre>

		<table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse;border:none">

		<tbody>' + @POLines + '</tbody>

		</table>'
		/* https://msdn.microsoft.com/en-us/library/ms190307.aspx */

		EXEC msdb.dbo.sp_send_dbmail

			@profile_name = 'Workflow Notification'

			,@recipients = @EmailRecipient

			,@subject = @EmailSubject

			,@body = @EmailBody

			,@body_format ='HTML'

	END
GO

Read original post SQL Scripts for Microsoft Dynamics GP: Custom Purchase Order Email Notification to Originator on Workflow Final Approval at azurecurve|Ramblings of an IT Professional