Page Comparison

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

   Code Block
   language 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 as tax rate name, pcode, percentage applied, dates, original amounts, statementdetailsID, etc.

...

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

...

To reconcile the tax log to the UDRBiller table,  you 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)

...

• 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

...

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)

...