Tax engine documentation

Tax calculation in general

Freeside supports a few mechanisms for calculating taxes. On version 4 and later, these share an internal interface, FS::TaxEngine.

On 3.x (and as far back as 1.7, I think) there are two tax mechanisms, without any kind of common interface. All references to them are hardcoded. The systems are:

• "Internal" taxes via cust_main_county. These require manual setup, and the kinds of taxes that it handles are very limited.
• "CCH" taxes via tax_rate.

On 4.x, we add:

• SureTax (the online version of CCH)
• Avalara's "AvaTax" product
• Avalara's "AvaTax For Communications" product, formerly known as "Billsoft".

Requirements

For many clients (those not selling regulated voice telecom services, and those with national sales tax such as Australia and Canada), taxes are fairly simple. Each customer is located in some tax jurisdiction, which defines a tax rate. This is multiplied by the sale amount to that customer to obtain the tax amount, which is added to their invoice.

For voice call service, it's a lot more complex. A typical U.S. phone service might require the carrier to collect:

• State, county, and local sales tax, typically on the entire sale amount.
• State universal service fund (USF) and TDD fund taxes, based on either the sale amount or just the toll charges.
• Federal USF and excise tax, as a percentage of interstate long-distance charges.
• E911 fees.

Other complications related to taxes:

• Some customers are exempt from some or all taxes.
• Some packages are exempt from some or all taxes, also.
• Some customers are exempt from some taxes up to some threshold amount. (Called "Texas tax" in the code.)
• In general, crediting a sale that was taxed should "give tax back" (that is, also credit the tax that applied to that sale). Because the credit can be for any amount, we have to track which line items the credit was applied to, and how much was applied to tax or non-tax amounts.
• And crediting a sale that was exempt up to a threshold should "give back" exemption, also.
• We should be able to report on the taxable sales, tax-exempt sales, tax credited, and tax collected in any billing period.

High-level overview

"Tax line items" are cust_bill_pkg records that have pkgnum = 0 and feepart = null. They will also only have setup fees, not recurring fees, and may have "itemdesc" set to the name of the tax as it should appear on the invoice.

Starting with Freeside 3.x, every tax line item has "allocation records" linking it to both the sale items that it was charged on, and its tax definition. These have two foreign keys to cust_bill_pkg: "billpkgnum" (the tax charge) and "taxable_billpkgnum" (the taxed sale).

Tax calculation happens as the last step in creating an invoice (FS::cust_main::Billing::bill). It generates a list of tax line items (FS::cust_bill_pkg objects) each linked to a list of tax allocation records.

In Freeside 3.x, this involved calling two methods in FS::cust_main::Billing:

• _handle_taxes: Identify the tax definitions that apply to this invoice, and make a list of all sale line items that are subject to each tax definition. This also finds any exemptions that apply.
• calculate_taxes: For each tax definition, calculate its tax on the set of taxable items it applies to. Create an allocation record for each of them. Then consolidate those records to make a single tax line item for each tax name. Insert those records and the allocation records.

All of this was done before any of the records were inserted, which involved building a large free-floating data structure with references from tax line items to allocation records to taxable items to exemption records. FS::cust_bill_pkg::insert would then traverse this structure and insert the records, turning in-memory references into foreign keys.

In 4.x this is somewhat simpler: FS::cust_main::Billing inserts the invoice, along with all its package line items, then calls FS::TaxEngine::calculate_taxes to get a list of tax line items. It then inserts all of them and adds the tax amount to cust_bill.charged. This was needed to support batch mode, since the invoice may sit for the rest of the day before being submitted for tax calculation. Before the taxes have been added, the invoice has the 'pending' flag set so that we know it's not finished.

tax_data_vendor selects the tax service to use. If it's a batch-mode service (i.e. billsoft), freeside-daily calls FS::TaxEngine::transfer_batch, which takes all pending invoices and sends them in for batch calculation, then appends the resulting taxes to the invoices and marks them as non-pending.

Schema

For internal taxes

Tax rates are defined in cust_main_county, which has the following information:

• The tax rate ("tax"), as a percentage.
• The tax name.
• Where the tax applies: country, state, county, city, and district. Except country, they can be null.
• What the tax applies to: a "taxclass" string, and "setuptax" and "recurtax" flags indicating that setup / recur charges are exempt. (Yes, the names are confusing.)
• "exempt_amount": the per-customer monthly exemption if any.
• "source": keeps track of which tax rates are automatically maintained. Currently only applies to the Washington State Sales Tax.

Tax allocation records are in cust_bill_pkg_tax_location, and contain:

• Foreign keys linking to the cust_bill_pkg records of both the tax and the taxed sale.
• A foreign key to the cust_main_county record defining the tax.
• The amount of tax.
• "taxtype", always 'FS::cust_main_county' for now.
• "pkgnum" and "locationnum" fields, which are now redundant, since the taxed sale is already linked back to the package.

Exempt sale records are in cust_tax_exempt_pkg. One exempt sale record is created for each tax that the sale was exempted from. They contain:

• The billpkgnum of the sale.
• The cust_main_county record (taxnum) it was exempted from.
• The year and month that the exemption occurred. This is to make monthly exemptions easier to calculate.
• The sale amount that was exempted.
• Several "exempt_" flags indicating why the sale was exempt: because the customer is tax-exempt ("cust") or selectively exempt ("cust_taxname"), or the tax doesn't apply to setup / recur fees, or there's a monthly exemption.
• A foreign key to cust_credit_bill_pkg. This is used for negative exemption records created when a credit is applied to a tax-exempt sale. For "normal" exempt sale records it will be null.

Package tax classes are in the part_pkg.taxclass field. This is a string, not a foreign key, though there's a list of allowed tax classes in part_pkg_taxclass.

For external/vendor taxes

Most tables related to external taxes have a "data_vendor" field which identifies which tax service vendor the record came from / applies to.

Tax rates are defined in tax_rate. For CCH taxes, all fields in tax_rate are used for the tax calculation. For Suretax / Avalara taxes, we still create a record for each tax name + jurisdiction as the tax service tells us about them, but the other fields are left empty. The essential fields are:

• The tax name.
• data_vendor
• Where the tax applies, in terms of the "geocode" field, which links to tax_rate_location.
• What kind of tax it is: "taxclassnum", a foreign key to tax_class.

The allocation records are in cust_bill_pkg_tax_rate_location and are pretty similar to the ones for internal taxes, except that they also have "taxratelocationnum", a link to tax_rate_location.

External taxes have a more complex system for classifying the taxability of packages. "Tax products" are defined by the vendor, and stored locally in part_pkg_taxproduct:

• "taxproduct" is the vendor's code for this product.
• "description" is how it should look to the Freeside user.

Each part_pkg can then be associated with a tax product via the taxproductnum field. However, a single package can generate several kinds of charges with different tax treatment. So there are also part_pkg options named "usage_taxproductnum_FOO" where FOO = "setup", "recur", or a usage class number. This allows different taxes to apply to recurring and non-recurring fees, and to local, interstate, or international calls.

Tax vendors also typically have their own coding scheme for customer locations. We refer to these codes as "geocode". cust_location.geocode is the geocode of a package location. The geocodes are defined in tax_rate_location, which has these fields:

• data_vendor
• geocode
• country, state, county, city

part_pkg_taxrate relates tax products to the taxes that apply to them in each geocoded region. This is the core of the CCH tax engine and is unused for any other tax system.