Day: August 24, 2022

Custom Email Notification When PO Create from PR in Microsoft Dynamics GP

Ian Grieve6 minute read2 Comments

Microsoft Dynamics GPThe workflow module in Microsoft Dynamics GP can send quite a lot of different notification emails, but the one I am often asked for, an email notification when a purchase is created from a purchase requisition, does not exist.

After being asked a few times, I had a think about how this could be done and came up with a SQL trigger on the Purchasing Requisition History (POP30200) table which joins to the SOP_POPLink (SOP60100) table when a PO has been created. This is easily deployed and also easily customised by customers to meet their own needs by, for example, changing the content of the notification message.

The first highlighted section is the email address of the receipient, in the example below I am building the email using the requested by username concatenated with a email domain internal to my test VM; the second highlighted section is the name of the SQL Database Mail profile which will be used to send the email.

/*
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 TRIGGER utr_AZRCRV_POP30200_PurchaseRequisition ON POP30200 AFTER INSERT AS
	
	DECLARE @EmailRecipient VARCHAR(100)
	DECLARE @EmailSubject VARCHAR(1000)
	DECLARE @EmailBody VARCHAR(MAX)
	DECLARE @PRFound BIT = 0

	SELECT
		@EmailRecipient = RTRIM(['Purchase Requisition Work'].REQSTDBY) + '@azurecurve.isc'
		,@EmailSubject = 'Purchase Order ' + RTRIM(CAST(['SOP_POP Link'].PONUMBER AS VARCHAR(100))) + ' has been created'
		,@EmailBody = 'Purchase Order ' + RTRIM(CAST(['SOP_POP Link'].PONUMBER AS VARCHAR(100))) + ' has been created from purchase requisition ' + RTRIM(CAST(['Purchase Requisition Work'].POPRequisitionNumber AS VARCHAR(100)))
		,@PRFound = 1
	FROM
		inserted AS ['Purchase Requisition Work']
	INNER JOIN
		SOP60100 AS ['SOP_POP Link'] -- SOP_POPLink (SOP60100)
			ON
				['SOP_POP Link'].SOPNUMBE = ['Purchase Requisition Work'].POPRequisitionNumber

	/* send email using database mail
	https://msdn.microsoft.com/en-us/library/ms190307.aspx
	*/
	IF (SELECT @PRFound) = 1
	BEGIN
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = 'MDGP Workflow'
			,@recipients = @EmailRecipient
			,@subject = @EmailSubject
			,@body = @EmailBody
			,@body_format ='HTML'
	END
GO