Freight and Shipping Tools
      Back to home
      1. EBMS Knowledge Base
      2. Sales
      3. Freight and Shipping Tools

      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 fieldsUPS export fieldsEBMS table

      ARSMSHIP

      		Shipment Information: Step 5  listed aboveShipment Information Step 13 listed aboveARSMSHIPINFO

      METHOD

      		Service TypeService TypeSRVC_TYPE

      ARSMSHIP

      		Ship To: Step 6Collection DateSHIP_DATE

      INVOICE

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

      Total Shipment and Handling Published...

      		COST
      C_ADDRESS1Address 1

      Lead Tracking Number

      		TRACK_NO
      C_ADDRESS2Address 2Ship To:   Step 15ARSMSHIPINFO
      C_CITYCity or town

      Tax ID

      		INVOICE
      C_STATEState/Province/CountyResidential IndicatorRESIDENTL
      C_ZIPPostal CodePackage:  Step 16ARSMSHIPINFO
      C_COUNTRYCountry/TerritoryVoid IndicatorVOIDED
      C_PHONETelephonePackage Published Charge   ^ ignore setting if Step 14 was chosenSHIP_COST
      C_EMAILEmail AddressTracking Number   ^ ignore setting if Step 14 was chosenTRACK_NO
      RESIDENTIALResidential IndicatorWeightWEIGHT
      ARSMSHIPPackage: Step 7Reference 3ID
      PO_NOReference 1

      C_IDReference 2

      NTF_OPTQVN Option

      C_EMAIL

      		QVN Failed Email Address

      NTF_MEMOQVN Memo

      INSURE_CHGDeclared Value Option

      INSURE_AMTDeclared Value Amount

      COD_OPTIONCOD Option

      COD_AMOUNTCOD Amount

      LIFT_GATE

      		not used

      SHIPPERnot used

      RECNO5not used

      ARSMSHIPDET: Step 8
      Defined as a foreign key      		Package

      ARINV_AIDSHIP_AID

      INSURE_CHGDeclared Value Option

      INSURE_AMTDeclared Value Amount

       

      Review Using EBMS Data within UPS WorldShip to test connection.