Difference between revisions of "ITrack/Pro/UPS and FedEx Integration"

From ISoft Wiki
< ITrack‎ | Pro
Jump to navigationJump to search
(New page: 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 Supe...)
 
(44 intermediate revisions by 2 users not shown)
Line 1: Line 1:
If a user requests UPS integration you will most likely need to contact this UPS rep to setup things on the UPS Worldship side:
==How It All Works==
Paul Walker
#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)
UPS Customer Solutions
#MySQL users(ups and fedex) exist that only have access to that view.(Already exists as they went out on the AU)
Solutions Engagement Supervisor
#A ODBC data source is configured using the ups or fedex account.
Telephone (904)994-2971
#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:<br>
<br>
Paul Walker<br>
UPS Customer Solutions<br>
Solutions Engagement Supervisor<br>
Telephone (904)994-2971<br>
pwalker at ups dot com
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:<br>
<br>
Brian<br>
(850)210-2182


=In UPS=
==Using Quote Number==
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 read into UPS.
If they want to use quote #'s instead of invoice numbers, see the "Using Quote Number" section below
 
=Using Invoice 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
DROP VIEW IF EXISTS `v_quote_shipping_address`;
 
CREATE VIEW `v_quote_shipping_address` AS  
(
(
SELECT invoicenum AS OrderNumber, IF(shipcompany = '', shipname, shipcompany) AS `Name`, shipstreet AS Address,  
SELECT `quote`.`invoicenum` AS `OrderNumber`,  
shipcity AS City, shipstate AS State, shipzip AS PostalCode  
IF(`quote`.`shipcompany` = '', `quote`.`shipname`, `quote`.`shipcompany`) AS `Name`,
FROM `invoice`  
IF(`quote`.`shipcompany` = '', '', `quote`.`shipname`) AS `Attn`,
WHERE document = 'Invoice'
`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'
)
;
</source>
</source>
=Using Quote Number=
 
Some users will want the integration to use the quote number so they can get the calculated shipping cost in the quote before they convert it to an invoice. If they decide they want that instead they will no longer be able to use the invoice number.
==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.
<source lang="mysql">
<source lang="mysql">
DROP VIEW IF EXISTS `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,  
shipcity AS City, shipstate AS State, shipzip AS PostalCode  
IF(`invoice`.`shipcompany` = '', `invoice`.`shipname`, `invoice`.`shipcompany`) AS `Name`,
FROM `quote`
IF(`invoice`.`shipcompany` = '', '', `invoice`.`shipname`) AS `Attn`,
WHERE document = 'Quote'
`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'
)
;
</source>
</source>
==Using It==
===In UPS Worldship===
[[Image:UPSWorldshipImportExportMenu.png|UPS Worldship Import/Export Menu]]
[[Image:UPSCreateMap.png|UPS Map Creation]]
[[Image:UPSFieldMapping.png|UPS Field Mapping]]
[[Image:UPSKeyedImport.png|UPS Keyed Import Menu]]
===Problems setting up Worldship===
If you've created the ITrack Import Map, and attempt to key in a Salesorder or Invoice # and get this error returned:
[[Image:worldship_error.png|Prepared Statements Error]]
Then you need to open the ODBC Profile that the ITrack Import map is utilizing, open the advanced settings at the bottom, go to the Misc section and check "Prepare Statements on the client."
[[Image:odbc_config.png|ODBC Config Panel for Prepared Statements]]
Make sure to restart UPS Worldship before you attempt to key in another Sales Order.
===In FedEx===
I'd love to know.
[[Category:Integrations]]

Revision as of 15:57, 5 August 2022

How It All Works

  1. 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)
  2. MySQL users(ups and fedex) exist that only have access to that view.(Already exists as they went out on the AU)
  3. A ODBC data source is configured using the ups or fedex account.
  4. 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.
  5. 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

UPS Worldship Import/Export Menu UPS Map Creation UPS Field Mapping UPS Keyed Import Menu

Problems setting up Worldship

If you've created the ITrack Import Map, and attempt to key in a Salesorder or Invoice # and get this error returned:

Prepared Statements Error

Then you need to open the ODBC Profile that the ITrack Import map is utilizing, open the advanced settings at the bottom, go to the Misc section and check "Prepare Statements on the client."

ODBC Config Panel for Prepared Statements

Make sure to restart UPS Worldship before you attempt to key in another Sales Order.

In FedEx

I'd love to know.

Retrieved from "https://wikido.isoftdata.com//index.php?title=ITrack/Pro/UPS_and_FedEx_Integration&oldid=13709"