UPS WorldShip Integration (Standard Edition)

Contents of this Topic: ShowHide

Overview

• This topic outlines the steps required to integrate the Standard Edition of OMS with UPS WorldShip. Enterprise Edition users should consult the UPS WorldShip Integration (Enterprise Edition) topic for instructions.

• OMS easily integrates with UPS WorldShip via an ODBC connection abbreviation for Open Database Connectivity, which is the technology that allows applications to access data from a database, independent of the programming language of the application, or the operating system upon which it is run. For example, Monsoon OMS uses an ODBC Connection to share information with UPS WorldShip. and mapping the related fields in each application.

• Once those steps are completed, you can lookup the OMS order number in WorldShip and print the shipping label without re-typing the shipping information in WorldShip. At the end of the day, the tracking numbers can be sent back to OMS .

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.

3. Select to "left-click" the mouse on an object, usually a row in a table, in the context of Monsoon OMS documentation an entry from Profile Name and click the Reference tab.

4. Enter 0 in Reference Number 1.

5. Select Use on all Packages.

6. Click the Options tab and ensure Quantum View Notify is cleared.

7. Click Save Profile.

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

9. Click Close to return to the main WorldShip screen synonymous with the Microsoft Access Form object or graphical user interface (GUI).

Execute the SEOM UPS WorldShip Setup Function

The UPS WorldShip Setup function:

• assumes that WorldShip is installed on the same PC Personal Computer, or workstation as OMS and that WorldShip is a standalone installation

• searches for a local installation of UPS WorldShip

• creates an ODBC connection to the OMS data file on the local workstation

• transfers OMS 's WorldShipOrders query from the program file The MS Access database that contains the Monsoon OMS application code. This database does not contain the user's order information, which is housed in the Store Data File. The program file name begins with SEOrdMan* and has the extension of .mdb. (SEOrdMan*.mdb) to your store's data file

• this routine does not automatically create the Import or Export maps if WorldShip 9.0 or higher is installed, you must set these up manually

1. Go to Main Menu > Settings > Shipping Functions > UPS WorldShip Setup.  

2. The Enter name for System DSN Data Source Name - associates a specific SQL Server, driver, and database together in a connection string used to access the data. dialog box opens. Enter a name for the ODBC connection between WorldShip and your store's data file. The name cannot have spaces or punctuation in it. Make the name something simple like StoneEdgeDataFile. Note: Remember the name you entered in this step because you will need it later.

3. Click OK.

4. A message box saying Done! appears. Click OK.

5. Another message box stating the name of the ODBC connection appears.

6. Click OK.

7. If you receive the error below when pressing this button, please proceed to step 8.

8. This error is caused by the PC being 64-bit and OM is trying to create the ODBC as 64-bit. Instead, this needs to be created as a 32-bit ODBC manually.

9. a) Open the file path: C:\Windows\SysWOW64\odbcad32.exe 
   b) Select System DSN 
   c) Add.. 
   d) Microsoft Access Driver (*.mdb) > Finish 
   e) Enter a DSN name 
   f) Select... 
   g) Browse to your store database > Double-click the database > OK.

Create an Import Map in WorldShip

1. Launch WorldShip if it is not already open.

2. Go to Import/Export Data > Create/Edit Map Minimum Advertised Price; applies to Consumer Goods categories and listed on Amazon Seller Central Market.

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

4. Select Shipment from the drop-down list.

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

6. Click Create. The Select an ODBC source dialog box opens.

7. Select the ODBC connection (DSN) created when you ran the UPS WorldShip Setup function at the beginning of this topic.

8. Click OK.

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

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

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

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

13. The first field to map is OrderNumber. Before mapping it, you must define this field as the primary key. Select OrderNumber and click 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.

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

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

16. 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

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

18. Click Edit to map WorldShip service types to OMS shipping methods.
    
    

19. The Mapping Connections into Shipment Information window opens.

20. Click Translations.

21. In Select a common map, select Service.

22. Click OK. This loads the default service options into the External Field and WorldShip Field lists.

23. 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.

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

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

26. When you are finished renaming all your service types, click OK.  

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

28. Click 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. Click Create.

6. The Select an ODBC source screen opens.

7. Select the ODBC connection (DSN) created when you ran the UPS WorldShip Setup function at the beginning of this topic.

8. Click OK.

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

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

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

9. Click 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.

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

UPS WorldShip Export Map

*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.

11. Click OK twice to return to the main WorldShip screen.

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

13. 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

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

15. Click 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 option. 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 click 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

• Error Message: "Primary Key cannot contain a null value" when exporting tracking information.

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.)

• Tracking numbers form is not visible on Process Orders screen: If the Reference 1 field contains text-based information (alpha characters), the tracking number is returned but the tracking numbers form does not appear at the Process Orders screen when you try to view the tracking numbers. This is due to a conversion error when changing the text-based order number field to a numeric value.

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. Click on 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.

• If the following error message is encountered, it is mostly likely caused by the fact that the OMS store data file the MS Access or SQL database that contains the users order information and program preferences. It does not contain Reports, which are housed in the Monsoon OMS program file (MS Access database). is an SQL database, which requires additional setup steps besides those outlined in this topic. Refer to UPS WorldShip Integration (Enterprise Edition).

Additional Information

UPS WorldShip Shipping Method Translations

UPS WorldShip Integration (Enterprise Edition)

Created:

Revised: 10/12/12

Published: 04/14/16