Using UPS WorldShip to automate a Pick and Pack
Shipping is a core part of business for many companies, and that means that integrating shipping into your LOB (Line of Business) software has an immediate and tangible value. While we might dream of a single, unified method, most major shipping companies offer proprietary solutions.
UPS offers WorldShip. Since it is sued in a variety of industries with differing needs, it contains a number of integration options. The two main features we will focus on are batch label printing and individual label printing. The most common way to use UPS WorldShip is through method typically referred to as "Pick and Pack".
Many of us already have software to do this, but we usually don't cover the "last mile" of connection to the shipping company. This is where Worldship connectivity comes into play.
Overall Breakdown of Process
There are a few steps involved in integrating your system with WorldShip. Let's look at high-level breakdown of the process.
- An order is placed and a picking slip is created.
- A warehouse "picker" then prints out a packing slip that contains a barcode.
- WorldShip then grabs the order information from the ORDERS file and inserts the shipping information into its application.
- The WorldShip operator then presses F7 which will process the shipment with UPS, thus retrieving a tracking number.
- WorldShip marks the order as shipped and inserts the tracking number into the ORDERS file in the database.
- WorldShip prints an active shipping label for your customer's order.
Setting Up Your Database
The interface we will be using in this article is ODBC. While you might implement a different connector between your application and Worldship, much of the thought process described here, and some of the implementation, will be the same.
As we all know, our business applications are very complex and contain lots of data that a thrid-party shipper doesn't care about. Unfortunately, all that excess data actually causes problems when using ODBC and WorldShip.
The best way to keep a stable ODBC connection between WorldShip and our database is to simplify the files to only hold the information that's relevant to our shipping partner.
To do this, I recommend creating a separate account call WORLDSHIP or UPS, and then create two files: UPS_ORDERS and UPS_ORDER_TRACKING. Why a separate account? It has more to do with keeping things clean and secure than anything else. It's a personal preference. The two new files, however, are very important and help keep things clean. This is especially true if we are going to link other shipping partners in the future. We'll use this model throughout the article.
The UPS_ORDERS file will contain the shipping information for WorldShip to query against. This file will not be updated by WorldShip. This does give you options to use correlatives or I-Types, but I don't recommend it. It is best to follow the KISS (Keep It Simple Shipping) principle.
The UPS_ORDER_TRACKING file will be updated by WorldShip with the tracking number for each order.
Keep in mind we are creating these file to be used with ODBC/SQL and that it is important to live within those limitations. For example, do not use dots in your dictionary names; Use underscores instead. If your data is longer than the dictionary lengths, then trim it.
WorldShip and ODBC can be very picky about its data and the error message won't always be helpful, and sometimes don't every show up.
The layout in figure 1 is the bare minimum needed by WorldShip in order to fill in the shipping information for a package.
Like I said before, since WorldShip is just querying the data, you could replace all the SHIP_TO fields as I-Type or Correlatives, but it is simpler to just create each shipment record when you need it and clean it up later.
There are two approaches you may want to consider when managing the content of this table. We can opt for a deep integration and change out programs to update this table in lockstep with the production ORDERS or we could create this data from ORDERS with an after-the-fact method.
Either way, as you can see, the data is really simple. The one thing I would like to point out is the difference in ID and ORDER_ID. The ID dictionary is the shipment id, which can be different from the ORDER_ID. I've added the ORDER_ID into these records for reference and so it can be used by the program that will be processing the UPS_ORDER_TRACKING file.
This file acts as a temporary holding location for the tracking number for individual shipments. It is much easier to have WorldShip insert rows to an ODBC table than have it updated an existing dataset.
The layout in Figure 2 is all that is required if we are just having WorldShip add new records to the database.
Getting this information back into the main database can be done several different ways. The simplest is to have a program process them in batch if real time isn't a requirement. The next best way is to add a file trigger that will copy the tracking number back into your main application.
This article is written with Universe as the database. Each system has a different way to handle file triggers, but in the general sense they are do the same thing. You will need to make the appropriate adjustments for your environment.
Figure 3 shows a sample of the file trigger. If you need to know more about file triggers, make sure you visit the International Spectrum website. We have plenty of other examples.
SUBROUTINE UPS.TRACKING.UPDATE(TEST,SCHEMA,TABLE,EVENT,TIME,NEWID,NEWREC,OLDID,OLDREC, ASSOC,ASSOC.EVENT,COUNT,CHAIN.CASCADE,CASCADE) COMMON /UPS/ UPS.ORDERS.FILE FILEOPENED = FILEINFO(UPS.ORDERS.FILE,0) IF (FILEOPENED # 1) THEN OPEN '','UPS_ORDERS' TO UPS.ORDERS.FILE ELSE CRT 'COULD NOT OPEN UPS_ORDERS'; RETURN END * UPS.SHIPMENT.ID = NEWID UPS.TRACKING.NO = NEWREC<1> UPS.TRACKING.DATE = DATE() UPS.TRACKING.TIME = TIME() * READ UPS.ORDERS.ITEM FROM UPS.ORDERS.FILE, UPS.SHIPMENT.ID ELSE UPS.ORDERS.ITEM = "" END * ORDER.ID = UPS.ORDERS.ITEM<1> * *** Do something with the data * RETURN END
Once you have the trigger compiled and cataloged, then attach it to the file:
>CREATE TRIGGER UPS.TRACKING.UPDATE AFTER DELETE ON UPS_TRACKING FOR EACH ROW
Now, every time Worldship adds tracking information to the database, your system will get updated.
Integrating UPS WorldShip
Since we are talking about using an ODBC connection to interface with WorldShip, the following steps are focused on the Connection Assistant.
You will need to install the appropriate 32-bit ODBC driver and configure it with your database. If you have never setup ODBC for your database, then we have more details instructions on the International Spectrum website for each database.
Once you have your ODBC connection setup and working with your database, you will need to create your data import and export mapping.
Follow these steps below which reference the UPS instructions starting on Page 10: UPS Importing Shipment Instructions ( Importing_Shipment_Data.pdf ).
Please note the following steps from the PDF:
- Step 4: Make sure you select 'By Known ODBC Source' and select your installed ODBC driver you setup previously.
- Step 8 (part 1): You want to select the 'ups_orders' table and map the appropriate shipment information to the WorldShip fields on the right.
- Step 8 (part 2): When mapping your data from your orders table, make sure you set the Reference ID field to the shipment id. This allows you to 1) Use the shipment id later when exporting your tracking data and 2) You can then search UPS by your tracking number OR your Reference ID which is also your shipment id (very convenient if a tracking number is lost!) [Figure 4]
- Step 12: If you have custom shipping options that is predetermined make sure you map these as seen in this step.
- Name your map something meaningful (i.e.: 'Shipment Import').
- Step 20: Make sure you select your newly named import map under Keyed Import as this is how WorldShip knows to use your ODBC driver and map to import your shipping data [Figure 5].
Follow these steps below which reference UPS Exporting Shipment Data Instructions ( Exporting_Shipment_Data.pdf ).
Please note the following:
- Skip to Page 8: 'Export Shipment Data using Connection Assistant' since we want to automatically update our 'ups_order_tracking' table after a label is processed.
- Step 8: Make sure you map the tracking number and shipment id to the 'ups_order_tracking' table.
- Name your map something meaningful(i.e.: Shipment Export).
- Step 12: You can either configure WorldShip to update your UPS_ORDER_TRACKING table at the end of day or after processing each shipment.
This really depends on your system requirements. I would recommend having WorldShip update UPS_ORDER_TRACKING after each label is printed so the data is immediately available in the database.
End the day processing requires your WorldShip computer to be on and the WorldShip application to be running. If computer is off or the WorldShip application is closed, then the tracking numbers will never be sent back to the database.
Processing the Shipment
Now that you have setup WorldShip to interface with your database, you can use the 'Keyed Import' functionality to start processing packages. If you plan on using a barcode scanner to enter the shipment id, then make sure the 'Keyed Import' box has focus and is waiting for import.
Once the shipment id is entered, WorldShip will query UPS_ORDERS for that shipment id, and populate the proper WorldShip fields. Once everything is correct, the user can press 'F7' to process the shipment and WorldShip will insert the tracking number back into UPS_ORDER_TRACKING.
Your shipment — and depending on the actions in the file trigger, your order information — is now marked as shipped, updated with a tracking number, and you have a package ready to be picked up by UPS.
Email the Customer a Tracking Number
Just recording the tracking information in your system may be enough for your needs, but you don't have to stop there. Since the data is now fully exposed to you, you can do things like email the tracking information to your customer.
While this can be done from inside of WorldShip itself, I recommend doing from within your business application. To give one critical example, Worldship doesn't offer much in the way of customizing the email.
By taking control, you can provide branded emails and more details about the shipment like what was included and how to handle returns
While this seems like a lot of steps to go through, it's a lot simpler than you might think. It also streamlines warehouse processes and halves your company time and resources, not to mention reducing mistakes.