Universal Service Fund (USF) Tax Configuration

Summary

This article describes the customized process to tax USF on services. Taxing is done by a USF script, this tax script when executed adds a tax line item to the latest invoice on a customer once the bill run has complete. It will calculate taxes on any transaction associated to a service that has a service tax category of 'USF Taxable'. When billing runs, all charges will be added, the invoice rendered etc and EngageIP will update the bill run history. This tax script is triggered off of the update to the bill run history. It then goes through the invoices in the bill run history and applies the associated tax values.

Configuration

Add a service under each branded owner, name it as "USF Tax Adjustment". This is required when a zero dollar charge is added for the corresponding USF Tax
Add Service Tax Category "USF Taxable" on each service which you would like to apply USF Tax
Add Tax Rate with name of "USFTaxRate" on each branded owner and tie this Taxrate with any given TaxCode
Make sure you have a customField on UDR with "Classification"

Transaction Example:

The below is an example of how the transaction looks when added, note the Detail column. It can be represented on the invoice in any way needed, separately, consolidated with other taxes on the account or otherwise.

Action

Add the below action on the top owner (its only needed at the top owner and will process on all owners below it, branded or not). This requires the EngageIP Event Manager service to be running in order for this to execute.

Action name: "Add USF Tax"
EventCode: BillRunHistory.Update
Script:

const string USF_SERVICE_NAME = "USF Tax Adjustment";
const string USF_TAX_RATE = "USFTaxRate";
const string STATEMENT_DETAILS_DETAIL = "USF Tax";
const string USF_PACKAGE_PROFILE_QUESTION = "seat";
const string USF_PACKAGE_PROFILE_ANSWER = "yes";
const int DIGITS_TO_ROUND_TO = 2;

var billRunHistory = BillRunHistory.GetByID(Convert.ToInt32(context["BillRunHistoryID"]));
var billRunID = billRunHistory.BillRunID;
var billRun = BillRun.GetByID(billRunID);
var connection = Config.GetConnectionString();
var owner = Logisense.Boss.Logic.DomainModel.Owner.GetByID(billRun.OwnerID);
var bossConnection = new System.Data.SqlClient.SqlConnection(connection);

Service adjustmentService = null;

try
{
    bossConnection.Open();
}
catch (Exception ex)
{
    Logisense.Boss.Logic.Core.Alert.Create("Error while establishing Database Connection: " + ex.Message, owner.ID);
}

if (billRunHistory.AmountBilled > 0)
{
    var invoiceQuery = new InvoiceQuery
    {
        BillRunHistoryIDMin = billRunHistory.ID,
        BillRunHistoryIDMax = billRunHistory.ID
    };                                

    foreach (var invoice in Invoice.GetCollection(ref invoiceQuery))
    {
        var countOfSeats = 0;
        var taxableUsage = 0D;  
        var periodStart = DateTime.MinValue;
        var periodEnd = DateTime.MinValue;
        TaxRate taxRate = null;
        TaxCode taxCode = null;
        System.Data.SqlClient.SqlCommand cmd = null;
        System.Data.SqlClient.SqlDataAdapter dataAdapter = null;
        var invoiceUser = User.GetByID(invoice.UserID);

        try
        {
            taxRate = invoiceUser.GetActingOwner().SearchTaxRateByName(USF_TAX_RATE)[0];
        }
        catch
        {
            Logisense.Boss.Logic.EventLogger.Log(Int32.MinValue, invoiceUser.ID, "USF Tax", "USF Tax", "The USF Tax Rate: " + USF_TAX_RATE + " is not found", invoiceUser.OwnerID, "", Int32.MinValue, null);
        }

        try
        {
            taxCode = taxRate.GetTaxCodeTaxRateConnectorCollection()[0].GetTaxCode();
        }
        catch
        {
            Logisense.Boss.Logic.EventLogger.Log(Int32.MinValue, invoiceUser.ID, "USF Tax", "USF Tax", "The USF Tax Rate is not tied to TaxCode: " + USF_TAX_RATE + " is not found", invoiceUser.OwnerID, "", Int32.MinValue, null);
        }

#region Find the count of services which are taxable on this user
        foreach (var statementDetailsInvoice in ViewStatementDetailsInvoiceByPackage.SearchByInvoiceID(invoice.ID))
        {
            try
            {
                if (statementDetailsInvoice.PackageID != Int32.MinValue && statementDetailsInvoice.PeriodStart.Day == 1 && statementDetailsInvoice.PeriodStart != DateTime.MinValue)
                {
                    var package = Package.GetByID(statementDetailsInvoice.PackageID);
                    /*
                    All packages billed on the invoice which have package profile question of Seat with answer "Yes"
                    we will have a count of packages (countofSeats)
                    */
                    foreach (var packageAttributeProfileAnswer in package.GetPackageAttributeProfileAnswerCollection())
                    {
                        if (packageAttributeProfileAnswer.GetPackageProfileQuestion().GetProfileQuestion().Name.ToLower() == USF_PACKAGE_PROFILE_QUESTION && packageAttributeProfileAnswer.GetProfileAnswer().Value.ToLower() == USF_PACKAGE_PROFILE_ANSWER)
                        {
                            countOfSeats++;
                            break;
                        }
                    }
                }

                if (periodStart == DateTime.MinValue && statementDetailsInvoice.PeriodStart != DateTime.MinValue)
                {
                    periodStart = statementDetailsInvoice.PeriodStart;
                }

                if (periodEnd == DateTime.MinValue && statementDetailsInvoice.PeriodEnd != DateTime.MinValue)
                {
                    periodEnd = statementDetailsInvoice.PeriodEnd;
                }
            }
            catch (Exception ex)
            {
                Logisense.Boss.Logic.EventLogger.Log(Int32.MinValue, invoiceUser.ID, "USF Tax", "USF Tax", ex.Message, invoiceUser.OwnerID, "", Int32.MinValue, null);
            }
        }
#endregion end of finding the count of services which are taxable on this user

#region Find UDR which are tied to this Invoice
        /*
        Usage - which has a UDR.classification of the following:
        Toll-Free
        Operator Assistance
        Directory Assistance
        International
        */
        var classList = new List<string> {"Toll-Free", "Operator Assistance", "Directory Assistance", "International"};

        foreach (var udrBiller in UDRBiller.SearchByInvoiceID(invoice.ID))
        {
            foreach (var udr in UDR.GetCollection(string.Format("Where UDRBillerID = {0} and Classification in ('{1}')",udrBiller.ID, String.Join("','", classList.ToArray()))))
            {
                taxableUsage += udr.Amount;
            }
        }
#endregion end of finding UDR

#region Tax Add adjustment
        var sqlUSFTaxExist = string.Format(@"select * from StatementDetails sd
            inner join InvoiceStatementConnector isc on isc.StatementID = sd.StatementID
            where sd.Detail = '{0}' and isc.InvoiceID = {1}", STATEMENT_DETAILS_DETAIL, invoice.ID);
       
        cmd = new System.Data.SqlClient.SqlCommand(sqlUSFTaxExist, bossConnection);
        dataAdapter = new System.Data.SqlClient.SqlDataAdapter(cmd);                    
        var myDataTable = new System.Data.DataTable();
        try
        {
            dataAdapter.Fill(myDataTable);
        }
        finally
        {
            dataAdapter.Dispose();
            cmd.Dispose();
        }

        if (countOfSeats != 0 && myDataTable.Rows.Count == 0)
        {
            try
            {
                adjustmentService = invoiceUser.GetActingOwner().SearchServiceByName(USF_SERVICE_NAME)[0];
            }
            catch
            {
                Logisense.Boss.Logic.EventLogger.Log(Int32.MinValue, invoiceUser.ID, "USF Tax", "USF Tax", "The USF Tax service: " + USF_SERVICE_NAME + " is not found", invoiceUser.OwnerID, "", Int32.MinValue, null);
            }

            var openInvoiceID = Logic.Billing.Bill.RetrieveUserOpenInvoice(invoiceUser.ID).ID;
            if (invoice.InvoiceStatusTypeID == InvoiceStatusType.GetByName("Closed").ID && openInvoiceID == Int32.MinValue)
            {
                invoice.InvoiceStatusTypeID = InvoiceStatusType.GetByName("Open").ID;
                invoice.Update();
            }

            //add USF Tax
            double usfTax = ((countOfSeats * 18) + taxableUsage) * 0.649 * taxRate.Rate;
            usfTax = Logisense.Boss.Logic.Billing.Rounding.Round(usfTax, DIGITS_TO_ROUND_TO);

            var statementId = Logisense.Boss.Logic.Billing.Bill.RetrieveStatement(invoiceUser.ID).ID;
            try
            {
                // We don't need to perform an invoice summary calculation as it is done when the invoice is closed
                var statementDetails = Logisense.Boss.Logic.Billing.Bill.AddCharge(statementId, invoiceUser.ID, 0.0, STATEMENT_DETAILS_DETAIL, periodStart, periodEnd, false, DateTime.Now, adjustmentService, new InvoiceSummaryCalculationNotNeeded());

                var statementDetailsTax = (Logic.Core.StatementDetailsTax)StatementDetailsTax.GetNew();
                statementDetailsTax.Amount = usfTax;
                statementDetailsTax.StatementDetailsID = statementDetails.ID;
                statementDetailsTax.TaxRateID = taxRate.ID;
                if (taxCode != null)
                {
                    statementDetailsTax.TaxCodeID = taxCode.ID;
                }
                statementDetailsTax.Reversed = false;

                // We don't need to perform an invoice summary calculation as it is done when the invoice is closed
                statementDetailsTax.Create(new InvoiceSummaryCalculationNotNeeded());

                Logisense.Boss.Logic.Invoicing.CloseInvoice(invoice, DateTime.Now, Int32.MinValue);
                Logisense.Boss.Logic.Invoicing.RenderInvoice(invoice);
                Logisense.Boss.Logic.EventLogger.Log(Int32.MinValue, invoiceUser.ID, "USF Tax", "USF Tax", "USF Tax is added, amount: " + usfTax, invoiceUser.OwnerID, "", Int32.MinValue, null);
            }
            catch (Exception ex)
            {
                Logisense.Boss.Logic.EventLogger.Log(Int32.MinValue, invoiceUser.ID, "USF Tax", "USF Tax", ex.Message, invoiceUser.OwnerID, "", Int32.MinValue, null);
            }
        }
#endregion end of adding Tax adjustment

    } //end of for each Invoice

}

try
{
    bossConnection.Close();
    bossConnection.Dispose();
}
catch (Exception ex)
{
    Logisense.Boss.Logic.Core.Alert.Create("Error while closing Database Connection: " + ex.Message, owner.ID);
}

return true;