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.


Create Transaction User Defined Fields in Control to store the UPS information

  1. UPS Tracking Number : Text Field, Edit Box, Not Required.
  2. UPS Package Weight : Number Field, Not Required.
  3. UPS Shipper Charges : Number Field, Not Required.
  4. UPS Total Charges : Number Field, Not Required.
  5. UPS Shipment Date : Text, Not Required.
  6. Restart Control (so the UDFs are actually created in the database).

Preparing Control SQL Database.

  1. Log into SSLIP.
  2. Open Database Browser and go to Advanced tab.
  3. Copy text into SQL window from ShipperInfoTable and Execute.

**Note:** Be sure to adjust the dbo.Store.ID = 1001 line to match the actual Store.ID for Control database as not all databases use 1001 as the main Store.ID.

  1. Copy text into SQL window from UPSShippingLogTable and Execute.
  2. Copy text into SQL window from CreateTableIndex and Execute.
  3. Copy text into SQL window from CreateTrigger and Execute.

Create a "Shipper" User in SQL with limited rights

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

Create an ODBC connection

  1. Open Control panel in Windows and go to Administrative Tools.
  2. Open Data Sources(ODBC).
  3. Click on System DSN tab.
  4. Click Add and select SQL Server entry from list.(Bottom of list)
  5. Name Data Source ControlSQL.
  6. Server value will be \
  7. Click Next.
  8. Choose “With SQL Server authentication…” option.
  9. Click “Client Configuration” button.
  10. Select TCP/IP and uncheck “Dynamically Determine Port” option.
  11. Set Port Number to 1433.
  12. Click OK.
  13. Login ID should be shipper.
  14. Password will be autoshipper.
  15. Click Next.
  16. Select “Change the default database to:” option to StoreData.
  17. Click Next and click Finish.
  18. Test the data source and verify that you are able to connect to the server.
  19. Click OK to close ODBC Administator.
  20. 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. Go to Import/Export Data menu and select Create / Edit Map….
  2. Under New Map, select Import from ODBC Database.
  3. Select Shipment from the drop down list.
  4. In the Name field, type ControlImport.
  5. Click Create button.
  6. Select ControlSQL from the ODBC source list.
  7. Input the user shipper with password autoshipper into the authentication boxes.
  8. Click OK.
  9. On left hand menu, scroll down and select ShipperInfo view. (Will be past the normal alphabetically ordered table names.)
  10. Select OrderNumber and click Define Primary Key for Import button.
  11. On right hand menu, select Ship To.
  12. 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
    • ContactFullName : Attention
    • ContactEmailAddress : Email Address
    • PhoneNumber : Telephone
    • StreetAddress1 : Address 1
    • StreetAddress2 : Address 2
    • City : City or Town
    • State : State/Province/County
    • PostalCode : Postal Code
    • Country : Country/Territory
  13. Click OK.
  14. Select Export data from WorldShip.
  15. Select Shipment from drop down menu.
  16. In the Name field, type ControlExport and click Create.
  17. Select ControlSQL from the ODBC Source list.
  18. Input the user shipper with password autoshipper into the authentication boxes.
  19. Click OK.
  20. On left hand menu, select Shipment Information.
  21. On right hand menu, select UPSShippingLog.
    • Collection Date : ShipDate
    • Total Shipment Published Charge : TotalCharge
    • Total Shipper Published Charge : ShipperCharge
  22. Change left hand menu to Ship To.
    • Customer ID : OrderNumber
  23. Change left hand menu to Package.
    • Weight : Weight
    • Tracking Number : TrackingNumber
  24. Click OK twice to exit Import/Export Mapping.
  25. Go to Import/Export Data
  26. Go to Automatic Export After Processing Shipment and click More…
  27. Select ControlExport and click Export.

Using UPS Worldship to process Control Orders

  1. Go to Import/Export Data.
  2. Go to Keyed Import and then select More…
  3. Select ControlImport and click Import. (This will open a dialogue box for you to type in the Order number you want to ship through UPS Worldship.)
  4. Type in the Order Number and hit . (This will populate the shipping address for that Order into UPS.)
  5. Process the shipment through UPS.
  6. Verify the UDFs of that Order have been updated with the various shipment information.
Changes
  • Adjusted SQL Trigger to include changes necessary to refresh cached records in Control 5.1 and later when using Order UDFs to integrate with UPS WorldShip.
You could leave a comment if you were logged in.