Alert on Past Due Open CO Lines - 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 e-mail 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 e-mail 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 e-mail.
• The stored procedure outputs these values:
  • A list of subscribers stored in a variable.
  • A message body for the Late 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 LateOrderShippingAlert has 3 actions:

• Action Type: Call Database Method - Calls LateOrderShippingAlertsSp 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 LateOrderShippingAlertsSp.
• 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: TRUE
• Condition Retest Interval: 86400
• Trigger Reset Interval: 86400
• Schedule: Fires initially when the handler is activated. Trigger then fires every 24 hours thereafter.

Related Topics

About Workflow Event Handlers

Setting Up and Activating Workflow Event Handlers

Modifying Workflow Event Handlers