Interfacing UPS WorldShip with EBMS

The EBMS Ship Manager module allows the importing and exporting of address and tracking information between UPS WorldShip & EBMS.  UPS WorldShip is a shipping Software provided by the United Parcel Service to weigh packages and print shipping labels before shipping.

The EBMS software can push the shipping preferences selected within the EBMS order entry system.  Shipping details such as the customer name, email address, destination address, insurance options, etc. are pushed directly to UPS.      Shipping details such as tracking information can be communicated from UPS WorldShip back to EBMS.   Shipment and Insurance costs returned from UPS WorldShip can then be applied to the Invoice total.

The configuration the UPS WorldShip interface is a technical process that should be handled by the company IT staff or other technical individuals.  This section includes multiple steps.   Please complete all steps before using the WorldShip interface with EBMS.   

Complete each of the following steps to interface EBMS to WorldShip:

  1. Network the UPS WorldShip shipping computer to allow access to the EBMS data.

  2. Add UPS to the shipper list.  Review Setup Shippers for instructions.

  3. Configure an ODBC driver on the shipping computer.   Review Configurating ODBC Database Drivers for instructions.

  4. Configure the communication to pull shipping information from the EBMS software:  Complete the following steps to launch the UPS WorldShip Connection Assistant to map communication to import shipping information from EBMS:

    1. Launch the UPS World Ship software and Select Import-Export > Tools button > Connection Assistant as shown below:

    2. Select Create a New Map for Import option as shown above and click Next.  

    3. Select the Import Data Type option Shipment and click Next to open the following wizard page:

    4. Select the EBMSUPS ODBC Sources as shown above.   Review the Configurating ODBC Database Drivers if the EBMS ODBC driver source does not appear.

    5. Set the EBMS user name as the ID and Password that is used to access EBMS data.   The user name and password should be set with the same credentials as the ODBC driver.   The user name must be prefixed with the 3 character EBMS Company ID found in File > Company Information > Advanced tab > Company ID.   In the User Name example shown above, the QUA represents the Company ID and the UPS text is the user name.  Review the [Main] Getting Started > Security > Creating Users and Assigning Rights for more details on creating users and setting passwords.

    6. Click Next to open the next page as shown below:

    7. Select New Map and enter EBMSUPS into New Map Name entry as shown above and click Next.

    8. Click Finish to complete the wizard and open the following import map dialog.

  5. Complete the following steps to connect the WorldShip Shipment Information field:

    1. Select the ARSMSHIP  from the ODBC Tables drop down and the WorldShip Fields drop down to Shipment Information as shown above:

    2. Click on the Method field from the ARSMSHIP fields on the left column and click Service Type on the right column

    3. Click the Connect button to associate the EBMS field to the WorldShip field.

  6. Complete the following steps to connect the WorldShip Ship To fields:

    1. Change the WorldShip Fields drop down setting to Ship To and keep the ODBC Tables set to ARSMSHIP.  

    2. Select ODBC > Invoice field in the left column and the WorldShip Fields > Tax_ID and clicking the Connect button.

    3. Connect each EBMS ODBC field with the corresponding WorldShip > Ship To field described within  import field mapping table at the end of this section.  

  7. Connect each EBMS ODBC field within the ARSMSHIP table with the corresponding WorldShip > Package field as listed in the mapping table at the end of this section.

  8. Connect each EBMS ODBC field within the ARSMSHIPDET table with the corresponding WorldShip > Package field as listed in the mapping table at the end of this section.

  9. Define Primary Key for Import but selecting the Invoice Column Name within the EBMS ODBC Table ARSMSHIP and clicking the Define Primary Key for Import button as shown below:

  10. Associate child EBMS files to ARSMSHIP tables by completing the following steps:

    1. Click on the Define Foreign Key Relationships button to open the following dialogs:

    2. Since the main EBMS shipment export table is ARSMSHIP, select ARSMSHIP within the Relate Frompane and Relate To: file of ARSMSHIPDET as shown above.

    3. Select the main key field ARSMSHIP > AUTOID in the left pane and relate to the ARSMSHIPDET > SHIP_AID field in the right pane and click Add Relationship button.

    4. Click OK to save.

  11. Complete the following steps to launch the UPS WorldShip Connection Assistant to map communication to export tracking and billing information to EBMS:

    1. Launch the UPS WorldShip software as done in step 3A:

    2.  Select Import-Export > Tools button > Connection Assistant from the WorldShip menu to launch the following wizard:

    3. Select Create a new map for Export option as shown above and click Next.

    4. Select Export Data Types option to Shipment as shown above and click Next.

    5. Select the EBMSUPS ODBC Sources as shown above.   Review the Configurating ODBC Database Drivers if the EBMS ODBC driver source does not appear.

    6. Set the EBMS user name as the ID and Password that is used to access EBMS data.   Use the same user name and password as set in step 3E as described above.  Click Next to continue to the next page as shown below:

    7. Select the New Map option and enter a New Map Name such as UPSEBMS since the data will be exported to EBMS.  Click Next.

    8. Click Finish to complete the wizard and open the following export map dialog.

  12. Select the WorldShip Fields drop down setting of Shipment Information and select ARSMSHIPINFO  from the ODBC Tables drop down.   

    1. Map each WorldShip field to the corresponding EBMS field as shown above.   Utilize the export field mapping table below to identify the appropriate field association.  

  13. The following steps are needed to configure group multiple packages within UPS WorldShip.     The step is done to group multiple packages under a common tracking number.   This option will allow the user to attach multiple packages including WorldShip calculated handling fees to a single EBMS invoice.  This optional may not be important if the cost is calculated within the EBMS sales invoice rather than calculated by WorldShip.    Set the following additional WorldShip Fields for Shipment Information settings to group multiple packages:    

    1. Copy the Shipment Information > Total Shipment and Handling Published... within WorldShip to the ARSMSHIPINFO > SHIP_COST field instead of copying Package > Package Published Charge to ARSMSHIPINFO > Cost as described in step #16.

    2. Copy the Shipment Information > Lead Tracking Number within WorldShip to the ARSMSHIPINFO > TRACK_NO instead of copying Package > Tracking Number as described in step #16.   See the details within the export field mapping table at the end of this section.

  14. Select the WorldShip Fields drop down option of Ship To and map each WorldShip field to the corresponding ARSMSHIPINFO field as listed in the export field mapping table below:

  15. Select the WorldShip Fields drop down option of Package and map each WorldShip field to the corresponding ARSMSHIPINFO field as listed in the export field mapping table below:  Click OK to save

  16. Complete the following steps to setup automatic exports:  This automatic process is important to push information back to EBMS after WorldShip as processed the shipment.

    1. Select Import-Export Data > Automatic Export After Shipment Processing from the main WorldShip menu as shown below:

    2. Select the More... option from the submenu to select the EBMS export table as shown below:

    3. Select the EBMSUPS ODBC DSN file as shown above.   Review the Configurating ODBC Database Drivers if the EBMS ODBC  source does not appear.

    4. Click Export to select the EBMS file to export automaticly.   The EBMSUPS file will appear on the menu as shown above.

  17. Test the connection to EBMS from UPS WorldShip.   Review Using EBMS Data within UPS WorldShip to test connection.

  18. Use the folowing table to edit the field mapping relationship between EBMS and UPS WorldShip if any information is not being copied between EBMS and UPS.

    1. Import map will read from the EBMS ARSMSHIP & ARSHIPDET Tables via the ODBC Connection and import into WorldShip

    2. Export map will copy data to the EBMS ARSMSHIPINFO table using the WorldSSelect the ARSMSHIP  from the ODBC Tables drop down.   

EBMS table

UPS import fields UPS export fields EBMS table

ARSMSHIP

Shipment Information: Step 5  listed above Shipment Information
Step 13 listed above
ARSMSHIPINFO

METHOD

Service Type Service Type SRVC_TYPE

ARSMSHIP

Ship To: Step 6 Collection Date SHIP_DATE

INVOICE

Tax_ID (needed for write back) Package:  Step 14
Alternative fields when grouping multiple packages in WorldShip ^
ARSMSHIPINFO
C_NAME Company or name

Total Shipment and Handling Published...

COST
C_ADDRESS1 Address 1

Lead Tracking Number

TRACK_NO
C_ADDRESS2 Address 2 Ship To:   Step 15 ARSMSHIPINFO
C_CITY City or town

Tax ID

INVOICE
C_STATE State/Province/County Residential Indicator RESIDENTL
C_ZIP Postal Code Package:  Step 16 ARSMSHIPINFO
C_COUNTRY Country/Territory Void Indicator VOIDED
C_PHONE Telephone Package Published Charge
  ^ ignore setting if Step 14 was chosen
SHIP_COST
C_EMAIL Email Address Tracking Number
  ^ ignore setting if Step 14 was chosen
TRACK_NO
RESIDENTIAL Residential Indicator Weight WEIGHT
ARSMSHIP Package: Step 7 Reference 3 ID
PO_NO Reference 1    
C_ID Reference 2    
NTF_OPT QVN Option    

C_EMAIL

QVN Failed Email Address    
NTF_MEMO QVN Memo    
INSURE_CHG Declared Value Option    
INSURE_AMT Declared Value Amount    
COD_OPTION COD Option    
COD_AMOUNT COD Amount    

LIFT_GATE

not used    
SHIPPER not used    
RECNO5 not used    
ARSMSHIPDET: Step 8
Defined as a foreign key
Package    
ARINV_AID SHIP_AID    
INSURE_CHG Declared Value Option    
INSURE_AMT Declared Value Amount    

Review Using EBMS Data within UPS WorldShip to test connection.