Procedure for using UPS Worldship with MAS 90 or MAS 200 Version 4

Sage Customer Support Services does not support UPS Worldship with MAS 90 or MAS 200 but the procedure below can be used to create ODBC connections to allow Worldship to “see” MAS 90/200 data. The examples below will involve creating a new shipment map in Worldship using 2 Data Source Names (DSNs).

Starting with Version 4.00, MAS 90/200 modules such as General Ledger, Library Master, Visual Integrator, Custom Office, and Development Studio are considered to be “Version 4 standard” modules while the others are considered to be “legacy” modules. In Version 4.10, Accounts Receivable, Sales Order Processing, RMA, and eBusiness Manager modules will also be coded at the Version 4 standard.

All the Version 4 standard modules useonly Crystal Reports as the reporting engine. Whenever a report from one of these modules is printed/previewed, a Crystal work table is created. All of these work tables are exposed in the MAS 90/200 dictionary. However, the physical file behind the work table often will not exist beforehand until the report for the work table is printed/previewed. When using software products that use ODBC connections, such as Crystal Reports, Business Alerts, Microsoft Access, SQL Data Transformation Services (DTS), any type of ODBC querying tool, etcetera, they will send a single command to request a list of tables available from the dictionary. At that point you can select the tables you need in your report/query/script.

When creating an ODBC connection with Worldship however, instead of requesting an initial list of tables, it will instead open and close an ODBC connection on each and every table in the dictionary (including the pre-defined views). Once completed it will present the list of tables and columns to the user. This creates 2 problems with MAS 90/200 ODBC connections. First, since many of the MAS 90/200 work tables do not exist until the report associated with it is run, the error “Physical File Not Found” will occur. Second, because separate ODBC connections are opened and closed for each table regardless if the table is actually used in the shipment map, there will be a 4 – 10 minute delay before Worldship displays the available list of tables and columns. This means in Worldship, when entering the Sales Order Number for the Keyed Import value, a 4 – 10 delay will occur. This delay is due to the Worldship architecture. You must circumvent this problem by using a 3rd party database application as an intermediate application.

The example below will use a scheme involving creating a query in Microsoft Access to retrieve all the columns in the SO1_SOEntryHeader table from MAS 90/200. Then a new shipment map will be used to run the Access query. This scheme will avoid “Physical File Not Found” errors and the long delay.

There are 3steps to allow Worldship to work with MAS 90/200:

  1. Create an Access (or similar) database with a SQL Specific Pass Through Query
  2. Create an Access DSN pointing to the Access database
  3. Create a New Shipment Map in Worldship pointing to the Access DSN

Creating an Access Database with a SQLSpecificPass Through Query

The following example uses MS Access to create a database that will use linked connections to the SO1_SOEntryHeader but you can substitute the SO5_InvoiceDataEntryHeader table if you will be pulling from the invoice files. These tables are used primarily when creating shipment maps. Although MS Access is used here as an example, any database application could actually be used.

Note: Knowledge of MS Access is required. Best Software cannot assist in this task

  1. Start up Microsoft Access. The steps below will vary depending on version of Access and personal preferences. Click on File, New and choose Blank Database. Save the database on a local drive or in a location of your choice. In this example, the database is saved to C:\UPS\WorldshipConnect.mdb
  2. Open Crystal and start a new report
  3. Base report on ONE MAS200 table, in this case SO1
  4. Pull into the detail section all fields you want (see example below)

  1. Go to Database on the menu
  2. Go to Show SQL Statement
  3. Highlight and copy the entire SQL statement.

  1. Open your Access application
  2. Go to Queries
  3. Create New Query
  4. Close table box that opens.
  5. Change Query type to SQL specific
  6. When edit window opens paste in SQL statement from Crystal
  7. Go to View on menu
  8. Go to Properties
  9. You will need to type in the connection string as follows because the ODBC connection Wizard in Access will not work with the MAS 4.0 ODBC driver in many cases:
    ODBC;DSN=SOTAMAS90;UID=XXX|ABC;PWD=XYZ;Directory=\\account\account$\Best\MAS 200\Version4\MAS90\;SERVER=NotTheServer
  10. Where XXX is the user ID, ABC is the company code and XYZ is the password. The rest will be based on your system. Note there is a | between the user ID and the company code.
  11. A very very important note is on the ODBC connection string, make sure that DirtyReads=1 and BurstMode=1 is removed. It is critcial to omit these options in the connection string.
  12. Save.
  13. You now have a working pass-through query with auto-login. This method returns data faster than a linked table and allows you to connect to MAS without having to create a silent DSN.

Create an Access DSN pointing to the Access database

The objective of this procedure is to create an DSN that will point to the C:\Ups\WorldshipConnect.mdb Access database. This DSN will use the Microsoft Access Driver as opposed to the MAS 90 32-BIT ODBC Driver.

  1. Go to the Control Panel of each work station that will be running the World Ship software.
  2. Go to Administrative Tools
  3. Go to Data Sources
  4. Click Add

  1. Select the Microsoft Access Driver and click Finish

  1. Type in UPS Data Source for Data Source Name and Description

  1. Click on Select under Database and locate the Access database created in the steps above.

8. Click OK on all panels and close the Control Panel and Administrative Tools.

..

Using Worldship’s Connection Assistant to Create a New Shipment Map

Now that the Access DSN has been created, the final objective is to create a new shipment map in Worldship itself that points to the Access DSN that you just created. You can modify an existing shipment map instead of creating a new one but you will need to recreate the MAS 90/200 to UPS field mappings regardless.

In this new or existing shipment map, you will point the ODBC connection to the Access DSN you just created. Following the earlier example, once completed Worldship will “see” just the SO1 query. If you are using the invoice files instead of the sales order files then it will be SO5 instead of SO1. Now you may proceed to map the appropriate MAS 90/200 fields to the Worldship fields. Once completed you can perform the Keyed Import or use your favorite method of using the shipment map. There will not be a 3 – 10 minute delay.