Tax Log - Logging for AvaTax and SureTax

1 Summary
2 Configuration
- 2.1 UDRBiller and UDR Taxing
3 Table Columns
4 Reconciling

Summary

Sometimes specific formats for taxes are required when submitting taxes payable to government departments or other organizations. The tax log was created as a way to provide a more direct export and streamlined format for the display of taxes payable.

When enabled the tax log table provides granular detail on the taxes charged for reconciliation and tracking purposes.

Notice: in SureTax integrated environments the number of taxes displayed in the EngageIP tax log may not match the number of taxes shown in SureTax granular reports. This is typically due to state taxes being bundled with other taxes when SureTax returns the tax values to EngageIP. This discrepancy is merely a presentation issue, the total tax amount in the tax log and in SureTax's portal will be the same.

Configuration

Note: the tax log is enabled by default when the Avatax cloud tax solution is configured in EngageIP.

To ensure logging is being kept, you can turn it on in EngageIP by updating the EngageIPOption table:

Run the following command in SQL
INSERT INTO EngageIPOption (Name,VALUE) VALUES ('TaxLog','True')
Restart billing, event, job services.
Reset IIS

The table created and used once this is entered is called ‘TaxLog’ in BOSS. It holds columns such as tax rate name, pcode, percentage applied, dates, original amounts, statementdetailsID, etc.

An example of the detail recorded is below

Using a query in SQL to select from this table can provide the needed reporting for your requirements as it details all of the significant details about the tax that was calculated.

UDRBiller and UDR Taxing

Where UDRBiller taxing exists, the taxlog will have the UDRBillerID. UDRID for the same will be zero unless its been rerated, in which case the UDRID will reflect the actual UDR rerated. If you need to connect the taxlog record back to the UDR in either case, you can connect them using the UniquenessIdentifier column as the connecting point.

Table Columns

The list of columns are these:

ID - table id
Name - tax rate name gathered from the tax integration
PCode - this is specific to AvaTax integrations and is also known as the Jurisdiction code (JCode)
PCodeTax
Amount
AmountExemption - amount exempted from the calculated value
AmountProcessed
CreatedDate - date on which the record was added
Date - date stamped which is when the tax was added
Rate - the tax rate (%)
Type - AvaTax Tax Type
Level - AvaTax Level type (Federal, State, Local, etc)
TransactionType - transaction type as is listed in 'TransactionType' table in EngageIP linked by ID
ServiceType - as is listed in the ServiceType table in EngageIP linked by ID
UserID - ID of the user in EngageIP
StatementDetailsID - ID of the statementdetails table to which this row is associated
UDRID - ID of the UDR record in the UDR Table
TypeName - Tax type name as presented by the tax integration
CategoryDescription - Description as presented by the tax integration
Description - the value as shows on the tax transaction in EngageIP

Reconciling

To reconcile the tax log to the UDRBiller table, you can use the following views:

Update the UDRBillerID on the table to allow comparison

UPDATE TaxLog SET UDRBillerID = udr.UDRBillerID , UDRID = udr.id
FROM dbo.TaxLog INNER JOIN
dbo.UDR ON dbo.TaxLog.UniquenessIdentifier = dbo.UDR.UniquenessIdentifier

Group by udrbillerid on tax log

CREATE VIEW [dbo].[_ViewTaxLogUdrBillerID]
AS
SELECT UDRBillerID, SUM(Amount) AS TaxLogAmount
FROM dbo.TaxLog
GROUP BY UDRBillerID
GO

Group by UDRBillerid on udrbillertax

Create a view to compare the two tables

Select data to compare the two based on the views created

Reconciling to statementdetails and statementdetailstax can be done by creating a view that connects this table via the 'StatementDetailsID'. A custom report can be made out of this view or you can simply export it from the database directly.

Custom report to reflect items in EngageIP that are not reported in the Taxlog:

Custom report reflecting differences in taxes recorded where StatementDetailsID exists in each location:

(This report as example is good to show rounding differences which may be causing the reconciliation to be out by dollars)

StatementDetails Reconciliation Notes:

Prior to 8.6 - Credits were not logged in taxlog
Credit reversals are added to Taxlog with the same Date as the original credit so if a credit was added in January, reversed in February, the reversing tax log record would be dated in January - so January results will change and it will not be seen in February reporting
Tax Adjustments are not added to taxlog - these are manual tax adjustments and not calculated by Suretax (or Billsoft / Avalara) and so not added to taxlog
Any taxes calculated using the 'Local' tax configuration (possibly in parallel with a tax integration) will not be added to the taxlog. The taxlog is only for Suretax or Avalara / Billsoft records - tax calculated outside of EngageIP.
There are likely to be tax rounding differences comparing Sales report directly to taxlog as taxlog carries a greater number of decimal places than does the Sales report - this will account for a large proportion of smaller differences when reconciling
The Suretax granular report has line items for taxes where total tax for that rate is zero - taxlog only records items where tax is greater than zero so those zero total tax items in granular report need to be removed to reconcile line by line
Avatax Transaction Detail records transactions as committed until such time the invoice is then voided, when its voided, it marks the original transactions as uncommitted - so those need to be removed when reconciling for the numbers to match since it would otherwise increase the Avatax report sum by the value of the original invoice. If an invoice created in February but voided in March, those original transactions are still mark as ‘committed’ in Avalara. The value of those would need to be subtracted from Avalaras report

Suretax Reconciliation Process

Note that ReferenceID is the field that is common between the Taxlog table and Suretax reporting to match up transactions

Export Suretax Granular report for Suretax portal interface for the period you wish to reconcile
Go to Suretax Taxlog Report (custom EngageIP report returning data from the taxlog)
Filter by 3/1 to 3/31, and set dropdown as month (this groups the report per image)
Remove check marks on all columns except for Amount, AmountExemption,AmountProcessed and Created Date
Click update - the totals can be compared to the granular report

The Totals will match between the two reports. If you were to compare it line by line, you would find that reversals for example (the positive and negative values) show in the taxlog, where as the reversal (positive and negative amount) are not exported in the granular report because effectively they do cancel each other out.

Avatax Reconciliation Process

NOTE: BatchIdentifier in Taxlog matches ‘Document Code’ in Avalara reports

Export the Transaction Detail report from Avatax interface for the month you are trying to reconcile
Use a Tax Log report (custom report) and filter it by 'CreatedDate' for the same period (May 1 to May 31). The created date matches the 'Committed Date' as it is labeled on the Avatax side
Uncheck columns per image below so that the report groups and sums up based on your filters - these sums will be used to compare to the Transaction Details report from Avatax
Remove transactions from the Transaction Detail report that are marked as false under the 'Is Committed' column (these are voided transactions or transactions that should not be considered)

The sum of values from each will match at this point for Avatax and the Taxlog for this same period.

v9 Documentation