[ic] Setting up UPS lookup Tables

[Caleb Phillips]

> I've struggled with this long enough and I'm looking for somebody that 
> can help us set up internal UPS lookup tables for the following methods:
> 	Ground
> 	3 day select
> 	2 day
> 	next day air saver
> 	next day air
> 	next day air early am
> 	worldwide standard
> 	worldwide express
> 	worldwide express expedited
> 
> If at all possible I need to get this done this weekend.  We have no 
> trouble doing the standard tables that ship with interchange, but when 
> we try to do a table that is not a default shipping table nothing 
> works.  Any ideas?

I have done pretty much the same thing, but with fedex. The most time
consuming part is bringing in the zone table and the rate tables. Once
this is done, you can do shipping calculation pretty easily. Here is the
usertag I wrote that does the lookup and calculation. Obviously, much of
it is application specific, but I imagine it will help you a great deal.
Just save your version under <interchange dir>/usertags/something.tag

# [fedex_local_diff type='type' zip=zipcode weight='weight'
total='total']
#
# Calculates shipping price based on weight and zone
# 
# Author: Caleb Phillips
# Version: 2002.12.12
UserTag fedex_local_diff Order type zip weight total
UserTag fedex_local_diff Routine <<EOR
sub {
        my($type, $zip, $weight, $total, $opt) = @_;
	$zip='97045' if( !$zip );
 	return 0.3*$total if !($type && $weight && $total);
	
	# (1) shipping type is OVR or 2ND
	# 	compute Fedex OVR or 2ND shipping charge as required
	my ($g_zone, $e_zone, $e_zone_name, $g_zone_name);
       	my $db = $Db{fzones_tbl};
	# w/ express flag
       	my $sql = "SELECT zone, express FROM fzones_tbl WHERE (zip_max
>= $zip)"
		. " AND (zip_min <= $zip)";
        my $dbRef = $db->query($sql);
        foreach my $row (@$dbRef) {
            ($g_zone, $e_zone) = @$row;
        }
	if($e_zone > 12) { 
		$e_zone_name = 'zone13_16'; 
	} elsif($e_zone > 10) {
		# for whatever reason, fedex doesnt cover these zones in PO
		return 0 if( $type eq "fso" );
		$e_zone_name = 'zone11_12';
	} elsif($e_zone > 8) { 
		$e_zone_name = 'zone9_10'; 
	} else { 
		$e_zone_name = 'zone'.$e_zone; 
	}
	$g_zone_name = 'zone'.$g_zone;

	my $int_weight = ceil($weight);

	# 0 is an impossible zone, and marks non-available destination
	return 0.03*$total if !($e_zone && $g_zone);
	
	my $tbl_name = $type."rates_tbl";
	$sql = "SELECT $tbl_name.$e_zone_name FROM $tbl_name WHERE "
		."$tbl_name.weight=$int_weight";
	$dbRef = $db->query($sql);
	my $price=0;
	foreach my $row (@$dbRef) { $price = $row->[0]; }
	return $price;

}
EOR

This is what one entry looks like in my shipping.asc file...you get the
idea. The dont_care thing is decidedly a hack, but it saves the trouble
of putting that bastard mvasp tag on each page.

OVR     Overnight       weight  0       0       e Nothing to
ship!              {'PriceDivide' => "1",}
OVR     Overnight       weight  0       150     f [fedex_local_diff
type="fso" zip='[either][value zip][or][var SHIP_DEFAULT_ZIP][/either]'
weight="@@TOTAL@@" total='[subtotal noformat=1]' dont_care='[mvasp
tables="fzones_tbl fsorates_tbl"][/mvasp]']   {'PriceDivide' => "1",}

> If you can help please let me know ASAP.

That is all I have to give :). Good Luck.

Considering the frequency of this question, maybe I should make a howto
page, or the current local lookup could be expanded. Just a thought...

-- 
-------------------------------------------
| Caleb Phillips                          |
| The Studio Resource                     |
| IT Specialist                           |
|                                         |
| Handcrafted for you in Ximian Evolution |
-------------------------------------------