Occasionally there will be a discrepancy between the tax rates in ASPIRE provided by CCH and what the finance company or customer believes the tax rates to be for a given jursidiction. This article walks through a couple of ways to troubleshoot the issue as well as how to open up a ticket with Wolters Kluwer (providers of CCH) if those two options fail.
Confirm Tax Rate for Location
1. Have the finance company provide the address that they are having an issue with.
2.1 If the finance company has provided a location with a Plus4 already, remove Plus-4 from ZIP Code and perform search.
2.2 NOTE: MelissaData (required for CCH) uses the USPS database for address cleansing, so these should line up.
3. Many states, counties, or cities provide online tools for looking up sales tax rates. Typically I google "[state name] sales tax rate lookup". Use these to confirm the applicable tax rates
3.1 Sales Tax rates in the United States cannot be determined by city and state alone! CIDs, special tax areas, and other provisions add complexity to determining the correct tax area for a location. The Zip Plus-4 is critical to determining what tax area is applicable to a specific location.
3.2 Online sales/use tax lookup tools provided by the state may not provide a breakdown by the various applicable jurisdictions (state, county, city, or local taxes). If they do not, simply compare the total tax rate in ASPIRE to the tax rate in the online tax lookup tool
4. If there is a discrepancy in the ASPIRE provided tax rate and the online tax lookup's tax rate, proceed to the next troubleshooting technique "Review CCH Tax File"
It is possible that CCH is providing the correct rates, but ASPIRE is filtering them out during the tax rate update. The raw data file from CCH contains rates that are not applicable to equipment finance industry - e.g. rental tax rates. It is possible that ASPIRE is filtering them out incorrectly
5. Confirm with finance company what version of the CCH file they are on. This can be found in ASPIRE in Administration > Tax > Tax Data Update (tab). If the finance company is not on the latest version of the tax file, this could be the source of the issue.
6. If the finance company is on the latest version of the file, get a copy of the raw update file
6.1 I believe that IT is responsible for sending out this update to customers. If you are having trouble getting a copy of the raw data file, please reach out to me and I can help you track down a copy.
6.2 The raw file has multiple sub-folders and files
6.2.1 "[CCH Update Number] - Full" - This folder contains the raw text files from CCH
6.2.2 "RepairScripts" - This folder contains the SQL scripts that update the tax rates in ASPIRE. These were created and are owned by LTi.
6.2.3 "Deployment.json" - A text file that explains the ordering of the RepairScripts
7. Confirm CCH geocode in raw file
7.1 In "[CCH Update Number] - Full" > Data, open file Plus4.txt.
7.1.1NOTE: This fixed-length text file has 2,000,000+ lines in it. I find it easiest to first use a text editor to remove all lines for all states except for the location's state, then transfer the remaining records into excel. From excel you can use "Text to Columns" to delineate and filter the records
7.2 Find applicable geocodes for the location
7.2.1 Line position 1-5 is the base ZIP Code (e.g. 68137)
7.2.2 Line position 6-9 is the starting Plus-4 range (i.e. location's plus-4 should be greater than or equal to this)
7.2.3 Line position 10-13 is ending Plus-4 range (i.2. location's plus-4 should be less than or equal to this)
7.2.4 Line position 14-28 is the CCH geocode (the first four charachters should be the state abbreviation and "US" - e.g. "NEUS" for Nebraska locations.)
NOTE: Each state has a "baseline" geocode with a start range of "0000" and an end range of "9999". This only applies to the location if there is no other geocode that applies. Besides the "baseline" geocode, there should be only one geocode that applies to a location.
8. Confirm tax rates
8.1 In "[CCH Update Number] - Full" > Data, open file "Detail.text"
8.2 Find geocodes - drop the state abbreviation found in step 7 above (e.g. "NMUS35001000000" becomes "US35001000000"). There can be multiple lines that apply as these are the specific rates that apply.
8.3 Line position 13-14 is the tax type. ASPIRE only uses the following tax types - 01, 02, 03, 04, 05, U1, U2, U3, U4, U5, 44, 45, 46, 47, 48, 40, 41, 42, U6, U7, and U8 (Canada Only)
8.3.1 You can review "06-Import" in "RepairScripts" folder for further details on how this file is imported.
8.3.2 If there are rates that apply to the geocode, but do not fit the above tax types, please note them.
8.4 Line position 185-198 represents the decimal tax rate.
9. If geocode applicable tax rates do not match expected tax amount, open ticket with Wolters Kluwer (see below). If a tax rate in the raw file is not of the approriate tax type, but you believe it should have been included, then please open a ticket to be reviewed by the product team/development.
10. Reach out to product to see if they have credentials to open support tickets with Wolters Kluwer. NOTE: CCH issues are rare and it is likely that our credentials have been expired.
11. Navigate to Wolters Kluwer's
CCH Support Site. Either create new account or login with existing credentials
11.1 If we do not have active credentials, LeaseTeam's account ID is 4001387539. Please note that, at time of writing, we are actively trying to get our account updated to reflect our change to LTi Technology solutions. You may need to reach out to Accounting to confirm the account ID we receive on our invoices.
12. Open ticket - I have typically open tickets under the product "CCH® Sales Tax Rates & Taxability DB"