Liaison Messenger EDD PLUS
Case Studies
Using SQL Stored Procedures to create unattended tasks
CHALLENGE: A Customer with 5 remote shipping locations needed to automatically produce Packing Slips with data coming from 3 different order, invoicing, and shipping systems.
They needed to produce these Packing Slips automatically and have the tracking number(s) retrieved and appear on the document. Any batch of packing slips that are being created must immediately be separated, grouped and distributed to each
remote Shipping location.
Then, an Email must be sent (or SMS Texted) to the Customer (and the Salesperson for that account), to serve as an advanced shipment notification.
And finally, all this must happen automatically without any user intervention. Monday through Friday 24 hours a day.
SOLUTION:
In this scenario, a custom Packing Slip was created in Crystal Reports, bridging information from three different systems that was pre-processed in a temporary print table. A stored SQL procedure was created to check the Open Order table for any orders ready to be shipped and populate the temporary print table
Liaison Messenger EDD was able to accomplish this task with relative ease by using two specific EDD Scripts. The 1st script generates the Packing Slips and sends the printed output back to Messenger Server <Standard Processing Queue> so the second script could consume the output, parse, and distribute the Packing Slips.
The first script was created as a Schedule Action. It will run everyday at 30 minute intervals. This means that the script will execute every 30 minutes all day every day except over the weekends. Each line of each script will processe immediately upon completion of the previous line script.
The overview of the first script is as follows:
Line 1: Calls a SQL stored procedure (which they created) to look for and select any orders that were ready for shipping. If any orders are found, the stored procedure calls their in-house UPS shipping program to retrieve the tracking number(s) and assign them to the Order.
Line 2: Runs the Crystal Report and prints the documents directly into the <Standard Processing Queue> of Liaison Messenger EDD for immediate distribtion.
Before we talk about the second script which actually distributes the packing slips, lets take a closer look at the first script starting Line 1's configuration. The action Execute SQL Statement is selected from the General tab of the Configuration screen and the details filled in on the SQL tab. First the Data Source, User ID, the Password, the name of the SQL stored procedure, and any argument to be passed or populated, if any.
Line 2 is even easier. Run Crystal Reports was selected from the General tab. Then on the Crystal Reports tab, the CR filename and path, User ID, and Password is established and any arguments/parameters required for the report can be passed. Finally, the destination is set to <Standard Processing Queue> so the Messenger EDD Server can distribute the Packing Slips in a separate script as we will discuss shortly.
Every 30 minutes Monday thru Friday, this script runs and finds orders to be shipped. If orders exist, the Crystal Reports engine will print the packing slip output to the Liaison Messenger EDD queue in a PDF as the screenshot above illustrates.
Immediately after the creation of the initial output, Messenger EDD Server will begin processing the output using the second script which was created and called Distribute Packing Slip. The following screenshot, illustrates the EDD Script that will handle the distribution of the packing slips.
The first thing Messenger EDD will do is; burst and split the Crystal Report Packing Slip (output/print stream) by Document ID and Archive a copy in the designated archive folder or Document Management Library.
Then it will regroup the Packing Slips by Location (Shipping) Code and distribute the respective Packing Slips for each specific Location using the preferred method of delivery for that location (email, fax, or printed to printer).
Finally, it will send an email/text message to the Salesperson of the account notifying him/her that the order is shipping.
Lastly, it will send an email to the customer as a shipment notification as well.
Both of these scripts run without any user intervention and within seconds of each other and in one giant fell swoop...