Page Comparison

...

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.

...

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

...

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

...

(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

...

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)

...