Latest revision as of 13:38, 16 January 2017

Avalara currently has two tax services: the one we call "avalara", which is a general sales-tax services, and the one we call "billsoft" and they call "Avalara for Communications" (AFC).

AFC has a batch-oriented FTP interface that it inherited from Billsoft. This is what we support right now. It also has a nice REST interface which is somewhat experimental. Support for this is planned in the future.

Setup

Set the tax_data_vendor config to "billsoft", and the billsoft-company-code config to your three-letter company code.

Create an upload target (Configuration / with hostname ftp.billsoft.com, and the login and password provided by Avalara.

Import the data files. In Tools / Importing / Tax rates, select "Billsoft PCodes" and import the "all_adr.txt" file. Then select "Tax products" and import the "transervdesc.txt" file.

Run freeside-tax-location-update. This will assign PCodes for all existing package locations that don't yet have one, based on their zip codes.

Assign a tax product to each package definition. See below about this.

Setting up tax products

A "tax product" is a code assigned to a sale to identify what kind of product it is for tax purposes. For example, "recurring charge for local phone service" is a tax product. Tax products are listed in part_pkg_taxproduct. In the UI, entering a string into the tax product field will open an autocomplete search for tax products containing that string.

A package definition (part_pkg) has a single "taxproductnum" field for the base tax product for the package. In the simple case where a package has only one-time or only recurring charges, and no CDR usage charges, this is the only tax product it needs.

The "By usage class" button allows tax products to be set separately for setup, recurring non-usage, and usage by class. The taxproductnums are stored in part_pkg options named "usage_taxproduct_FOO" where FOO is the word 'setup' or 'recur' or an integer (a usage_class.classnum).

See the AFC Telecom Mapping Guidelines and other manuals for details on selecting tax products. For usage-billed phone service, AFC has separate tax product groups (T-codes) for interstate, intrastate, and local services. Local services are typically billed as "Local Exchange charge" (7:5); long-distance charges will be intrastate (2:1), interstate (1:1), or international (1:12) toll charges. Other local exchange charges such as FCC subcriber line / LNP recovery are their own categories. This is all different if you're using VoIP or cellular service.

(Local service also has a separate, parallel set of tax products ending in the word "Bundle". This is for service plans that don't break out the local and long-distance charge on the bill; for complicated reasons it's exempt from certain taxes.

There should probably be a switch to turn off sending all the CDRs to AFC and just give them a total amount billed in each usage class.

AFC requires that packages that provide "phone lines" of whatever type declare those. Usually this is the "Lines" tax product (such as local voice lines, 7:21, or VoIP lines, 19:21) but there are other options such as "PBX trunks" and even "extensions" (which would require more flexibility in counting units than we currently have). The "Per-line tax product" in the package definition sets the part_pkg.units_taxproductnum to this field.

Finally, there are "Invoice" tax products (always S-code 43). These do not need to be configured. If any tax product on the invoice has a T-code for which there's an Invoice tax product then an additional line will be submitted declaring the Invoice. For example, if an invoice includes a charge for local phone service (7:5) and one for VoIP access charges (19:6) then it will be sent with both a 7:43 and a 19:43, declaring it as both a local phone invoice and a VoIP invoice.

Batch workflow

freeside-daily runs FS::Cron::tax_batch::process_tax_batch. If the tax engine has the 'batch' flag (which only AFC has, probably ever), it will call transfer_batch() on the tax engine.

Note that if AFC is active and a customer has pending invoices, bill_and_collect will NOT run collect() for that customer (i.e. it won't run post-billing events, such as charging credit cards or mailing out invoices). We'll come back to that.

transfer_batch() calls create_batch() to write the batch into a file in $cache_dir/Billsoft/upload. These files follow a strict naming scheme: company code + %Y%m%d + two-letter sequential ID starting with 'AA'. So on Jan 12 2017, the first batch would be FIS20170112AA.CSV, the second would be FIS20170112AB.CSV, etc. Normally you should only need one batch per day, but if you somehow send another one, transfer_batch will detect that there's already one named *AA.CSV (in $cache_dir/Billsoft/upload) and name the next one AB, and so on.

create_batch() finds all pending invoices and writes transaction lines into the batch. See the next section for details.

It then compresses the CSV file into "FTP.ZIP" and uploads that, then polls the server waiting for a result batch. Currently this has a timeout of one day (the $TIMEOUT variable); that's probably longer than it should be.

The result batch will be named the same thing as the uploaded CSV file, but .ZIP, and with "R" after the company code (so "FISR20170112AA.ZIP"). It will be saved into $cache_dir/Billsoft/download. It contains several files describing many stages of processing, but the one we want is the detail report, ending in _dtl.rpt.csv. This is a CSV file containing one line for each tax on each transaction, which is conveniently the same data structure as our allocation table.

batch_import() reads this file line by line and creates a tax line item for each distinct tax name. It will also create tax_rate_location, tax_class, and tax_rate records as necessary to describe the taxes that are being charged.

After processing the file, batch_import() removes the pending flags from all invoices, then queues a job to run FS::cust_main::Billing::collect on each customer who had an invoice processed this way.

What we send them

AFC tax products have two parts, a transaction code (for the kind of service that's being sold, like local telephone, VoIP, etc.) and a service code (for the kind of charge that this is, like monthly subscription, toll, installation, etc.). I internally call these "T-codes" and "S-codes". See the various AFC manuals (Local Exchange Mapping, VoIP Mapping, etc.) for details.

AFC expects each possibly-taxable feature of the sale to be declared as a separate transaction line. So for each cust_bill_pkg record on the invoice, we send the following transaction lines:

• Each recurring fee (cust_bill_pkg.recur).
• Each non-recurring fee (cust_bill_pkg.setup).
• The number of phone lines (for per-line surcharges).
• Each CDR (for voip_cdr packages). Yes, this is a large number of records.

In almost all cases, per-line or per-customer) charges such as E911 fees need to be applied once per month, so the package must have a monthly billing cycle.

Fields applying to the whole invoice (%bill_properties):

• RequestType: Always 'CalcTaxes' for now.
• BillTo... (CountryISO, PCode, ZipCode, ZipP4): the customer's billing address.
• Date: The invoice date (YYYYMMDD).
• CustomerType: The customer's tax status ('B'usiness, 'R'esidential, etc.).
• InvoiceNumber: Freeside invnum (echoed in the result file, used to find this invoice again).
• CustomerNumber: Freeside custnum (echoed in the result file, used for error checking).
• and some sitewide flags describing the service provider's status:
  • Facilities: Indicates the provider owns the physical infrastructure used to deliver services.
  • Franchise: Indicates that the provider sells service pursuant to a franchise agreement with the jurisdiction.
  • Regulated: Indicates that the provider is a regulated public utility.
  • BusinessClass: Indicates whether the provider is classified as a CLEC or ILEC.

Fields applying to all line items of a specific package (%pkg_properties):

• Optional: Freeside billpkgnum (echoed in the result file; the field is just a generic place for a numeric identifier).
• Sale: Whether this package is sold on a wholesale basis (a part_pkg flag, currently not available in any package).

Fields sent with recurring fee or setup fee lines:

• Termination... (CountryISO, PCode, ZipCode, ZipP4): the package's service address.
• TransactionType/ServiceType: The tax product for the recurring or setup fee.
• Charge: The amount of the sale.

Fields sent with CDR lines:

• TransactionType/ServiceType: The tax product for the CDR's usage class.
• Charge: The rated charge for the call.
• Minutes: The call duration in minutes (including fraction).
• OriginationNpaNxx: The source number's prefix.
• TerminationNpaNxx: The destination number's prefix.

Note that if LRN lookup is enabled, the Origination/Termination prefixes will be taken from the resolved LRN numbers.

Fields sent with the "number of phone lines" transaction line:

• Charges: Zero.
• Lines: The number of phone services, from FS::part_pkg::calc_units. This transaction line will be sent only if it's greater than zero.
• TransactionType/ServiceType: The tax product selected with part_pkg.units_taxproductnum. Usually this will be S-code 21 ("Lines") but can be different for certain local exchange services.

For each T-code that was present on the invoice, we'll also send a "per invoice" transaction line with S-code 43. The "Lines" and "Charges" are both zero on this line.

Processing the result file

batch_import() reads records line by line and looks at several columns:

• InvoiceNumber and Optional columns contain invnum and billpkgnum. We use these to relate the tax back to a taxable line item.
• TaxTypeID (number) and TaxType (string) are the "kind" of tax, like "Sales Tax" or "Universal Service". We create a tax_class record with these values if there isn't one already.
• CountryISO, State, County, Locality, and PCode identify the tax locality. PCodes are unique and we store them as tax_rate_location.geocode. If there isn't a record with this PCode already, we create one.
• TaxLevelID is the taxing authority: national, state/province, county, city, or unincorporated district. We use this to decide which tax_rate_location field to use in the tax description (for example, "CA Sales Tax" vs. "Los Angeles County Sales Tax").
• TaxAmount is the amount of tax, in dollars.

batch_import() constructs the tax name from the geographic name of the taxing authority (like "US" or "NV" or "Las Vegas") and the TaxType string (like "Sales Tax") and finds or creates a tax_rate record with that name, and geocode = the PCode of the locality.

It then calls add_tax_item(), an internal method that creates a cust_bill_pkg record with the correct itemdesc (= the tax name). If there's already one (from another line item subject to the same-named tax) then add_tax_item increments the amount of that item.

Finally, it creates a cust_bill_pkg_tax_rate_location allocation record linking the tax and taxed line items, the tax_rate and tax_rate_location records, and the amount.

If there are errors processing the batch, we log them and continue. The invoice will remain in the pending state and be resubmitted in the next batch.

All pending invoices should be cleared at the end of daily processing. There should be a report / notification if any remain in the system after that, but there currently isn't.