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:

  1. Open the Tax Rates Import program (Program List > General Ledger > Tax > Tax Interface).

  2. Enter your required import criteria within the Options pane.

  3. Once you have confirmed that the information within the Details pane is valid, select the Import function.

  4. Upon completion, the Import Results pane is populated and summarizes the records imported.

  5. 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:

  1. Open the Tax Rates Import Direct to ADMTXG program (SYSPRO Button > Run a program > IMPPNH).

  2. 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.

  3. 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.

  4. Select the Import function.

  5. Upon completion, the Process completed section is updated with a summary of the records imported.

  6. Exit the Tax Rates Import Direct to ADMTXG program.

  1. Open the Update Invoice Reprint Tables program.

  2. 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.

  3. Indicate the required GL code and Tax code.

  4. Select the Start Processing 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:

  1. Open the Extended Tax Code Maintenance program (Program List > General Ledger > Tax > Setup).

  2. Select the New function.

  3. Enable the Split code option to enter the state, county, and city abbreviations separately, or disable the option to enter a single geo code.

  4. Depending on your selection of the Split code option, enter the Tax geo code or abbreviations for the State, County, and City.

  5. Enter a Description of the tax code.

  6. Indicate the Effective date.

  7. If freight is taxable, enable the Freight taxable option.

  8. Indicate None, Percentage, or Value at the Tax limits field.

  9. Enter a percentage in the Tax Rate column of the Current Sales Tax Rates pane.

  10. Enter a percentage in the Tax Rate column of the Previous Sales Tax Rates pane.

  11. 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.

  12. 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:

  1. Open the Customers program (Program List > Contact Management > Setup).

  2. Enter or browse for the relevant customer code within the Customer toolbar field.

  3. 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.

  4. 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.

  5. Repeat the above process for the customer’s Ship to address.

  6. 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 listview 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

This lets you save your selections and apply them when next the program is run in automated mode.

This option is only enabled in Design mode (Automation Design).

Form values and defaults are applied at operator level (i.e. they are not saved at either role or group level).

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

This lets you import the data from a file.

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

This indicates that the import file resides on the server.

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.

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.

  • The file is also deleted if you enable this option and select the Review file function.
  • In a client/server environment, both the import file on the server and the temporary import file created on the client are deleted.

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.
Print 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

This copies the lines in the Details pane.

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.

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.

This copies the current row to the end of the data grid.

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.