Thread: zip code tax, five digits

We have set up tax entries for five digit zip codes in our site. This works well for orders that have 5 digit zip codes.

When a customer submits a zip+4 entry, the tax for the five digit zip does not calculate and does not charge the customer tax.

How do I configure the site to only consider the first five digits for zip/tax lookup when a customer enters zip+4?

If you have source code, you can modify the sql statement to get the left 5 digits.

If you don't - like me, you are at the mercy of the developers. I was told they would revisit this, as in my case, I had to turn off the USPS address verification, because that ADNSF feature breaks the tax lookup - precisely because the Zipcode tax table would not match.

I had played around with doing an insert trigger on the customer table to modify the zipcode +4 back to 5 digit only Zipcode, that would be a work around people could do.

See this link to read up on triggers

http://www.eggheadcafe.com/community...s-edition.aspx

If you get it working - Please share !

Best of Luck

Thanks! I appreciate the response, but I think it may be a bit advanced for my skill set.

We actually installed the Avatax integration that was finally released, but it turns out that it doesn't work, either. It eliminates the state value from the customer's address, somehow, so the Avatax integration effectively prevents the site from taking credit cards.

Sounds like I might be stuck with yet another bug without any workaround, and no hope of a fix.

Well, this has been reported to us in support and, I think you're that person I've spoke to about this. I'd like to add a bit more information on it, you are getting "State field in address record is BLANK. That is not allowed!" correct? It seems like that error pops up only when AppConfig: Addins.Enabled set to true but no addins installed at all.

Actually, there is an add-in installed: the Avatax integration that was provided to us by ASPDNSF a few weeks ago.

When a customer has ZIP+4, tax rate is ignored completely. Is there an easy way around this?

First, I wonder if we can add a regular expression to the tax table?

Secondly, I tried to modify the aspdnsf_getZipTaxRate stored procedure to:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER proc [dbo].[aspdnsf_getZipTaxRate]
    @ZipCode nvarchar(10) = null,
    @TaxClassID int = null
  
AS
SET NOCOUNT ON 


SELECT ztr.ZipTaxID, substring(ztr.ZipCode,1,5) AS ztr.Zipcode, ztr.TaxClassID, ztr.TaxRate, ztr.CreatedOn, t.Name TaxClass
FROM dbo.ZipTaxRate ztr with (nolock) join dbo.TaxClass t with (nolock) on ztr.TaxClassID = t.TaxClassID 
WHERE ztr.ZipCode = COALESCE(substring(@ZipCode,1,5), ztr.ZipCode) and ztr.TaxClassID = COALESCE(@TaxClassID, ztr.TaxClassID)

It worked on SQL Server when I passed test parameters. But the tax amount still doesn't show up on either the estimate page or the checkoutpayment.aspx page.

Thanks.

did changing the SPROC work out?

I am clueless at this point. The tax still doesn't show.

Interesting stuff Dayhawk....

Will have to look at this as soon as I can.

Thanks !

Will post anything I figure out.

You need to change the aspdnsf_GetCartSubTotalAndTax stored procedure. I put the Case / IF statements around Country to handle Sales Tax at the Postal Code level internationally. And we were seeing both US and United States in our address table.

Change:
if @Country is not null
Begin
SELECT @CountryID = CountryID FROM dbo.Country WHERE Name = @Country
SELECT @StateID = StateID FROM dbo.[State] WHERE Abbreviation = @State
SELECT @ZipCode = ZipCode FROM Ziptaxrate WHERE zipcode = @PostalCode
End

To:
if @Country is not null
Begin
If @Country = 'US' OR @Country = 'United States' SET @PostalCode = Left(@PostalCode,5)
SELECT @CountryID = CountryID FROM dbo.Country WHERE Name = @Country
SELECT @StateID = StateID FROM dbo.[State] WHERE Abbreviation = @State
SELECT @ZipCode = ZipCode FROM Ziptaxrate WHERE zipcode = @PostalCode
End

AND Change:
left join dbo.ZipTaxRate zr with (nolock) on zr.ZipCode =
CASE
WHEN @ZipCode is not null then -- get the pass state
@PostalCode
ELSE -- use state
a.Zip
END

To:
left join dbo.ZipTaxRate zr with (nolock) on zr.ZipCode =
CASE
WHEN @ZipCode is not null then -- get the pass state
@PostalCode
ELSE -- use state
CASE a.Country WHEN 'US' THEN Left(a.Zip,5) WHEN 'United States' THEN Left(a.Zip,5) ELSE a.Zip END
END

Hello mikemike1,

The problem with the AvaTax plug-in call is that it adds a dummy shipping item to the cart collection to send to AvaTax to see if shipping is taxable. Because this isn't a real item, it causes the State cannot be blank error you're seeing. The answer is, you have to remove it after the call is done. Add one line of code below:

In the prices.cs file in the core, in the TaxTotal method, see below:

// create a dummy cart item to be added to the collection that contains the shipping as an item
//not in multi-store 9.0.1.3 code
CartItem ciShipping = new CartItem();
ciShipping.ProductID = 0;
ciShipping.ThisShoppingCart = cartItems.First().ThisShoppingCart;
ciShipping.CartType = CartTypeEnum.ShoppingCart;
ciShipping.IsTaxable = ShippingTax(shipCost, ThisCustomer.PrimaryShippingAddress) > System.Decimal.Zero;
ciShipping.Price = shipCost;
ciShipping.Quantity = 1;
ciShipping.Shippable = false;
ciShipping.SKU = "SHIPPING";
ciShipping.TaxClassID = AppLogic.AppConfigUSInt("ShippingTaxClassID");

collectionToSend.Add(ciShipping);

AppLogic.UseAddIn<ITaxRates>((tRates) => { TaxRatesHook(tRates, ThisCustomer, collectionToSend); });
tax = ThisTaxTotal;

// bug fix - remove the shipping item before going on
cartItems.RemoveAt(cartItems.Count - 1);

Wonderful! Thanks for sharing this here, pligon. This is much appreciated.

Cheers!

Dear edlaplante,

Where is that stored procedure located? I looked in the SP folder to no avail.

thanks!!