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 36: | Line 36: | ||
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. | 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. | ||
<source lang="mysql"> | <source lang="mysql"> | ||
CREATE VIEW `v_invoice_shipping_address` AS | CREATE VIEW `v_invoice_shipping_address` AS | ||
( | ( | ||
SELECT `invoice`.`invoicenum` AS OrderNumber, | |||
`invoice`.`shipcity` AS City, `invoice`.`shipstate` AS State, `invoice`.`shipzip` AS PostalCode, `invoice`.`shipphone` AS PhoneNumber, `customer`.`email` AS Email | 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' | |||
) | |||
; | |||
</source> | </source> | ||
Revision as of 11:36, 10 November 2010
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 `itrackpro`.`customer`
ON `itrackpro`.`quote`.`customernum` = `itrackpro`.`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.
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.
