Avalara Billsoft - Installation and Configuration

Summary

This article describes how the on-prem Avalara billsoft integration functions and is configured in EngageIP. If you are configuring the AvaTax Cloud version please see the Avalara AvaTax Cloud – Configuration article instead.

Normally within EngageIP taxes are calculated based on user-defined rates. A Tax Code which defines a geographical region will be setup under an Owner, within the Tax Code are rates which apply within the region (i.e. federal, state, county, city). To accurately tax a given customer, all necessary information regarding applicable taxes must be pre-populated by the Owner. A Tax Code which uses the EngageIP taxing system is setup using the Local tax vendor.

Using billsoft, this process is simplified. Instead of having to configure each and every tax, you simply setup one all-encompassing Tax Code which uses billsoft as its tax vendor. In this scenario no Tax Rates need to be setup within EngageIP by the user, EngageIP will communicate with Billsoft to retrieve all relevant taxes for a customer's location. If you configure Billsoft to work with EngageIP when you only have customers within one state, you will not have to reconfigure Tax Rates when you've grown to serve additional states.

EngageIP/ Billsoft Functionality Overview

When you add or edit a contact point's address in EngageIP a pcode (aka a Jurisdiction code in EngageIP) stored within the EngageIP database will also be updated. The pcode is a number that maps to the tax jurisdiction that an address falls into. Even if an address' tax jurisdiction changes, it's pcode/jcode never will. By storing this number, it means never having to look up any information about the address again, unless the address changes.

When EngageIP attempts to get the required taxes from the billsoft application it provides the amount and date and looks up the appropriate p-code. The date is important as Billsoft maintains an historical record of tax rates. Based on this info Billsoft passes a collection of taxes back to EngageIP with details including the name of the tax, the rate and the amount.

When EngageIP gets the taxes back from Billsoft, it looks at its current tax rates and sees if there are matches. Any taxes which do not have rates in the TaxRate table, have a new entry added. These auto-generated tax rate entries lack jurisdiction information and are not visible to the user. They are only stored so that the tax (name, rate) can be retrieved for tasks such as printing invoices.

If no jurisdiction code is present for a contact's address when EngageIP tries to charge taxes, EngageIP will attempt to retrieve the proper code. If the code is found it will be stored in the database and EngageIP will continue with tax calculations. If this fails (or for some other reason a tax rate cannot be found) a record gets created to indicate that the statement detail was not taxed. EngageIP will reattempt to charge tax on this detail when the invoice is closed. If it is successful, then it will add the tax to the detail, and remove the flag that indicates taxing was unsuccessful. However, if taxing is still unsuccessful, then the flag will stay set. When the user's next invoice is closed, EngageIP will reattempt to tax these old details. If it is successful, it will create a new $0.00 detail on the current invoice with taxes on it, and a description to indicate that they're previously failed taxes. Currently, EngageIP will try indefinitely to charge the taxes for an account with each closed invoice.

Jurisdiction Updates

Billsoft automatically updates jurisdictional boundaries when billsoft updates are applied. Any alterations in jurisdictional boundaries (such as an expansion of a city limit) will be reflected in the JCode of locations that are situated within the expanded jurisdiction area. The Avalara tax research staff monitors jurisdiction taxation activity to maintain current and accurate taxation information. The results of this research is applied to monthly updates which are provided to insure the most accurate taxation is applied to the records processed by the billsoft Engine. JCodes change as monthly updates are applied, it is the users responsibility to re-populate their database tables with the new JCodes every time a Billsoft update is provided. If this task is not performed, the Billsoft Engine will return incorrect tax calculations for jurisdictions that have been updated. However, when a transaction is submitted with the stable and location sensitive p-code, the Billsoft Engine can use internal functions to obtain the correct jurisdiction. Since the Billsoft Engine is updated with the re-mapped jurisdiction information monthly, tax calculations are always performed with the most current jurisdiction taxation information that applies to the address defined by the p-code.

Tax Application Process

Taxes are applied based on the following criteria in order of precedence:

1. If service tax category or account tax category exist on the service or the account, take this into account when checking 2 and 3

2. If the tax code exists on account, tax based on tax code

3. Else if Service Contact exists, tax service based on Service Contact address

4. Else (if none of the above are applicable) tax based on Billing Contact address

Sub Account Taxing

If a parent account in EngageIP has a taxable address, then any sub account created that has no address or tax code component of their own will be taxed based on the parents details. To tax a sub account differently ensure the sub account has either a valid address with an associated jurisdiction code address component or has a tax code component override (tax exempt or override).

System Requirements

Billsoft minimum requirements

• Windows 7 or Windows Server 2003

Base Configuration

To begin installation you will need to downloaded the installer from Avalara (this should be named something like win64_xxxx.zip).

1. Download either the 32bit or 64bit installer files. Which you choose will of course depend on whether the server you are installing it on is 64bit or 32bit. Extract the files. Run update.exe. This will walk you through installing the Billsoft software. Avalara strongly suggests using the default directories (i.e. C:\BillSoft). The EngageIP library expects to find EZtax in this location

2. If EngageIP is running on a 64bit server:

   1. Ensure you installed the 64bit version of Billsoft

   2. Verify the IIS APP POOL has 'Allow 32 bit' set to false

   3. Verify HANDLERS in IIS has only one .rails pointing to FRAMEWORK64 (Check this for the AdminPortal and default sites)
      

3. Configure the Environment Variables as outlined by the Billsoft Documentation *** Environment Path should be equal to: C:\BillSoft\EZTax\DLL;C:\BillSoft\EZTax\EXE; (Make sure no spaces are used and that the semicolons are in place)

   

4. Reboot the system for the Path changes to take effect

5. In the EngageIP AdminPortal, Setup page add the following Workflow to update p-codes based on the users address
   Create an "Action"
   Name: EZtax pcode
   Event: ContactPointAddress.Create,ContactPointAddress.Update
   Code:

   int id = Int32.Parse(context["ContactPointAddressID"].ToString());
   return EZTaxWrapper.GetPcode(id);

6. Configure the Billsoft tax vendor for transactional or invoice level taxing

   1. To configure taxation per transaction:

      1. Load the Setup page

      2. Load Tax Vendor

      3. Click Add

      4. In the TaxVendor Type list select BillSoft

      5. Click Save

      6. For Vendor enter Billsoft

      7. For the Charge Script enter

         TaxCode taxCode = (TaxCode)context["taxCode"];
         double taxableAmount = (double)context["taxableamount"];
         StatementDetails sd = (StatementDetails)context["statementdetails"];
         int taxTypeId = (int)context["taxtypeid"];
         return TaxVendorManager.GetTaxes(taxCode, taxableAmount, sd, taxTypeId);
         

      8. Click Save

   2. To configure taxation on invoice close:

      1. See the invoice level taxing section below

7. Enable the tax log, detailed here (further down page) This is required for reporting and reconciliation purposes

8. Check on the Configuration page in the EngageIP AdminPortal to ensure scripts are compiling correctly. If not, check for errors

9. Update the Billsoft install folder (default install directory name is 'BillSoft') granting EVERYONE - FULL CONTROL permissions

10. In the AdminPortal / Setup page add a Tax Code called 'Default' as shown below

    

    Note: the less you configure in the Tax Code the more customers it will apply to. I.e. if you do not populate any location details (Country, State, County, City) then all accounts under the owner will use the Tax Code and therefore use Billsoft. If you need to tax services slightly different by way of tax categories, you can create multiple tax codes called Default with different location configurations to meet your requirements
    

11. Import Service Tax Categories or Setup Custom Billsoft Tax Types (EngageIP 8.5.17.0+ required)

    1. Import Service Tax Categories using the the instructions provided below. You will need to make this list into an XML file for import using the following format: XML Files and Data Formatting
       
       The ‘Service TaxTypes Import’ xml file can be found below these steps under ‘Import Documents’
       
       Note: Service Tax Categories if used at all should be added to ALL services that require taxing to be sure the correct tax is applied. Please contact Avalara support for further assistance here
       
       Note: if you are running an EngageIP version below 8.5.17.0 and want taxes to be applied by Billsoft, you MUST use the service tax categories that Billsoft recognizes. Custom Service Tax Categories are not available prior to EngageIP 8.5.17.0
       

    2. See the Setting up Custom Billsoft Tax Types section below if you wish to setup Custom Billsoft Tax Types

12. Setup / Import Account Tax Categories (Billsoft supports the following tax categories - Residential, Residential Incorporated, Business, Business Incorporated)
    Note: An account tax category of just 'Residential' implies that the account is 'unincorporated'. The same is true for a 'Business' account tax category, it really means 'Business Unincorporated'

    1. Import Account Tax Categories using the XML provided in the 'Import Documents' area below or Configure Tax Categories on the Setup page and add them to the customer accounts as required

    2. Account Tax Categories are added as components on the customers 'Overview' page. Load the customer's Account and under the Components section click Add and then Account Tax Category. Select the Appropriate tax category and click Save
       Note: Account Tax Category needs to exist on the sub account and on the main account to tax the account specific packages and services correctly

13. For EngageIP 8.5.26.0+ Installs:

    1. Download, extract and run the EZTaxTaxTypeMappingInsertScript provided below on the EngageIP DB server to create and populate the EZTaxTaxTypeMapping table
       

Import Documents:

Post Configuration Steps

Testing

To validate that the Billsoft integration is working in EngageIP, do the following:

1. Add an Account

2. Load the Contacts page / Billing Contact

3. Add an Address Contact Pointy Type to the Billing Contact

4. Verify that the Billing Contact Address has a Jurisdiction Code Component added to it automatically (if not see Troubleshooting section below)

   

5. Add a Package that contains a Service to the account. The tax should be automatically calculated out of the EZtax database

6. Check the customer invoice or Transactions page to verify that taxes are being applied

Reporting

To add visibility into Accounts which may be missing Jurisdiction Codes (JCodes) you can setup a Custom Report in EngageIP. See the article Find Missing Jurisdiction Codes for setup instructions.

Applying Jurisdiction Codes to All Accounts

To add jurisdiction codes to all customers in the system (if you've configured Billsoft after customer Accounts were already present in EngageIP) without the need to navigate to and save every Address Contact Point Type, you can use the updater tool on the webserver located here:

C:\Program Files (x86)\EngageIP\Tools\

Double click the 'ContactPointAddressUpdate.exe' executable. This tool when run will look at all Users and attempt to add Jurisdiction Codes where there are none.

Additional Configuration Options

Setting up Custom Billsoft Service Tax Types

1. Load the Setup page

2. Click on Service Tax Categories

3. Click the Add button

4. Enter the name in this custom format CustomEZTax(Name,<transactionType>,<serviceType>)

   • the format above cross-references the Tax Type Name to the Transaction/service types

   • e.g. a Tax Type with the name CustomEZTax(MyTaxType,1,2) would configure a custom tax type with transaction type 1, and service type 2

   • the Name must be entered without slashes or spaces, i.e. CustomEZTax(VPNxxxMPLS,61,586)

5. Click Save when finished or Save/New to save the new custom Tax Type and create another one

Configuring Service Tax Categories

When using transaction codes, they should appear similar to the below:

Mapping Guidelines doc can generally be found in the updates folder after you download and extract the latest updates. The folder is called 'Support'. This folder is not by default in the install directory, just the extracted update file directory.

Adding Service Tax Categories to Services

1. Click Setup

2. Click Services

3. Click on the name of the desired service

4. Click the Add button under Components

   • Select Service Tax Category

   • Select the appropriate item

   • Click the Save button

5. Done - Repeat the steps to configure additional service tax categories on services as needed

Multiple Branded Owners Install

The below list needs to be completed on each branded owner

• Script below should be run to propagate scripts

• Account Tax Categories import

• Service Tax Categories imported

• Country set to USA

• Default Tax Code Created

The below will loop through all branded owners and insert the taxvendor script, one for each branded owner in a system where there are multiple owners needing the tax vendor configuration.

CREATE TABLE tempBillSoftXYZ44
(
OwnerID INT,
[Name] nvarchar(255),
ChargeScript ntext,
InvoiceChargeScript ntext
)

INSERT INTO tempBillSoftXYZ44 (OwnerID) SELECT o2.ID AS OwnerID FROM [Owner] AS o2 INNER JOIN ViewOwnerParent vop ON vop.OwnerParentID = o2.ID WHERE o2.ID NOT IN (SELECT o.ID FROM [Owner] o INNER JOIN TaxVendor tv ON o.ID = tv.OwnerID WHERE tv.Name = 'BillSoft') AND vop.Branded = 1;

UPDATE tempBillSoftXYZ44 SET [Name] = 'BillSoft', ChargeScript = 'TaxCode taxCode = (TaxCode)context["taxCode"];
double taxableAmount = (double)context["taxableamount"];
StatementDetails sd = (StatementDetails)context["statementdetails"];
return EZTaxWrapper.GetTaxes(taxCode, taxableAmount, sd);', InvoiceChargeScript = ''

UPDATE TaxVendor SET ChargeScript = 'TaxCode taxCode = (TaxCode)context["taxCode"];
double taxableAmount = (double)context["taxableamount"];
StatementDetails sd = (StatementDetails)context["statementdetails"];
return EZTaxWrapper.GetTaxes(taxCode, taxableAmount, sd);'
WHERE Name='BillSoft'

INSERT INTO TaxVendor (OwnerID, [Name], ChargeScript, InvoiceChargeScript) SELECT * FROM tempBillSoftXYZ44

DROP TABLE tempBillSoftXYZ44

UDR / CDR Taxing

Requirements for UDR Level Taxing:

• Billsoft or another integration installed

• EngageIPOption  (as below) set to TRUE

• Rating custom code / Feed code updated with Taxing lines as appropriate (generally customized as needed by LogiSense based on taxing requirements)

• Feed has a processor included on setup for 'Set UDR Taxing'

• Existing records will not be taxed if added prior to this configuration - rerating may need to occur to calculate taxes on existing records

To turn on UDR and CDR level taxing using Billsoft, execute the following SQL:

INSERT INTO EngageIPOption (Name,VALUE) VALUES ('CDRLevelTax','true')

If this setting is not present, it effectively means that its OFF or FALSE.

If this setting is false the system will calculate tax and insert it into the statementdetailstax table and use that for invoice presentation. Tax will be calculated based on the Service Tax Category of the service which is stamped on the Telecom Usage transaction.

If the setting is true EngageIP will use the tax found (previously calculated during rating) in the UDRBillerTax table.  It will sum up the UDRBillerTax value and then stamp that as the statementdetailsTaxID on the associated statementdetail record.

If this setting does not exist the system will use the TaxTypeID on the UDRBiller table. The TaxTypeID is set by the rating service, this is found by looking at the service where the rateplan exists on the User that is associated and uses the Service Tax Category from that service.

The UDRBiller.TaxTypeID comes from the ServiceAttributeTaxType of that User's UserService listed in UUIH or UserserviceAttributeUDRBucket(USAUB). Depending on the rules set in your specific RATING script that might be the Userservice related to the RatePlan OR the Userservice where the DID/UUIH.Identifier was located, and those could be different....If the UserID has multiple valid entries in UUIH each UUIH.Userservice's TaxTypeID will have a UDRBiller for it.

UDRBillers are also created for USAUBs and reflect their Userservice-ServiceAttributeTaxType.

The rating script should look something like below and have the 'Set UDR Taxing' processor in it:

International Taxing

International taxing with functions very similarly to the way US and Canada taxing functions. In the postal code text box, enter a valid code, and ensure that the country on setup has a valid ISO defined, 2 character. This will match to the CountryISO table in the EngageIP database.

International taxing with Billsoft operates at a country wide level (there is no granular taxing using this configuration).

Service Tax Categories need to be categories that are compatible with international taxing, for international transactions outside of U.S. States and Territories, Canada and Brazil, most countries support the following t/s pairs.

Import or add manually the below Service Tax Category to be used for international services. 'Telephony Service'

<TaxType Owner="admin" TaxType="Telephony Service"/>

Tax Exemption Options

Tax exemption is now available at multiple levels, as below.

• All - this exempts ALL Billsoft taxes

• Federal

• County

• State

• Local

• County_Local

• Other

• State_County_Local

• Unincorporated

Configuring Tax Exempt Levels

Tax Exempt Type Import XML file:

Account Level Tax Exemption:

1. Click on the Overview page of the account you wish to apply tax exemption to

2. On the right side under Components click Add

3. Select Tax Exempt Level (To determine the correct selections, see the description below this list describing how to find it)

   • Select the specific exemptions such as Federal exemption.

   • Optionally: select the tax exempt type (as illustrated in the image where there is a federal base exemption and the type is ADVANCED...)

   • Click Save

4. Repeat this process if you need to apply multiple tax exemptions at different levels to the user account (the above image shows to exemptions applied, one for state taxes and one for federal taxes)

Determining the exempt level selections:

To determine what the exemption type is that you should be selecting, first review the tax if it has been applied on a transaction, it will look like the below. The similar sections to be reading are colourized to assist in recognizing and associating them.

Sales Tax [State]_USA_CT_NEW HAVEN_YALESVILLE_0.0635

The level from above would be 'State' as described in the square brackets. The type then can be found by the information before the square brackets. The matching selection to exempt this applied tax would then be as shown in the below image:

In addition, if the tax detail shows 'VOIP', you will then need to look for the exemption type labeled 'SALES_TAX_VOIP'. A VOIP example would be this tax detail:

FCC Regulatory Fee (VoIP) [Federal]_USA_0.00375

The appropriate exempted setup would be this:

Level: Federal (where federal is specified in the square brackets providing the clue)

Type: FCC_REGULATORY_FEE_VOIP (where the type is basically what is shown in the transaction detail with the additional VOIP element attached.) (Similar bracketed values may include 'WIRELESS' and such)

Testing:

1. Add a transaction that taxes with the tax you want to exempt

2. Confirm the tax is charged as expected

3. Add the new exemption

4. Add a new transaction for the same amount

5. Confirm that the tax amount is zero for that same tax type

Tax Override Options

There are two basic ways to override taxes when using the Billsoft integration

1.  With the tax manager software provided by Avalara you can manage overrides, customization's, etc.

   • With the manager, one can make .OVR files which are placed in the BillSoft/Data folder and processed when Billsoft starts. The manager is not needed if you have an OVR file from another system

   • If you have your own  custom override file where the manager is not installed on the system you're updating, ensure the file is named 'eztax.ovr'

   • Do an IISReset after this to ensure all is reloaded here.

2. Implement EngageIPOption table items to force EngageIP to tax a specific rate at a specific value

EngageIPOption Method:

This method uses the EngageIPOption table to hardcode an amount for a specific rate that Billsoft would otherwise tax at the going rate.

Add an entry to EngageIPOptions with a name of BillSoftTaxOverRide and a value of something like 44,State,10.00. You need to look up the tax in the Transaction mapping guide provided by Billsoft (this is found in your Billsoft monthly installation directory in a folder called 'docs'). This will cause E911 Tax (Wireless) to always be this rate.  You can set a rate to 0. Make sure to reset IIS after updating the table for the new overrides to take effect.

If you want to override  911 (Wireless), you look it up in transaction mappings guide (the image above is example of multiple overrides in a system).

44 E911 Tax (Wireless)

You enter: 44,State,0 (ID, Tax Level, Amount in decimal form)

And it would never return that tax again.

If you enter: 44,State,10.00; It will always return 10.00

BillSoft: Found Tax - Communications Service Tax- Communications Service Tax [Local]_USA_FL_DADE_MIAMI 0.0572 ExemptAmount: 0 TaxAmount: 12.0297798764
BillSoft: Found Tax - Communications Service Tax- Communications Service Tax [State]_USA_FL_DADE 0.068 ExemptAmount: 0 TaxAmount: 14.301136916
BillSoft: Found Tax - E911 (Wireless)- E911 (Wireless) [State]_USA_FL_DADE 10 ExemptAmount: 0 TaxAmount: 10
BillSoft: Found Tax - Statutory Gross Receipts- Statutory Gross Receipts [State]_USA_FL_DADE_MIAMI 0.0237 ExemptAmount: 0 TaxAmount: 4.9843668369
BillSoft: Found Tax - Fed USF Cellular- Fed USF Cellular [Federal]_USA 0.153 ExemptAmount: 125.171 TaxAmount: 11.295837
BillSoft: Found Tax - FCC Regulatory Fee (Wireless)- FCC Regulatory Fee (Wireless) [Federal]_USA 0.015 ExemptAmount: 125.171 TaxAmount: 0.015

Tax Inclusion and Exclusion

Excluding Country or State Taxes Per Owner

The configuration steps below describe how to exclude taxes per Owner at a Country level (both Federal and State taxes) or at a State level (State/Provincial taxes only).

Tax Exclusion Application:

Tax Exclusion logic references the Jurisdiction code (JCode) on a Service or Billing Contact's Address. If the Country/State of the Jurisdiction Code on the customer's Service Contact Address matches the Country/State of a Tax Exclusion setup under the Owner then all taxes of the appropriate type (Federal/State) will be excluded when taxes are applied to a Service or Usage. If an Account does not have a Service Contact configured with a JCode on its Address Contact Point then the JCode on the Billing Contact's Address will be referenced instead. If neither Contact exists on the Account with an Address and the corresponding JCode then a check will be performed for JCode's on a Parent Account's Service or Billing Contact's Address. If no JCodes exist on Addresses on the Account or Parent Account then no tax will be excluded.

Tax Exclusion Configuration:

1. Load the Setup page

2. Under the Taxes heading click on Tax Exclusions

3. Click Add

4. Enter the details for the location you wish to exclude from taxation

   

   • Country ISO: enter a two or three digit ISO Code for the Country (e.g. US or USA are both valid entries). If you wish to exempt all Federal and State taxes enter the Country ISO alone without any State value

   • State: enter the two digit State or Province abbreviation to exclude taxation at a State level (the image above shows how to exclude California State Taxes)

5. Select an option to Save the Tax Exclusion

   • Save/New will add the exclusion and proceed to add another exclusion

   • Save on 'X' Branded Owners will add the Tax Exclusion to the current Owner you are configuring and the Branded Owners that exist underneath the current Owner

Importing Tax Exclusions

You can import Tax Exclusions by using the EngageIP XML Import feature. First create the XML (sample TaxExclusionImport xml)

Then do the following:

1. Load the Setup page

2. Click on Import

3. Either click Choose File or paste the XML into the XML Data field

4. Click Save and you should see a 'Import successful.' message

5. Return to the Setup page and load the Tax Exclusions page to verify the imported exclusions are present

Excluding Specific Rates Per Owner

An advanced configuration exists where you can tell EngageIP to only tax particular rates across an owner, or exclude particular rates across an owner. This requires EngageIP version 8.4.5.1 or greater. To configure this, two tables need to be created with below query.

1. Open BOSS database

2. Execute table creation queries as below

3. In the include or exclude tables enter the Owner ID (gathered from owner table) and the tax rate name.
   Note: this is the short name, not the long name. Examples are 'Sales Tax' or 'E911 (VoIP)'

4. Reset IIS - this is required for EngageIP to read the table after you've updated it

5. Login to adminportal

6. Add a transaction for testing purposes to confirm that either the tax rates defined were excluded, or if in the include table, ONLY those tax rates were applied for the owner they are attached to.

If there is something in the TaxVendorTaxInclude table for that owner, then this table will be used. It will only charge tax for the tax name (Tax Description) in this table. You can have Multiple entries:

Sales Tax
E911

Only Sales Tax and E911 tax will be charged and log into taxlog. Nothing else. TaxVendorTaxExclude will exclude the tax for that owner.

Example rate names:

"E911 (VoIP)"
"E911"
"E911 (Wireless)"
"E911 Equalization Surcharge"
"E911 (Business)"
"E-911 Technical Charge"
"E911 Operational Surcharge County Commission"

Create Tables SQL:

CREATE TABLE [dbo].[TaxVendorTaxInclude](
[ID] [INT] IDENTITY(53,1) NOT NULL,
[OwnerID] [INT] NOT NULL,
[TaxName] [nvarchar](255) NOT NULL,
CONSTRAINT [PKC_TaxVendorTax
Include_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TaxVendorTaxInclude] WITH CHECK ADD CONSTRAINT [FK_TaxVendorTaxInclude_Owner] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[Owner] ([ID])
GO

ALTER TABLE [dbo].[TaxVendorTaxInclude] CHECK CONSTRAINT [FK_TaxVendorTaxInclude_Owner]
GO

CREATE TABLE [dbo].[TaxVendorTaxExclude](
[ID] [INT] IDENTITY(30,1) NOT NULL,
[OwnerID] [INT] NOT NULL,
[TaxName] [nvarchar](255) NOT NULL,
CONSTRAINT [PKC_TaxVendorTaxExclude_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TaxVendorTaxExclude] WITH CHECK ADD CONSTRAINT [FK_TaxVendorTaxExclude_Owner] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[Owner] ([ID])
GO

ALTER TABLE [dbo].[TaxVendorTaxExclude] CHECK CONSTRAINT [FK_TaxVendorTaxExclude_Owner]
GO[cce lang="sql"]

Excluding Taxes on Specific Services

If you require no tax to be applied to specific Services you can configure a Service Tax Category of 'No Tax' and then add the Service Tax Category Component to the tax exempted Services on the Setup tab

Configuring Inclusive Taxes on Services

When added to a Service the 'Inclusive Taxes' Component cause taxes to be calculated within the set price of the Service in question. Inclusive tax calculations are performed by Billsoft with EngageIP using the returned values to set the revenue and taxes on Services.

Inclusive Tax System Configuration

1. Access the EngageIP host

2. Open the BillSoft installation folder and edit/add the EZtax.cfg file in the Data sub-directory (e.g. C:\BillSoft\EZTax\Data\EZTax.cfg)

3. Using a Text Editor add the following line at the end of this file

   reversecalcsession=on

4. Save and close the file

5. Load the EngageIP Adminportal Setup page and select Tax Vendor

6. Edit the BillSoft tax vendor and replace the existing script with the one listed below
   BillSoft Tax Vendor Script

   TaxCode taxCode = (TaxCode)context["taxCode"];
   double taxableAmount = (double)context["taxableamount"];
   StatementDetails sd = (StatementDetails)context["statementdetails"];
   int taxTypeId = (int)context["taxtypeid"];
   return TaxVendorManager.GetTaxes(taxCode, taxableAmount, sd, taxTypeId);

Adding the Inclusive Tax Component to Services

1. Login into the AdminPortal

2. Load the Setup page

3. Select Roles and enable ServiceAttributeInclusiveTaxes permissions for the roles that need to be able to add/delete the Inclusive Taxes component

4. Return to the Setup page and click on Services

5. Add the new Inclusive Taxes component on the Services where Inclusive taxes are required

To verify Taxing is working, first follow the Testing Billsoft Taxing section below, and then add the appropriate package to a test account and verify that inclusive taxation is occurring by referencing the Transactions page.

Canadian Billsoft Tax Code Configuration

To setup Billsoft for Canada, simply create a new taxcode called 'Default - Canada' (for example), set the country = Canada and the tax vendor = EZtax. Click save and test on a Canadian account (Canadian address) to confirm taxing is applied at the correct rates.

Billsoft Region Specific Taxing

For Canada, a CRTC Levy is required in some cases, this requires manual configuration.

CRTC Levy: When a charge is taxed it checks the p-code and if 266 is present it adds an override before it does anything. If the override already exists it skips it. In EngageIPOptions (database table update to be done by LogiSense Customer Care) there must be the following entry:

Name=BillSoftLevy
Value=1.15

If you change the value you must restart everything (billservice, adminportal). It only runs this code of BillSoftLevy is present, if it is not, it is like it always was. The images below depict the address and pcode as they might show on a user and an example of the CRTLevy tax applied on a transaction of $10.

Billsoft Updates

Avalara provides monthly updates which must be installed on your webserver for taxes to be calculated correctly. Avalara also provides monthly update instructions but generally they are as is listed below:

1. Login to Avalara Support site with your username and password

2. Download the update zip file for the monthly update

3. Stop the following services: (this is required as these may be 'holding onto' the EZtax dll's)

   • EngageIP Event Manager service

   • EngageIP Billing service

   • EngageIP Job Service

   • EngageIP Tier Sync service (if installed) (administrative tools/services)

   • Any other potential processes connected to EngageIP

4. Extract to the webserver on C:\BillSoftInstallFiles (the folder name is irrelevant as long as its extracted)

5. Execute 'update.exe' (based on BillSoft's instructions)

   • Check in the BillSoft /data folder that all files were updated - Billsoft will usually prompt you if a reboot is needed

6. Start services as needed from step #3

7. Reset IIS

8. Reboot of the server to fully release DLLs and clear any .TMP files that exist in the BillSoft folder (the Billsoft update may also request a reboot)

9. Testing / Confirmation

   • In the AdminPortal,  go to the top owner and click Configuration. At the bottom of the configuration page is the status of the BillSoft install as it relates to EngageIP. There should be no errors reported there, if there are, please contact LogiSense Customer Care

     

   • Test that taxing is taking place. If not, review the Alerts report and event log report for detail

     • load the test customer acccount

     • add a transaction ($1.00 for example)

     • confirm taxes are applied and accurate
       Note: the first transaction added can take up to 5 minutes to load since the Billsoft files have to be initialized after the updates from Billsoft are installed on your system

   • Ensure that rating is running and working after the updates application (if you have usage rating in place and configured)

10. Check the Backup folder (C:\BillSoft\EZTax\Data\BACKUP) to ensure that back-up files are not consuming excessive disk space. Purge old files as needed

Extra Information

• Two attachments describing the list of tax categories Billsoft supplementary documents can be found in the Billsoft install directory (i.e. C:\BillSoft\win32_v9_0806\support\Docs). In this path you'll find tax category mapping information and other detailed info relating to your Billsoft installation

• Extra or missing p-codes can be found in a flat file here for reference: C:\BillSoft\EZTax\Txt\all_adr.txt

Testing Billsoft Taxing

To test, you can perform the list of procedures below:

1. Go to a known good taxing account or where taxes have been applied previously

2. Click on their billing contact

3. Click on the Address contact point

4. Notice the 'Jurisdiction code'. This is auto generated by the Billsoft integration

   • Delete the jurisdiction code on the billing contact by clicking the 'delete' link beside it

   • Click save on the address screen (this will prompt the system to regenerate the code)

   • Click on the address contact point again and see if the jurisdiction code has been added again

   • If not, see troubleshooting below - possibly the Event Manager service isn't running or there is a larger issue

5. Next, on a good known taxing account that you can use for testing, add a test charge that should prompt the calculation and addition of taxes. i.e. add a $10 charge and confirm that taxes have been automatically calculated and applied

If both steps 4 and 5 are working, Billsoft has been updated correctly and is functioning correctly. If either one is not working, see troubleshooting steps below.

Additional Testing Features

• There is a Billsoft test tool which can be run on the command line. Format from prompt is 'billsofttest.exe 12345' where 12345 = the statementdetailsID. This is normally used by LogiSense for specific tax application review

• Billsoft provides an 'AFC Viewer' which comes with the Manager software as shown below. With it you can enter a jurisdiction code or other combination of details to see what EZtax would calculate for a particular transaction type.

• If you do not see the option to load this under 'Tools', it means you need a licence file in the install directory of the manager tool. The license file is a .bin file that enables this option and can be acquired from Avalara.

  

Zero Jurisdiction Code Updater Action

If jurisdiction codes are not able to be found, the Billsoft integration will simply assign a zero for the pcode (jurisdiction code). To locate and update these in an automated fashion, the following action can be implemented to set generic pcodes in states / provinces where its not able to locate a specific pcode.

Event:

Event can be timer.midnight. Be sure that there is a timer named that in the database.

1. Open BOSS database

2. Open table 'WorkflowEventTimer'

3. Enter name in name column

4. Enter hours and minutes in same named columns, this is 24 hour time. 23 = 11pm

5. Hit enter. The first and last column can be left null and will be updated by the system.

6. Remember the exact naming here as it will be entered on the action below

Action:

It should not be triggered off of ContactPoint.Update or add since that will fight for resources with the general jurisdiction code updater script.

The below is for free form state entry, where state is not configured as predefined so this looks by state name, not ID. Need to determine which the system users and select the appropriate action script

ViewContactPointAddressAttributeJurisdictionCodeQuery vcpajcq = new ViewContactPointAddressAttributeJurisdictionCodeQuery();
vcpajcq.JurisdictionCodeMin = 0;
vcpajcq.JurisdictionCodeMax = 0;
foreach (ViewContactPointAddressAttributeJurisdictionCode my_vcpajc in ViewContactPointAddressAttributeJurisdictionCode.GetCollection(ref vcpajcq))
{
long pcode = 0;
EZTaxWrapper.GetPcode(my_vcpajc.ContactPointAddressID);

ViewContactPointAddressAttributeJurisdictionCode reload = ViewContactPointAddressAttributeJurisdictionCode.GetByID(my_vcpajc.ID);
State state = State.GetByID(reload.StateID);

switch (state.Name)
{

case "BC": pcode = 4540500;
break;
case "NB": pcode = 4633300;
break;
case "NL": pcode = 4675600;
break;
case "AB": pcode = 4482300;
break;
case "NT": pcode = 4704800;
break;
case "NU": pcode = 5101301;
break;
case "QC": pcode = 4882600;
break;
case "PQ": pcode = 4882600;
break;
case "SK": pcode = 5031900;
break;
case "ON": pcode = 4746700;
break;
case "MB": pcode = 4588300;
break;
case "NS": pcode = 4710700;
break;
case "PE": pcode = 4875900;
break;
case "YT": pcode = 5099700;
break;
default:
pcode = 0;
break;

}
if (pcode != 0)
{

try
{
ContactPointAddressAttributeJurisdictionCode cpaajc = ContactPointAddressAttributeJurisdictionCode.GetByID(my_vcpajc.ID);
cpaajc.JurisdictionCode = pcode;
cpaajc.Update();
}
catch
{
}
}

}
return true;[cce lang="C#"]

The below code is for a system where states are predefined and therefore run off IDs

ViewContactPointAddressAttributeJurisdictionCodeQuery vcpajcq = new ViewContactPointAddressAttributeJurisdictionCodeQuery();
vcpajcq.JurisdictionCodeMin = 0;
vcpajcq.JurisdictionCodeMax = 0;
foreach (ViewContactPointAddressAttributeJurisdictionCode my_vcpajc in ViewContactPointAddressAttributeJurisdictionCode.GetCollection(ref vcpajcq))
{
long pcode = 0;

EZTaxWrapper.GetPcode(my_vcpajc.ContactPointAddressID);

ViewContactPointAddressAttributeJurisdictionCode reload = ViewContactPointAddressAttributeJurisdictionCode.GetByID(my_vcpajc.ID);
State state = State.GetByID(reload.StateID);

switch (state.Name)
{

case "BC": pcode = 4540500;
break;
case "NB": pcode = 4633300;
break;
case "NL": pcode = 4675600;
break;
case "AB": pcode = 4482300;
break;
case "NT": pcode = 4704800;
break;
case "NU": pcode = 5101301;
break;
case "QC": pcode = 4882600;
break;
case "PQ": pcode = 4882600;
break;
case "SK": pcode = 5031900;
break;
case "ON": pcode = 4746700;
break;
case "MB": pcode = 4588300;
break;
case "NS": pcode = 4710700;
break;
case "PE": pcode = 4875900;
break;
case "YT": pcode = 5099700;
break;
default:
pcode = 0;
break;

}
if (pcode != 0)
{

try
{
ContactPointAddressAttributeJurisdictionCode cpaajc = ContactPointAddressAttributeJurisdictionCode.GetByID(my_vcpajc.ID);
cpaajc.JurisdictionCode = pcode;
cpaajc.Update();
}
catch
{
}
}

}
return true;
Rate Logic Modifier Plugin F

Rate Logic Modifier Plugin For Billsoft

Description: The rate logic modifier provided by Avalara requires minor configuration in EngageIP to tell Billsoft the location of the override file (as sent through the API).

1. Create override file using rate logic modifier application - provided by Avalara

2. Copy or save the .ovr file that you created in step 1 to c:\billsoft\data\eztax.ovr  (as shown in the image below)

   

3. Restart the following services

   • Event Manager service

   • EngageIP Billing service

   • IIS - (command: iisreset)

Billsoft Logging

Sometimes specific formats for taxes are required when submitting taxes payable to government departments or other organizations. The Tax Log was created as a way to provide a more direct export and streamlined format for the display of taxes payable. To ensure logging is being kept, you can turn it on in EngageIP by updating the EngageIPOption table:

1. Run this command in SQL:

   INSERT INTO EngageIPOption (Name,VALUE) VALUES ('TaxLog','True')

2. Restart billing, event, job services.

3. Reset IIS

The TaxLog table has nothing to do with billing, it simply logs the taxes that were calculated and applied.

Temporary Disabling Billsoft Integration

If you need to turn off Billsoft and use the out of the box taxcode config and options, you need to at minimum do the following. This should only by done by LogiSense as it could cause other inconsistencies in the database.

• Ensure that no taxcodes are associated to Billsoft taxvendor, if they are, the next item will error with foreign key error.

• Remove the TaxVendor EZtax (or named Billsoft)

• Remove all the workflows related, i.e. pcode action that sets jurisdiction code

Troubleshooting

BDL file not working or registering

Check whether you have an EZTax.cfg file existing in the data directory with reference to this file, this should exist so Billsoft knows about this file. Check also that an incomplete or invalid EZTax.cfg doesn't exist in c:\Windows\System32. If it exists, delete this file and ensure a proper cfg file exists in the Billsoft data directory. Reset IIS and services if changes are made here so the new files are loaded and applied.

Jurisdiction Code not correct for County that the Address is in

If you are finding that the jurisdiction code auto selected by EngageIP is not correct for the county that the customer is actually in and needs to be taxed for, enter the ZIP+4 value instead of just the ZIP code. This allows EngageIP to select the correct jurisdiction code for the county they are in.

Example is Clinton County in Missouri. The first choice for zipcode 64429 is Caldwell county and the system defaults to this, but if you enter zip+4: 64429-2026, the system then can find the most correct Jurisdiction code of 2076500

2061001,0,USA,MO,CALDWELL,CAMERON,64429,64429

2076500,0,USA,MO,CLINTON,CAMERON,64429,64429

Arithmetic Overflow error

This occurs with old versions of EngageIP if they are installed on a server beyond Windows 2008 R2. Billsoft had an issue running on IIS8 and didn't have a compiled EZTax.Net.Dll available that worked with IIS8 on Windows 2012. Upgrading EngageIP to a version with updated eztax.net.dll will resolve this.

Failed: An EZTaxSession hasn't been specified for this transaction

1. Ensure that Environment Variables are setup and include a semicolon to separate the values. Also ensure that the server was rebooted after the Environment Variables were setup

2. Verify that the correct EZtax installer was run (32-bit or 64-bit)

Error: Initialize Failed

• If an OVR or UPR file was added or changed recently try reverting or removing those files and rebooting the server to confirm if those are the cause.

• Check that there is not an outdated EZTax2.dll in adminportal/bin or Services folders or anywhere else. They should be removed - The integration should really only ever rely on the Environment Variables to find that file in the Billsoft installation directory.

Taxes are not being Applied

1. Ensure you are viewing the invoice to verify that taxing is occurring (taxes are not reflected in the 'Amount' column on the Packages list)

2. If you are taxing using the Service or Billing Contact 'Address' ensure a Jurisdiction Code is present as a Component on the Address Contact Point (the JCode will be added automatically by EngageIP)

   • To see all accounts with no Jurisdiction Codes you can setup a Custom Report, see Find Missing Jurisdiction Codes for instructions. Refer to the 'Jurisdiction Code is not being added as a Component on the Address' section below for further troubleshooting steps

3. If a Jurisdiction Code is present ensure tax is applicable, for example some US States have no sales tax. Setup an account with an address which taxes are applicable to, for instance in Ontario HST is applied to all sales. If the JCode is being returned as zero, see the 'Jurisdiction Code Equals Zero' section below

4. Ensure the Account in question and its Parent Accounts (if any) do not have a 'Tax Exempt' or 'Tax Exempt Level' component present which would exempt the taxes you are attempting to apply

5. Check the Event Log Report for taxing errors

6. Make sure the default tax code (when in the US) is using the country name 'USA'. Having 'United States' specified there will not work as Billsoft wants to see 'USA'.

Jurisdiction Code is not being added as a Component on the Address

1. Ensure a valid address is entered (address must include a valid Country, State, City and Postal Code)

2. Ensure that the EngageIP Event Manager is running on the EngageIP App Server. The Event Manager assigns Jurisdiction Codes

3. Check the Event Log Report for taxing errors

EZTax.EZTaxInit() failed -- likely a license problem

Errors found in the Event Log similar to this 'Exception during execution of workflow "BillSoft Pcode" at transition "BillSoft pcode": EZTax.EZTaxInit() failed -- likely a license problem' usually mean the Billsoft application is out of date/expired (as the license expires on a monthly basis requiring updates). Attempt to upgrade Billsoft to address this.

Jurisdiction Code Equals Zero

1. For taxing in the United States run the INSERT script for 8.5.26.x that adds ISO code - INSERT INTO CountryISO (Name, ISOCode) values ('USA', 'US')

2. Ensure the correct State name is used next to the state short name: CA = California. Ensure other States are up to date accordingly as well

3. If you are running an EngageIP version previous to 8.5.17.0 and want taxes to be applied by Billsoft , you MUST use the service tax categories that Billsoft recognizes. Custom Service Tax Categories are not supported in earlier EngageIP versions

P-codes that Cannot be Found

If a PCode cannot be found for some reason, there is a flat file in the EZtax directory where the pcode can be found, located here (assuming a base install): C:\BillSoftEZTax\Txtall_adr.txt

Its information looks something like this:

4902100,0,CAN,PQ,,COLERAINE STATION,G0N 1B0,G0N 1B0
4902200,0,CAN,PQ,,COLOMBOURG,J0Z 1T0,J0Z 1T0
4902300,0,CAN,PQ,,COMPTON,J0B 1L0,J0B 1L0
4902400,0,CAN,PQ,,COMTOIS,J0Y 2T0,J0Y 2T0
4902500,0,CAN,PQ,,CONTRECOEUR,J0L 1C0,J0L 1C0
4902600,0,CAN,PQ,,COOKSHIRE,J0B 1M0,J0B 1M0
4902700,0,CAN,PQ,,COTE ST LUC,H3X 1A0,H3X 1A0

To auto update these with an action, see the 'Zero Jurisdiction Code Updater Action' section above.

Rate / Tax Bundler

If you're using the tax bundler file and entering more rows, the max number of a row is 32000 so if you try to use 50000 it won't be used.

The trans/serv type is limited to 32000.

Generating the Bundler file (.bdl) is done via Avalara Manager software and the bdl file resides in the data folder in c:\billsoft\data. It requires an iisreset, then EngageIP uses the service tax category per below to make use of the bundle for taxing.

To assign in EngageIP, add a new service tax category with that bundle  name and IDs per format below.

Invoice Charge Script - Taxing on Invoice Close

As the name implies tax on invoice close (a.k.a. InvoiceLevelTaxing) calculates and applies tax when closing an invoice. If the invoice is opened, taxes will remain but if the invoice is closed again taxes will be recalculated. Taxlog entries will be removed accordingly and added back in a reopen/close invoice scenario.

This option exists because some taxes are 'per invoice' taxes, for instance this type of taxing is used in the U.S. state of Maryland. Tax is applied to the total invoice amount and not per transaction. In this case special logic needs to be switched on so that EngageIP taxes on invoice closure and not on transactions.

The script below enables taxing on invoice closure and can be handled by LogiSense support:

UPDATE TaxVendor SET InvoiceLevelTaxing=1
UPDATE TaxVendor SET InvoiceChargeScript = 'var pendingTaxes = (List<Logic.Core.ViewStatementDetailsPendingTaxes>)context["StatementDetailsPendingTaxes"];
User user = pendingTaxes[0].User;
return EZTaxWrapper.GetCustomerTaxes(user, pendingTaxes);'

Tax Logging and Reporting to Avalara - Compliance Reporting

The Taxlog table has all the information required for compliance reporting however it needs to be presented in a specific format for Avalara to process it. Please contact customer support and we can provide further detail on how to implement compliance reporting on your system.