Tax Log - Logging for AvaTax and SureTax

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.

Configuration

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

1. Run the following command in SQL

   INSERT INTO EngageIPOption (Name,VALUE) VALUES ('TaxLog','True')

2. Restart billing, event, job services.

3. 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 VIEW [dbo].[_ViewUdrBillerTaxUdrBillerID]
AS
SELECT UDRBillerID, SUM(Amount) AS UDRBillerTaxAmount
FROM dbo.UDRBillerTax
WHERE (UDRBillerID IN
(SELECT DISTINCT UDRBillerID
FROM dbo.TaxLog))
GROUP BY UDRBillerID

Create a view to compare the two tables

CREATE VIEW [dbo].[_ViewUdrBillerCompareTaxlog]
AS
SELECT dbo._ViewTaxLogUdrBillerID.UDRBillerID, dbo._ViewTaxLogUdrBillerID.TaxLogAmount, dbo._ViewUdrBillerTaxUdrBillerID.UDRBillerTaxAmount
FROM dbo._ViewTaxLogUdrBillerID INNER JOIN
dbo._ViewUdrBillerTaxUdrBillerID ON dbo._ViewTaxLogUdrBillerID.UDRBillerID = dbo._ViewUdrBillerTaxUdrBillerID.UDRBillerID
GO

Select data to compare the two based on the views created

SELECT * FROM dbo._ViewUdrBillerCompareTaxlog

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:

SELECT dbo.ViewReportSaleByService.[USER], dbo.ViewReportSaleByService.UserID, dbo.ViewReportSaleByService.ID, dbo.ViewReportSaleByService.Date, dbo.ViewReportSaleByService.PostingDate,
dbo.ViewReportSaleByService.Tax, dbo.ViewReportSaleByService.Charge, ROUND(SUM(dbo.TaxLog.Amount), 2, 1) AS SumTaxLogAmount, dbo.TaxLog.Date AS TaxLogDate,
dbo.ViewReportSaleByService.BillGroup, dbo.ViewReportSaleByService.Owner, dbo.ViewReportSaleByService.UserStatus, dbo.ViewReportSaleByService.Invoice, dbo.ViewReportSaleByService.UserPackage,
dbo.ViewReportSaleByService.Detail, dbo.ViewReportSaleByService.PeriodStart, dbo.ViewReportSaleByService.PeriodEnd
FROM dbo.ViewReportSaleByService LEFT OUTER JOIN
dbo.TaxLog ON dbo.ViewReportSaleByService.ID = dbo.TaxLog.StatementDetailsID
WHERE (dbo.ViewReportSaleByService.Tax > 0) AND (dbo.TaxLog.Amount IS NULL) AND (dbo.ViewReportSaleByService.Date > '2019-1-1')
GROUP BY dbo.ViewReportSaleByService.ID, dbo.TaxLog.StatementDetailsID, dbo.ViewReportSaleByService.Date, dbo.ViewReportSaleByService.PostingDate, dbo.ViewReportSaleByService.Tax,
dbo.ViewReportSaleByService.Charge, dbo.TaxLog.Date, dbo.ViewReportSaleByService.[USER], dbo.ViewReportSaleByService.UserID, dbo.ViewReportSaleByService.BillGroup,
dbo.ViewReportSaleByService.Owner, dbo.ViewReportSaleByService.UserStatus, dbo.ViewReportSaleByService.Invoice, dbo.ViewReportSaleByService.UserPackage, dbo.ViewReportSaleByService.Detail,
dbo.ViewReportSaleByService.PeriodStart, dbo.ViewReportSaleByService.PeriodEnd

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)

SELECT dbo.ViewReportSaleByService.ID, dbo.ViewReportSaleByService.Date, dbo.ViewReportSaleByService.PostingDate, dbo.ViewReportSaleByService.Tax, dbo.ViewReportSaleByService.Charge,
ROUND(SUM(dbo.TaxLog.Amount), 2, 1) AS SumTaxLogAmount, dbo.TaxLog.Date AS TaxLogDate, ROUND(ROUND(SUM(dbo.TaxLog.Amount), 2, 1) - dbo.ViewReportSaleByService.Tax, 2) AS Diff
FROM dbo.ViewReportSaleByService LEFT OUTER JOIN
dbo.TaxLog ON dbo.ViewReportSaleByService.ID = dbo.TaxLog.StatementDetailsID
GROUP BY dbo.ViewReportSaleByService.ID, dbo.TaxLog.StatementDetailsID, dbo.ViewReportSaleByService.Date, dbo.ViewReportSaleByService.PostingDate, dbo.ViewReportSaleByService.Tax,
dbo.ViewReportSaleByService.Charge, dbo.TaxLog.Date
HAVING (ROUND(SUM(dbo.TaxLog.Amount), 2, 1) <> dbo.ViewReportSaleByService.Tax)

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

Suretax Reconciliation Process

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

1. Export Suretax Granular report for Suretax portal interface for the period you wish to reconcile

2. Go to Suretax Taxlog Report (custom EngageIP report returning data from the taxlog)

3. Filter by 3/1 to 3/31, and set dropdown as month (this groups the report per image) 

4. Remove check marks on all columns except for Amount, AmountExemption,AmountProcessed and Created Date

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

1. Export the Transaction Detail report from Avatax interface for the month you are trying to reconcile

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

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

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