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 email 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.
Technical Details
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 email 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 email.
- The stored procedure
outputs these values:
- A list of subscribers stored in a variable.
- A message body for the 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.
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 email 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 email generated in the previous action was successfully sent.
These default values are used for scheduling the OrderShippingAlert 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.