Difference between revisions of "ITrack/Pro/UPS and FedEx Integration"
From ISoft Wiki
Jump to navigationJump to search
Daytonlowell (talk | contribs) |
Daytonlowell (talk | contribs) |
||
| Line 1: | Line 1: | ||
==How It All Works== | ==How It All Works== | ||
#There | #There are two views in the itrackpro database: v_invoice_shipping_address and v_quote_shipping_address.(Already exists as they went out on the AU) | ||
# | #MySQL users(ups and fedex) exists that only has access to that view.(Already exists as they went out on the AU) | ||
#A ODBC data source is configured using the ups account. | #A ODBC data source is configured using the ups or fedex account. | ||
#UPS Worldship is configured to access shipping information for customers via the ODBC data source and fields are mapped in UPS Worldship or FedEx. | #UPS Worldship or FedEx is configured to access shipping information for customers via the ODBC data source and fields are mapped in UPS Worldship or FedEx. | ||
#Via some option in the menu, the user of Worldship or FedEx can enter in the quote #(they | #Via some option in the menu, the user of Worldship or FedEx can enter in the invoice # or quote #(depending on which they are using) and it will automatically fill out the correct fields in Worldship/FedEx 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. | For any other shipping application to use this functionality it would need to support ODBC data sources. | ||
| Line 28: | Line 28: | ||
This has been rolled out on the AU so everyone should already have this view: | This has been rolled out on the AU so everyone should already have this view: | ||
<source lang="mysql"> | <source lang="mysql"> | ||
DROP VIEW IF EXISTS ` | DROP VIEW IF EXISTS `v_quote_shipping_address`; | ||
CREATE VIEW ` | CREATE VIEW `v_quote_shipping_address` AS | ||
( | ( | ||
SELECT `quote`.`invoicenum` AS `OrderNumber`, | SELECT `quote`.`invoicenum` AS `OrderNumber`, | ||
| Line 42: | Line 42: | ||
`quote`.`shipphone` AS `PhoneNumber`, | `quote`.`shipphone` AS `PhoneNumber`, | ||
`customer`.`email` AS `Email` | `customer`.`email` AS `Email` | ||
FROM | FROM `quote` | ||
JOIN `customer` | JOIN `customer` | ||
ON `quote`.`customernum` = `customer`.`customernum` | ON `quote`.`customernum` = `customer`.`customernum` | ||
WHERE | WHERE `quote`.`document` != 'VOID' | ||
) | ) | ||
; | ; | ||
| Line 66: | Line 66: | ||
`invoice`.`shipzip` AS PostalCode, | `invoice`.`shipzip` AS PostalCode, | ||
`invoice`.`shipphone` AS PhoneNumber, | `invoice`.`shipphone` AS PhoneNumber, | ||
`customer`.`email` AS Email | `customer`.`email` AS Email, | ||
`invoice`.`trackingnum` AS TrackingNumber, | |||
`invoice`.`shipdate` AS ShipDate | |||
FROM `invoice` | FROM `invoice` | ||
JOIN `customer` | JOIN `customer` | ||
Revision as of 12:34, 22 June 2011
How It All Works
- There are two views in the itrackpro database: v_invoice_shipping_address and v_quote_shipping_address.(Already exists as they went out on the AU)
- MySQL users(ups and fedex) exists that only has access to that view.(Already exists as they went out on the AU)
- A ODBC data source is configured using the ups or fedex account.
- UPS Worldship or FedEx is configured to access shipping information for customers via the ODBC data source and fields are mapped in UPS Worldship or FedEx.
- Via some option in the menu, the user of Worldship or FedEx can enter in the invoice # or quote #(depending on which they are using) and it will automatically fill out the correct fields in Worldship/FedEx 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 or FedEx
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
FedEx
If a user requests FedEx integration you will most likely need to contact this FedEx rep to setup things on the FedEx side:
Brian
(850)210-2182
Using Quote Number
This has been rolled out on the AU so everyone should already have this view:
DROP VIEW IF EXISTS `v_quote_shipping_address`;
CREATE VIEW `v_quote_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 `quote`
JOIN `customer`
ON `quote`.`customernum` = `customer`.`customernum`
WHERE `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/FedEx.
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,
`invoice`.`trackingnum` AS TrackingNumber,
`invoice`.`shipdate` AS ShipDate
FROM `invoice`
JOIN `customer`
ON `invoice`.`customernum` = `customer`.`customernum`
WHERE document = 'Invoice'
)
;
Using It
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 quote #(by default) and the customer shipping will automatically be pulled into UPS.
In FedEx
I'd love to know.
