Courion Tip – “Nag” emails

After a request has not been approved, we wanted to send out an email nagging the person saying that the request has, effectively, timed out and they need to do something. I could not find a suitable criteria inside of the courion workflows to accomplish this, so I did it inside of SQL server.

First off, set the time out comment inside the workflow to say “Timeout …..” (I just used the first 7 characters, so you can make the rest of it whatever you want it to say. I have blanked out the approval steps in the following screen shot.

Next, create a database trigger, so that when an approval step’s comment field is changed to “Timeout….”, a record gets created in another table with the information. This second table (MailsToBeSent – based on a article on the internet which I have now lost the link to) will then be used as a basis for emails going out via an SSIS process. Here is the trigger.

USE [ILM]
GO

CREATE TRIGGER [dbo].[SAP.Request.Add.Timeout]
ON [dbo].[ApprSteps]

AFTER UPDATE
AS
IF UPDATE (Comments)
Insert into [ILM_PROFILE].[dbo].[MailsToBeSent]
([MailID],[From],[TO],[CC],[BCC],[Subject],[Body],[IsHTMLFormat],[Priority],RequestID,DateTimeAdded)
VALUES
(
(SELECT MAX(MAILID)+1 from [ILM_PROFILE].[dbo].[MailsToBeSent]),
(SELECT [ParameterValue] FROM [ILM_PROFILE].[dbo].[ConfigurationParameter] where ParameterName =’NoReplyFromEmailAddress’),
‘noreply@company.com’,’noreply@company.com’,’noreply@company.com’
–‘subject’
–,(SELECT REQUESTID from INSERTED),
,’Request Escalation’
–‘body’
,’This is to inform you that request no. ‘ + CAST((SELECT REQUESTID from INSERTED) AS VARCHAR(10))
+ ‘ for user creation is still pending after ‘
,’True’,1
,(SELECT REQUESTID from INSERTED)
, GetDate())
GO

Advertisements

Posted November 22, 2011 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: