UPS WorldShip Integration (Enterprise Edition)

Contents of this Topic: Show

Overview

Create an Intermediary MS Access Database to Link SEOM Tables with WorldShip

  1. Create a blank MS Access database. Name it "WorldShip.mdb" and save it in your StoneEdge directory.

  2. Go to File>Get External Data>Link Tables.

  3. At the bottom of the Link window, select ODBC Databases() from Files of type drop-down list.

  1. Select the Machine Data Source tab on the Select Data Source screen.

    1. If you are using SQL Authentication, you will also be prompted to encrypt your password.

    2. Select Save Password.

  1. Select the Data Source Name that relates to the OMS SQL data file.

  2. Select OK.

  3. When the Link Tables dialog opens, hold the Ctrl or Shift button and select dbo.Orders and dbo.ShippingMethods.

  4. Select OK to see the tables listed in the new intermediary database window.

  1. From the File drop-down menu, select Get External Data> Import. The Import window opens.
  2. Navigate to the OMS installation directory (usually in C:\StoneEdge) and select the program file, typically named SEOrdManEnt200*.mdb.

  1. Select Import.

  1. Select the Queries tab and select WorldShipOrdersSQL.
  2. Select OK. The WorldShipOrdersSQL now appears in the Queries section of the WorldShip.mdb.

  1. Now, create a new System DSN for the WorldShip.mdb by going to Control Panel > Administrative Tools > Data Sources.

  1. Select on the System DSN tab and select Add.

  1. Select Microsoft Access Driver (*.mdb) and select Finish.

  1. Enter a name for the DSN in Data Source Name.

  2. Select Select.

  1. Navigate to the location of WorldShip.mdb and select OK.

  2. Select OK in the ODBC Microsoft Access Setup screen. The DSN name appears in your ODBC Data Source Administrator window.

Configure a UPS WorldShip Profile

WorldShip profiles allows you to set default values to speed shipment processing. To avoid errors sending tracking information back to OMS , it is necessary to set some default values in your WorldShip profile or profiles. Refer to UPS WorldShip Help or Support for assistance beyond the scope of this topic.

  1. Open UPS WorldShip.

  2. Go to Tools > Profile Editor.

  1. Select an entry from Profile Name and select the Reference tab.

  2. Enter 0 in Reference Number 1.

  3. Select Use on all Packages.

  4. Select the Options tab and ensure Quantum View Notify is cleared.

  5. Select Save Profile.

  6. Repeat this process for any other profiles you may use.

  7. Select Close to return to the main WorldShip screen.

Create an Import Map in WorldShip

  1. Launch WorldShip if it is not already open.

  2. Go to Import/Export Data > Create/Edit Map.

  1. In the New Map section, select Import from ODBC Database.

  1. Select Shipment from the drop-down list.

  2. Enter a name for the map in the Name field. (Choose any name, such as WorldShip Import Map.)

  3. Select Create. The Select an ODBC source dialog box opens.

  1. Select the ODBC connection (DSN) for the WorldShip.mdb created at the beginning of this topic.

  2. Select OK.

  3. The Edit 'YOUR MAP NAME' import map screen opens.

  4. In ODBC Tables, select WorldShipOrders. A list of fields in the table appears below the drop-down list.

  5. In WorldShip Fields, select Package. A list of fields in that category appear below the drop-down list.

  6. Now the process of mapping fields from the WorldShipOrders table in OMS (left side) to corresponding WorldShip Package fields (right side) can begin.

  1. The first field to map is OrderNumber. Before mapping it, you must define this field as the primary key. Select OrderNumber and select Define Primary Key for Import. This sets the OMS order number as the primary identifier for the order record. A small blue key appears next to the OrderNumber field.

  2. While OrderNumber is still selected in the left, select Reference 1 from the list on the right and select Connect. This maps the OMS field, "OrderNumber" to the "Reference 1" field in the "Package" category in WorldShip.

  3. A small red infinity symbol appears next to the fields indicating that they are now linked, and that association is also displayed in What You Have Mapped So Far:.

  4. Continue mapping all of the fields listed in the table below in the same manner. (For each map pairing, be sure to select the correct category from the WorldShip Fields drop-down list - the same field name may exist in more than one category).

UPS WorldShip Field Map

OMS Field

WorldShip Category

WorldShip Field

OrderNumber

Package

Reference1

ActualShippedWeight or UPSWeight

Published weight is used if actual weight is not present.

Package

Weight

SourceOrderNumber and
SourceOrderID (Web Order Number)

Package

Reference2

Email

Shipment Information

Notification Recipient 1Email

Shipping

Shipment Information

Service Type

Cancelled

Shipment Information

Void Indicator

UPSNotification1

Shipment Information

QVN Ship Notification or

Return Notification 1 Option

UPSNotification1

Shipment Information

QVN or Return Notification Option

CustomerID

Ship To

CustomerID

Email

Ship To

Email Address

Residential

Ship To

Residential Indicator

UPSCompanyOrName

Ship To

Company Or Name

UPSAttention

Ship To

Attention

UPSAddr1

Ship To

Address 1

UPSAddr2

Ship To

Address 2

UPSCity

Ship To

City or Town

UPSState

Ship To

State/Province/County

UPSZip

Ship To

Postal Code

UPSCountry

Ship To

Country/Territory

UPSPhone

Ship To

Telephone

 

  1. When you have finished mapping all of the fields, select WorldShipOrders:Shipping in the lower window, What You Have Mapped So Far.

  2. Select Edit to map WorldShip service types to OMS shipping methods.

  1. The Mapping Connections into Shipment Information window opens.

  2. Select Translations.

  3. In Select a common map, select Service.

  4. Select OK. This loads the default service options into the External Field and WorldShip Field lists.

  5. The External Field list represents the OMS shipping method names for corresponding UPS services listed in the WorldShip Field list. In this example, 1DA is the OMS default name for WorldShip Next Day Air.

  6. Select a service pairing from the list. In External Field (yellow background), enter the OMS shipping method name for that UPS service type.

  7. Select Rename to save each service type with the new name. (If you make a mistake, select Translations, and then Service, OK to re-load the default service option lists again.)

  8. When you are finished renaming all your service types, select OK.  

  1. When all of the translations are completed, select OK. Your view returns to the Create/Edit Map window.

  2. Select OK.

Creating an Export Map in UPS WorldShip

  1. Go to Import/Export Data > Create/Edit Map.

  2. In the New Map section, select Export Data from WorldShip.

  3. Select Shipment from the drop-down list.

  4. Enter a name for the map in the Name field (for example, StoneEdge Export Map).

  5. Select Create.

  6. The Select an ODBC source screen opens.

  7. Select the ODBC connection (DSN) that points to the OMS SQL store file. (This was created when you converted the original OMS MS Access store data file to SQL.)

  8. Select OK.

  9. The Edit 'YOUR MAP NAME' export map screen opens.  

  1. Select Tracking from ODBC Tables, and then choose OrderNum from the list below the drop-down.

  2. Select Package from WorldShip Fields, and then select Reference 1 from the list below the drop-down.

  3. Select Connect to link the fields. A small red infinity symbol appears next to the fields indicating that they are now linked. You can then see the linked fields in the What You Have Mapped So Far list at the bottom of the screen.

  4. Repeat steps 7 through 9 until all the required fields in the table shown below have been mapped.

UPS WorldShip Export Map

WorldShip Category

WorldShip Field

OMS Field

Package

Reference 1

OrderNum

Package

Tracking Number

TrackingID

Shipment Information

CollectionDate

PickupDate

Package

Void Indicator

IsVoid

Map only one of the following fields to the Cost field:

 

 

Package

 

Package Charge

Cost*

OR

 

 

Shipment Information

Total Shipment and Handling Published Charges (DEFAULT)

If your business uses Negotiated Rates or Cost Center Calculator, use Total Shipment and Handling Negotiated Rates Charge or Total Shipment and Handling CCC Rates Charge

Cost*

 

*There are many fields in Worldship that contain cost information and the user should determine which is best for their environment. Some of our customers have had better results using the Package Charge field rather than the Total Shipping and Handling fields.

  1. Select OK twice to return to the main WorldShip screen.

  2. Go to the UPS Import/Export Data menu.

  3. Choose ONE of the following selections:

    1. Automatic Export at End of Day to send tracking numbers to OMS once, at the end of the day

    2. Automatic Export After Process Shipment to send tracking numbers to OMS after processing each shipment

  4. After making your Automatic Export selection, select More. Select the export map that was just created in Select map from list for export.

  5. Select Export. When you return to this menu your export map is displayed with a check mark beside it, indicating that this export map is being used.

Processing Orders in WorldShip

  1. Launch WorldShip if it is not already open.

  2. Go to Import/Export Data.

  3. Select Keyed Import.

  4. Select the name of the Import Map created in a previous step in this topic. Note: If the name does not appear on the menu, select More. The name should appear in the list, but if it does not, you will need to create the import map manually. This is common occurrence for network setups of UPS, or if WorldShip was installed in a path other than the default.

  5. The Enter Import Key window opens. Enter an "approved" OMS order number.

  6. Press Enter (do not select the Close button). The shipping information from the order appears in the WorldShip form. Process the order as you normally would.

  7. When the shipping label for that order is printed, enter a new order number in the Enter Import Key dialog box to process another order.

Note: If you return to OMS and make changes to an order (such as approving an order), you will not see the changes in WorldShip until you close and re-open the Enter Import Key dialog box.

Common Errors

WorldShip imports the OMS order number into its Reference 1 field. When WorldShip generates a tracking number, it exports the tracking number and the order number in Reference 1 field back to OMS . If the Reference1 field is empty, WorldShip can't return tracking numbers to OMS .

Possible causes:

Processing orders non-OMS orders through WorldShip: If you process any orders in WorldShip that were not imported from the Order Manager, the Reference 1 field is left blank. When you close the batch in WorldShip, the empty Reference 1 field (NULL) causes the ODBC to generate the Primary Key cannot contain a null value error that prevents it from returning tracking numbers to OMS .

An order has multiple package shipments: If an order has multiple package shipments, OMS 's order number is only associated with the first tracking number for the order. When you close the batch in WorldShip, the ODBC only returns one tracking number because only the first shipment in the order has a  value (order number) in the Reference 1 field. Subsequent shipments for the same order have an empty Reference 1 field, causing the Primary Key cannot contain a null value error, preventing tracking numbers from being sent to OMS .

Solution: To avoid this error, the Reference 1 field must contain a numeric value such as 0, so that the tracking number can be returned. You can manually type a numeric value into the Reference 1 field for each order OR set up a Shipper Profile in WorldShip that pre-populates the Reference 1 field with a numeric value. (Note: WorldShip Profile Editor allows you to set up default values for many of the fields in WorldShip when a new order is added.) Using the WorldShip Profile Editor, set up a new profile, add "0" (or any default numeric value) to the Reference 1 field, and select "Use on all packages" option for Reference 1. When processing orders, select the profile with these settings before you begin. (Note: The tracking number is returned to OMS without an associated order number.)

Solution: Go to the Main Menu in OMS and press F11 to open the database window. Click on Tables and double-click the Tracking table. Look through the OrderNum fields for any values that contain text or non-numeric characters (such as dashes) and remove those records from the table. Close the Tracking table. Select the Window menu and choose Hide to remove the database window from the screen. Return to the Process Orders form. Tracking numbers should now be visible.

Additional Information

UPS WorldShip Shipping Method Translations

UPS WorldShip Integration (Standard Edition)

Created:

Revised: 10/10/14

Published: 04/14/16