Navision Financials & UPS Worldship Integration
A
T F
Michael Heydasch
D
R
August 2, 2004
Table of Contents Introduction .......................................................................................................................................................................... 4 Prerequisites ......................................................................................................................................................................... 4 Step by step .......................................................................................................................................................................... 5 Disclaimer .............................................................................................................................................................................. 5 Zope and eGenix mxODBC Zope DA ........................................................................................................................... 6 MySQL ................................................................................................................................................................................... 10 Linux: a PHP script for Cron and the shipd daemon ............................................................................................. 14 Navision ................................................................................................................................................................................ 16 UPS Worldship .................................................................................................................................................................... 18
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
3
August 2, 2004
Introduction This guide will help the user establish a link between UPS Worldship and Navision Financials at little cost through the use of several add-on packages, some of which are freely available on the Internet. The UPS Worldship system retrieves shipment-specific information from Navision and, through the use of a dataport, writes shipment data back to Navision (such as freight cost and tracking number). This solution is not designed for freight quoting purposes. This guide describes how to set up a link wherein the UPS Worldship system will connect to a MySQL database that contains replicated data from Navision. Links to Navision via C/ODBC have traditionally been very slow. By contrast, the connection to MySQL is instantaneous because it is "always on". If the user has limited or no experience with some of the add-in software, there may be a learning curve involved. This guide will help to some degree, but can not replace the documentation that accompanies each software package. It is best to implement each prerequisite fully before moving to the next step.
Prerequisites • Navision Financials installed on a server running NT/2K/XP. For the purpose of this guide, this server will be named NAVISION. • A Navision license file with either Solution Developer or Application Builder, and experience with Navision's Object Designer and Navision's C/AL programming language. • A server running Linux, MySQL, PHP, and Samba. Basic computers can be acquired for less than $750(sans operating system) and loaded with Linux. If the decision is made to use a spare workstation for the Linux server, ensure the CPU processing speed is relatively recent (previous year or so), ensure the hard disk is recent, and ensure there is plenty of RAM (512mb or greater). • C/ODBC installed and configured on server NAVISION. C/ODBC will be found on the Navision CD. • ZOPE installed and configured on server NAVISION. For help: http://www.zope.org/ • mxODBC connector installed and configured on server NAVISION. For help: http://www.egenix.com/files/python/mxODBC-Zope-DA.html • Version 5.0 (build 37) or greater of UPS Worldship. • MyODBC installed and configured on the UPS Worldship workstation. For help: http://dev.mysql.com/downloads/connector/odbc/3.51.html My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
4
August 2, 2004
Step by Step • populate.php runs on a periodic basis on the Linux server and is responsible for replicating shipment data from Navision's Sales Shipment Header table to MySQL's Sales_Shipment_Header table. • The UPS Worldship workstation allows a keyed import from MySQL's Sales_Shipment_Header table, using the Shipment No. as the primary key. • The user presses F10 to process the shipment, and the Worldship software automatically writes shipment data to MySQL's Shipment_Information table. • The invoicing user accesses a sales order that shipped UPS. Before invoicing, the user presses F11. Navision writes a text file to a predetermined location (see "Shipping Agent"."External System Source File") that contains the Shipping Agent Account No. and the Sales Order No. • A daemon on Linux called shipd finds the text file, looks the information up in MySQL's Shipment_Information table, and writes the appropriate record(s) to the "External System Data File". The source file is erased. • Navision recognizes the data file and does an automatic import into the Shipment Information table. The Shipment Tracking No. and the appropriate freight charges are automatically added to the order. The data file is erased.
Disclaimer The documentation and scripts provided with this resource were created with the help of open source software, documentation and resources freely available on the Internet (with the exception of mxODBC, for which there is a nominal charge to purchase), and my own personal programming and troubleshooting experience. Iused the search engine Google™ extensively throughout the creation of these resources. Enterprising individuals will be able to take these resources and customize them to their own organization. Individual help is not available to assist you in your endeavors due to time constraints. Chiefly, the benefit of using these resources is the creation of a solution at little or no cost. Other (secondary) benefits may include (but are not limited to) a personal sense of accomplishment, additional experience, and the application of open source software. Use of the resources mentioned or provided herein are presented/recommended to you at your own risk. Michael Heydasch My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
5
August 2, 2004
Zope and eGenix mxODBC Zope DA The user should use the Zope Manual to ensure Zope is installed, configured and tested. Assuming C/ODBC has already been installed, configured and tested, install the eGenix mxODBC Zope DA. Using the Zope interface through a web browser, create an “eGenix mxODBC Database Connection” in the Zope Root Folder and call it “Nav”. Use the properties at right to configure the Nav database connection. The user may have to restart Zope for the connection to open. The following three pages detail the objects required in Zope.
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
6
August 2, 2004
Create a folder in the Zope Root Folder called “Customer”. Create a “Z SQL Method” object within the folder Customer called “getCustomer”. Make the Connection ID “Nav” and the arguments “Number:string”. Populate it with this code: SELECT "No_" AS Number, Contact, Name, Address, "Address 2" AS Address2, City, State, "ZIP Code" AS Zip, "Country Code" AS Country, "Phone No_" AS Phone, "Fax No_" AS Fax, "E-Mail" AS Email, "Home Page" AS Webpage, "Shipment Notification Pref_" AS ShipNotify, Residence AS Residence FROM Customer WHERE "No_" LIKE
Create a “DHTML Method” object within the folder Customer called “viewCustomerFull”. Populate it with this code: No= Name= Contact= Address= Address2= City= State= Zip= Phone= Fax= Email= Webpage= ShipNotify= Residence= error
Create a folder in the Zope Root Folder called “Shipments”. Create a “Z SQL Method” called “getShipmentFull” with Connection ID “Nav”, arguments “Number:int”, and code: SELECT "Sell-to "Ship-to "Ship-to "Ship-to "Ship-to "Ship-to
Customer No_" AS CustNo, "No_" AS ShipNo, Code" AS ShipToCode, Name" AS Name, "Ship-to Name 2" AS Name2, Address" AS Address, "Ship-to Address 2" AS Address2, City" AS City, "Ship-to State" AS State, ZIP Code" AS Zip, "Ship-to Country Code" AS Country,
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
7
August 2, 2004
"Ship-to Contact" AS Contact, "Order No_" AS OrderNo, "Order Date" AS OrderDate, "Posting Date" AS PostingDate, "Document Date" AS DocumentDate, "Shipment Date" AS ShipmentDate, "Your Reference" AS YourReference, "Payment Terms Code" AS PymtTermsCode, "Shipment Method Code" AS ShipMethodCode, "Shipment Method".Description AS ShipMethodDesc, "Shipping Agent Code" AS ShipAgentCode, "Customer Posting Group" AS CustPostGrp, "Gen_ Bus_ Posting Group" AS GenBusPostGrp, "Salesperson Code" AS Salesperson FROM "Sales Shipment Header", "Shipment Method" WHERE ("No_" LIKE '') AND ("Shipment Method".Code = "Sales Shipment Header"."Shipment Method Code")
Create a “DTML Method” object called “viewShipmentFull” in folder Shipments with code: CustNo= ShipNo= ShipToCode= Name= Name2= Address= Address2= City= State= Zip= Country= Contact= OrderNo= OrderDate= PostingDate= DocumentDate= ShipmentDate= YourReference= PymtTermsCode= ShipMethodCode= ShipMethodDesc= ShipAgentCode= CustPostGrp= GenBusPostGrp= Salesperson= error
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
8
August 2, 2004
Create a “Z SQL Method” object called “getShipmentsFullByNum” in folder Shipments with Connection ID “Nav”, arguments “Number:int”, and code: SELECT "No_" AS ShipNo FROM "Sales Shipment Header" WHERE "No_" > ''
Create a “DTML Method” object called “viewShipmentsFullByNum” in folder Shipments with this code: error
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
9
August 2, 2004
MySQL The reader is expected to be familiar with creating databases and tables in MySQL. For reference, please consult the MySQL Manual. For the purpose of this guide, a user “service” will be created with read/write access to a database named NAVISION and the tables therein. Following are descriptions of the requisite tables. Output of SHOW TABLES command: mysql> USE NAVISION; Database changed mysql> SHOW TABLES; +-----------------------+ | Tables_in_NAVISION | +-----------------------+ | Sales_Shipment_Header | | Shipment_Information | +-----------------------+ 2 rows in set (0.00 sec) mysql>
The Sales_Shipment_Header table is populated by the PHP script populate.php and contains information from Navision that will be read by the UPS Worldship workstation. The Shipment_Information table contains information exported from the UPS Worldship workstation.
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
10
August 2, 2004
mysql> DESCRIBE Sales_Shipment_Header; +---------------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------------+---------------+------+-----+---------+-------+ | Sell_To_Customer_No | varchar(20) | | MUL | | | | Shipment_No | varchar(20) | | PRI | | | | Ship_to_Code | varchar(10) | YES | | NULL | | | Ship_to_Name | varchar(30) | YES | | NULL | | | Ship_to_Name_2 | varchar(30) | YES | | NULL | | | Ship_to_Address | varchar(30) | YES | | NULL | | | Ship_to_Address_2 | varchar(30) | YES | | NULL | | | Ship_to_City | varchar(30) | YES | | NULL | | | Ship_to_State | varchar(30) | YES | | NULL | | | Ship_to_ZIP_Code | varchar(20) | YES | | NULL | | | Ship_to_Country_Code | varchar(10) | YES | | NULL | | | Ship_to_Contact | varchar(30) | YES | | NULL | | | Order_No | varchar(20) | YES | MUL | NULL | | | Order_Amt | double | YES | | NULL | | | Order_Date | date | YES | | NULL | | | Posting_Date | date | YES | MUL | NULL | | | Document_Date | date | YES | | NULL | | | Shipment_Date | date | YES | | NULL | | | Your_Reference | varchar(30) | YES | | NULL | | | Payment_Terms_Code | varchar(10) | YES | | NULL | | | Shipment_Method_Code | varchar(10) | YES | | NULL | | | Shipping_Agent_Code | varchar(10) | YES | | NULL | | | Customer_Posting_Group | varchar(10) | YES | | NULL | | | Gen_Bus_Posting_Group | varchar(10) | YES | | NULL | | | Customer_Phone_No | varchar(30) | YES | | NULL | | | Customer_Fax_No | varchar(30) | YES | | NULL | | | Customer_Email | varchar(80) | YES | | NULL | | | Customer_Webpage | varchar(80) | YES | | NULL | | | Customer_Ship_Notify_Pref | varchar(10) | YES | | NULL | | | Customer_Residence | char(1) | YES | | NULL | | | Call_Tag_Option | char(1) | YES | | NULL | | | Saturday_Delivery_Option | char(1) | YES | | NULL | | | Shipment_Notify_1_Option | char(1) | YES | | NULL | | | Shipment_Notify_1_Type | varchar(10) | YES | | NULL | | | Shipment_Notify_1_Memo | varchar(80) | YES | | NULL | | | Reference_1 | varchar(40) | YES | | NULL | | | Reference_2 | varchar(40) | YES | | NULL | | | Reference_3 | varchar(40) | YES | | NULL | | | Reference_4 | varchar(40) | YES | | NULL | | | Reference_5 | varchar(40) | YES | | NULL | | | Ship_From_Name | varchar(80) | YES | | NULL | | | Created | timestamp(14) | YES | | NULL | | +---------------------------+---------------+------+-----+---------+-------+ 42 rows in set (0.01 sec) mysql>
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
11
August 2, 2004
mysql> DESCRIBE Shipment_Information; +------------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------------+--------------+------+-----+---------+-------+ | Shp_Void_Indicator | char(1) | YES | | NULL | | | Shp_Service_Type | varchar(22) | YES | | NULL | | | Shp_Book_No | varchar(12) | YES | | NULL | | | Shp_Page_No | varchar(12) | YES | | NULL | | | Shp_Shipment_ID | varchar(37) | YES | MUL | NULL | | | Shp_HundredWeight | char(1) | YES | | NULL | | | Shp_Actual_Weight | varchar(9) | YES | | NULL | | | Shp_Ext_Area_Indicator | char(1) | YES | | NULL | | | Shp_Billable_Weight | varchar(9) | YES | | NULL | | | Shp_Billing_Opt | varchar(20) | YES | | NULL | | | Shp_Pickup_Date | varchar(34) | YES | | NULL | | | Shp_Insured_Value_Opt | char(1) | YES | | NULL | | | Shp_Insured_Value_Chg | varchar(9) | YES | | NULL | | | Shp_Insured_Value_Amt | varchar(9) | YES | | NULL | | | Shp_Handl_Chg_Opt | char(1) | YES | | NULL | | | Shp_Handl_Chg_Amt | varchar(9) | YES | | NULL | | | Shp_Handl_Chg_Type | varchar(10) | YES | | NULL | | | Shp_Handl_Chg_Flat_Rate | varchar(8) | YES | | NULL | | | Shp_Handl_Chg_Perc | varchar(6) | YES | | NULL | | | Shp_Return_Svc_Opt | char(1) | YES | | NULL | | | Shp_Return_Svc_Type | varchar(50) | YES | | NULL | | | Shp_Recipient_Email_Addr | varchar(50) | YES | | NULL | | | Shp_Failed_Email_Addr | varchar(50) | YES | | NULL | | | Shp_Return_Svc_Chg | varchar(9) | YES | | NULL | | | Shp_Call_Tag_Ref_No | varchar(12) | YES | | NULL | | | Shp_Sat_Delivery_Opt | char(1) | YES | | NULL | | | Shp_Sat_Delivery_Chg | varchar(9) | YES | | NULL | | | Shp_Sat_Pickup_Opt | char(1) | YES | | NULL | | | Shp_Sat_Pickup_Chg | varchar(9) | YES | | NULL | | | Shp_QVN_Opt | char(1) | YES | | NULL | | | Shp_QVN_Chg | varchar(9) | YES | | NULL | | | Shp_QVN_Publ_Chg | varchar(9) | YES | | NULL | | | Shp_QVN_Shp_From_Name | varchar(35) | YES | | NULL | | | Shp_QVN_Subj_Line | varchar(75) | YES | | NULL | | | Shp_QVN_Memo | varchar(150) | YES | | NULL | | | Shp_QVN_Failed_Email_Addr | varchar(50) | YES | | NULL | | | Shp_QVN_Ship_Notify_1_Opt | char(1) | YES | | NULL | | | Shp_QVN_Deliv_Notify_1_Opt | char(1) | YES | | NULL | | | Shp_QVN_Excep_Notify_1_Opt | char(1) | YES | | NULL | | | Shp_Notify_Recip_1_Type | varchar(6) | YES | | NULL | | | Shp_Notify_Recip_1_Name | varchar(35) | YES | | NULL | | | Shp_Notify_Recip_1_Contact | varchar(35) | YES | | NULL | | | Shp_Notify_Recip_1_Phone | varchar(15) | YES | | NULL | | | Shp_Notify_Recip_1_Fax_Email | varchar(50) | YES | | NULL | | | Shp_Notify_Recip_1_Intl_Fax | varchar(15) | YES | | NULL | | | Shp_Desc_of_Goods | varchar(50) | YES | | NULL | | | Shp_Docs_Only_Indicator | char(1) | YES | | NULL | | | Shp_Special_Instructions | varchar(69) | YES | | NULL | | | Shp_Shipper_No | varchar(6) | YES | | NULL | | | Shp_Total_Shipment_Chg | varchar(9) | YES | | NULL | | | Shp_Total_Shipper_Chg | varchar(9) | YES | | NULL | | | Shp_Total_Receiver_Chg | varchar(9) | YES | | NULL | | My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
12
August 2, 2004
| Shp_Total_Ship_Hndl_Chgs | varchar(9) | YES | | NULL | | | Shp_No_of_Packages | char(3) | YES | | NULL | | | ShpTo_Customer_No | varchar(35) | YES | MUL | NULL | | | ShpTo_Name | varchar(35) | YES | | NULL | | | ShpTo_Contact | varchar(35) | YES | | NULL | | | ShpTo_Address | varchar(35) | YES | | NULL | | | ShpTo_Address_2 | varchar(35) | YES | | NULL | | | ShpTo_Address_3 | varchar(35) | YES | | NULL | | | ShpTo_Country | varchar(50) | YES | | NULL | | | ShpTo_ZIP_Code | varchar(10) | YES | | NULL | | | ShpTo_City | varchar(30) | YES | | NULL | | | ShpTo_State | varchar(5) | YES | | NULL | | | ShpTo_Phone_No | varchar(15) | YES | | NULL | | | ShpTo_Fax_No | varchar(15) | YES | | NULL | | | ShpTo_Tax_ID_No | varchar(15) | YES | | NULL | | | ShpTo_Receiver_UPS_Acct_No | varchar(10) | YES | | NULL | | | ShpTo_Location_ID | varchar(10) | YES | | NULL | | | ShpTo_Residential_Indicator | char(1) | YES | | NULL | | | Pkg_Void_Indicator | char(1) | YES | | NULL | | | Pkg_Package_Chg | varchar(11) | YES | | NULL | | | Pkg_Package_Type | varchar(15) | YES | | NULL | | | Pkg_Weight | varchar(5) | YES | | NULL | | | Pkg_Tracking_No | varchar(30) | | PRI | | | | Pkg_Oversize_Indicator | varchar(10) | YES | | NULL | | | Pkg_Reference_1 | varchar(35) | YES | | NULL | | | Pkg_Reference_2 | varchar(35) | YES | MUL | NULL | | | Pkg_Reference_3 | varchar(35) | YES | | NULL | | | Pkg_Reference_4 | varchar(35) | YES | | NULL | | | Pkg_Reference_5 | varchar(35) | YES | | NULL | | | Pkg_Addtl_Handling_Opt | char(1) | YES | | NULL | | | Pkg_Addtl_Handling_Chg | varchar(9) | YES | | NULL | | | Pkg_COD_Opt | char(1) | YES | | NULL | | | Pkg_COD_Control_No | varchar(12) | YES | | NULL | | | Pkg_COD_Chg | varchar(9) | YES | | NULL | | | Pkg_COD_Amt | varchar(9) | YES | | NULL | | | Pkg_Cashiers_Check_Indicator | char(1) | YES | | NULL | | | Pkg_Add_Ship_Chgs_Indicator | char(1) | YES | | NULL | | | Pkg_Insured_Value_Opt | char(1) | YES | | NULL | | | Pkg_Insured_Value_Chg | varchar(9) | YES | | NULL | | | Pkg_Insured_Value_Amt | varchar(5) | YES | | NULL | | | Pkg_HazMat_Opt | char(1) | YES | | NULL | | | Pkg_HazMat_Chg | varchar(9) | YES | | NULL | | +------------------------------+--------------+------+-----+---------+-------+ 94 rows in set (0.00 sec) mysql>
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
13
August 2, 2004
Linux: a PHP script for Cron and the shipd daemon The PHP script populate.php is called from Cron periodically. It is responsible for updating the shipment information in the table MySQL:Navision/Sales_Shipment_Header with the correct information from the NAVISION server. Here is the entry in /etc/crontab: # # automation; populate MySQL:NAVISION/Sales_Shipment_Header with records # from Navision's "Sales Shipment Header" table; WorldShip retrieves fr MySQL. # 0-59/5 * * * * root /usr/bin/php /var/www/html/ship/populate.php 2>&1 >/dev/null
The duration is five minutes; thus, every five minutes, cron launches populate.php, which pulls the appropriate records from Navision and populates the MySQL table. Five minutes must elapse between the time the sales order is shipped and the shipment number is entered on the UPS Worldship workstation. This duration should be adjusted according to the needs of the organization. The astute reader will notice that populate.php is a web script located in a published directory (i.e., /var/www/html for RedHat users). It is the reader’s responsibility to adjust this script to match the security needs of the organization. The contents of the populate.php and shipfcns.php scripts are included with this archive. A daemon on the Linux operating system is a special program that waits around for a particular event. In this case, the daemon created for this project listens for users requesting freight charges and tracking information. More accurately, every few seconds the daemon scans for a text file containing a shipper account number and a sales order number; if it finds the file, then a response file is written containing the desired shipment record(s) from the table MySQL:NAVISION/ Shipment_Information. Each user requesting freight information must have a drive mapped to the Linux server. For the purpose of this guide, it is assumed that the user has drive W: mapped to /tmp/folder on the Linux server. The technical explanation for how this is accomplished is beyond the scope of this manual; for more information, please read the Samba HOWTO. The source code must be compiled with MySQL support. The user must acquire the MySQLdevelopment package. If the user has an RPM-based Linux distribution (such as Red Hat), obtain the package called Yum (http://linux.duke.edu/projects/yum/download.ptml). Install Yum, then type yum install mysql-devel to automatically download and install MySQL support. The daemon must be run at startup. Add the following command to /etc/rc.d/rc.local (Red Hat), and be sure to reflect the correct path for your organization: /var/www/html/ship/shipd My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
14
August 2, 2004
The command to compile the source code is: gcc -o shipd shipd.c -I/usr/include/mysql -L'/usr/lib/mysql' -lmysqlclient -lz -lcrypt -lnsl -lm
The contents of the source code file shipd.c are included with this archive. The daemon requires a configuration file, called shipd.conf, included with this archive.
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
15
August 2, 2004
Navision Import objects Table 50002, "Shipment Information", Form 50002, "Shipment Information", Dataport 50002, "Shipment Info 1-UPS Imp", Dataport 50003, "Shipment Info 2-FDX Imp", and Codeunit 50002, "Shipment Information". Modify Table 291, "Shipping Agent", by inserting the following fields: Enabled Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Field No. Field Name 50000 External System No. 50001 External System Activate 50002 External System Dataport 50003 External System Form 50005 External System Source File 50006 External System Data File 50007 External System Handling Chg 50008 External System Last Exe Date 50009 External System Last Exe Time 50010 External System Last Exe User
Data Type Code Boolean Integer Integer Text Text Decimal Date Time Code
Length 20
250 250
20
Populate the aforementioned fields with values similar to the following: Field Code Name Internet Address Account No. External System No. External System Activate External System Dataport External System Form External System Source File External System Data File
Value UPS UPS http://www.ups.com/tracking/tracking.html XXXXXX 1-UPS Yes 50002 50002 w:\nav1.dat w:\nav2.dat
Freight is added to a sales order by adding lines of type "Account (G/L)". Currently, the system determines which G/L Account number to use by referencing the Gen. Business Posting Group table. Add these fields to Table 250, "Gen. Business Posting Group": Enabled Yes Yes
Field No. Field Name 50000 Freight Billed Account No. 50001 Free Freight Account No.
Data Type Code Code
Length 20 20
Populate the aforementioned fields with values similar to the following (values from the G/L Account table): Freight Billed Account No. 5105-3005
Free Freight Account No. 5110-3000
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
16
August 2, 2004
Modify Form 42, "Sales Order". Add C/AL Global: Name ShipInfoRoutines
DataType Codeunit
Subtype Shipment Information
Length
On button "Posting", add menu item "Shipping Integration", shortcut key F11 (optional), with code: ShipInfoRoutines.ShipInfoRead( Rec ); CurrForm.UPDATE( FALSE );
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
17
August 2, 2004
UPS Worldship MyODBC should be installed and configured on the Worldship workstation. Under Control Panel, Administrative Tools, Data Sources (ODBC), a User DSN should be created using the driver MySQL ODBC 3.51 (or later). This User DSN will be used when configuring Worldship. The reader is expected to know how to create import and export maps on the Worldship workstation. Create an export map called MYSQL_EXP, and an import map called MYSQL_IMP. (The field mappings begin on this page.) The MYSQL_IMP map uses the field "Sales Shipment Header"."Shipment No." as the primary key when performing a keyed import; be sure to define this field as the primary key when defining the map. After the MYSQL_EXP map is created, be sure to turn ON the following option in Worldship: UPS OnLine Connect, Automatic Export After Processing Shipment, MYSQL_EXP. Define Map:
"MYSQL_EXP", Shipment, myodbc
ODBC Tables: Shipment_Information Map Fields: Shipment Information.Void Indicator -> Shipment Information.Shp_Void_Indicator Shipment Information.Total Shipper Charge -> Shipment Information.Shp_Total_Shipper_Chg Shipment Information.Total Shipment and Handling Charges -> Shipment Information.Shp_Total_Ship_Handl_Chgs Shipment Information.Total Shipment Charge -> Shipment Information.Shp_Total_Shipment_Chg Shipment Information.Total Receiver Charge -> Shipment Information.Shp_Total_Receiver_Chg Shipment Information.Special Instructions for Shipment -> Shipment Information.Shp_Special_Instructions Shipment Information.Shipper Number -> Shipment Information.Shp_Shipper_No Shipment Information.Shipment ID -> Shipment Information.Shp_Shipment_ID Shipment Information.Service_Type -> Shipment Information.Shp_Service_Type Shipment Information.Saturday Pickup Option -> Shipment Information.Shp_Sat_Pickup_Opt Shipment Information.Saturday Pickup Charge -> Shipment Information.Shp_Sat_Pickup_Chg Shipment Information.Saturday Delivery Option -> Shipment Information.Shp_Sat_Delivery_Opt Shipment Information.Saturday Delivery Charge -> Shipment Information.Shp_Sat_Delivery_Chg Shipment Information.Return Service Type -> Shipment Information.Shp_Return_Svc_Type Shipment Information.Return Service Option -> Shipment Information.Shp_Return_Svc_Opt Shipment Information.Return Service Charge -> Shipment Information.Shp_Return_Svc_Chg Shipment Information.Recipient Email Address -> Shipment Information.Shp_Recipient_Email_Addr Shipment Information.QVN Subject Line -> Shipment Information.Shp_QVN_Subj_Line My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
18
August 2, 2004
Shipment Information.QVN Ship Notification 1 Option-> Shipment Information.Shp_QVN_Ship_Notify_1_Opt Shipment Information.QVN Ship From Company or Name -> Shipment Information.Shp_QVN_Shp_From_Name Shipment Information.QVN Published Charge -> Shipment Information.Shp_QVN_Publ_Chg Shipment Information.QVN Option -> Shipment Information.Shp_QVN_Opt Shipment Information.QVN Memo -> Shipment Information.Shp_QVN_Memo Shipment Information.QVN Failed Email Address -> Shipment Information.Shp_QVN_Failed_Email_Addr Shipment Information.QVN Exception Notification 1 Option -> Shipment Information.Shp_QVN_Excep_Notify_1_Opt Shipment Information.QVN Delivery Notification 1 Option -> Shipment Information.Shp_QVN_Deliv_Notify_1_Opt Shipment Information.QVN Charge -> Shipment Information.Shp_QVN_Chg Shipment Information.Pick up date -> Shipment Information.Shp_Pickup_Date Shipment Information.Page Number -> Shipment Information.Shp_Page_No Shipment Information.Number of Packages -> Shipment Information.Shp_No_of_Packages Shipment Information.Notification Recipient 1 Type (fax or Email) -> Shipment Information.Shp_Notify_Recip_1_Type Shipment Information.Notification Recipient 1 Telephone -> Shipment Information.Shp_Notify_Recip_1_Phone Shipment Information.Notification Recipient 1 International Fax -> Shipment Information.Shp_Notify_Recip_1_Intl_Fax Shipment Information.Notification Recipient 1 Fax or Email -> Shipment Information.Shp_Notify_Recip_1_Fax_Email Shipment Information.Notification Recipient 1 Contact Name -> Shipment Information.Shp_Notify_Recip_1_Contact Shipment Information.Notification Recipient 1 Company or Name -> Shipment Information.Shp_Notify_Recip_1_Name Shipment Information.Insured Value Option -> Shipment Information.Shp Insured Value Opt Shipment Information.Insured Value Charge -> Shipment Information.Shp Insured Value Chg Shipment Information.Insured Value Amount -> Shipment Information.Shp_Insured_Value_Amt Shipment Information.HundredWeight -> Shipment Information.Shp_HundredWeight Shipment Information.Handling Charge Type -> Shipment Information.Shp_Handl_Chg_Type Shipment Information.Handling Charge Percentage -> Shipment Information.Shp_Handl_Chg_Perc Shipment Information.Handling Charge Option -> Shipment Information.Shp_Handl_Chg_Opt Shipment Information.Handling Charge Flat Rate -> Shipment Information.Shp Handl Chg Flat Rate Shipment Information.Handling Charge Amount -> Shipment Information.Shp Handl Chg Amt Shipment Information.Failed Email Address -> Shipment Information.Shp_Failed Email Addr Shipment Information.Extended Area Indicator -> Shipment Information.Shp_Ext_Area_Indicator Shipment Information.Documents Only Indicator -> Shipment Information.Shp_Docs_Only_Indicator My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
19
August 2, 2004
Shipment Information.Description of Goods -> Shipment Information.Shp_Desc_of_Goods Shipment Information.Call Tag Reference Number -> Shipment Information.Shp_Call Tag Ref No Shipment Information.Book Number -> Shipment Information.Shp_Book No Shipment Information.Billing Option -> Shipment Information.Shp_Billing Opt Shipment Information.Billable Weight -> Shipment Information.Shp_Billable_Weight Shipment Information.Actual Weight -> Shipment Information.Shp Actual Weight Ship To.Telephone -> Shipment Information.ShpTo Phone No Ship To.Tax ID Number -> Shipment Information.ShpTo Tax ID No Ship To.Street Address -> Shipment_Information.ShpTo_Address Ship To.State -> Shipment_Information.ShpTo_State Ship To.Room/Floor/Address 2 -> ShpTo_Address_2 Ship To.Residential Indicator -> ShpTo Residential Indicator Ship To.Receiver UPS Account Number -> ShpTo Receiver UPS Acct No Ship To.Postal/ZIP Code -> ShpTo_ZIP_Code Ship To.Location ID -> ShpTo Location ID Ship To.Fax Number -> ShpTo_Fax_No Ship To.Department/Address 3 -> ShpTo Address 3 Ship To.Country -> ShpTo Country Ship To.Company or Name -> ShpTo Name Ship To.City -> ShpTo City Ship To.Attention -> ShpTo Contact Package.Weight -> Pkg_Weight Package.Void Indicator -> Pkg_Void_Indicator Package.Tracking Number -> (PRI) Pkg_Tracking_No Package.Reference 5 -> Pkg Reference 5 Package.Reference 4 -> Pkg Reference 4 Package.Reference 3 -> ShpTo Customer No Package.Reference 2 -> Pkg Reference 2 Package.Reference 1 -> Pkg Reference 1 Package.Package Type -> Pkg_Package_Type Package.Package Charge -> Pkg Package Chg Package.Oversize Indicator -> Pkg Oversize Indicator Package.Insured Value Option -> Pkg Insured Value Opt Package.Insured Value Charge -> Pkg Insured Value Chg Package.Insured Value Amount -> Pkg Insured Value Amt Package.Hazardous Materials Option -> Pkg HazMat Opt Package.Hazardous Materials Charge -> Pkg HazMat Chg Package.Cashier's Check/Money Order Only Indicator -> Pkg Cashiers Check Indicator Package.COD Option -> Pkg COD Opt Package.COD Control Number -> Pkg COD Control No Package.COD Charge -> Pkg COD Chg Package.COD Amount -> Pkg COD Amt Package.Additional Handling Option -> Pkg Addtl Handling Opt Package.Additional Handling Charge -> Pkg Addtl Handling Chg Package.Add Shipping Charges to COD Indicator -> Pkg Add Ship Chgs Indicator
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
20
August 2, 2004
Define Map:
"MYSQL_IMP", Shipment, myodbc
ODBC Tables: Sales_Shipment_Header Map Fields: Sales_Shipment_Header. Shipment_Notify 1 Type -> Shipment Information.Notification Recipient 1 Type (fax or Email) Shipment Notify 1 Option -> Shipment Information.QVN Ship Notification 1 Option Shipment Notify 1 Memo -> Shipment Information.QVN Memo Ship to ZIP Code -> Ship To.Postal/ZIP Code Ship to State -> Ship To.State Ship to Name -> Shipment Information.Notification Recipient 1 Company or Name Ship to Name -> Ship To.Company or Name Ship to Country Code -> Ship To.Country Ship to Contact -> Ship To.Attention Ship to City -> Ship To.City Ship to Address 2 -> Ship To.Room/Floor/Address 2 Ship to Address -> Ship To.Street Address Ship From Name -> Shipment Information.QVN Ship From Company or Name Sell To Customer No -> Package.Reference 3 Saturday Delivery Option -> Shipment Information.Saturday Delivery Option Reference 2 -> Package.Reference 2 Reference 1 -> Package.Reference 1 Customer Residence -> Ship To.Residential Indicator Customer Phone No -> Ship To.Telephone Customer Fax No -> Ship To.Fax Number Customer Email -> Shipment Information.Recipient Email Address Customer Email -> Notification Recipient 1 Fax or Email
My Documents\Navision\docs\UPS Worldship Integration\literature\UPS_Worldship_Integration.pmd
21
August 2, 2004
