====== ====== 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 ===== ==== Create Transaction User Defined Fields in Control to store the UPS information ==== - UPS Tracking Number : Text Field, Edit Box, Not Required. - UPS Package Weight : Number Field, Not Required. - UPS Shipper Charges : Number Field, Not Required. - UPS Total Charges : Number Field, Not Required. - UPS Shipment Date : Text, Not Required. - Restart Control (so the UDFs are actually created in the database). ==== Preparing Control SQL Database. ==== - Log into SSLIP. - Open Database Browser and go to Advanced tab. - Copy text into SQL window from {{ :createshipperinfotable.txt|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.
- Copy text into SQL window from {{ :createupsshippinglogtable.txt|UPSShippingLogTable}} and Execute. - Copy text into SQL window from {{ :createindexontable.txt|CreateTableIndex}} and Execute. - Copy text into SQL window from {{ :createtriggerontable.txt|CreateTrigger}} and Execute. ==== Create a "Shipper" User in SQL with limited rights ==== - Run SQL Server's Management Studio application and log into SQL Server. - Expand Security folder then right-click Logins and click New Login. - Login Name will be //shipper//. - Select SQL Server Authentication. - Password will be //autoshipper//. - Uncheck "Enforce Password Policy". - Select "Server Roles" on left hand list. - Select all checkboxes on right side. - Click "User Mapping" on the left side. - Select the StoreData database to associate this login with this database. - Click OK and close Management Studio. ===== Setting up the client (Shipper's) machine ===== ===Create an ODBC connection=== - Open Control panel in Windows and go to Administrative Tools. - Open Data Sources(ODBC). - Click on System DSN tab. - Click Add and select SQL Server entry from list.(Bottom of list) - Name Data Source ControlSQL. - Server value will be \ - Click Next. - Choose "With SQL Server authentication..." option. - Click "Client Configuration" button. - Select TCP/IP and uncheck "Dynamically Determine Port" option. - Set Port Number to 1433. - Click OK. - Login ID should be //shipper//. - Password will be //autoshipper//. - Click Next. - Select "Change the default database to:" option to StoreData. - Click Next and click Finish. - Test the data source and verify that you are able to connect to the server. - Click OK to close ODBC Administator. - **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.=== - Go to Import/Export Data menu and select Create / Edit Map.... - Under New Map, select Import from ODBC Database. - Select Shipment from the drop down list. - In the Name field, type ControlImport. - Click Create button. - Select ControlSQL from the ODBC source list. - Input the user shipper with password autoshipper into the authentication boxes. - Click OK. - On left hand menu, scroll down and select ShipperInfo view. (Will be past the normal alphabetically ordered table names.) - Select OrderNumber and click Define Primary Key for Import button. - On right hand menu, select Ship To. - 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 - Click OK. - Select Export data from WorldShip. - Select Shipment from drop down menu. - In the Name field, type ControlExport and click Create. - Select ControlSQL from the ODBC Source list. - Input the user shipper with password autoshipper into the authentication boxes. - Click OK. - On left hand menu, select Shipment Information. - On right hand menu, select UPSShippingLog. * Collection Date : ShipDate * Total Shipment Published Charge : TotalCharge * Total Shipper Published Charge : ShipperCharge - Change left hand menu to Ship To. * Customer ID : OrderNumber - Change left hand menu to Package. * Weight : Weight * Tracking Number : TrackingNumber - Click OK twice to exit Import/Export Mapping. - Go to Import/Export Data - Go to Automatic Export After Processing Shipment and click More... - Select ControlExport and click Export. ===Using UPS Worldship to process Control Orders=== - Go to Import/Export Data. - Go to Keyed Import and then select More... - 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.) - Type in the Order Number and hit . (This will populate the shipping address for that Order into UPS.) - Process the shipment through UPS. - 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.