Tax Connectors
SYSPRO 8 caters for connecting to cloud-based tax calculation systems (e.g. Avalara and Vertex) which allows you to remain compliant when facing complex tax scenarios. Seamless interaction is made possible using a pre-built connection that establishes a direct link between SYSPRO and the relevant platform.
Exploring
Tax Connectors manage the export of data from within SYSPRO that can be used by compatible tax calculation systems in quarterly and annual sales tax returns for state filing purposes.
Without an interactive account to third party tax software such as Avalara and Vertex, the Tax Connectors feature is limited to the manual import of tax rate tables from the third-party tax software to SYSPRO.
SYSPRO sends transaction data to Avalara or Vertex and receives the tax total in return, enabling the following capabilities:
-
Calculate tax requirements.
-
Record tax transactions in Avalara and Vertex.
-
Use the tax transactions recorded in Avalara and Vertex for reporting purposes.
-
Record invoices.
-
Create, maintain and store Avalara tax exemption certificates (only applicable to Avalara).
-
Perform other tax-related functions.
-
Simplified sales and use tax processes for all US states (from tax determination on transactions to exemption certificate management and tax returns).
-
Monthly updates to ensure constant application of current tax rates.
-
Analysis capability by state, county and city.
-
Enhanced VAT process efficiency (from indirect tax determination to reporting).
-
Improved accuracy and reliability of tax calculations at the federal, state, and local levels in the US.
The programs related to this feature are accessed from various locations in SYSPRO:
-
SYSPRO Ribbon bar > Setup
-
Program List > General Ledger > Tax
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.
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.
Vertex solutions are powered by technology that combines pre-built integrations to leading business applications, a global content database supporting major indirect tax types, and flexible deployment options spanning on-premise, cloud or hybrid.
The Vertex platform is desktop software that provides all US and Canadian sales, use and rental tax rates at the component part level, and it also includes a combined rate.
Starting
To use this feature, the following module(s) must be licensed within your software license agreement:
-
Sales Orders
-
Tax Connector - Avalara or Tax Connector - Vertex
To use this feature, the following setup option(s) must be configured within the Setup Options program:
Company General
Setup Options > Company > General
-
Nationality code
This must be defined as USA.
Company Tax Options
Setup Options > Tax > Company Tax Options
-
Tax system
This must be defined as USA tax by advanced geocodes.
Sales and Use Tax
Setup Options > Tax > Sales and Use Tax
-
Sales and use tax system required
This option must be enabled.
-
Interface type
This must be defined as Avalara or Vertex.
-
Default ship from information
The correct shipping address must be defined against these fields.
During the update process to SYSPRO 8 2021 R1 or later, if you previously used the legacy Tax Interface system integrated with Avalara or Vertex, the system migrates your information automatically using the SYSPRO Third Party Tax Files Conversion program as follows:
-
The system verifies if any entries exist within the AdmUsaZipCodes table of the system-wide database:
If no entries exist, and the legacy ISAM file SHPFZ5.DAT/IDX is found within the \Work folder, then the entries contained within the SHPFZ5.DAT/IDX file are uploaded into the AdmUsaZipCodes table.
The original SHPFZ5.DAT/IDX file is retained in its original location.
-
The AdmCompanyControl table is checked to see whether the setup options for the legacy third party integration exist. If the prior configuration is found and has not already been converted, then the system converts this as required.
-
The system checks for the existence of the legacy Avalara table (TxrParExemptCert) to establish any certificate credentials for previous linkage to Avalara CertCapture:
If this is found, all entries are moved to the AdmCompanyControl table within the system-wide database.
The original TxrParExemptCert table is deleted.
-
The system verifies if any Avalara or Vertex passwords exist and converts them as required.
-
The legacy State Filter file (TXRFFL.DAT located in the \Data folder) is checked to establish if any state filtering was previously configured for integration with Avalara:
If the file contains existing entries, these are uploaded to the AdmTaxFltrUsState table within the company database.
The original TXRFFL.DAT file is deleted.
-
The legacy Country Filter file (TXRFFC.DAT located in the \Data folder) is checked to establish if any country filtering was previously configured for integration with Avalara:
If the file contains existing entries, these are uploaded to the AdmTaxFltrCountry table within the company database.
The original TXRFFC.DAT file is deleted.
-
The legacy Product Class Filter table (TxrProdClsFltr) is checked to establish if any product class filtering was previously configured for integration with Avalara:
Existing entries within the legacy table are moved to the AdmTaxFltrPcls table of the company database.
The original TxrProdClsFltr table is dropped from the database.
-
The legacy ZIP code range file (TXRFRZ.DAT/IDX located in the \Data folder) is checked to establish if any ZIP code filtering was previously configured for integration with Avalara or Vertex:
If the file contains existing entries, these are uploaded to the AdmTaxZipRange table within the company database.
The original TXRFRZ.DAT/IDX file is deleted.
-
The system checks for the existence of the legacy Tax Base table (TxrTaxNetGross) in the company database:
If this is found, all entries are moved to the AdmTaxNetGross table.
The original TxrTaxNetGross table is deleted.
-
The system checks for the existence of the legacy Avalara Tax Exemption Certificate cross reference table (TxrCertInfo) in the company database:
If this is found, all entries are moved to the AdmTaxCertXref table.
The original TxrCertInfo table is deleted.
-
The AdmCompanyControl table is updated to indicate that the conversion is complete.
As part of the migration process, any existing triggers, events and/or Electronic Signatures that called the various legacy tax programs are updated with the new program codes.
The following cross reference indicates the legacy versus new programs:
Legacy Program | New Program | Program Description |
---|---|---|
SHPPZP |
IMPPZP |
ZIP Codes Maintenance |
TXRBUS |
IMPTAT |
Tax Interface |
TXRPSP |
IMPPSP |
Filter by Product Class |
TXRPCI |
IMPPCI |
CertCapture Import |
TXRPCS |
IMPPCS |
Exemption Certificates Setup |
TXRPDW |
IMPPDW |
Update Invoice Reprint Tables |
TXRPEC |
IMPPEC |
Tax Exemption Certificates |
TXRPEL |
IMPPEL |
Tax Interface Error Log Print |
TXRPID |
IMPRID |
Tax Rates Import |
TXRPNG |
IMPPNG |
Tax Base Indicator |
TXRPRZ |
IMPPRZ |
Define Filter by ZIP Codes |
TXRPSU |
IMPPSU |
Tax Interface Setup |
TXRPSW |
IMPPSW |
Define Filter by Country |
Any additional customization to call programs (such as VBScripts and custom hyperlinks) is not verified or updated. Therefore, any customization you have that is not covered during the migration must be updated manually.
You can secure this feature by implementing a range of controls against the affected programs. Although not all these controls are applicable to each feature, they include the following:
- You restrict operator access to activities within a program using the Operator Maintenance program.
- You can restrict operator access to the fields within a program (configured using the Operator Maintenance program).
- You can restrict operator access to functions within a program using passwords (configured using the Password Definition program). When defined, the password must be entered before you can access the function.
- You can restrict access to the eSignature transactions within a program at operator, group, role or company level (configured using the eSignature Setup program). You can restrict access to the eSignature transactions within a program at operator, group, role or company level (configured using the Electronic Signature Configuration Setup program). Electronic Signatures provide security access, transaction logging and event triggering that gives you greater control over your system changes.
- 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).
- You can restrict operator access to programs by assigning them to roles and applying access control against the role (configured using the Role Management program).
Solving
The Tax Connectors feature is limited to the North American markets at present.
Any additional customization to call programs (such as VBScripts and custom hyperlinks) is not verified or updated during the migration process.
Therefore, any customization you have that is not covered during the migration must be updated manually.
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.
The following indicates important things to consider when configuring your geographic areas:
-
Foreign addresses outside of USA and Canada
The Geographic Area code 4N is reserved exclusively for use with foreign addresses to indicate any country outside of the USA and Canada.
-
Canada addresses
The Geographic Area code CN is reserved for use with Canadian addresses.
Canadian postal codes are 6 digits, therefore the system will also verify the postal code attached to the transaction. If a 5 digit postal code is located, then it is assumed that the transaction is for a USA address and will be submitted as a US transaction.
-
Canadian Tax
To invoke sales tax for Canada, the following is required:
-
The word Canada must exist in address line 5 of the Ship-to address for the applicable customers.
Or...
-
The Geographic Area code for the customer must be CN.
Or...
-
The customer's Nationality Code must be CAN.
-
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.
The Special calculations fields within the Tax Interface Setup program are used to calculate tax on a different amount from what is actually on a sales order line.
For example:
In some states a vendor may sell particular items at a discount, but is still required to pay sales tax on the full retail price.
The special calculation will be applied only to invoices for customers who are in the customer class defined in the Tax Interface Setup program, and whose ship-to zip code falls within the defined range.
The special price is then calculated by replacing the price paid by the customer with the price code defined in the Tax Interface Setup program.
If you don't receive tax rate updates directly from the tax integration software and want to view or print any errors that may have occurred during the posting of transactions, then you can access the error log as follows:
-
Open the Tax Interface Error Log Print program (Program List > General Ledger > Tax > Tax Interface).
-
Within the Tax Interface Error Log Print Options pane, enter the date range that you want to include in the report.
-
Optional... Indicate whether you want the report to be sequenced by transaction date or sales order.
-
Select the Start Processing function.
The results are displayed within the Tax Interface Error Log Print pane.
-
Indicate if you want the log file to be deleted or not.
-
Exit the program.
Using
When using Tax Connectors, the system requires certain custom form fields to be defined before configuring the tax connector.
A Customer Usage Type custom form field (e.g. CusUsg) is required against the ArCustomer table and/or the ArMultAddress table and must be added within the Customers program.
If the Customer Usage Type is defined for both the ArCustomer and ArMultAddress, then the one against the ArMultAddress takes precedence.
Why is it required?
The system requires this to identify if any special tax rules must be applied for the customer (e.g. Federal Government, Charitable Organization, Agricultural Production, etc.).
If this applies in your company, then operators also require the ability to select an Entity/Use Code type against this field.
Although you don't have to use an Entity/Use Code, the set up of this custom form field is still mandatory.
How are the Entity/Use Codes populated?
SYSPRO supplies the information for the Entity/Use Codes in the IMPPUT.CBL file, which populates the custom form table during the initial Avalara setup.
Any changes to the IMPPUT.CBL file only take effect when the service is refreshed.
What is the process to add the custom form field?
-
Create and add the Customer Usage Type field to the ArCustomer table:
-
Open the Customers program (Program List > Contact Management > Setup) and navigate to the General Details pane.
-
Right click on the General Information header and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Custom Usage Type
(or any name meaningful to your company)
-
Field Name = CusUsg
-
Column Name = CusUsg
-
Field Type = Alpha
-
Field Length = 25
-
Default = {Spaces}
-
Validation type = Lookup (not mandatory)
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
Drag and drop the newly created CusUsg field onto the General Details pane under the General information section and close the Field Selector window.
-
Exit the Customers program.
-
-
Create and add the Customer Usage Type field to the ArMultAddress table:
-
Open the Multiple Ship to Addresses program (Program List > Accounts Receivable > Setup).
-
Right click on the Ship to address information header and select the Field Selector option.
-
Repeat the custom form field creation steps above (1.c to 1.e) to create an identical custom form field.
-
Drag and drop the newly created CusUsg field onto the Multiple Ship to Address Details pane under the Ship to address information section and close the Field Selector window.
-
Exit the Multiple Ship to Addresses program.
-
The AvaTax Code custom form field (i.e. AvaTax) applies to a company’s stocked items and is therefore required within the Stock Code Maintenance program.
Why is it required?
In situations where an item being sold requires special taxing rules to be applied, the AvaTax Code will identify the type of product on the stock code level, so that the proper tax rate can be applied.
For example:
Digital goods fall into a number of categories that may have different tax rates: digital pictures are considered for taxes separately from streamed music.
The AvaTax Code field may be left blank, but if a value is entered, it must be a valid AvaTax Code from Avalara.
Avalara determines if the item is taxable or not taxable, based on the AvaTax Code submitted in the corresponding custom form field. Therefore, the standard Tax Code indicator on the sales order detail line doesn't affect tax calculations by Avalara.
What is the process to add the custom form field?
-
Open the Stock Code Maintenance program (Program List > Inventory > Setup) and navigate to the Sales Details pane.
-
Right click on the Tax Codes header and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = AvaTax Code
(or any name meaningful to your company)
-
Field Name = AvaTax
-
Column Name = AvaTax
-
Field Type = Alpha
-
Field Length = 15
-
Validation type = None
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
-
Drag and drop the newly created AvaTax field onto the Sales Details pane under the Tax Codes section and close the Field Selector window.
-
Exit the Stock Code Maintenance program.
The AvaTax Code for Merchandise custom form field (i.e. NSAvTx) must be associated with sales order merchandise lines (stocked or non-stocked) and is therefore required within the Sales Order Entry program.
Why is it required?
In situations where an item being sold requires special taxing rules to be applied, the AvaTax Code for Merchandise identifies the type of product at the sales order line level so that the proper tax rate can be applied.
For stocked items only:
If the AvaTax Code for Merchandise field is left blank on the sales order line, then the system will send the value of the AvaTax Code field defined against the stock code entered on the line. However, if the AvaTax Code for Merchandise field on the sales order line is populated, then it takes precedence over the AvaTax Code field.
What is the process to add the custom form field?
-
Open the Sales Order Entry program (Program List > Sales Orders > Sales Order Processing) and enter a customer in order to access the Order Line > Stocked pane.
-
Right click on any caption within the Order Line > Stocked pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = AvaTax Code for Merchandise
(or any name meaningful to your company)
-
Field Name = NSAvTx
-
Column Name = NSAvTx
-
Field Type = Alpha
-
Field Length = 15
-
Validation type = None
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
-
Drag and drop the newly created NSAvTx field onto the Stocked pane.
-
Navigate to the Non-stocked Line pane and right click on any caption to select the Field Selector option.
-
Drag and drop the newly created NSAvTx field onto the Non-stocked pane.
-
Exit the Sales Order Entry program.
The Entity use code custom form field (i.e. CusUsL) must be associated with stocked sales order lines and is therefore required within the Sales Order Entry program.
Why is it required?
Defining this field as TAXABLE against an order line enables you to indicate that the order line must be treated as taxable, even if the sales order is non-taxable.
What is the process to add the custom form field?
-
Open the Sales Order Entry program (Program List > Sales Orders > Sales Order Processing) and enter a customer in order to access the Order Line > Stocked pane.
-
Right click on any caption within the Order Line > Stocked pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Entity Use Code (Line)
(or any name meaningful to your company)
-
Field Name = CusUsL
-
Column Name = EntUseCodLin
-
Field Type = Alpha
-
Field Length = 25
-
Validation type = Lookup (not mandatory)
-
-
Select the Edit hyperlink at the Configure field.
-
From the Custom Form Lookup window, select the New toolbar function to add a new item:
-
Enter the Item as TAXABLE.
-
Enter the Description as TAXABLE LINE.
-
Save the entry and close the Lookup Maintenance window.
-
-
Close the Custom Form Lookup window.
-
Exit the Custom Forms Entry program.
-
-
Drag and drop the newly created CusUsL field onto the Stocked pane.
-
Exit the Sales Order Entry program.
The AvaTax code for miscellaneous custom form field (i.e. MscAvx) is required against non-merchandise sales order lines (i.e. freight or miscellaneous) and is therefore required within the Sales Order Entry program.
Why is it required?
In some situations, sales tax must be calculated for freight and other miscellaneous charges. Therefore, the AvaTax code for miscellaneous custom form field indicates to Avalara which charges are taxable so that tax can be determined properly.
What is the process to add the custom form field?
-
Open the Sales Order Entry program (Program List > Sales Orders > Sales Order Processing) and enter a customer in order to access the Order Line > Freight pane.
-
Right click on any caption within the Order Line > Freight pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = AvaTax Code for Miscellaneous
(or any name meaningful to your company)
-
Field Name = MscAvx
-
Column Name = MscAvx
-
Field Type = Alpha
-
Field Length = 15
-
Validation type = None
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
-
Drag and drop the newly created MscAvx field onto the Freight pane.
-
Navigate to the Misc Charge pane and right click on any caption to select the Field Selector option.
-
Drag and drop the newly created MscAvx field onto the Misc Charge pane.
-
Exit the Sales Order Entry program.
The Warehouse custom form field (i.e. NStkWh) is required against non-stocked sales order lines and is therefore required within the Sales Order Entry program.
Why is it required?
Certain taxing authorities require that a shipment’s origin address be used to determine the correct taxing method. This can be problematic for companies that ship made-to-order items (i.e. non-stocked) from multiple warehouse locations.
Since standard SYSPRO only allows a single non-stocked warehouse (and therefore a single origination address) the system will check each non-stocked line on a sales order for the NStkWh custom form field, where the operator can enter a standard SYSPRO warehouse code for the non-stocked item. The system then sends the address of the indicated warehouse to Avalara for the sales order line.
The new field will have a browse button to select any standard SYSPRO warehouse during sales order entry.
What is the process to add the custom form field?
-
Open the Sales Order Entry program (Program List > Sales Orders > Sales Order Processing) and enter a customer in order to access the Order Line > Non-stocked pane.
-
Right click on any caption within the Order Line > Non-stocked pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Caption, Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Warehouse
-
Field Name = NStkWh
-
Column Name = NStkWh
-
Field Type = Alpha
-
Field Length = 10
-
Validation type = Lookup (Not mandatory)
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
-
Drag and drop the newly created NStkWh field onto the Non-stocked pane.
-
Exit the Sales Order Entry program.
The Send to Avatax custom form field (i.e. Sn2Avx) is required against sales order headers and is therefore required within the Sales Order Entry program.
Why is it required?
This field is required in order to indicate that system must send the sales order to Avalara for tax calculation.
Therefore, if the field is defined as N for a sales order, then the order is not sent to Avalara and the tax is set to zero. Any other value within this field will result in the system sending the order to Avalara.
What is the process to add the custom form field?
-
Open the Sales Order Entry program (Program List > Sales Orders > Sales Order Processing) and navigate to the Order Header pane.
-
Right click on any caption within the Order Header pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Send to AvaTax
(or any name meaningful to your company)
-
Field Name = Sn2Avx
-
Column Name = Send2AvaTax
-
Field Type = Alpha
-
Field Length = 1
-
Default = Y
-
Validation type = List
-
-
Select the Edit hyperlink at the Configure field.
-
From the List of Valid Items window, enter the following:
-
Value 1 = Y
-
Value 2 = N
-
-
Select the Save function.
-
Exit the Custom Forms Entry program.
-
-
Drag and drop the newly created Sn2Avx field onto the Order Header pane.
-
Exit the Sales Order Entry program.
The Buyer's VAT ID custom form field (i.e. ARCTID) is required within the Customers program.
Why is it required?
When the ARCTID custom form field is entered on the customer master record, the system will identify the field as the buyer's VAT ID.
What is the process to add the custom form field?
-
Open the Customers program (Program List > Contact Management > Setup) and navigate to the Customer Details pane.
-
Right click on the Customer information header within the Customer Details pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Buyer's VAT ID
(or any name meaningful to your company)
-
Field Name = ARCTID
-
Column Name = CustomerTaxId
-
Field Type = Alpha
-
Field Length = 30
-
Default = {Spaces}
-
Validation type = None
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
-
Drag and drop the newly created ARCTID field onto the Customer Details pane.
-
Exit the Customers program.
You can apply the Country ID custom form field (i.e. Country) against customers as an alternative to defining the line number for the Country field of the Tax Interface Setup program.
What is the process to add the custom form field?
-
Open the Customers program (Program List > Contact Management > Setup) and navigate to the Customer Details pane.
-
Right click on the Ship to Address header within the Contact Details pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Country ID
(or any name meaningful to your company)
-
Field Name = CounID
-
Column Name = Country
-
Field Type = Alpha
-
Field Length = 40
-
Default = {Spaces}
-
Validation type = None
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
-
Drag and drop the newly created Country field onto the Contact Details pane.
-
Exit the Customers program.
You can also add the {Country} custom form field within the Order Header pane of the Sales Order Entry program to indicate the same information at sales order level.
You can apply the Coupon Apportionment custom form field (i.e. CpnApr) within the Sales Order Entry program to denote a miscellaneous line with a coupon value. This value is then used to proportionally reduce the order line's values.
What is the process to add the custom form field?
-
Open the Sales Order Entry program (Program List > Sales Orders > Sales Order Processing) and enter a customer in order to access the Order Line > Misc Charge pane.
-
Right click on any caption within the Order Line > Misc Charge pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Coupon Apportionment
(or any name meaningful to your company)
-
Field Name = CpnApr
-
Column Name = CouponApportn
-
Field Type = Alpha
-
Field Length = 1
-
Default = N
-
Validation type = List
-
-
Select the Edit hyperlink at the Configure field.
-
From the List of Valid Items window, enter the following:
-
Value 1 = Y
-
Value 2 = N
-
-
Select the Save function.
-
Exit the Custom Forms Entry program.
-
-
Drag and drop the newly created CpnApr field onto the Misc Charge pane.
-
Exit the Sales Order Entry program.
By default, the system sends the SYSPRO Company code (e.g. EDU1) to the tax integration platform when processing transactions.
Alternatively, you can apply the Company against Branch custom form field (i.e. AvxCo) against AR branches within the AR Branch Maintenance program and enable the Use company code against branch option within the Tax Interface Setup program.
The system then replaces the SYSPRO Company code with the code entered in the AvxCo custom form field against the branch of the sales order header.
This is useful if you have a single company in Avalara or Vertex, but multiple companies within SYSPRO.
What is the process to add the custom form field?
-
Open the AR Branch Maintenance program (Program List > Accounts Receivable > Setup).
-
Right click on any caption within the Branch Details pane and select the Field Selector option.
-
From the Field Selector window, select the New function to launch the Custom Forms Entry program.
-
Select the New toolbar function and enter the following information within the Field Properties pane:
The text entered in the Field Name and Column Name fields must be entered exactly as it appears below.
-
Caption = Company against branch
(or any name meaningful to your company)
-
Field Name = AvxCo
-
Column Name = AvataxCompanyCode
-
Field Type = Alpha
-
Field Length = 4
-
Default = {Spaces}
-
Validation type = None
-
-
Select the Save Field function and exit the Custom Forms Entry program.
-
-
Drag and drop the newly created AvxCo field onto the Branch Details pane.
-
Exit the AR Branch Maintenance program.
The following process explains the configuration required to use the Tax Connectors feature:
-
Open the Setup Options program and navigate to the Company Tax Options form (Setup Options > Tax > Company Tax Options):
-
Define the Tax system option as USA tax by advanced geocodes.
SYSPRO requires at least one extended tax code defined against customers within the Customers program when the Tax system option is defined as USA tax by advanced geocodes. Therefore, you can configure a generic extended tax code with a zero (0) tax rate within the Extended Tax Code Maintenance program.
-
Within the Company for tax codes file field, indicate the company database to be updated when using the Tax Connectors.
Tax rates are up-to-date throughout SYSPRO modules, but only one company’s database holds the updates.
-
Define the Sales tax calculation option as Invoice level.
-
-
Navigate to the Sales and Use Tax form (Setup Options > Tax > Sales and Use Tax):
-
Enable the Sales and use tax system required option.
-
Optional
Enable the Apply USA 3rd party tax in Sales Orders if you want the system to call the tax connector platform to immediately calculate the tax and total values displayed for every sales order line added or maintained within the Sales Order Entry and Point of Sale Entry programs.
This may slow down the sales order process as it adds a performance overhead. In addition, enabling this option can result in higher costs as every query transaction is generally charged for by the tax connector platforms.
Alternatively, leave the option disabled to calculate the tax based on the advanced geo codes (which map to the imported tax rates) when querying the sales order values.
-
Define the Interface type option as Avalara or Vertex as required.
-
Indicate the correct shipping address within the Default ship from information fields.
If this is not defined correctly, the integration to the Tax Connectors platform will fail as the tax on non-stocked items, freight and miscellaneous charges is calculated according to this information.
-
Vertex users only:
Optionally indicate the Identification level, User company and User division as applicable.
The User company and User division fields are free-form and used to accumulate all tax information against the specification.
-
Enable the Update tax for this company option.
This ensures that the company's tax database is automatically updated by the Tax Connectors platform.
-
Select the Run Setup option to continue the configuration using the Tax Interface Setup program.
-
-
Once in the Tax Interface Setup program, ensure that the Integration Software toolbar field reflects the tax connector you require.
-
When using the Tax Connector - Avalara, the following configuration is required within the Avalara pane:
-
Define the following options with your specific details:
-
AvaTax URL
-
Account number
-
License key
-
Timeout
-
Default tax code for freight
-
Default tax code for misc charges
-
-
Optional
Use the Filter options to exclude certain states, countries or product classes from submission to Avalara if required (i.e. the invoice is not sent through to Avalara when you invoice from a warehouse within an excluded state).
-
Define the Location code source as required to indicate which information must be inserted in the Avalara header.
-
Ensure to define the Date format for AvaTax as you require it to be rendered in Avalara.
If this is not configured correctly, failures will occur when you submit your taxes.
-
Select the Update hyperlink at the Update invoice reprint tables field to launch the Update Invoice Reprint Tables program.
This is required to ensure that existing data in the following tables (that was processed before linking to Avalara) is updated to allow for the accurate re-print of taxes for SYSPRO AR Invoices:
-
Sales Order USA Tax Reprint (SorUsaTaxRep)
-
MDN Reprint Dispatch Note Master (MdnMasterRep)
-
MDN Reprint Consolidated Dispatch Master (MdnMasterRepCon)
We recommend that you perform a backup of these tables before proceeding.
From the Update Invoice Reprint Tables program, proceed as follows:
-
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 Start Processing function.
Upon completion, a summary window is displayed with the number of processed records, errors found (if any) and invoices converted successfully.
-
-
Once you have concluded the above mentioned steps, select the Test Connection toolbar function to ensure all entries are correct and that you can connect to Avalara successfully.
-
-
When using the Tax Connector - Vertex, the following configuration is required within the Vertex pane:
-
Configure the following options with your specific details:
-
Vertex URL
-
Connect to Vertex
-
Login and Password (This applies if you selected Login/Password at the Connect to Vertex option)
-
Trusted ID (This applies if you selected Trusted ID at the Connect to Vertex option)
-
Timeout
-
Vertex department
-
-
Once you have configured your connection details, select the Test Connection toolbar function to ensure all entries are correct and that you can connect to Vertex successfully.
-
-
Within the Company Details pane:
-
Configure the options as required.
-
Optional
Enable the Use customer/invoice presentation length option to use the presentation length when passing customer and invoice numbers to the Tax Connectors software. Alternatively, the full 15-character numeric number is used when this option is disabled.
-
Optional
Indicate the customer class, price code and zip code range for any special tax calculations you require.
-
-
Within the Address pane, configure how addresses are formatted on sales orders (Ship-to Address (Customer)) and warehouse records (Ship-from Address (Warehouse)), as per your requirements.
This indicates how the integration will pass the address lines through to Avalara or Vertex, as sales tax is determined by city and state, so the interface must be able to determine where to find this information.
Ensure that the addresses defined against your company, customers and warehouses match this configuration to ensure that the mapping works correctly and no errors occur when integrating with Avalara or Vertex.
For example:
Ship-to Address (Customer)
Address line number for street
2
Address line numbers for city and state
City and State in separate lines
Address line for city
3
Address line for state
4
Country
5
Ship-from Address (Warehouse)
Address line number for street
2
Address line numbers for city and state
City and State in separate lines
Address line for city
3
Address line for state
4
Country
5
Use the country name as entered
Disabled
Recommendations:
-
If you are using the Tax Connector - Vertex, we recommend enabling the Use the country name as entered option, as Vertex uses the actual country name.
-
Enable the Use ZIP code to determine city and state option to allow SYSPRO to determine the city and state based on the ZIP code to ensure correct details are passed through to Avalara or Vertex.
This is useful if sales order headers or warehouse delivery addresses are not always formatted the same way. The system can then cross-reference the customer’s or warehouse's zip code with the zip code table to determine the city and state.
-
-
Select Save and Exit.
-
You must have valid company, warehouse and customer addresses to ensure that invoices are passed through to the Tax Connectors platform correctly. Therefore, ensure that the positioning of your address lines are configured correctly within the following programs:
These addresses must match up with your configuration of the Address pane fields within the Tax Interface Setup program or errors will occur when posting transactions.
-
Your Company address is defined within the Address form of the Setup Options program (Setup Options > Company > Address).
-
Customer addresses are defined within the Contact Details pane of the Customers program (Program List > Contact Management > Setup).
-
Warehouse addresses are defined within the Delivery Address pane of the Warehouse Maintenance for Stock Code program (Program List > Inventory > Setup).
If the zip code is incomplete or only contains five digits, ensure to include the full nine-digit zip code if required, as some states have multiple tax rates.
This process only applies to the Tax Connector - Avalara.
Ensure that you have a valid connection to CertCapture to avoid failed transactions when you add, change or delete a customer:
-
Open the Exemption Certificates Setup program (Program List > General Ledger > Tax > Tax Interface).
-
Capture your connection details.
-
Select the Test Connection function to ensure all entries are valid.
-
Save and exit the program.
This process only applies to the Tax Connector - Avalara.
You must configure an eSignature trigger to ensure that the Tax Exemption Certificates program is automatically launched when you add, change or delete a customer within the Customers program.
-
Open the eSignature Setup program (Program List > Administration > Electronic Signatures).
-
Define the Access control of the following transactions as Log Only:
-
AR Customer added
-
AR Customer changed
-
AR Customer deleted
-
-
Against each of these transactions, select the Configure hyperlink to define the required trigger as follows:
-
From the Logging and Trigger Options pane, enable the Transaction successful option under the Triggers section.
-
Select the Setup Trigger function to launch the Trigger Setup program:
-
Within the Maintain Trigger pane, define the Type as Run any program.
-
Enter IMPPEC within the Program field.
-
Select the Save function and exit the program.
-
-
Select the Apply function to save your trigger against the transaction and close the screen.
-
-
Exit the eSignature Setup program and restart SYSPRO.
This process only applies to the Tax Connector - Avalara.
The eSignature triggers against the AR Customer added¸ AR Customer changed and AR Customer deleted transactions must be configured within the eSignature Setup program prior to this process.
After adding, changing or deleting a customer in the Customers program, the Tax Exemption Certificates program is launched when you save your changes. Proceed as follows:
-
Confirm the entries within each field and indicate if there are any states which must be exempt from paying tax.
-
Select the Connect to CertCapture function to post the exemption certificate to CertCapture within the Avalara platform.
This writes an entry to the AdmTaxAuditLog table within the database and returns the allocated CertCapture client ID.
You can then use the fields within the Customer Information pane of the Customer Query program (Program List > Accounts Receivable ) for one of the following:
-
View the exemption certificate:
Select the Show hyperlink against the Avalara > View exemption certificate field.
-
Request the exemption letter to be emailed to the email address defined against your operator code:
Select the Show hyperlink against the Avalara > Request exemption letter field.
-
View the customer’s current status and client ID on Avalara CertCapture:
Select the Show hyperlink against the Avalara > View customer status field.
-
View the current status of the exemption certificate on Avalara CertCapture:
Select the Show hyperlink against the Avalara > View certificate status field.
You can also view the certificate data within the Avalara CertCapture platform.
The following explains the process that occurs when you create sales orders after setting up the Tax Connectors feature:
-
Capture the sales order as required within the Sales Order Entry program (Program List > Sales Orders > Sales Order Processing).
-
Select the End Order function, followed by the Print function.
-
Once this completes, the Reprint Document screen is displayed advising the document reference (i.e. invoice number). The system then passes the invoice and tax details through to Avalara or Vertex.
During the upload process, these details are written the SorUsaTaxRep and AdmTaxAuditLog tables in the SQL database.
An archive file is also created within the \Base\AvtxArch folder which includes the invoice details and tax breakdown. An AV prefix indicates an Avalara file and an VR prefix indicates a Vertex file. This is followed by the SYSPRO company ID and then the sales order number.
The LineType column of the AdmTaxAuditLog table (or the last line of the AvtxArch archive file) indicates if the upload was successful or if there was a failure:
-
“S” indicates that the process was successful.
-
“F” indicates that the process has failed and provides a reason for the failure.
For example:
-
This occurs if your customer or warehouse address is not defined correctly in SYSPRO.
-
This occurs if the current invoice number already exists within the Avalara database.
-
-
-
You can then view the invoice within the Tax Connectors platform.
This process only applies to the Tax Connector - Avalara.
If you have a list of existing tax exemption state certificates and want to import these into SYSPRO, proceed as follows:
-
Open the CertCapture Import program (Program List > General Ledger > Tax > Tax Interface).
-
Indicate the name and full location path of the import file.
-
Select the Import function.
-
Review the Process completed fields for summary information about the records imported.
The two key pieces of information used in calculating sales tax are the customer’s city and state. However, sales order headers and warehouse delivery addresses may not always be formatted the same way. Therefore, the system may not be able to determine which address lines contain the city or state, or whether any abbreviations have been used.
To avoid this, you can use the separate, unique field for ZIP codes within the sales order header, customer or warehouse address. SYSPRO can then cross-reference the ZIP code with its respective city and state to pass along the correct information.
In the scenario where sales tax must be calculated for stocked items based on the warehouse ZIP code, ensure that the ZIP codes are configured correctly against your warehouses using the Warehouse Maintenance program. If the ZIP code is incomplete or only contains five digits, edit it to include the full nine-digit ZIP code.
To add, maintain or delete Zip codes:
-
Open the ZIP Codes Maintenance program (Program List > General Ledger > Tax > Tax Interface).
-
When adding a new Zip Code...
Enter a new ZIP code and the relevant City, State and County on a new line within the list view.
When maintaining an existing Zip Code...
Edit an existing Zip code as required within the list view.
When deleting an existing Zip Code...
Delete an existing Zip code by selecting the applicable line within the list view and select the Delete function.
-
Select the Save function and close the program.
This process only applies if you use the Tax Connector - Avalara and want to manually import your sales tax rates.
Tax rates can be manually imported into SYSPRO via an ASCII file provided by Avalara. Then, once you run the import program, SYSPRO uses the updated data in all tax calculations throughout the system (i.e. invoices, sales orders, purchase orders, etc. will use the updated tax rates).
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.
This process only applies if you use the Tax Connector - Avalara and want to manually import your use tax rates.
Tax rates can be manually imported into SYSPRO via an ASCII file provided by Avalara. Then, once you run the import program, SYSPRO uses the updated data in all tax calculations throughout the system (i.e. invoices, sales orders, purchase orders, etc. will use the updated tax rates).
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.
This step applies if you require tax calculations per state to be based on the Net or Gross amount.
To configure the tax rate calculation basis per state, proceed as follows:
-
Open the Tax Base Indicator program (Program List > General Ledger > Tax > Tax Interface).
-
Select the Add function to open the Tax Base Indicator Maintenance window.
-
Enter the applicable State and indicate if the tax must be calculated on the Net amount or Gross amount.
-
Select the Save function and close the window.
-
Exit the Tax Base Indicator program.
You can also use this program to maintain or delete existing states with tax base indicators.
The following indicates areas in the product that may be affected by implementing this feature:
SYSPRO Ribbon bar > Setup
The USA 3rd Party Tax form has been renamed to Sales and Use Tax and enables you to define the following configuration related to Tax Connectors:
-
Sales and use tax system required
-
Interface type
-
Default ship from information
-
Update tax for this company
Once you configure the required options, the Run Setup hyperlink under Tax interface launches the Tax Interface Setup program from where you can continue the Tax Connectors setup.
Setup Options > Tax > Sales and Use Tax > Tax interface > Run Setup
This program lets you configure the following information for your selected tax connector:
-
Connection details
-
Filter requirements (by state, country or product class)
-
Location code source (Avalara only)
-
Date format (Avalara only)
-
Company options
-
Customer address format (i.e. Ship-to addresses)
-
Warehouse address format (i.e. Ship-from addresses)
Program List > General Ledger > Tax > Tax Interface
This program only applies to the Tax Connector - Avalara.
This program lets you configure and test your connection to CertCapture for access to tax exemption certificates, to avoid failed transactions when you add, change or delete a customer.
Accessible from the Update hyperlink of the Tax Interface Setup program
This program lets you indicate a date range of invoices that the system can use to pull the tax values recorded within the ArTrnSummary table to update the corresponding re-print tables.
This ensures the accurate re-print of taxes for SYSPRO AR invoices that were processed before linking to Avalara, by updating existing data in the following tables:
-
SorUsaTaxRep (Sales Order USA Tax Reprint)
-
MdnMasterRep (MDN Reprint Dispatch Note Master)
-
MdnMasterRepCon (MDN Reprint Consolidated Dispatch Master)
Automatically launched when you add, change or delete a customer within the Customers program (Dependent on eSignature setup)
This program only applies to the Tax Connector - Avalara.
This program lets you add, update or delete customer information and CertCapture ID's by connecting to the CertCapture system within Avalara whenever you add, maintain or delete a customer.
Accessible from the Show hyperlinks within the Avalara pane of the Customer Query program
This program only applies to the Tax Connector - Avalara.
This program lets you perform the following actions with regards to the exemption certificate saved against a customer:
-
View the customer's current exemption certificate recorded in CertCapture.
-
Email the customer to request their exemption letter.
-
View the customer’s current status and client ID within CertCapture.
-
Establish the current status of a customer's exemption certificate within CertCapture.
Program List > General Ledger > Tax > Tax Interface
This program lets you add, change or delete the ZIP codes used for tax calculations.
Program List > General Ledger > Tax > Tax Interface
This program lets you exclude states from the company's tax integration calculations and subsequent submission to Avalara.
When any state is defined as excluded, sales order transactions for ship-to locations in those states are then excluded from the transaction files sent to Avalara (i.e. no sales tax will be calculated for or added to any sales order shipped to an excluded state).
Program List > General Ledger > Tax > Tax Interface
This program lets you exclude countries from the company's tax integration calculations and subsequent submission to Avalara.
When any country is defined as excluded, sales order transactions for ship-to locations in those countries are then excluded from the transaction files sent to Avalara (i.e. no sales tax will be calculated for or added to any sales order shipped to an excluded country).
Program List > General Ledger > Tax > Tax Interface
This program lets you define a range of ZIP codes to be included in the special tax calculations for tax integration with Avalara or Vertex.
Program List > General Ledger > Tax > Tax Interface
This program only applies to the Tax Connector - Avalara.
This program lets you exclude a range of product classes from the company's tax integration calculations and subsequent submission to Avalara.
When a product class is defined as excluded, sales order transactions that contain stock codes within that product class are then excluded from the transaction files sent to Avalara.
Program List > General Ledger > Tax > Tax Interface
This program lets you add, change or delete tax base indicators (i.e. the tax rate calculation basis per state).
This is useful if you require tax calculations per state to be based on the net or gross amount.
Program List > General Ledger > Tax > Tax Interface
This program only applies to the Tax Connector - Avalara.
This program lets you update the list of states for your customers in CertCapture by importing data from an Excel CSV file directly into Avalara.
Each line of the import file is sent to CertCapture separately to add the customer details and update the list of exemption states for each customer.
The client ID (retrieved during the import process) and exemption states are then added to the Certcapture Cross Reference table (AdmTaxCertXref).
Program List > General Ledger > Tax > Tax Interface
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).
SYSPRO Button > Run a program > IMPPNH
This program only applies to the Tax Connector - Avalara.
This program lets you manually import use tax rates for all states (except Colorado) using an ASCII file provided by Avalara.
SYSPRO then uses the updated use 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).
Program List > General Ledger > Tax > Tax Interface
This program lets you view and print any errors that may have occurred during the posting of transactions to Avalara or Vertex.
Program List > Accounts Receivable
This program contains the following fields within the Avalara pane which launch the CertCapture - Show Exemption Certificate program to query information regarding the exemption certificate saved against the customer:
-
View exemption certificate
-
Request exemption letter
-
View customer status
-
View certificate status
The following indicates the business objects that are affected by this feature:
The Tax Interface business object interfaces with the Avalara and Vertex tax systems.
Copyright © 2022 SYSPRO PTY Ltd.