Alert on Late Order Shipping - Workflow Event Handler

When an order ships late, and a mobile user has subscribed to late order shipping alerts for a particular customer, 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 email notifications for this alert. By default, order shipping alert notifications are sent when the handler is activated and every 24 hours thereafter. (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 there are any subscribers to the Late Order Shipping Alert), and updates the list each time a late order is shipped.

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

• It checks for publication subscribers for the late 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 email addresses for the subscribers, using the address defined in the Users form.
• For each publication subscriber, the stored procedure finds any late 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 email.
• The stored procedure outputs these values:
  • A list of subscribers stored in a variable.
  • A message body for the Late Order Shipping Alert email.

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

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