Avalara Tax Compliance Reporting
- Mahnoor Khan (Deactivated)
- Greg Walsh
Avalara Tax Compliance Reporting
Description:
Custom code / scripts used to generate the Avalara tax compliance custom report in EngageIP.
This will create a custom report with the data which can then be exported as needed on a monthly basis
Configuration:
Run the following queries in order in SQL
#1
USE [EngageIP]
GO
IF OBJECT_ID('AvalaraTaxReport') IS NOT NULL
BEGIN
DROP TABLE [AvalaraTaxReport]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AvalaraTaxReport](
ID int,
PCodeTax int,
TaxType int,
TaxLevel int,
TaxRate float,
TaxAmount float,
SaleAmt float,
Exempt float,
Adjustments float,
NetTaxable money,
Minutes int,
Lines int,
Billable bit,
OwnerID int,
ServiceProvider nvarchar(255),
CreatedDate datetime
) ON [PRIMARY]
GO#2
USE [EngageIP]
GO
IF OBJECT_ID('TaxLog_Master') IS NOT NULL
BEGIN
DROP TABLE [TaxLog_Master]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TaxLog_Master](
[TaxMonth] [nchar](10) NULL,
[ID] [int] NULL,
[InvoiceID] [int] NULL
) ON [PRIMARY]
GO#3
USE [EngageIP]
GO
IF OBJECT_ID('usp_TaxLog_Master_Update') IS NOT NULL
BEGIN
DROP PROCEDURE [usp_TaxLog_Master_Update]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TaxLog_Master_Update] AS
-- =============================================
-- Author: Anita Middleton
-- Company: Logisense
-- Create Date: Nov 2, 2017
-- Description: Adds TaxLog IDs into a master list for tax reporting
-- History: Created for ticket #106505
-- =============================================
/* gets statementdetails taxlogID and posting date */
INSERT INTO TaxLog_Master (TaxMonth, ID, InvoiceID)
SELECT CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE) TaxMonth, tl.ID, i.ID InvoiceID
FROM TaxLog tl WITH (NOLOCK)
INNER JOIN StatementDetails sd WITH (NOLOCK) ON tl.StatementDetailsID = sd.ID
INNER JOIN InvoiceStatementConnector isc WITH (NOLOCK) on sd.StatementID = isc.StatementID
INNER JOIN Invoice i WITH (NOLOCK) on isc.InvoiceID = i.ID
INNER JOIN InvoiceStatusType ict WITH (NOLOCK) ON ict.ID = i.InvoiceStatusTypeID
WHERE tl.ID NOT IN (SELECT ID FROM TaxLog_Master)
AND ict.Name = 'Closed'
AND sd.Credit = 0
AND i.ID NOT IN (SELECT Voiding_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
AND i.ID NOT IN (SELECT Voided_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
UNION ALL
SELECT CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE) TaxMonth, tl.ID, i.ID InvoiceID
FROM TaxLog tl WITH (NOLOCK)
INNER JOIN StatementDetails sd WITH (NOLOCK) ON tl.StatementDetailsID = sd.ID
INNER JOIN StatementDetailsCreditInvoiceConnector sdcic WITH (NOLOCK) on sd.ID = sdcic.StatementDetailsID
INNER JOIN Invoice i WITH (NOLOCK) on sdcic.InvoiceID = i.ID
INNER JOIN InvoiceStatusType ict WITH (NOLOCK) ON ict.ID = i.InvoiceStatusTypeID
WHERE tl.ID NOT IN (SELECT ID FROM TaxLog_Master)
AND ict.Name = 'Closed'
AND sd.Credit = 1
AND i.ID NOT IN (SELECT Voiding_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
AND i.ID NOT IN (SELECT Voided_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
UNION ALL
/* gets UDR taxlogID and posting date */
SELECT CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE) TaxMonth, tl.ID, i.ID InvoiceID
FROM TaxLog tl WITH (NOLOCK)
INNER JOIN UDRBiller ub WITH (NOLOCK) ON tl.UDRBillerID = ub.ID
INNER JOIN StatementDetails sd WITH (NOLOCK) ON ub.StatementDetailsID = sd.ID
INNER JOIN InvoiceStatementConnector isc WITH (NOLOCK) on sd.StatementID = isc.StatementID
INNER JOIN Invoice i WITH (NOLOCK) on isc.InvoiceID = i.ID
INNER JOIN InvoiceStatusType ict WITH (NOLOCK) ON ict.ID = i.InvoiceStatusTypeID
WHERE tl.ID NOT IN (SELECT ID FROM TaxLog_Master)
AND ict.Name = 'Closed'
AND sd.Credit = 0
AND i.ID NOT IN (SELECT Voiding_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
AND i.ID NOT IN (SELECT Voided_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
UNION ALL
SELECT CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE) TaxMonth, tl.ID, i.ID InvoiceID
FROM TaxLog tl WITH (NOLOCK)
INNER JOIN UDRBiller ub WITH (NOLOCK) ON tl.UDRBillerID = ub.ID
INNER JOIN StatementDetails sd WITH (NOLOCK) ON ub.StatementDetailsID = sd.ID
INNER JOIN StatementDetailsCreditInvoiceConnector sdcic WITH (NOLOCK) on sd.ID = sdcic.StatementDetailsID
INNER JOIN Invoice i WITH (NOLOCK) on sdcic.InvoiceID = i.ID
INNER JOIN InvoiceStatusType ict WITH (NOLOCK)ON ict.ID = i.InvoiceStatusTypeID
WHERE tl.ID NOT IN (SELECT ID FROM TaxLog_Master)
AND ict.Name = 'Closed'
AND sd.Credit = 1
AND i.ID NOT IN (SELECT Voiding_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
AND i.ID NOT IN (SELECT Voided_InvoiceID FROM InvoiceVoid WITH (NOLOCK))
GO#4
#5 Install custom code:
Name: Populate Tax Report Job
Â
Name: Avalara Compliance Report
#6 Log out and back in
Usage Steps:
Once completed, the custom IJob will start running on the first of every month at the specified time. The job will populate the AvalaraTaxReport table, which will act as the underlying data responsible for the custom report.
The report is available from the Reports page. The report can be exported to .CSV, which should meet the file requirements to generate an .ssf file for Avalara.
To generate the monthly compliance report for Avalara, run the AvalaraComplianceReport.sql script.
There are two reports that are returned in this script. The first is the .nba report which is all non-billable taxation items. The second is the .ssf file which is all billable taxation items. The results should be saved as comma delimited without column headers and should have the appropriate file extension (.nba or .ssf) - example below. If the .nba file is null (meaning you have no non-billable taxes), then nothing needs to be sent to Avalara. NOTE: we are sending the PCodeTax value instead of the demographic information.