{$page}

Cyrious Control integrates with UPS Worldship so that shipping address information may be automatically retrieved and tracking and cost information can be fed back into Control during the closeout process.

Setting up the Server to work with Worldship

Before starting this setup, please verify that the customer has the shipping tab module. If they do not have the shipping module they must purchase this first and you can direct the customer to sales@cyrious.com for pricing .

Preparing Control SQL Database

Database Object Name Type
UPSShippingLogForShipments Table
UpdateUPSShipment Trigger
ShipperInfoForShipments View

Create a “Shipper” User in SQL with limited rights

  1. Run SQL Server's Management Studio application and log into SQL Server.
  1. Expand Security folder then right-click Logins and click New Login.
  1. Login Name will be shipper.
  1. Select SQL Server Authentication.
  1. Password will be autoshipper.
  1. Uncheck “Enforce Password Policy”.
  1. Select “Server Roles” on left hand list.
  1. Select all checkboxes on right side.
  1. Click “User Mapping” on the left side.
  1. Select the StoreData database to associate this login with this database.
  1. Click OK and close Management Studio.

Setting up the client (Shipper's) machine

Create an ODBC connection.

  1. Open Control panel in Windows and go to Administrative Tools.
  1. Open Data Sources(ODBC).
  1. Click on System DSN tab.
  1. Click Add and select SQL Server entry from list.(Bottom of list)
  1. Name Data Source ControlSQL. (The .dat files below will not work with any other name)
  1. Server value will be \
  1. Click Next.
  1. Choose “With SQL Server authentication…” option.
  1. Click “Client Configuration” button.
  1. Select TCP/IP and uncheck “Dynamically Determine Port” option.
  1. Set Port Number to 1433.
  1. Click OK.
  1. Login ID should be shipper.
  1. Password will be autoshipper.
  1. Click Next.
  1. Select “Change the default database to:” option to StoreData.
  1. Click Next and click Finish.
  1. Test the data source and verify that you are able to connect to the server.
  1. Click OK to close ODBC Administator.
  1. NOTE: If you are using Windows x64 edition then the ODBC connection you created will likely not appear in UPS. This is because UPS Worldship uses a 32-bit ODBC connection and by default when you create an ODBC connection in 64-bit windows it is only available to 64-bit applications. If you encounter this problem you need to create a 32-bit ODBC connection by going to C:\Windows\SysWOW64\odbcad32.EXE. Create your connection under this ODBC configuration utility and then it will be available for use within UPS.

Setup UPS Worldship software for Integration.

  1. Download and save ControlExportForShipTab.dat and ControlImportForShipTab.dat into the UPS WorldShip Shipment folder. If UPS WorldShip was installed on the root level of the C drive the path may look like this: C:\UPS\WSTD\ImpExp\Shipment or C:\UPS\Client Share\UPS\WSTD\ImpExp\Shipment. Note: If this is an Admin workstation, the \UPS\WSTD\ImpExp folder is found in the Admin Shared folder, so you may have to look in the users directory under the Admin account folder and you will see the UPS folder there refer to UPS link at bottom of this page for more information!
  1. Note: If the Control Import or Export options are not appearing in the Import/Export > Automatic Import/Export… menu items, the problem may be caused by UPS looking for the map files in a path other than the default. The way to find the location the files need to be in is to manually create a map file in UPS and then search the PC for that specific filename to find where that file was created.\

### Another cause for the files to not show up might be if the ODBC is not present or not correct. Verify that it is correct if the profiles are not appearing.

  1. Open UPS WorldShip, go to the Import/Export Data menu.
  1. Go to Automatic Export After Processing Shipment and click More…
  1. Select ControlExportForShipTab and click Export.
  1. Or set up the mappings manually following these steps:
  1. Go to Import/Export Data menu and select Create / Edit Map….
  1. Under New Map, select Import from ODBC Database.
  1. Select Shipment from the drop down list.
  1. In the Name field, type ControlImportForShipTab.
  1. Click Create button.
  1. Select ControlSQLForShipTab from the ODBC source list.
  1. Input the user shipper with password autoshipper into the authentication boxes.
  1. Click OK.
  1. On left hand menu, scroll down and select ShipperInfoForShipments view. (Will be past the normal alphabetically ordered table names.)
  1. Select OrderNumber and click Define Primary Key for Import button.
  1. On right hand menu, select Ship To.
  1. Below is a chart for linking the table names: (after each association and later associations, you will click the “Connect” button.)
    • LEFT SIDE : RIGHT SIDE
    • OrderNumber : CustomerID
    • CompanyName : Company or Name
    • FullName : Attention
    • Email : Email Address
    • Phone : Telephone
    • StreetAddress1 : Address 1
    • StreetAddress2 : Address 2
    • City : City or Town
    • State : State/Province/County
    • PostalCode : Postal Code
    • Country : Country/Territory
  1. On right hand menu, select Package
    • UseInsuredValue : DeclareValueOption
    • InsuredValue : DeclaredValueAmount
  1. Click OK.
  1. Select Export data from WorldShip.
  1. Select Shipment from drop down menu.
  1. In the Name field, type ControlExportForShipTab and click Create.
  1. Select ControlSQL from the ODBC Source list.
  1. Input the user shipper with password autoshipper into the authentication boxes.
  1. Click OK.
  1. On left hand menu, select Shipment Information.
  1. On right hand menu, select UPSShippingLogForShipments.
    • Collection Date : ShipDate
    • Total Shipment Published Charge : TotalCharge
    • Total Shipper Published Charge : ShipperCharge
Note: For customers that ship multiple packages per Shipment, it may work better to change the map to the Package.PublishedCharge : ShipperCharge instead
  1. Change left hand menu to Ship To.
    • Customer ID : OrderNumber
  1. Change left hand menu to Package.
    • Weight : Weight
    • Tracking Number : TrackingNumber
  1. Click OK twice to exit Import/Export Mapping.
  1. Open UPS WorldShip, go to the Import/Export Data menu.
  1. Go to Automatic Export After Processing Shipment and click More…
  1. Select ControlExportForShipTab and click Export.

- Some of the more recent versions of UPS Worldship will export null values back to the UPSShippingLogForShipments. If this occurs, manually create entries using the following mappings:

On Import

- Bind ShipperInfoForShipments.OrderNumber ShipmentInformation.Reference2

On Export

- Bind Package.Reference2 UPSShippingLogForShipments.OrderNumber

If you are still having issues with exporting info back to Control after shipment is processed, check the UPSShippingLogForShipments table in SQL. Expand that table, then expand Columns. Right-click on Order Number and select Modify. Change Data Type for Order Number to nvarchar(50).

Using UPS Worldship to process Control Orders

  1. Go to Import/Export Data.
  1. Go to Keyed Import and then select More…
  1. Select ControlImportForShipTab and click Import. (This will open a dialogue box for you to type in the Order number you want to ship through UPS Worldship.)
  1. Type in the Shipment Number (ex: 1234-SAA) and hit . (This will populate the shipping address for that Order Shipment into UPS.)
  1. Process the shipment through UPS.
  1. Verify the Shipment Item on the Shipping Tab of that Order have been updated with the various shipment information. ***Note: If the order was open during the UPS processing, it may not reflect the change and closing and reopening it will force it to refresh.

Links

UPS pdf with more information on page 1 that may be of assistance UPS Worldship PDF