3.4.4.� Topic: UPS WorldShip Integration

3.4.4.1. Basic Requirements
3.4.4.2. Process Flow

In this section we will provide an overview describing xTuple ERP's support for UPS WorldShip integration. UPS WorldShip is a Windows-based, shipping software application for UPS Customers who have high-volume shipping needs. When UPS WorldShip and xTuple ERP are integrated, you have the ability to export key UPS information—that is, Tracking Numbers and Freight charges—directly into xTuple ERP.

Note

For technical support using and/or configuring UPS WorldShip, please contact your local or area UPS WorldShip representative. Detailed WorldShip integration information can also be found on the UPS website at http://www.ups.com.

3.4.4.1.�Basic Requirements

The requirements for setting up UPS WorldShip to communicate with xTuple ERP include the following:

Install UPS WorldShip software on a networked workstation
Download and install ODBC driver for PostgreSQL

Note

The ODBC driver for PostgreSQL is needed so the UPS WorldShip software can export shipping information to an xTuple ERP Database. xTuple ERP Databases run on the PostgreSQL database server.

Configure UPS WorldShip to auto-export using the PostgreSQL ODBC driver
Create user-defined field for entering xTuple ERP Sales Order Numbers
Define export mapping to associate UPS WorldShip data fields with corresponding xTuple ERP data fields

UPS WorldShip supports both import and export maps. However, for integration with xTuple ERP, you only need an export map since the communication is one-way. The following screenshot shows a sample export map created using the UPS WorldShip mapping utility:

Export Map in UPS WorldShip

In our example, we have created a map called "export." The map type is "Shipment," which indicates the data export will occur when the shipment is shipped using UPS WorldShip. And the ODBC driver used to export the data to the xTuple ERP Database is called "openmfg."

The following screenshot shows the utility you would use to edit your export map within UPS WorldShip:

WorldShip Utility for Editing Export Map

Your primary goal when creating an export map is to match UPS WorldShip tables and fields with the corresponding xTuple ERP Database tables and fields. The following table provides you with the information you will need to successfully map the tables and fields from one system to the other.

Table�3.1.�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-5]	shipdata	shipdata_cohead_number
Shipment Information	Void Indicator	shipdata	shipdata_void_ind
Shipment Information	Actual Weight	shipdata	shipdata_weight
Shipment Information	Shipment ID	shipdata	shipdata_cosmisc_tracknum
Shipment Information	Total Shipment & Handling Charges	shipdata	shipdata_total_freight
Shipment Information	Billing Option	shipdata	shipdata_billing_option
Shipment Information	Total Shipper Charge	shipdata	shipdata_base_freight

Tip

By default, UPS WorldShip gives you a Reference field for miscellaneous purposes. Because this field may prove useful at some point, you should create a second Reference field for storing xTuple ERP Sales Order Numbers.

In addition to requiring specific table and field mappings, UPS WorldShip will also require you to provide the following export map information:

Map Name

Enter a name to describe your export map. In our example, we have give the export map the name "export."

Data Source Information

Specify the following Data Source information:

DSN Name: Enter a name to describe the Data Source Name (DSN) connection to xTuple ERP (e.g., "openmfg).
DSN Description: Enter a brief description of the DSN connection (e.g., "connection to openmfg").
ODBC Driver Name: Specify the name of the required ODBC driver. For xTuple ERP Databases, the driver is "PostgreSQL".
ODBC Driver: Indicate the path where the ODBC driver for PostgreSQL is located, as in the following: C:\\WINDOWS\\system32\\psqlodbc.dll.
Database Name: Enter the name of the xTuple ERP Database which will be receiving UPS WorldShip data (e.g., "production").
Database Path: Not required. This information is defined when configuring the ODBC driver for PostgreSQL.
Database Server: Not required. This information is defined when configuring the ODBC driver for PostgreSQL.

Map Information

Specify the following map information:

Map Type: The map type selection specifies the point at which the data export should occur. In this case, you should select the "Shipment" map type. Data export will occur when the shipment is shipped using UPS WorldShip.
Map Version: Specify version "12".
Primary Table: Not required.
Primary Key: Not required.
Package Name: Not required.
Prompt for Table Name: No.

3.4.4.2.�Process Flow

Once the integration requirements are completed, you are ready to begin processing and shipping Sales Orders. The following diagram illustrates the basic process flow:

Basic Process Flow for UPS WorldShip Integration

The process flow begins in xTuple ERP with the following sequence:

Enter Sales Order(s) for Customer(s)
Issue Stock to Shipping (optional)

Note

You can issue Stock to Shipping before or after you have shipped an Order in UPS WorldShip. The choice is up to you, based on your desired process flow.

After Stock has been issued to Shipping—but not yet shipped—the process flow moves to the UPS WorldShip system. Perform the following steps from your UPS workstation:

Enter Customer destination information
Enter xTuple ERP Sales Order Number(s) in the special Reference field created for this purpose
Weigh package(s) you will be shipping with UPS
Finish Order and ship it in UPS WorldShip

As soon as you ship the Order in WorldShip, the data will be exported to xTuple ERP via the ODBC connection. The following UPS information will now be stored in the xTuple ERP Database:

UPS Tracking Number
Actual Weight
Total Shipping and Handling Charges

The process flow returns now to xTuple ERP, where we either 1) issue Stock to Shipping or 2) ship the Order. For this example, we have already issued two Line Items to Shipping. We see these two Line Items on the Ship Order screen, as shown below:

Sales Order with UPS WorldShip Information

As you can see in int the example, our Order contains UPS Tracking Number information. And the total shipping and handling charges appear in the Freight field.

Note

The actual weight of the UPS package(s) is stored in the xTuple ERP Database, but is not visible using the application.

In this example, there are two distinct Tracking Numbers shown on the Ship Order screen. However, only one Tracking Number can be associated with each xTuple ERP shipment. The fact that there are two Tracking Numbers in this example indicates there are additional Line Items for this Sales Order which have been shipped in UPS WorldShip, but have not yet been issued to Shipping in xTuple ERP.

We are now ready to complete the final steps:

Select the correct Tracking Number
Ship the Order

The Tracking Number is now associated with the shipment. It will not appear on the list of Tracking Numbers when the remaining Line Items are shipped. In this way, users are prevented from accidentally using the same Tracking Number twice.

Tip

You can track UPS shipments from xTuple ERP using the S/R Module Display "Shipments by Sales Order." You will be connected directly to the UPS website when you select the "Query Shipments" option from the right-click menu.