...
Group by udrbillerid on tax log
| Code Block |
|---|
|
CREATE VIEW [dbo].[_ViewTaxLogUdrBillerID]
AS
SELECT UDRBillerID, SUM(Amount) AS TaxLogAmount
FROM dbo.TaxLog
GROUP BY UDRBillerID
GO |
Group by UDRBillerid on udrbillertax
| Code Block |
|---|
|
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
| Code Block |
|---|
|
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
| Code Block |
|---|
|
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:
| Code Block |
|---|
|
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)
| Code Block |
|---|
|
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) |
...