Overview
This document describes the support in xTuple ERP to integrate with “UPS WorldShip” software, and shows the data the applications can share to generate the shipment information. “WorldShip” is a windows based application provided and supported by UPS that allows UPS customers to enter shipment information, create and print labels, obtain tracking numbers, request package pickup and get estimated cost for their shipments. Once the systems are connected, specific data can be pulled from xTuple to create the shipments and result data can be pushed back into xTuple.
Note: UPS WorldShip technical support and configuration assistance is provided directly by UPS. Please contact your local dealer or visit ups web site at www.ups.com for reference.
Take into consideration the system requirements from UPS.
Installation
Set up of the integration will require a couple of things to be configured in order to allow both systems to communicate between them.
Lets walk thru them as follow:
PostgreSQL Connection Driver
The first step will be to download and install the PostgreSQL ODBC 32 bits drivers.
The driver can be downloaded from https://www.postgresql.org/ftp/odbc/versions/msi/ and run the installer. It is very important to choose the 32 bits driver. The 64 bits driver will not work with UPS WorldShip.
The driver can be also installed using the PostgreSQL Stack Builder utility as shown below:
Once the ODBC driver has been installed, open the “ODBC Data Source Administrator (32-bit)” and go into the “System DSN” tab and “Add...” a record selecting the “PostgreSQL ODBC Driver (UNICODE) as shown below:
Complete the setup screen answering the following fields using your connection data:
- Data Source - It must be “xTuple-UPS” to work
- Database - The name of your production database
- Server - The IP address of your production server
- User Name - The user for the connection, it must have disabled “Enhanced authentication”
- SSL Mode - Prefer or required
- Port - Your database server configured port
- Password - The password for the user
After entering the connection information, test it by clicking on the TEST button, and you should receive a result like this:
If the result is successful, you have finished the PostgreSQL connection driver configuration and can proceed to the setup of WorldShip.
WorldShip
Setup
The first step will be to download the WorldShip installer from the UPS website. Once it's downloaded, you should then run the installer and follow the instructions.
Registering
Users will be asked to enter their UPS account at the end of the setup and will need a proof of ownership of the UPS account. For this, WorldShip will ask you to provide information from the latest invoice you received from UPS (not older than 60 days). You may need to call UPS support for activation. Having access to the communications setup lets the program know how to access UPS servers on the Internet.
Once the activation is complete, you should be able to access the WorldShip application, which will look like this:
The application will ask you to complete the setup of label and invoice printers. Be sure to perform that task before you try to print any labels.
Import Maps
The next step is to configure the map fields. This task requires knowledge of xTuple’s database at the table and field level. Fortunately, xTuple has configured this part for. You can download the configuration files from the xTuple customer portal. Once you have the configuration file, copy them to the C:\ProgramData\UPS\WSTD\ImpExp\Shipment folder to make them available on the system’s import/export menu as shown below:
Once you can see the maps, you will need to edit each one of the maps by touching the first map on the map list and click the “ODBC Login” button. Once the login screen pop up, you must change the credentials of the map (username and password) to allow the map to work. This task must be performed for both maps.
Configure Ship Vias
In order to pass the ship via data to the WorldShip, the ship via code must match the service name and the description must be blank (e.g., ship via code: Ground.
Operation
Importing Shipment Information
Now it is time to import data from xTuple to generate the labels and process the shipment in UPS. For this, go to the Import/Export menu option and choose “Keyed Import.” In that menu option, you will have to select which map to use. Please use “xTuple’s Shipment Import.” Once you have selected the map from the “more…” menu, next time it will appear automatically in the menu.
By clicking the map, this will show a field asking for a value as shown below:
Enter the xTuple’s shipment number on the screen and click CLOSE. The shipment data will be obtained from xTuple's shipment and imported into the WorldShip application, as shown on the following screenshot:
Now you can complete the needed data and process your shipment according to the WorldShip’s manual.
Remember that no data is available to export until the “End of day” process is run in WorldShip.
Export Data
Now that shipments have been created, labels have been printed and end of day has been run, the data is ready to be exported into xTuple. Open the Import/Export menu option and select the “Batch export” to show the screen shown below:
Choose the “xTuple Shipment Export” map and the date range you want to export. Click next and packages data will be inserted into the table “shipdata” in the public schema. Once the data is inside xTuple’s database, users can customize reports or screens to use the data available.
| Data Mapping Information | |||
| WorldShip Table | WorldShip Field | xTuple ERP Table | xTuple ERP Field |
| Package | Tracking Number | shipdata | shipdata_cosmisc_packnum_tracknum |
| Package | Package Type | shipdata | shipdata_package_type |
| Package | Reference 1 | shipdata | shipdata_cohead_number |
| Package | Reference 2 | shipdata | shipdata_shiphead_number |
| Package | Tracking Number | shipdata | shipdata_cosmisc_tracknum |
| Shipment Information | Void Indicator | shipdata | shipdata_void_ind |
| Shipment Information | Actual Weight | shipdata | shipdata_weight |
| Shipment Information | Total Shipment & Handling Charges | shipdata | shipdata_total_freight |
| Shipment Information | Service Type | shipdata | shipdata_billing_option |
| Shipment Information | Total Shipper Charge | shipdata | shipdata_base_freight |