When working with AvaTax for Sage 500 ERP we are often asked what data is exchanged between the two solutions. In order to help provide a better understanding of this, below is an outline of many of the data elements that are passed between Sage 500 ERP (“500”) and AvaTax. This does not attempt to address every scenario, but instead provide a guide to help navigate the information used in the tax calculation.
The example provided with this article is specific to Invoices created in the Accounts Receivable module of 500. This example also assumes that you have already configured and confirmed your connectivity of 500 with AvaTax. Further, it assumes that you are familiar with basic navigation within both 500 and AvaTax in order to open the tasks highlighted below. If you need assistance with your installation of AvaTax for Sage 500 ERP please reach out to Avalara and/or your Sage Business Partner.
Within 500, once an invoice has been created in the Accounts Receivable module, the act of saving the invoice will initiate the interaction with the AvaTax Service. Below we have provided screen shots of the user interfaces of 500 and AvaTax highlighting key data elements. In addition, you will find a summary of the mapping between the two systems. It is important to understand that this is not intended to provide the details of every scenario. Instead, it is meant to provide an example that you can reference when reviewing your own transactions.
There are several places in which 500 interacts with the AvaTax Service, but there is no way for you to review the information within the AvaTax interface. This is because when AvaTax calculates a tax estimate there is no tax liability established and these documents are therefore not maintained in their system. This means you will not be able to review the details of the tax calculation in AvaTax for Quotes, Sales Orders and Return Material Authorizations. Documents that are available for review in the AvaTax User Interface include those that establish tax liability, such as Invoices, Credit Memos and Debit Memos.
Below are screen captures of a 500 Accounts Receivable Invoice that are used to show the data mapping to AvaTax. Please note that there are some values that are not visible to the user in both systems. For example, you may notice that there is no “(2)” on the 500 Enter Invoice screen. This is because the value that matches up to the Avalara value is not directly visible to the user. Please review the Data Value Mappings later in this article for more information.
Figure 1: Sage 500 ERP Invoice
This invoice header represents a document that utilized AvaTax to calculate sales tax for the order. The data elements that have numeric values next to them are identified to show how these values are used as a part of the integration to AvaTax.
Figure 2: Sage 500 ERP Invoice Detail with Trade Discount
With the invoice line detail it is important to note that the line amount passed to AvaTax takes into account the discount when it is sent to AvaTax.
Figure 3: Sage 500 ERP Invoice Detail with Freight
A key item to point out with the below is that while the Freight Amount from each line is passed to AvaTax, the Ship Via utilized comes from the header of the invoice.
Figure 4: Sage 500 ERP Invoice Line Sales Tax
The below provides an example of the AvaTax results that are returned to the Invoice Line. It is important to note that even though Avalara breaks down the product and freight tax into separate lines, they are maintained in 500 as a single line item.
Below are screen captures of an AvaTax Document that represents the information in AvaTax after a call from 500 has been made to request tax calculation of a 500 Invoice. The figures below provide a visual indication of the data mapping from 500 to AvaTax.
With each save of a document within 500, a call will be made to the AvaTax Service. This will in turn update the information stored by AvaTax resulting in a new calculation of tax and finally the update of the 500 Invoice to reflect any change in tax liability.
Figure 5: AvaTax Invoice Header Information
The below provides a view of the header information for the tax document as seen in the AvaTax user interface.
Figure 6: AvaTax Invoice Line Summary Information
The below provides a summary of the line item detail as it is considered by AvaTax. AvaTax breaks out each line item into product and freight line items. This is due to the fact that taxability can vary for the product and freight amounts.
Figure 7: AvaTax Invoice Product Line Detail Information
This screen capture provides the product portion of taxability for the line item. An important piece of information to be familiar with is the Tax Code that is used on each line. If this Tax Code is not set correctly, your tax liability maybe incorrectly calculated. If a tax code is not specified AvaTax will assign the default tax code. The default tax code is identified as Tangible Personal Property (“P0000000”) and will typically be treated as taxable.
Figure 8: AvaTax Invoice Freight Line Detail Information
This screen capture provides the freight portion of taxability for the line item. An important piece of information to be familiar with is the Tax Code that is used for freight. If this Tax Code is not set correctly, your tax liability maybe incorrectly calculated.
The below provides an overview of the data values that are passed from 500 to AvaTax for tax calculations.
The below provides an overview of the data values that are returned from AvaTax to 500 after tax calculations are completed.
In case you missed it -> AvaTax for Sage 500 ERP: Mapping Items to Product Tax Codes