ITrack/Pro/UPS and FedEx Integration

The Gist of It

1. There is a view in the itrackpro database: v_invoice_shipping_address.(Already exists as it went out on the AU)
2. A MySQL user(ups) exists that only has access to that view.(Already exists as it went out on the AU)
3. A ODBC data source is configured using the ups account.
4. UPS Worldship is configured to access shipping information for customers via the ODBC data source and fields are mapped in UPS Worldship.
5. Via some option in the menu, the user of Worldship can enter in the quote #(they have to tell us if they want to use invoice # instead of quote #) and it will automatically fill out the correct fields in Worldship so the user doesn't have to manually enter it.

For any other shipping application to use this functionality it would need to support ODBC data sources.

Details below.

Contact UPS

If a user requests UPS integration you will most likely need to contact this UPS rep to setup things on the UPS Worldship side:

Paul Walker
UPS Customer Solutions
Solutions Engagement Supervisor
Telephone (904)994-2971
pwalker at ups dot com

Using Quote Number

This has been rolled out on the AU so everyone should already have this view:

DROP VIEW IF EXISTS `v_invoice_shipping_address`;

CREATE VIEW `v_invoice_shipping_address` AS 
(
SELECT `quote`.`invoicenum` AS `OrderNumber`, 
	IF(`quote`.`shipcompany` = '', `quote`.`shipname`, `quote`.`shipcompany`) AS `Name`,
	IF(`quote`.`shipcompany` = '', '', `quote`.`shipname`) AS `Attn`, 
	`quote`.`shipstreet` AS `Address`, 
	`quote`.`shipmailing` AS `Address2`,
	`quote`.`shipcity` AS `City`, 
	`quote`.`shipstate` AS `State`, 
	`quote`.`shipzip` AS `PostalCode`, 
	`quote`.`shipphone` AS `PhoneNumber`,
	`customer`.`email` AS `Email`
FROM `itrackpro`.`quote`
   JOIN `customer`
     ON `quote`.`customernum` = `customer`.`customernum`
WHERE `itrackpro`.`quote`.`document` != 'VOID'
)
;

Using Invoice Number

Some users may want the integration to use the invoice number. If so, they will no longer be able to use the quote number from within UPS.

DROP VIEW IF EXISTS `v_invoice_shipping_address`;

CREATE VIEW `v_invoice_shipping_address` AS 
(
SELECT `invoice`.`invoicenum` AS OrderNumber, 
	IF(`invoice`.`shipcompany` = '', `invoice`.`shipname`, `invoice`.`shipcompany`) AS `Name`,
	IF(`invoice`.`shipcompany` = '', '', `invoice`.`shipname`) AS `Attn`, 
	`invoice`.`shipstreet` AS Address, 
	`invoice`.`shipmailing` AS Address2,
	`invoice`.`shipcity` AS City, 
	`invoice`.`shipstate` AS State, 
	`invoice`.`shipzip` AS PostalCode, 
	`invoice`.`shipphone` AS PhoneNumber,
	`customer`.`email` AS Email
FROM `invoice` 
   JOIN `customer` 
     ON `invoice`.`customernum` = `customer`.`customernum`
WHERE document = 'Invoice'
)
;

In UPS Worldship

Go to Import/Export Data at the top menu > Keyed Import > <import name given> The user will then be able to enter in the invoice #(by default) and the customer shipping will automatically be pulled into UPS.