[ic] UPS - multiple package - 150+lbs ( SOLUTION )

[Mark Weaver]

Hi List,

Over the past week and a half I've been working feverishly on getting 
Interchange to process orders using UPS Ground that are multiple 
packages with a cumulitive weight over 150 pounds. There was a lot of 
useful information contributed, but for what ever reason I was unable to 
get any of it to work correctly. I found that doing internal lookups to 
be the most promising route to follow, however I was unable to make any 
use of the "UserTag" routines that were contributed. I do plan to 
continue working on those just because I hate giving up on something 
when it comes to coding.

The solution I've come up with is an internal lookup.  I've taken the 
Ground.csv rate chart that comes with Interchange and extended it from 
150 to 999 lbs so that now I can do internal lookups on freight without 
having to do realtime lookups through one of the UPS servers. This rate 
chart is made up 100% of rates from UPS and is accurate across all 
zones. Believe me... it has been thrashed thoroughly to make sure the 
shipping charges it presents when processing an order are correct. I 
will tell you that I had some help from our in house accountant, who of 
all people, knew the correct algorithm to use to work the numbers that 
UPS has posted on their website. http://www.ups.com

Weights from 1 - 150 are the current UPS Residential rates for zones 2 - 
8. ( this same process can be used for UPS commercial as well. ) From 
151  - 199 I had to manually extend those rates based on the percentage 
difference between the previous rate in that zone for that next weight 
of the same zone. This was done for zones 2 - 8. The rates for 151 - 199 
had to be extended in the manner because UPS does not, for some unknown 
reason, provide a rate chart for this weight range that I'm aware of.

For the rates in the weight range of 200-999 I made use of the UPS 
Hundred Weight rate table that can be found at the UPS website and 
calculate the rest of the rate table that exists in Ground.csv. The 
break down looks like this:

For Weights 200 - 999

weight      2            3           4           5            6 
    7            8
===============================================
200
   |           21.50    28.55    35.55    42.90    50.20     57.55     64.80
499
===============================================
500
   |           20.00    26.55     33.15    40.05    46.90     53.60 
60.40
999
===============================================

Contrary to the way the table reads that one finds at the UPS site for 
hundred weight shipping, the correct manner in which to process this 
information is thusly. and keep in mind that the weight being plugged 
into the formula increases by one pound for each successive row. ( since 
I'm in zone 2 those are the numbers I'll use as examples. )

Formula for Calculating Rates:

	weight/100 x Rate

In other words like this:

	for 200 pounds zone 2
	( 200 / 100 ) x 21.50 = 43.00
	( 201 / 100 ) x 21.50 = 43.22
	etc....

If you going to do as I did and lay this all out in an excell spread 
sheet using the above formula it would look something like this: given 
the "weight" column is column "A" and "n" = some row number where the 
weight 200 appears.

		zone 2						zone 3					zone 3       ... zone 8
	=sum((A201 / 100) * 21.50)      =sum((A201 / 100)* 28.55)       etc.... 
               .....

After you get the first row done for the weight of 200lbs simple extend 
that down to 499. Now follow the same previous steps above for 500 - 999 
using the appropriate rates that appear in the table above and you're done!

Internal, independant lookups using current UPS rates for 200 through 
999 lbs. The only caveat that I see in this is that when the rates 
change you've got to change your Ground.csv file, but then thats not any 
more difficult then just plugging in the new number for the 200lb row 
and the 500lb row and then extending them down and generating a new 
Ground.csv file.

If you'd like to see the actual excell file I've got with all the rates 
in it that we're using email me off list and I'll send it to you. tarred 
of course! I guess Excell is good for something afterall.  ;)

mark at americanmicroinc.com
-- 
Mark Weaver
American Micro - Webmaster
1-800-558-2058