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 8: | Line 8: | ||
pwalker at ups dot com | pwalker at ups dot com | ||
=Using | =Using Quote Number= | ||
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"> | ||
CREATE VIEW `v_invoice_shipping_address` AS | CREATE VIEW `v_invoice_shipping_address` AS | ||
( | ( | ||
SELECT invoicenum AS OrderNumber, IF(shipcompany = '', shipname, shipcompany) AS `Name`, shipstreet AS Address, | SELECT `quote`.`invoicenum` AS OrderNumber, IF(`quote`.`shipcompany` = '', `quote`.`shipname`, `quote`.`shipcompany`) AS `Name`, `quote`.`shipstreet` AS Address, | ||
shipcity AS City, shipstate AS State, shipzip AS PostalCode | `quote`.`shipcity` AS City, `quote`.`shipstate` AS State, `quote`.`shipzip` AS PostalCode, `quote`.`shipphone` AS PhoneNumber, `customer`.`email` AS Email | ||
FROM ` | FROM `quote` | ||
WHERE document = ' | JOIN `customer` ON `quote`.`customernum` = `customer`.`customernum` | ||
WHERE `document` != 'VOID' | |||
); | ); | ||
</source> | </source> | ||
=Using | |||
Some users | =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. | |||
<source lang="mysql"> | <source lang="mysql"> | ||
DROP VIEW `v_invoice_shipping_address`; | DROP VIEW `v_invoice_shipping_address`; | ||
CREATE VIEW `v_invoice_shipping_address` AS | CREATE VIEW `v_invoice_shipping_address` AS | ||
( | ( | ||
SELECT invoicenum AS OrderNumber, IF(shipcompany = '', shipname, shipcompany) AS `Name`, shipstreet AS Address, | SELECT `invoice`.`invoicenum` AS OrderNumber, IF(`invoice`.`shipcompany` = '', `invoice`.`shipname`, `invoice`.`shipcompany`) AS `Name`, `invoice`.`shipstreet` AS Address, | ||
shipcity AS City, shipstate AS State, shipzip AS PostalCode | `invoice`.`shipcity` AS City, `invoice`.`shipstate` AS State, `invoice`.`shipzip` AS PostalCode, `invoice`.`shipphone` AS PhoneNumber, `customer`.`email` AS Email | ||
FROM ` | FROM `invoice` | ||
WHERE | JOIN `customer` ON `invoice`.`customernum` = `customer`.`customernum` | ||
WHERE document = 'Invoice' | |||
); | ); | ||
</source> | </source> | ||
Revision as of 12:47, 23 March 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:
CREATE VIEW `v_invoice_shipping_address` AS
(
SELECT `quote`.`invoicenum` AS OrderNumber, IF(`quote`.`shipcompany` = '', `quote`.`shipname`, `quote`.`shipcompany`) AS `Name`, `quote`.`shipstreet` AS Address,
`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 `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 `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`, `invoice`.`shipstreet` AS Address,
`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
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.
