Email Notification Event Stored Procedures

Notification event stored procedures are the main work engine for creating and processing notification events. Notification event stored procedures have the following requirements:

Parameters

All notification event stored procedures must contain these parameters:

Example of Create Procedure Definition

CREATE PROCEDURE  [dbo].[Your Procedure Name]

@ORG_ID                        [dbo].[ORG],

@ORG_UNIT_ID            [dbo].[ORG],

@SCHEMA_MODE        [VARCHAR](1) = ‘N’

Result Sets

The very first select statement in the notification event stored procedure must be a selection of email addresses the notification event will process notifications for. The very first column in this result set must be the email address.

In addition to the email address, you can include additional columns in this select statement that will allow you to further filter down your result sets in subsequent tables.

For example, if you want to send an email message to a customer when a new order is created. If your customer was to create two orders in the system before the next new order email notification event fired, your customer would receive one email message with both orders listed in it. If you wanted to ensure that the customer received an email message for each order, you could include the Order Master Order Number in the select list of the email address result set.

If you add additional columns for filtering to the email address result set you must also ensure that column or columns are in any other result sets that the stored procedure will select and return to the notification service. In the example the column Order # is included in the email address selection. The column Order # is also contained in all other select statements. When this notification event fires an email message will be created for each distinct email address + order # combination.

Each additional result set returned by the stored procedure must have the email address as the first column returned in the result set. At runtime, the notification service uses the email address to join the data in each of the result sets of the notification event stored procedure when creating the mail message.

Attachments

Each notification event can have a single result set within the notification event stored procedure that returns a list of UNC paths and file names where attachment files can be found and attached to an email message. The result set for an attachment table must contain the column names shown in the table below (in addition to the mandatory email address column described earlier in this document) to identify the path and file name of the attachment file. The notification service will attempt to attach the file in the result set for the outgoing mail message if the file exists at the specified location.

Conditional Data Elements

You can conditionally include data elements in your HTML mail messages by controlling whether or not data is selected for your condition. An example of where you may want to conditionally include data elements in your mail message is when you are sending a notification email message to a customer to indicate that you have received a payment from them. In certain situations it is possible that the customer could have sent you an overpayment. In order to inform the customer that an overpayment has in fact been made you can create a result set in your notification event procedure that selects the overpayment amount whenever that overpayment amount is greater than zero. You would also create a column in your result set to hold the message you want to display to your customer in the email message. In your message template you would include the column containing the message ##OVERPAYMENTMESSAGE## and the column containing the overpayment amount ##OVERPAYMENTAMOUNT##. At runtime, if there is data in the overpayment result set then the overpayment message and overpayment amount will be added to the outgoing email message. If there is no overpayment data selected for the recipient of the mail message then the template place holders for overpayment message and overpayment amount are removed from the outgoing mail message. The Personify360 E-Mail Notification base notification event Payment received provides an example of this functionality. See Appendix E at the end of this document for additional information.

Dates and Amounts

As of 7.4.0SP1, If you want to include currency in your notifications, be sure to add the currency symbol before the amount. If the numbers are not prefixed with a character, the system will convert the currency into a date. For example, 8.25 will convert to Aug 25; use $8.25 instead. If your organization uses the multi-currency features of Personify360 you can select the currency symbol from the APP_CURRENCY table and concatenate that symbol with the amount you would like to include in the notification email.

The Notification Service uses the English (United States) regional date setting by default. If necessary, you can change this setting on the server. From the Start menu, open the Control Panel and click Region and Language. Select the appropriate Format from the drop-down, as shown below.

Be sure to restart the notification service after you make any changes.

Additionally, if you want to change the date format for a specific instance, you can modify the event stored procedure like below:
--[OD].[ORDER_DATE]  [ORDER_DATE],
(DATENAME(weekday,[OD].[ORDER_DATE])+', ' +convert(varchar, [OD].[ORDER_DATE], 107) )     [ORDER_DATE],

The code above would translate into the highlighted text below:

Dear John Smith,

Thank you for placing your order with the ABC Association. Your reference number, which can be used when contacting the ABCA regarding this order, is 1000000821 on date Monday, Apr 18, 2011. Below is a summary of your order: