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

USE [msdb]
GO

-- =============================================
-- Author: Anita Middleton
-- Company: Logisense
-- Create Date: Nov 2, 2017
-- Description: Modifies TaxLog_Master table with new TaxLog.IDs
-- History: Created for ticket #106505
-- =============================================

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TaxLog_Master',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Inserts] Script Date: 1/9/2018 12:47:19 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Inserts',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec usp_TaxLog_Master_Update',
@database_name=N'EngageIP',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Updates] Script Date: 1/9/2018 12:47:19 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Updates',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'/* updated the TaxLog_Master invoice number
in cases where an invoice gets voided and
TaxLog_Master has already been added */

UPDATE tlm
SET InvoiceID = isc.InvoiceID
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetails sd WITH (NOLOCK) ON tl.StatementDetailsID = sd.ID
INNER JOIN InvoiceStatementConnector isc WITH (NOLOCK) ON sd.StatementID = isc.StatementID
WHERE tlm.InvoiceID <> isc.InvoiceID
--and tlm.InvoiceID in (112225,109467)

UPDATE tlm
SET InvoiceID = sdcic.InvoiceID
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetailsCreditInvoiceConnector sdcic WITH (NOLOCK) ON tl.StatementDetailsID = sdcic.StatementDetailsID
WHERE tlm.InvoiceID <> sdcic.InvoiceID
--and tlm.InvoiceID in (112225,109467)

UPDATE tlm
SET InvoiceID = ub.InvoiceID
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN UDRBiller ub WITH (NOLOCK) ON tl.UDRBillerID = ub.ID
WHERE tlm.InvoiceID <> ub.InvoiceID
--and tlm.InvoiceID in (112225,109467)

/* update tax month */
UPDATE tlm
SET TaxMonth = CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE)
FROM TaxLog_Master tlm
INNER JOIN Invoice i WITH (NOLOCK) ON tlm.InvoiceID = i.ID
WHERE tlm.TaxMonth <> CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE)

',
@database_name=N'EngageIP',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete Voided Items] Script Date: 1/9/2018 12:47:19 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Voided Items',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'/* updated the TaxLog_Master to delete
IDs on voided invoices */

DELETE tlm
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetails sd WITH (NOLOCK) ON tl.StatementDetailsID = sd.ID
INNER JOIN InvoiceStatementConnector isc WITH (NOLOCK) ON sd.StatementID = isc.StatementID
INNER JOIN ViewInvoice i WITH (NOLOCK) ON isc.InvoiceID = i.ID
WHERE i.Void != ''no''

DELETE tlm
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetailsCreditInvoiceConnector sdcic WITH (NOLOCK) ON tl.StatementDetailsID = sdcic.StatementDetailsID
INNER JOIN ViewInvoice i WITH (NOLOCK) ON sdcic.InvoiceID = i.ID
WHERE i.Void != ''no''

DELETE tlm
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN UDRBiller ub WITH (NOLOCK) ON tl.UDRBillerID = ub.ID
INNER JOIN ViewInvoice i WITH (NOLOCK) ON ub.InvoiceID = i.ID
WHERE i.Void != ''no''
',
@database_name=N'EngageIP',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20171102,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'76bf65da-10f8-4c11-a725-869de3c39c19'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

#5 Install custom code:

Name: Populate Tax Report Job

using System.Collections.Generic;
using System.Linq;
using Logisense.Boss.Logic;
using Logisense.Boss.Logic.Core;
using Job = Logisense.Boss.Logic.DomainModel.Job;

namespace Logisense.Boss.CustomScripts.Avalara.Jobs
{
public class PopulateTaxReportJob : IJob
{
public const string PROFILE_QUESTION = "Tax Presentation";
public const string PROFILE_ANSWER = "Yes";
public const string SERVICE_PROVIDER_ROLE_NAME = "Service Provider";
public const string ADMIN_ROLE_NAME = "Admin";

public string Name
{
get { return "PopulateTaxReportJob"; }
}
public string Description
{
get { return " Job that will populate the Tax Report"; }
}
public bool Run(Job job)
{
var serviceProviderResults = GetServiceProviderResultsWithUserAttributeProfileAnswerSetToYes();
PopulateBroadSoftTaxReportTable(serviceProviderResults);
return true;
}

internal static IList<int> GetServiceProviderResultsWithUserAttributeProfileAnswerSetToYes()
{
const string SQL =
@"SELECT
u.OwnerID
FROM UserAttributeProfileAnswer uapa
INNER JOIN RoleAttributeProfileQuestion rapa ON rapa.ID = uapa.RoleAttributeProfileQuestionID
INNER JOIN ProfileQuestion pq ON pq.ID = rapa.ProfileQuestionID
INNER JOIN ProfileAnswer pa ON pa.ID = uapa.ProfileAnswerID
INNER JOIN Role r ON r.ID = rapa.RoleID
INNER JOIN [User] u ON u.ID = uapa.UserID
WHERE pq.Question = @profileQuestion
AND pa.Value = @profileAnswer
AND (r.Name = @SERVICE_PROVIDER_ROLE_NAME or r.Name =@ADMIN_ROLE_NAME) ";

return SQLHelper.ExecuteList<int>(SQL, new SQLParameters
{
{ "profileQuestion", PROFILE_QUESTION},
{ "profileAnswer", PROFILE_ANSWER},
{ "SERVICE_PROVIDER_ROLE_NAME", SERVICE_PROVIDER_ROLE_NAME},
{ "ADMIN_ROLE_NAME", ADMIN_ROLE_NAME},
}).Distinct().ToList();

}

private static void PopulateBroadSoftTaxReportTable(IList<int> serviceProviderResults)
{
if (serviceProviderResults == null || !serviceProviderResults.Any())
{
return;
}

var SQL = string.Format(
@"DECLARE @endDate date = CAST(DATEADD(dd, -DAY(getdate()) + 1, getdate()) AS DATE)
DECLARE @startDate date = dateadd(month, -1, @endDate)
DECLARE @currentMonth varchar(30) = CAST(@startDate AS DATE)

;WITH CTE (OwnerParentName, OwnerParentID, Parent_OwnerID)
AS (SELECT vop.OwnerParentName, vop.OwnerParentID, vop.Parent_OwnerID
FROM ViewOwnerParent vop WITH (NOLOCK)
INNER JOIN UserOwner uo WITH (NOLOCK) ON vop.OwnerParentID = uo.OwnerID
INNER JOIN ViewUser u WITH (NOLOCK) ON uo.UserID = u.ID AND u.Role = 'Service Provider'
)

INSERT INTO [AvalaraTaxReport] (ID, PCodeTax, TaxType, TaxLevel, TaxRate, TaxAmount, SaleAmt, Exempt, Adjustments, NetTaxable, Minutes, Lines, Billable, OwnerID, ServiceProvider, CreatedDate)
SELECT
tl.ID,
tl.PCodeTax,
tl.[Type] AS TaxType,
CASE WHEN tl.Level = 'Federal' THEN 0
WHEN tl.Level = 'State' THEN 1
WHEN tl.Level = 'County' THEN 2
WHEN tl.Level = 'Local' THEN 3
WHEN tl.Level = 'Unincorporated' THEN 4
ELSE 99 END AS TaxLevel,
ROUND(tl.Rate,6) AS TaxRate,
tl.Amount AS TaxAmount,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' THEN 0
ELSE tl.AmountExemption END
+ CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption = 0 THEN tl.AmountProcessed * -1
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption <> 0 THEN (tl.AmountProcessed - tl.AmountExemption) * -1
ELSE 0 END
+ CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountExemption = tl.AmountProcessed THEN 0
WHEN tl.Amount < 0 THEN 0
ELSE tl.TaxableMeasure END AS SaleAmt,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' THEN 0
ELSE tl.AmountExemption END AS Exempt,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption = 0 THEN tl.AmountProcessed * -1
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption <> 0 THEN (tl.AmountProcessed - tl.AmountExemption) * -1
ELSE 0 END AS Adjustments,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountExemption = tl.AmountProcessed THEN 0
WHEN tl.Amount < 0 THEN 0
ELSE tl.TaxableMeasure END AS NetTaxable,
0 AS [Minutes],
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' AND tl.AmountProcessed >= 0 THEN sd.BulkQuantity
WHEN tl.CategoryDescription = 'E-911 CHARGES' AND tl.Name NOT LIKE '%_USA_CA_%' AND tl.AmountProcessed < 0 THEN sd.BulkQuantity * -1
ELSE 0 END AS Lines,
tl.Billable,
o.ID AS OwnerID,
CASE WHEN u.ActingOwnerID = 2 THEN cte.OwnerParentName ELSE o.Name END AS ServiceProvider,
GETDATE() Date
FROM TaxLog tl WITH (NOLOCK)
INNER JOIN [ViewUser] u WITH (NOLOCK) ON tl.UserID = u.ID
INNER JOIN Owner o WITH (NOLOCK) ON u.ActingOwnerID = o.ID
LEFT JOIN StatementDetails sd WITH (NOLOCK) ON tl.StatementDetailsID = sd.ID
LEFT JOIN CTE ON u.ActingOwnerID = cte.Parent_OwnerID
WHERE tl.[reverse] is null
AND tl.ID IN (SELECT id from TaxLog_Master where TaxMonth = @currentMonth)
AND u.ActingOwnerID IN ({0})", string.Join(",", serviceProviderResults));

SQLHelper.ExecuteNonQuery(SQL);
}
}
}

Name: Avalara Compliance Report

using System;
using System.Linq;
using Logisense.Boss.Logic;
using Logisense.Boss.Logic.Core;
using Logisense.Boss.Utility;
using DomainModel = Logisense.Boss.Logic.DomainModel;

namespace Logisense.Boss.CustomScripts.Avalara.Reports
{
public class AvalaraComplianceReport : ICustomCodeInstaller
{
//The job will be executed on the first of of the month at the specified job timetime
//On initial installation, this timespan can be adjusted to provide the start time for the Job
//Once the custom code is installed, this can be adjusted through the JobSchedule UI on the Logisense Administartion Owner
//(24h clock, {00,01,23} => 12:01:23 AM, {23:59:59} => 11:59:59 PM)
public static readonly TimeSpan JobFirstStartTime = new TimeSpan(02, 00, 00);

public const string REPORT_NAME = "Avalara Tax Compliance";
public const string VIEW_NAME = "ViewAvalaraTaxReport";
public const string TABLE_NAME = "AvalaraTaxReport";
public const string JOB_NAME = "PopulateTaxReportJob";
public const string OWNER_NAME = "Logisense Corporation";

public void Install(int ownerID)
{
Log.Information("Installing custom report {0} for OwnerID {1}", REPORT_NAME, ownerID);
try
{
SetupView();
SetupCustomReport(ownerID);
SetupJobToGenerateData();
Log.Information("Completed installing custom report {0} for OwnerID {1}", REPORT_NAME, ownerID);
}
catch (Exception exception)
{
var error = string.Format("Error setting up Custom Report {0}: {1}", REPORT_NAME, exception.Message);
Alert.Create(error, ownerID);
Log.Exception(exception, error);
}
}

private static void SetupView()
{
Log.Debug("Setting up view {0}", VIEW_NAME);
try
{
var SQL_VIEW = string.Format(
@"SELECT
PCodeTax,
TaxType,
TaxLevel,
TaxRate,
ROUND(SUM(TaxAmount),6) AS TaxAmount,
ROUND(SUM(SaleAmt),6) AS SaleAmt,
ROUND(SUM(Exempt),6) AS Exempt,
ROUND(SUM(Adjustments),6) AS Adjustments,
ROUND(SUM(NetTaxable),6) AS NetTaxable,
Minutes,
SUM(Lines) AS Lines,
Billable,
ServiceProvider,
OwnerID,
CreatedDate
FROM {0}
GROUP BY PCodeTax,
TaxType,
TaxLevel,
TaxRate,
Minutes,
Billable,
ServiceProvider,
OwnerID,
CreatedDate", TABLE_NAME);

SQLHelper.CreateView(VIEW_NAME, SQL_VIEW, false);
}
catch (Exception ex)
{
throw new ApplicationException(string.Format("Views: {0}", ex.Message), ex);
}
}

private static void SetupCustomReport(int ownerID)
{
try
{
Log.Debug("Setting up custom report {0} for OwnerID {1}", REPORT_NAME, ownerID);
if (ReportAlreadyExists(ownerID))
{
Log.Trace("Custom report {0} already exists for OwnerID {1}. No need to create the report.", REPORT_NAME, ownerID);
return;
}

Log.Trace("Custom report {0} doesn't already exist for OwnerID {1}. Creating the report.", REPORT_NAME, ownerID);
var report = new CustomReport
{
OwnerID = ownerID,
Name = REPORT_NAME,
ConnectionString = ConnectionStringManager.ENGAGEIP,
Table = VIEW_NAME,
LoadView = false,
CustomReportFilter = (int)CustomReport.CustomReportFilterType.CurrentOwner,
};
report.CreateOrUpdateWithPermissions();
}
catch (Exception exception)
{
throw new ApplicationException(string.Format("Customer Reports: {0}", exception.Message), exception);
}
}

private static bool ReportAlreadyExists(int ownerId)
{
var query = new Logic.DomainModel.CustomReportQuery { Name = REPORT_NAME, PageSize = 1, OwnerID = ownerId };
return CustomReport.GetCollection(ref query).Any();
}

private void SetupJobToGenerateData()
{
var query = new DomainModel.JobScheduleQuery
{
Job = JOB_NAME,
PageSize = 1
};
var existingJob = JobSchedule.GetCollection(ref query).FirstOrDefault();
if (existingJob != null)
{
return;
}

var newJob = JobSchedule.GetNew();
newJob.OwnerID = Owner.GetByName(OWNER_NAME).ID;
newJob.Job = JOB_NAME;
newJob.Parameters = "notused";
newJob.Schedule = "1M";
newJob.Enabled = true;
newJob.Comment = "custom job to populate the Avalara Tax Report data into the correct database tables";
newJob.LastRun = GetJobScheduleLastRunDate();
newJob.Create();
}

internal DateTime GetJobScheduleLastRunDate()
{
return GetJobScheduleLastRunDate(SystemDateTime.Now);
}

internal DateTime GetJobScheduleLastRunDate(DateTime date)
{
return new DateTime(date.Year, date.Month, 1, JobFirstStartTime.Hours, JobFirstStartTime.Minutes, JobFirstStartTime.Seconds);
}
}
}

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

USE EngageIP

-- =============================================
-- Author: Anita Middleton
-- Company: Logisense
-- Create Date: Nov 2, 2017
-- Description: Generates Avalara compliance report
-- History: Created for ticket #106505
-- =============================================

DECLARE @endDate date = CAST(DATEADD(dd, -DAY(getdate()) + 1, getdate()) AS DATE)
DECLARE @startDate date = dateadd(month, -1, @endDate)
DECLARE @currentMonth varchar(30) = CAST(@startDate AS DATE)

SELECT @currentMonth AS ReportMonth
SET NOCOUNT ON;

DECLARE @TaxReport AS TABLE (
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)

/* pull tax data for requested month */
INSERT INTO @TaxReport (ID, PCodeTax, TaxType, TaxLevel, TaxRate, TaxAmount, SaleAmt, Exempt, Adjustments, NetTaxable, Minutes, Lines, Billable)
SELECT
tl.ID,
--tl.PCode,
tl.PCodeTax,
tl.[Type] AS TaxType,
CASE WHEN tl.Level = 'Federal' THEN 0
WHEN tl.Level = 'State' THEN 1
WHEN tl.Level = 'County' THEN 2
WHEN tl.Level = 'Local' THEN 3
WHEN tl.Level = 'Unincorporated' THEN 4
ELSE 99 END AS TaxLevel,
ROUND(tl.Rate,6) AS TaxRate,
tl.Amount AS TaxAmount,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' THEN 0
ELSE tl.AmountExemption END
+ CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption = 0 THEN tl.AmountProcessed * -1
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption <> 0 THEN (tl.AmountProcessed - tl.AmountExemption) * -1
ELSE 0 END
+ CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountExemption = tl.AmountProcessed THEN 0
WHEN tl.Amount < 0 THEN 0
ELSE tl.TaxableMeasure END AS SaleAmt,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' THEN 0
ELSE tl.AmountExemption END AS Exempt,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption = 0 THEN tl.AmountProcessed * -1
WHEN tl.AmountProcessed < 0 AND tl.AmountExemption <> 0 THEN (tl.AmountProcessed - tl.AmountExemption) * -1
ELSE 0 END AS Adjustments,
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' THEN 0
WHEN tl.AmountExemption = tl.AmountProcessed THEN 0
WHEN tl.Amount < 0 THEN 0
ELSE tl.TaxableMeasure END AS NetTaxable,
0 AS [Minutes],
CASE WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' AND tl.AmountProcessed >= 0 THEN sd.BulkQuantity
WHEN tl.CategoryDescription = 'E-911 CHARGES' AND Name NOT LIKE '%_USA_CA_%' AND tl.AmountProcessed < 0 THEN sd.BulkQuantity * -1
ELSE 0 END AS Lines,
tl.Billable
FROM TaxLog tl
LEFT JOIN StatementDetails sd ON tl.StatementDetailsID = sd.ID
WHERE tl.[reverse] is null
AND tl.ID IN (SELECT id from TaxLog_Master where TaxMonth = @currentMonth)

/* query for unsummarizied .nba report */
SELECT PCodeTax, TaxType, TaxLevel, TaxRate, ROUND(SUM(TaxAmount),6) AS TaxAmount, ROUND(SUM(SaleAmt),6) AS SaleAmt, ROUND(SUM(Exempt),6) AS Exempt, ROUND(SUM(Adjustments),6) AS Adjustments, ROUND(SUM(NetTaxable),6) AS NetTaxable,
Minutes, SUM(Lines) AS Lines
FROM @TaxReport
WHERE Billable = 0
GROUP BY PCodeTax,
TaxType,
TaxLevel,
TaxRate,
Minutes

/* query for unsummarized .ssf report */
SELECT PCodeTax, TaxType, TaxLevel, TaxRate, ROUND(SUM(TaxAmount),6) AS TaxAmount, ROUND(SUM(SaleAmt),6) AS SaleAmt, ROUND(SUM(Exempt),6) AS Exempt, ROUND(SUM(Adjustments),6) AS Adjustments, ROUND(SUM(NetTaxable),6) AS NetTaxable,
Minutes, SUM(Lines) AS Lines
FROM @TaxReport
WHERE Billable = 1
GROUP BY PCodeTax,
TaxType,
TaxLevel,
TaxRate,
Minutes