WA state destination based sales tax

[kidsngarden 0]

Hello,

I use oscommerce as my cart and am based in WA state. Effective July 1, 2008 I will have to charge sales tax based on where my products are being sent too, not my cities tax rate. So If I was sending to Seattle, I would have to charge the Seattle tax rate, not my own. Obviously this sets up a whole lot of problems for me as the oscommerce cart is not set up to handle 100's of tax rates within one state.

 

Read more about destination based sales tax here.

 

Aside from the fact that I think this is nuts and going to be a whole lot of work, I was wondering if oscommerce is doing anything to be able to accomodate this?

 

Thanks,

Bethany

Capella's Garden Soap Co.

www.capellasgarden.com

[rwest 0]

Hello,

I use oscommerce as my cart and am based in WA state. Effective July 1, 2008 I will have to charge sales tax based on where my products are being sent too, not my cities tax rate. So If I was sending to Seattle, I would have to charge the Seattle tax rate, not my own. Obviously this sets up a whole lot of problems for me as the oscommerce cart is not set up to handle 100's of tax rates within one state.

 

Read more about destination based sales tax here.

 

Aside from the fact that I think this is nuts and going to be a whole lot of work, I was wondering if oscommerce is doing anything to be able to accomodate this?

 

Thanks,

Bethany

Capella's Garden Soap Co.

www.capellasgarden.com

I agree that this is nuts, and a whole lot of work. This will take a better programmer than me to implement, however this is my 2 cents worth.

My initial request for info from the state's help link for destination based sales tax (streamlined sales tax) verified that using tax lookup based on city name or zipcode is not detailed enough. Zip+4 will be needed. Others have written posts to this forum on the challenge of getting customers to use zip+4. I have also asked for info on the number of zip+4 codes which will be included in the downloadable file available from the state after April 1, 2008. Still awaiting an answer, but I am am sure it will be enormous.

 

Another way to do the zip+4, rather than require the customer to enter it, is to use the USPS api to do a direct lookup based on address. I believe that WA state may also provide this feature, although I don't know if it will be anything more than a manual lookup (which they have now).

 

I also don't want to require all my customers to enter zip+4, only those in Washington state. Also, any lookup based on the zip+4 should happen for WA only. The changes need to apply only to WA customers, not others. This is more complex than it seems on the surface.

 

I only hope that someone steps in and develops a contribution that works. I haven't found one on this forum yet. There are some pieces of the puzzle, several on zip+4 functionality for example. If anyone reading this knows of contribs that I should look at, please let me know.

 

Without some help, I may have to consider two other options. First, I may just stop shipping within Washington altogether. This would be unfortunate, but I don't do all that much business within the state. Several of my customers are close enough to do a local pickup, and that doesn't require any tax changes.

 

Second, and I don't know if this is legal, or practical, but possibly charging the lowest tax rate of any location in the state, and then subsidizing the balance due to the state out of my own pocket my be cheaper/easier than this ridiculous law. I am fairly sure that charging anyone more than the actual tax rate is illegal, even with the intention of refunding the overage.

 

thanks,

Ron

[Jet200 1]

I'm a WA based business as well but I very rarely have in-state sales. The workaround I would use is to install one of the Order Editor contributions and just manually adjust the tax rate on each order. Like I said, this works if you have a small amount of in-state orders. If you do volume, it's going to be a pain. For me it's just not worth the effort to accomodate such a silly law with a bunch of time spent on development.

[♥Java Roasters 5]

There are several county based tax contributions, you just need to modify one to fit your needs. Then post it for others to use.

WA is not the first state to do this.

[rwest 0]

Yes, but I haven't any idea how to take it from a county based to a zip+4 based lookup. County based lookup is about as far as anyone has gone. Everyone in my situation, so far as I can determine, has apparently given up, not considering it to be worth the time and expense.

[♥Java Roasters 5]

http://addons.oscommerce.com/info/1240

 

http://addons.oscommerce.com/info/2792

 

Both use the zip code to get the tax rate.

[rwest 0]

Thank you for those. It gives me a starting point.

 

I will need to know how to create the .sql zip tax table from whatever format is provided by the state on April 1st. Also, I expect conservatively some hundreds-of-thousands of zip+4 codes. I don't have any experience with loading .sql files that big. Any potential problems due to the sheer size?

[lindascott121 0]

I am thinking that the contribution 2792 (referred to by the previous poster) could easily be modified for us Washingtonians. I will try to look at it a little more closely this weekend.

Linda

[rwest 0]

I am thinking that the contribution 2792 (referred to by the previous poster) could easily be modified for us Washingtonians. I will try to look at it a little more closely this weekend.

Linda

Did you get anywhere with the New York Ziptax contrib? I have been playing with it, and I think it might work fine for Washington.

 

The one thing I can't get working, however, is taxes on Shipping and Handling. As soon as I install this mod, my shopping cart no longer applies tax to shipping. Can anyone help?

 

Ron

[rwest 0]

I'm a WA based business as well but I very rarely have in-state sales. The workaround I would use is to install one of the Order Editor contributions and just manually adjust the tax rate on each order. Like I said, this works if you have a small amount of in-state orders. If you do volume, it's going to be a pain. For me it's just not worth the effort to accomodate such a silly law with a bunch of time spent on development.

I think that an order editor is a good idea. I just need to know what to do to prevent my Washington orders from just being processed immediately via Authorize.net. I have the AIM contrib installed, and I usually never even see the order until it is already paid for. Anyone have any ideas?

 

It is not a problem to manually submit orders to Authorizenet, as I have a low number of in-state orders. I would want to place all Washington State orders into some sort of 'hold' status. Does anybody have any idea how to do that?

 

Ron

[keith03 0]

Hello all,

 

I too am a washington merchant. I am also a PHP programmer. I have a client who needs this destination-based sales tax module for OS commerce. I also will need it for my web site as well.

 

I am interested in helping out to get this working. What are your successes on this module? I agree that this http://addons.oscommerce.com/info/2792 module may be the better starting point

 

Do we really need zip+4? That could be a pain. The USPS has a DB of Zip+4 available for Washington (or any state) for $22.50 per year. The problem is, it is raw data that can be used to validate and add the last four digits to the ZIP code. It is not an usable application of any kind. I was hoping the city name would be enough.

.

Well, I will read more about this.

 

Here are some resources that could help:

USPS raw ZIP+4 data

http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm

 

WA State Destination-Based SalesTax Page

http://dor.wa.gov/Content/FindTaxesAndRate...ed/default.aspx

 

I hope to hear back from you all about this. Let me know how I can help.

[keith03 0]

Okay, upon further examination, the solution provided here http://addons.oscommerce.com/info/2792 does appear to have enough to work with except for the +4 part of the zip code.

 

The WA DOR site has a DB available (for free) that contains every valid address range and tax code for the entire state of Washington. It is 42MB in size! The state data file would allow a lookup by address (number and streetname) and zipcode to obtain these fields:

 

http://dor.wa.gov/Content/FindTaxesAndRate...scriptions.aspx

 

The location code can then be used to obtain the tax rate from the Location code and rate table. Here are the fields of this DB:

http://dor.wa.gov/Content/FindTaxesAndRate...ableFields.aspx

 

All of this information and downloadable databases can be found here:

http://dor.wa.gov/Content/FindTaxesAndRate...tdownloads.aspx

 

I think the first step would be to be able to take a Washington address and obtain the location code and +4 information. Also, there would need to be an easy update funtion to allow the store owner a way to easily update the lookup database once per quarter. I can work on those pieces if someone can help me integrate it into the osCommerce system.

 

Lets do this! Together we can make this happen. I am sure of it.

 

More soon.

[zogthefuzzy 0]

Lets do this! Together we can make this happen. I am sure of it.

 

More soon.

 

I can't do much coding, but I can volunteer to test this tax system on my new osCommerce website. Just let me know what to do. (And yes...this is a most outrageous, unfair, ridiculous excuse for a law!)

[keith03 0]

I can't do much coding, but I can volunteer to test this tax system on my new osCommerce website. Just let me know what to do. (And yes...this is a most outrageous, unfair, ridiculous excuse for a law!)

 

 

Thanks! We will need to test it. For sure.

 

Update:

 

I have installed all the software for testing. I am currently evaluating the NYC Zip code tax module. It works but needs modifications to work with WA data.

 

The NYC DB has approximately 2800 entries for zip code tax rate lookups. To do this correctly according to the state of Washington, we will need a DB of 732,405 records to determine the tax rate for a given destination address. :blink: No seriously!

 

There should be an easier way. WA DOR does offer an online tool but it is not automated; it is manual (no good). If we asked the user for his/her Zip+4 it would be easier but who knows that stuff? We could ask the user for their latitude/longitude! LOL

 

In order to do a proper match against this DB, we will need to obtain the house/address number and street name as well as the supplied zip code. This will give us the location code that is then used to look up the tax rate for that region.

 

I am donating (GPL open sourcing) some code I wrote for parsing entered addresses. The code accepts a typed address line and finds the house number, street direction prefix, street name, street type, street post direction, unit type(if any) and unit number. It may need some work for some addresses but it works pretty well as is.

 

 

I am going to modify the admin portion of this ziptax module to allow uploads of new state data files. They state that this must be done once a quarter. I think they just want us all to move away. :lol: I will make it so that a store owner can simply DL the current DB from the WA DOR site and upload it to the store for decompression and installation. This may only work on accounts with sufficiently large hosting quotas.

 

If anyone has a better idea, i'm all ears! If anyone wants to help, by all means! Hop aboard!

 

I will keep you all updated.

[mtveten 0]

integrating a +4 lookup based on a customers address is simply not feasible for carts on shared or even smaller dedicated servers due to size & server load during operation. For a small business like us (1+million annually) equipment upgrades to perform the operation in a timely manner are not cost effective.

 

Sending the +4 lookup request to USPS via their API was a great idea however their service agreement prevents us from using it for anything other than USPS shipping services.

 

The existing methods used by others cover only 5 digit zip codes or subuerb (county) selection. Neither of these solutions are workable because they either don't factor city taxes, cities that overlap counties, and/or zip codes that overlap cities and/or counties.

 

For us to correctly use a destination tax method we have to 1.) apply a state tax, 2.) apply a county tax, 3.) apply a city tax, 4.) apply applicable transit taxes. The only address function that will give us what we need to apply all the correct taxes due to county/zip/city/trasit overlaps is zip+4. Creating a simply lookup table for zip+4 is simply enough however how many residents know their +4 zip code, I for one don't and after quick poll around the office I wasn't able to find anyone that did, so expecting our customer to provide it is not realistic.

 

With the problem clearly defined the question is how to best deal with it.

 

Aside from the obvious move or stop shipping within state we are left with:

 

1. charge by zip+4 and provide a link where they can go to find theirs. although doable and accurate requiring customers to enter information they don't have means they don't order.

 

2. charge by 5 digit zip using the high/low values provided by the DOR. Using the high zip value means some customers would be overcharged by as much as 1.3% due to count & city overlap withing certain zip codes; charging based off the low would mean we would eat up to 1.4% for those same customers. How would we report it correctly?

 

3. use a subzone system for counties and cities (selection fields) to add up the first 3 tax levels and charge based off that. With this option customers would have to know their county and it still wouldn't be able to ascertain current RTA taxes for county/city combination where they vary but it would have a .9% tighter tolerance that the zip only.

 

 

 

If anyone else has an idea on how to tackle the issue I would love to hear it.

[keith03 0]

integrating a +4 lookup based on a customers address is simply not feasible for carts on shared or even smaller dedicated servers due to size & server load during operation. For a small business like us (1+million annually) equipment upgrades to perform the operation in a timely manner are not cost effective.

 

Sending the +4 lookup request to USPS via their API was a great idea however their service agreement prevents us from using it for anything other than USPS shipping services.

 

The existing methods used by others cover only 5 digit zip codes or subuerb (county) selection. Neither of these solutions are workable because they either don't factor city taxes, cities that overlap counties, and/or zip codes that overlap cities and/or counties.

 

For us to correctly use a destination tax method we have to 1.) apply a state tax, 2.) apply a county tax, 3.) apply a city tax, 4.) apply applicable transit taxes. The only address function that will give us what we need to apply all the correct taxes due to county/zip/city/trasit overlaps is zip+4. Creating a simply lookup table for zip+4 is simply enough however how many residents know their +4 zip code, I for one don't and after quick poll around the office I wasn't able to find anyone that did, so expecting our customer to provide it is not realistic.

 

With the problem clearly defined the question is how to best deal with it.

 

Aside from the obvious move or stop shipping within state we are left with:

 

1. charge by zip+4 and provide a link where they can go to find theirs. although doable and accurate requiring customers to enter information they don't have means they don't order.

 

2. charge by 5 digit zip using the high/low values provided by the DOR. Using the high zip value means some customers would be overcharged by as much as 1.3% due to count & city overlap withing certain zip codes; charging based off the low would mean we would eat up to 1.4% for those same customers. How would we report it correctly?

 

3. use a subzone system for counties and cities (selection fields) to add up the first 3 tax levels and charge based off that. With this option customers would have to know their county and it still wouldn't be able to ascertain current RTA taxes for county/city combination where they vary but it would have a .9% tighter tolerance that the zip only.

 

 

 

If anyone else has an idea on how to tackle the issue I would love to hear it.

 

 

You have very good points. This is a serious problem for small business. On one hand, we want to comply to the state tax requirements and on the other hand, we need to be able to manage the solution.

 

The overhead of the address look up is large. For example, the state of Washington supplies an address database of close to a million records. Well, only 726,228 actually. This is huge! They offer a lookup tool but it is not automated. But, unless we take our business elsewhere, we have to come up with something.

 

I think the state is doing a good job of supplying the needed data to comply with their new tax laws. However, they should offer an automated system that can be used by multiple shopping cart programs. Perhaps even at a small fee (small!! like 20$ a year).

 

In the meantime, rather than balk at the problem, we should be working on solutions.

 

I have some working admin code to allow easy (as in less painless) updates of the address database supplied by Washington State Dept. of Revenue. It allows a person to upload a 7-8MB zip file to the oscommerce admin and then it decompresses the data file (about 42MB of data) and inserts it into a DB table.

 

This is a lot of data but goes in pretty fast (about 2-3 minutes). I think a smaller oscommerce store with say, a 100MB hosting account, could perform this operation server-side.

 

The real problem is when the store has many orders coming in at once or on a server with many hosted shopping carts all accessing their address look up data. This could really impact a servers performance.

 

However, holding an address database and doing searches to determine the tax amount is, currently, the only logical method to comply with the new tax law. I am pretty sure they are'nt going to take too kindly of short-cuts or number padding.

 

I am halfway there. I have the admin updater portion worked out (read: "roughed out"). I am going to work on the address lookup portion of the code to add tax rate lookups to be performed on a persons given ship-to address. Of course I will try to make it fast and efficient. Perhaps, I can make it as fast as I can and then others, in the spirit of open source software, can make it better, faster.

 

Of course there are going to be times when an address entered cannot be found in the database. In this case, it is not possible to assign the proper tax rate to the purchase+shipping amount. The cart will have to "guess" or not allow the sale to proceed. Both are undesirable but if it is a rare occurance, we could handle this by first guessing a high or average amount, charging the customer that amount and then manually processing the order to report it correctly. Any thoughts on this situation?

 

I think it can be done. At least it will be a solution until something better comes up. Us Washingtonians have until July 1st, 2008 to come up with something.

 

I am going to do this. Please stop me! I am beggin you! :lol: We need a better solution...

[mtveten 0]

I agree that address lookup is the only fully compliant answer but given the expected server load problems, I am going to focus on a tiered subzone system. When you get your lookup solution working I would love to give it a try and see if your servers can handle it.

 

Mark

[keith03 0]

I agree that address lookup is the only fully compliant answer but given the expected server load problems, I am going to focus on a tiered subzone system. When you get your lookup solution working I would love to give it a try and see if your servers can handle it.

 

Mark

 

So far, so good. I have done some preliminary query optimizations and have found the searches to be very fast. I am not processing any addresses yet simply doing manual queries to get indexes working well.

 

Here is my table structure for the big table (address data):

 

CREATE TABLE `wa_zip4` (
 `Id` int(11) unsigned NOT NULL auto_increment,
 `addr_low` int(6) unsigned default NULL,
 `addr_high` int(6) unsigned default NULL,
 `odd_even` char(1) default NULL,
 `street` varchar(255) default NULL,
 `state` char(2) default NULL,
 `zip` int(6) unsigned default NULL,
 `plus4` int(4) unsigned default NULL,
 `period` varchar(10) default NULL,
 `code` int(6) unsigned default NULL,
 `rta` char(1) default NULL,
 `ptba_name` varchar(40) default NULL,
 `cez_name` varchar(40) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ZipIndex` (`zip`),
 KEY `LowIndex` (`addr_low`),
 KEY `HighIndex` (`addr_high`),
 FULLTEXT KEY `StreetIndex` (`street`)
) ENGINE=MyISAM AUTO_INCREMENT=726229 DEFAULT CHARSET=latin1;

 

 

Here is a sample query to determine the location code for a given address:

select * from wa_zip4 
where (1818 between addr_low and addr_high) 
and zip = 98502 
and match (street) against ('evergreen') group by code;

 

This query finds one valid row within 0.02 seconds. Pretty good!

 

Due to the added indexes, the upload/decompress/insert phase now takes about 8 minutes from 2-3. Still not too painful since it only needs to be performed once a quarter.

 

Of course, it is likely that we will get results with multiple matches and will have to process them and determine which one is the best match.

 

These performance results are very preliminary but promising. I will continue to keep you all updated. I will also start a contribution section on the oscommerce site soon and post a link here.

 

I am looking forward to having people test this solution out.

[zogthefuzzy 0]

These performance results are very preliminary but promising. I will continue to keep you all updated. I will also start a contribution section on the oscommerce site soon and post a link here.

 

I am looking forward to having people test this solution out.

 

I GREATLY appreciate the updates, keith03, and of course am ready to test it when you need me to.

[Curley61 0]

I GREATLY appreciate the updates, keith03, and of course am ready to test it when you need me to.

 

I just saw these postings and wanted to add my two cents. Kansas has had this same ridiculous law for several years. However, after talking to them they try to tell me that there are many large businesses in Kansas that manually look up every address for the sales tax rate. I told her she was crazy. I figure I have enough margin to deduct the sales tax from the total amount and declare it. In the last quarter I only had one in state customer.

 

My point is that Kansas has a WSDL that can be coded directly into the shopping cart. The way it works is that when the customer is checking out, it goes out to this site and pulls in the tax rate for their address. My only problem is that I am not enough of a programmer to be able to integrate the WSDL into my cart.

 

Have any of you checked to see if WA state has anything similar? It would save you all this time and effort of programming or loading almost a million records into your databases. I had to really search to find it on the Kansas website.... but it was there. So, I would say that if Kansas has it, then Washington may or may install it soon. I would check into that....WSDL.

[lemonbean 0]

Does anyone know of a solution for sales tax for the state of California? The sales taxes are determined by the city name, not by zip code.

 

I'm just setting up my online store using ShopSite. Their tech support directed me to OSCommerce to try to solve the tax look-up problem. I'm not clear what a "solution" would be. A "plug-in"? "script"? a small piece of code? I understand that I need my shopping cart (Shopsite) to look up the tax due based on the recipient's address, but I'm not sure how to add a script or database lookup even if one exists.

 

The tax table from the state of California over 1700 line items. There are actually only about 12 tax rates, but 1700 cities.

 

Thanks for any help.

[keith03 0]

I just saw these postings and wanted to add my two cents. Kansas has had this same ridiculous law for several years. However, after talking to them they try to tell me that there are many large businesses in Kansas that manually look up every address for the sales tax rate. I told her she was crazy. I figure I have enough margin to deduct the sales tax from the total amount and declare it. In the last quarter I only had one in state customer.

 

My point is that Kansas has a WSDL that can be coded directly into the shopping cart. The way it works is that when the customer is checking out, it goes out to this site and pulls in the tax rate for their address. My only problem is that I am not enough of a programmer to be able to integrate the WSDL into my cart.

 

Have any of you checked to see if WA state has anything similar? It would save you all this time and effort of programming or loading almost a million records into your databases. I had to really search to find it on the Kansas website.... but it was there. So, I would say that if Kansas has it, then Washington may or may install it soon. I would check into that....WSDL.

 

 

Thanks for your input. I may call the Department of Revenue next week and ask if they plan to have such an automated system available any time soon. So far as I can tell, they only have the online form which is manual. I agree that it would be crazy for anyone to manually process this. Besides, why would you even need a shopping cart system if you were going to manually calculate sales tax? Hmmm...

 

As soon as I have an address lookup working, I will post it for testing. My idea is to have it only perform the lookup if the person has a Washington state address as the ship-to address in the order. This would be the only time it would need to be run anyway. ;-)

 

What it will do is: take the ship-to address line (the first one) and parse it for the address number, street name, street type, and any supplied pre/post directionals. It will use these parsed "chunks" to search the DB for the address ranges that match the street and zip code from the ship-to address supplied. Upon a sucessful find it will obtain the location code and last four digits of the zip code. The location code and zip+4 information can then be used to look up the sales tax rate in the rates table. Sounds easy huh? :blink:

 

I have the DB (and updater admin code), the parser and the New York zip tax hack to work with. Preliminary DB queries show promising performance figures. I just hope the state's database is thourough and not error ridden.

[keith03 0]

Hello all,

 

I have created a contribution in the community section. I have uploaded the code I have working so far for testing.

 

This is very much test code and not to be used in any production server. Please, only testers should use this right now. I will not take any responsibility if you hose your shopping cart. I recommend installing a fresh copy of osCommerce and then applying the modifications.

 

In the near future, there will be a production copy for your use, just hang in there.

 

I would like to have input on the functioning of this modification. Currently, if an address cannot be found, it doesn't charge any sales tax. Of course this is not the preferred behavior. Also, if multiple rates are found for an address (which is more rare), it just picks the first one. Also, not a desired behavior. It will need to go through the list of rates found and find the one that matches the address using more information supplied by the shopper.

 

I called the Washington State Dept. of Revenue today and asked about what to do if an address/rate is not found. He said, "...use the city name rate lookup...". I think the best solution for an address/rate that is not found is to tax the customer some average state tax rate and mark the sale for manual processing for proper reporting. This will minimize lost revenue and make sure the sale completes for the customer. We certainly don't want the sale to be halted if they make a typo in the address/zip fields.

 

Well, I would love to post a link to the contribution here but it seems that the addons.oscommerce.com site is currently unavailable. I will edit this post with the link when I find the server is available again.

 

In the meantime, I will continue testing and adding more functionality.

 

Any input from you on this would be greatly helpful.

[keith03 0]

Well, I would love to post a link to the contribution here but it seems that the addons.oscommerce.com site is currently unavailable. I will edit this post with the link when I find the server is available again.

 

 

Well, I was unable to complete the edit so, here is the link to the contribution:

http://addons.oscommerce.com/info/6013

 

Remember, this is only for testing right now! Be careful with any production site!

[BrianMillar12 0]

Well, I was unable to complete the edit so, here is the link to the contribution:

http://addons.oscommerce.com/info/6013

 

Remember, this is only for testing right now! Be careful with any production site!

 

I should have read the forums before adding this mod, I've been scrambling to get this figured out myself since my employer has me hanlding his web site and shopping cart.

 

I got it installed on the production cart that is nowhere near ready for launch, ie. still have to get the spreadsheets from the office to populate it and get them to provide me with content for the main pages, but the link I get within the admin interface is: BOX_TAXES_WA_TAXES, that link does bring in the upload area just fine regardless. I haven't tested the upload process on it, now thinking it would be better to start with a clean install from another directory, of which I now have.

 

I moved the correct files over, and when I ran the sql originally, I ran the entire sql file all at once including the marker tags. What I did differently on the second one was cut and pasted each sql table/statement individually without the comments. The first one when I did that outputted the code within the phpmyadmin window which was strange, the second one appeared to import them just fine. I'm rather new to using phpmyadmin obviously.

 

Now, when I log into the admin interface on the fresh one I have no link added within it so can't see if it installed correctly and calling the php file directly in the browser, yeilds no results, I don't want to change the folders permissions, which is likely the cause in that regard. Any help would be apprieciated.