Alert on Order Shipping - Workflow Event Handler

When an order ships to a company, and a portal user belonging to that company has subscribed to order shipping alerts, this event handler sends a notification of the shipment to that user.

When the event handler is activated, a new event trigger for the alert is created to control the scheduling of e-mail notifications for this alert. By default, order shipping alert notifications are sent out daily at 3 p.m. (You can change the defaults associated with the scheduling of the event on the Event Triggers form, by resetting the condition, condition retest interval and trigger reset interval values on the form.

After the event handler is activated and the OrderShippingAlert event trigger has been created, the system uses a TrackRows table to keep a list of shipments that need to have notifications sent (based on whether the customer has any subscribers to the Order Shipping Alert), and updates the list each time an order is shipped.

The event trigger runs the stored procedure OrderShippingAlertsSp at the specified time each day. It performs the following tasks:

• It checks for publication subscribers for the order shipping alert, based on customer number. Publication subscribers are found in the Publication Subscribers form, using Publication Name = 'OrderShippingAlert' and Key Value = customer number.
• If publication subscribers exist, the stored procedure gets e-mail addresses for the subscribers, using the address defined in the Users form.
• For each publication subscriber, the stored procedure finds any order shipping records for that customer that were created since the last time the stored procedure ran. It inserts those into a variable that will become the message body of an e-mail.
• The stored procedure outputs these values:
  • A list of subscribers stored in a variable.
  • A message body for the Order Shipping Alert e-mail.

  An e-mail message body should contain only one order and its line/items per e-mail. The capacity of the message body is about 4,000 characters (roughly 40 lines). If the message body created exceeds 4,000 characters, another e-mail is created with the rest of the line/items that did not fit into the previous e-mail.

The stored procedure DeleteCoShipTrackRowsSp clears the TrackRows table after the order shipping e-mail notification is successfully sent.

Technical Details

Associated Event and Stored Procedures

The event OrderShippingAlert has 3 actions:

• Action Type: Call Database Method - Calls OrderShippingAlertsSp and returns two variables, one for the list of subscribers and another one for the message body.
• Action Type: Send Email - Creates an e-mail and sends it to the list of subscribers using variables passed in from OrderShippingAlertsSp.
• Action Type: Call Database Method - Calls DeleteCoShipTrackRowsSp. This is called only if an e-mail generated in the previous action was successfully sent.

Event Trigger

• Condition: CONDITION(DATEPART(hour, CURDATETIME()) = 15)
• Condition Retest Interval: 60
• Trigger Reset Interval: 85,500
• Schedule: Fires daily at 3 p.m. Trigger sleeps for 23 ¾ hours, then awakens and checks each minute until 3 p.m. arrives again.

Related Topics

About Workflow Event Handlers

Setting Up and Activating Workflow Event Handlers

Modifying Workflow Event Handlers