Avalara Setup Procedures - Updating Elliott Tables
Release Date:
4/28/23
Version: 8.6 and above
Updating Elliott Tables
Generate States Table
If you do not have state table setup yet, go to Accounts Receivable-> Maintenance-> State File -> Generate
The system comes with a pre-defined table of all 50 states and their abbreviations. All you need to do is to click the Generate menu button and the system will create all state abbreviations and names. This happen very fast and you may notice it as it happen in a flash.
Note the state table contains fields Tax Type & Tax Local which are related to nexus and the rule to charge sale or use tax. These two fields is updated through a separate step in System Utilities-> Avalara/Vertex Interface-> Utilities and choose Create/Update State Tax Codes.
Synchronize Zip Code Table
Elliott system comes with a pre-defined table of all zip codes in USA. If you do not have this zip code table in your system, go to System Utilities-> Zip Code File-> Synchronize. You can verify if any new zip codes added by scrolling to the end of the report. See sample below:
452 Zip Code Added (A)
41,253 Zip Code Changed (C)
21 Zip Code Skipped
41,726 Zip Code Sync.
41,253 Zip Code Changed (C)
21 Zip Code Skipped
41,726 Zip Code Sync.
System will update the zip code file in the DATA folder based on information in <ElliottRoot>\SYZIPCDS.BTR file. Netcellent typically update this file once per major release. This table provides zip codes and city info to ensure first level of address validation with our without using Avalara address validation feature.
Create Avalara Attributes
Go to System File Setup-> Maintenance-> Attribute File -> Create-> Avalara Attributes
Answer Y to the Do you like to proceed? prompt.
This will create the _AVATAXSNAPSHOT, _AVATAXFINAL, _GOODSERVICES, _TAXBREAKDOWN00 to TAXBREAKDOWN05, and _CALCAVALARA attributes.
Setup Default Avalara Attributes
Once the Avalara attributes are created, default attributes should be created for use codes and tax codes.
Define _USECODE Attribute for Customer and Ship-To for Tax Exempt Reason
If some of your customers are tax exempt, it is important you need to identify their Entity Use Code for the exempt reason. You can do so by adding _USECODE attribute to each customer, and if necessary to ship-to if exempt reason different from the customer. This might be a tedious task. Therefore, we allow you to add _USECODE attribute to the customer type. That will then be inherited by the customer unless _USECODE is defined for the customer. If it is necessary, you can import attribute from CSV file through this function: Util-Setup-> System File Setup-> Import -> Attribute Import.
Enter the Avalara Use Code. A valid entry must be entered. A search is available to lookup the appropriate code. If customer is tax exempt because they are a reseller, then enter the code value "G". See sample screen below:
If there is no _USECODE attribute defined for the ship-to, customer and customer type hierarchy structure, even though you still can make order, ship-to or customer non-taxable, but you are at risk of auditing by each state for not providing the reason why the order is not taxable.
Note: You do NOT have to set the TAXABLE use code for
taxable customers. Avalara advises against this. This will force it to
calculate taxes even if they do not need to collect sales tax for the customer.
You should only be concerned with the customers that are tax exempt.
The _GOODSERVICES attribute should be set up to establish the tax code of items by either Product Category, User Defined Code or Item. Setup tax code item by item can be time consuming, therefore, you may consider setup tax code by product category or user defined code which will be inherited by the corresponding item. If it is necessary, you can import attribute from CSV file through this function: Util-Setup-> System File Setup-> Import -> Attribute Import.
Please do not get confused between Avalara's tax code which refers to the classification of items for tax calculation purpose that is normally 8 to 9 digits, and Elliott tax code which refers to the tax jurisdiction which is 1 -3 digits.
Enter the Avalara Tax Code.
A valid entry must be entered. A search is available to lookup the appropriate code. In the below example, the % is the wildcard symbol used by SQL. if you enter CLOTHING, it will be converted to %CLOTHING% automatically which is a contain search for and Avalara tax code description contain the word "CLOTHING" with case insensitive . You can manually enter CLOTHING% which is a begin with search, or %CLOTHING which is an end with search.
Identify Avalara tax code for each item, product category or user defined code can be important if the taxability of your items can vary from state to state. If you do not defined tax code, then the default tax code in Global setup will be used.
Create/Update Elliott State Tax Codes
This section refers to Elliott tax codes, not Avalara tax codes. Go to System Utilities -> Avalara/Vertex Interface-> Utilities and choose Create/Update State Tax Codes. This will create a tax code record for every state as well as a county and a district tax code record. The state tax code record will be created with the state abbreviation plus a "1". The county and district records will be created with the state abbreviation plus a "2" for county and a "3" for city. Sometimes there are more that one taxing jurisdictions at the local level. The calculated taxes are combined and shown in tax code 2 as district taxes.
For example, the following tax codes will be created for California:
- CA1 - CALIFORNIA
- CA2 - CA County and District Tax Code. It does not refer to any specific CA county.
- CA3 - CA City Tax Code. It does not refer to any specific CA city.
In the above example, CA2 and CA3 are place holders and do not refer to any specific county or city. The tax percent in tax code file is not used. The initial Elliott tax estimated based on Zip Tax Codes table which you download from Avalara on the monthly basis. While the Elliott user interface allows 3 tax codes per customer, ship-to and orders, in reality the taxes in each state often consist of State, County, City and District. Our design principle is to combine District tax with County tax and is reflected in the 2nd tax code (e.g. CA2 in above example.)
On the other hand, the following fields in Elliott tax code file are meaningful:
- Account No: Default account from global setup will be populated here. You can manually override if necessary.
- Tax Freight: Since each state can have a different policy on whether freight is taxable or not, this application will update the "Tax Freight" flag in Elliott tax code table. This is necessary for Elliott to estimate tax amount before interfacing with Avalara. You can manually override this flag if necessary. The value of this flag in state tax code (e.g. CA1) will override value in county and city tax code (e.g. CA2 and CA3.) This application will also update the state tax types.
Below is a sample of Create/Update State Tax List:
For example, let’s say Colorado has a nexus rule of $500,000. If an Elliott user does not sell over $500,000 to Colorado, then that user does not need to collect use tax for Colorado. Hence, that user will override the default value “U” in the Tax Type field for state “CO” to “N”. However, the next time when this field is synchronized with Avalara again, the value will be overridden. To set this up correctly, you should set Avalara Sales Tax Only in the ADMIN
application on Avalara portal.
Updating Tax Code Messages
Elliott Full Match - The application was able to match the zip code, state, county and city.
Elliott Zip/State/City Match - The application was able to match the zip code, state and city but not the county.
Error: Invalid Address - The application was unable to match based on zip code, state and city of the address provided.
Error: Foreign Address - The zip code match was not found and the zip code is not numeric and not spaces.
Error: State/Zip Inconsistent - The zip code state and the state of the address being checked do not match.
Error - Zip Code Not On File - The zip code is numeric but it was not found in the SYZIPCDS file.
Update Customer Tax Codes
Go to System Utilities -> Avalara/Vertex Interface-> Utilities and choose Update Customer Tax Codes.
The application will create the _CALCAVALARA attribute for the customer and set the Elliott Zip Checked flag. If the lookup was successful, the Elliott address checked flag will be set to Y.
See sample screen below:
When validating the address via an Elliott lookup, this application will read every customer that is on file and validate if either of the following can be matched:
1.The zip code, state, county and city
2. The zip code, state and city
3. The zip code and state
4. The state
If any of these combinations is on file, the Elliott address checked flag will be set to Y in the _CALCAVALARA attribute. Also, the customer's tax code 1 will be changed to the state that was validated with the zip code lookup. The customer's tax code 2 will be changed to the state abbreviation plus the number 2 and tax code 3 will be changed to the state abbreviation plus the number 3. See sample screen below:
Note the tax rates above come from Zip Tax Codes table as an estimate.
Update Ship-To Tax Codes
Go to System Utilities ->
Avalara/Vertex Interface
-> Utilities and choose Update Ship-To Tax Codes.
This process is similar to updating customer tax codes.
When Validating the address via an Elliott lookup, this application will read every customer that is on file and validate if either of the following can be matched:
1.The zip code, state, county and city
2. The zip code, state and city
3. The zip code and state
4. The state
If any of these combinations is on file, the Elliott address checked flag will be set to Y in the _CALCAVALARA attribute. Also, the ship-to's tax code 1 will be changed to the state that was validated with the zip code lookup. The ship-to's tax code 2 will be changed to the state abbreviation plus the number 2 and tax code 3 will be changed to the state abbreviation plus the number 3.
Update Order Tax Codes
Go to System Utilities ->
Avalara/Vertex Interface-> Utilities and choose Update Order Tax Codes.
This process is similar to updating customer tax codes. Only un-posted orders will be processed in this application.
This application will read every order that is on file and validate if the zip code, state, county and city combination is on file. If the combination is not on file, the application will try to match on zip code, state, and city. If either condition is met, the order's tax code 1 will be changed to the state that was validated with the zip code lookup. The order's tax code 2 will be changed to the state abbreviation plus the number 2 and tax code 3 will be changed to the state abbreviation plus the number 3. This match will be noted on the report as well as in the reference 1 field of the _CALCAVALARA attribute for the order.
If a valid match is found, the application will also attempt to call Avalara to calculate taxes. If the call is successful, tax amounts 1, 2, and 3 will be returned and updated on the order.
Update Recur Order Tax Codes
Go to System Utilities ->
Avalara/Vertex Interface
-> Utilities and choose Update Recur Order Tax Codes.
This process is similar to updating customer tax codes.
This application will read every recurring order that is on file and validate if the zip code, state, county and city combination is on file.
If the combination is not on file, the application will try to match on zip code, state, and city. If either condition is met, the recurring order's tax code 1 will be changed to the state that was validated with the zip code lookup. The recurring order's tax code 2 will be changed to the state abbreviation plus the number 2 and tax code 3 will be changed to the state abbreviation plus the number 3. This is considered a match and this will be noted on the report as well as in the reference 1 field of the _CALCAVALARA attribute for the order.
If a valid match is found, the application will also attempt to call Avalara to calculate taxes. If the call is successful, tax amounts 1, 2, and 3 will be returned and updated on the order.
Programs Added: AVCUSTAS, AVCUSTAP, AVSHIPAS, AVSHIPAP, AVORDHAS, AVORDHAP, AVORDRAS, AVORDRAP
CLS