Tax Rates Import
Exploring
This program only applies to the Tax Connector - Avalara.
This program lets you import sales tax rates manually using an ASCII file provided by Avalara.
SYSPRO then uses the updated sales tax rates for all applicable tax calculations throughout the system (i.e. invoices, sales orders, purchase orders, etc. will use the updated tax rates when calculating tax requirements).
This program is accessed from the Program List of the SYSPRO menu:
- Program List > General Ledger > Tax > Tax Interface
The Avalara platform is a powerful and easy-to-use API that helps businesses automate the calculating, storing, auditing and reporting of transactional taxes.
The CertCapture component helps you maintain compliance by creating, validating, storing and managing sales tax exemptions and reseller certificates.
The tax code is linked to a percentage rate that is applied to the value of a transaction so that the relevant amount of tax can be determined.
Tax Connectors provide a connection for establishing a direct link between SYSPRO and compatible cloud-based tax calculation systems (e.g. Avalara and Vertex).
Tax Connectors manage the export of data to these systems so that quarterly and annual sales tax returns can be produced for state filing purposes.
Tax rate refers to the percentage of sales tax charged in a particular area.
For example:
The sales tax rate is 8.0% in Costa Mesa, California.
Starting
You can restrict operator access to programs by assigning them to groups and applying access control against the group (configured using the Operator Groups program).
The following configuration options in SYSPRO may affect processing within this program or feature, including whether certain fields and options are accessible.
The Setup Options program lets you configure how SYSPRO behaves across all modules. These settings can affect processing within this program.
Setup Options > Company > General
- Nationality code
Setup Options > Tax > Company Tax Options
- Tax system
Setup Options > Tax > Sales and Use Tax
- Sales and use tax system required
- Interface type
Solving
Depending on how your have configured your tax connector, the sales tax rates are either imported manually using the Tax Rates Import program, or automatically by Avalara. If you use the latter, then the following process is not required.
To manually import the sales tax rates, proceed as follows:
-
Open the Tax Rates Import program (Program List > General Ledger > Tax > Tax Interface).
-
Enter your required import criteria within the Options pane.
-
Once you have confirmed that the information within the Details pane is valid, select the Import function.
-
Upon completion, the Import Results pane is populated and summarizes the records imported.
-
Exit the Tax Rates Import program.
All your customers must be manually linked to their appropriate tax codes (based on their ship-to addresses) so that sales tax is calculated correctly. However, this is not required if your tax is automatically updated by Avalara.
Depending on how your have configured your tax connector, the use tax rates are either imported manually using the Tax Rates Import Direct to ADMTXG program, or automatically by Avalara. If you use the latter, then the following process is not required.
To manually import the use tax rates, proceed as follows:
-
Open the Tax Rates Import Direct to ADMTXG program (SYSPRO Button > Run a program > IMPPNH).
-
Enter the location and name of the file you want to import.
Although tax code files can be imported from any folder on the server, we recommend always placing them in the \Work folder.
-
Indicate if the tax code description is City, State or City, County.
Take care when making this selection, as tax codes will be imported according to this setting and certain reports will sort/print information based on this.
-
Select the Import function.
-
Upon completion, the Process completed section is updated with a summary of the records imported.
-
Exit the Tax Rates Import Direct to ADMTXG program.
-
Open the Update Invoice Reprint Tables program.
-
Indicate the Invoice date range required for the system to pull the tax values recorded within the ArTrnSummary table to update the corresponding re-print tables.
We recommend setting the From date as the date when SYSPRO was initially implemented and the To date as the date on which you implemented the Tax Connector - Avalara.
Reprint records created after the switch to Avalara contain U in the LimitFlag field. Therefore, the program checks this field first and doesn’t make any changes to the database for records already defined as U. This ensures that data is not negatively impacted if the program is executed more than once.
-
Indicate the required GL code and Tax code.
-
Select the function.
Upon completion, a summary window is displayed with the number of processed records, errors found (if any) and invoices converted successfully.
Most, if not all, of the tax codes used by your customers will be imported from Avalara, either through the manual import (Tax Rates Import) or the DLL.
The tax rate import facility is not available for the Tax Connector - Vertex.
However, if you need to add or update a tax code outside of the import, you can use the Extended Tax Code Maintenance program.
To add a new tax code, proceed as follows:
-
Open the Extended Tax Code Maintenance program (Program List > General Ledger > Tax > Setup).
-
Select the New function.
-
Enable the Split code option to enter the state, county, and city abbreviations separately, or disable the option to enter a single geo code.
-
Depending on your selection of the Split code option, enter the Tax geo code or abbreviations for the State, County, and City.
-
Enter a Description of the tax code.
-
Indicate the Effective date.
-
If freight is taxable, enable the Freight taxable option.
-
Indicate None, Percentage, or Value at the Tax limits field.
-
Enter a percentage in the Tax Rate column of the Current Sales Tax Rates pane.
-
Enter a percentage in the Tax Rate column of the Previous Sales Tax Rates pane.
-
Optional... Select the Tax Effective Rates toolbar function to view or maintain the extended tax rate history using the Browse on Extended Tax Code History program if required.
An unlimited number of historical rates of tax previously applied to your tax codes can be automatically stored here.
-
Select the Save function to save the newly added tax code.
Additions and changes made within the Extended Tax Code Maintenance program only come into effect after you exit SYSPRO.
To assign customer tax codes, proceed as follows:
-
Open the Customers program (Program List > Contact Management > Setup).
-
Enter or browse for the relevant customer code within the Customer toolbar field.
-
Within the Contact Details pane, navigate to the State (extended tax code) field of the Sold to address section and select the browse function.
This opens the Browse on Extended Tax Codes program.
-
Select the appropriate tax code, followed by the Select toolbar function.
Alternatively, you can create a new tax code using the Add function if required.
-
Repeat the above process for the customer’s Ship to address.
-
Save your changes and exit the program.
Using
-
Fields on a pane are sometimes removed by default. You can reinstate them using the Field Selector option from the context-sensitive menu (displayed by right-clicking any field in the pane). Select and drag the required fields onto the pane.
The captions for fields are also sometimes hidden. You can select the Show Captions option from the context-sensitive menu to see a list of them. Click on the relevant caption to reinstate it to the form.
If the Show Captions option is grayed out, it means no captions are hidden for that form.
-
Press Ctrl+F1 within a list view or form to view a complete list of functions available.
Referencing
This validates the contents of the import file and imports records according to your selections within the Options pane
Field | Description |
---|---|
Start import |
This imports the selected sales tax rates file and updates the tax rates throughout the system for all applicable tax calculations. Upon completion, the Import Results pane is populated and summarizes the records imported. |
Data source |
|
Import method |
From file
This lets you import the data from a file. Use data grid
This lets you enter the import data directly into the grid of the Details pane You can paste the contents of an Excel spreadsheet into the grid. A blank line is created as the first line in the grid because by default, focus is set on the first editable line for all import program grids. |
File location |
Server
This indicates that the import file resides on the server. Client
This indicates that the import file resides on the client. |
File name |
Indicate the name and full location of the sales tax rates file you want to import, if you selected From file at the Import method option. Although the tax rates file can be imported from any folder on the server, we recommend always placing it in the \Work folder. Review file
This loads the contents of the import file into the Details pane, which lets you validate the lines on the grid prior to importing the file. The Import function is enabled only when all lines are valid. The import file is deleted if you enable the Delete file after use option. |
Options |
|
Select tax code description |
Indicate if the tax code description is City, State or City, County. Tax codes are then imported according to this setting and certain reports will sort or print information based on this. |
After processing completed |
|
Print the Details |
This generates a report of the contents of the Details entry grid. |
Email the Details |
This generates an email of the contents of the Details entry grid. |
Print the Import Results report |
This generates a report of the contents of the Import Results pane. |
Email the Import Results report |
This emails the contents of the Import Results pane. |
Email recipients |
This indicates the email addresses of recipients to whom you want to email the information displayed in the output pane. |
Delete file after use |
Indicate whether you want to delete the import file after all records are successfully imported.
|
Close application |
This exits the program once processing is complete. Don't select this if you want to be able to view the information on screen when processing is complete, or you want to print or email the contents of the Details and Import Results panes. |
This pane includes the following details from the import file:
-
State
-
County or ZIP code
-
City
-
Effective date
-
Description
-
First rate 1 current
-
First rate 1 previous
-
First rate 2 current
-
First rate 2 previous
-
First rate 3 current
-
First rate 3 previous
-
Message
-
Freight taxable
-
Verazip county code
The Validation Status column indicates if the line is valid for importing.
Option | Description |
---|---|
Validate | This verifies whether there are any errors in the grid, without actually importing records. Exclamation marks in the Validation status column indicate that one or more fields in the line are invalid. An explanatory error message is displayed when you hover your mouse pointer over the exclamation marks. |
This lets you print the contents of the import file, or changes made within the grid. | |
Export to Excel | This outputs the data from the Details pane to an Excel spreadsheet. |
Search text | This lets your search on the text within the grid view. |
Clear | This clears the contents in the Search text field. |
Edit Options |
Copy
This copies the lines in the Details pane. Paste (all columns)
Ensure that you have data for all the columns that can appear in the data grid before selecting this option. When you select to Paste (all columns), the date you are pasting must be in the format CCYYMMDD. Although no validation errors are displayed, the date is not converted into your SYSPRO date format. Paste (visible columns only)
This pastes data into the columns that currently appear in the data grid, in the sequence that they appear. When you select to Paste (visible columns only) and the date you are pasting is not in the CCYYMMDD format, you are prompted for the date format. The program correctly inserts it into the grid view in the SYSPRO date format. Duplicate
This copies the current row to the end of the data grid. Find and Replace
This lets you search the data within the grid view and replace it with an entry of your choice. |
This displays a summary of the results of the validation/import.
This information can be printed or emailed as long as you didn't select to Close the application in the After processing completed section.
Copyright © 2022 SYSPRO PTY Ltd.