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
Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.