Sales Tax Rates & Taxability Database

Overview

You can use the CCH Sales Tax Rates and Taxability Database (CCH database) most effectively and efficiently when you understand the parts of the database, their relationships, and the concepts behind the database construction. That’s why this guide includes listings of table structures, layouts, and other technical information, as well as instructions on how to use them. To get you started, the diagram below shows you how the various parts of the database relate to each other.

CCH Sales Tax Rates and Taxability Database Schema

To accurately calculate sales tax for any good or service, each individual tax needs to be considered from several angles. The first step is checking the taxing authority at the location. A taxing authority is any government agency (state, municipality, township, etc.) that can impose and collect sales and use taxes. An important part of the database is keeping track of the boundaries of each taxing authority’s jurisdiction. Once you have established that the

location is affected by a taxing authority, you must check two more factors. Since taxability varies greatly from one authority to the next, it checks to see if the product or service is taxable at that location. If the good or service is taxable, the tax rate is determined. Next, reporting

requirements, pass through rules, and calculation methods are checked. With this information, you can calculate and report a tax.

As diagrammed above, five general categories of data are needed to complete those steps: taxing authorities, geography, taxability, rates (including tax-on-tax), and codes tied to descriptions of goods and services. The next chapter discusses taxing authorities and their central role in the database.

Taxing Authorities

All data in the various sections of the database are directly related to the taxing authority database table. The CCH database is organized around six types of taxing authorities:

Example 1

TaxAuthType	Description
0	FEDERAL
1	STATE
2	COUNTY
3	CITY
4	LOCAL (i.e., Special Districts)
5	REPORTING AGENCY

Federal Taxing Authorities impose specific taxes on goods and services nationally.

Example 2 (TaxAuthType = '0')

TaxAuthId	Country	AuthorityName
1	CA	CANADA REVENUE AGENCY
11	GB	COMMISSIONERS OF CUSTOMS AND EXCISE
16	US	FEDERAL COMMUNICATIONS COMMISSIONS
1004	US	INTERNAL REVENUE SERVICE

Note: When Federal taxes are presented in the TaxAuthority table, the State column will be blank.

State Taxing Authorities impose taxes on state-wide basis.

Example 3 (TaxAuthType = '1')

TaxAuthId	Country	State	AuthorityName
13	US	IN	INDIANA DEPARTMENT OF REVENUE
14	US	MT	MONTANA DEPARTMENT OF REVENUE
15	US	MT	MONTANA PUBLIC SERVICE COMMISSION
22	US	IN	INDIANA UTILITY REGULATORY COMMISSION
29	US	NE	NEBRASKA DEPARTMENT OF REVENUE
32	US	NE	NEBRASKA PUBLIC SERVICE COMMISSION

County Taxing Authorities generally impose taxes for their own counties, and are not applicable on a state-wide basis.

Example 4 (TaxAuthType = '2')

TaxAuthId	Country	State	AuthorityName
1000	US	KS	MCPHERSON, COUNTY OF
1001	US	NM	LUNA, COUNTY OF
1003	US	KS	MEADE, COUNTY OF
1005	US	NM	MCKINLEY, COUNTY OF
1006	US	KS	MIAMI, COUNTY OF
1007	US	NM	MORA, COUNTY OF

Note: There are cases when a county is listed as the Taxing Authority for a tax that extends beyond its geographical border.

City Taxing Authorities generally impose taxes for their own cities and are not applicable on either a county or state-wide basis.

Example 5 (TaxAuthType = '3')

TaxAuthId	Country	State	AuthorityName
6125	US	TX	NEW LONDON, CITY OF
6127	US	MO	CLAYCOMO, CITY OF
6128	US	OK	MUSTANG, CITY OF
6130	US	OK	NASH, CITY OF
6131	US	TX	NEW SUMMERFIELD, CITY OF
6132	US	MO	CLAYTON, CITY OF

Note: As with counties, there are instances when a city is listed as the Taxing Authority for a tax that extends beyond the city limits.

Local (or Special) Tax Districts generally fall outside the four categories above. They are established for a specific purpose (that purpose is usually indicated by their name) and often cross over city and county boundaries. They include such things as Independent School Districts, Fire Districts, transit authorities, etc.

Example 6 (TaxAuthType = '4')

TaxAuthId	Country	State	AuthorityName
2412	US	KY	ASHLAND INDEPENDENT SCHOOL DISTRICT
3452	US	LA	NEW ORLEANS INTERNATIONAL AIRPORT TAX DISTRICT
9614	US	TX	MAVERICK COUNTY LFW & CDC DISTRICT
10237	US	IL	SOUTHWEST CENTRAL E911
11198	US	WI	BASEBALL STADIUM DISTRICT
9498	US	SD	CHEYENNE RIVER SIOUX TRIBE, INDIAN COMMUNITY

The Reporting Agency taxing authority type differs from the above categories. A Reporting Agency does not have authority to impose taxes. It acts as a collection agency for its Tax Authority. Usually, the state level taxing authority is also its own reporting agency. Sometimes independent agencies collect taxes for the state.

Example 7 (TaxAuthType = '5')

TaxAuthId	Country	State	AuthorityName
1550	US	AL	ALATAX
3908	US	MI	MECEOLA CENTRAL DISPATCH
628	US	TX	NORTEX REGIONAL PLANNING COMMISSION (NORTEX)
857	US	KS	NECA SERVICES
639	US	CO	W-Y COMBINED COMMUNICATIONS CENTER

Each tax in the database is connected to a specific taxing authority. When dealing with rates, each authority is referenced by its TaxAuthId or ReportTo codes. Some of the taxability rules in the Matrix table also reference the specific taxing authority.

After being connected to a taxing authority, each tax is related to its tax type (TaxType) and category (TaxCat).

The table below gives examples of how the geographic identifiers and tax authority types are represented for each of the six types of taxing authorities discussed above.

Example 8 (Geographical example of TaxAuthTypes by Geography [Geocode])

#²	Country	State	Geocode	TaxAuthType	TaxAuth	[ReportType]³	ReportTo
1	US		US0000000000	0	16	0	16
2	US		US0000000000	0	1004	0	1004
3	US	OK	US4013713135	0	16	0	16
4	US	OK	US4013713135	0	1004	0	1004
5	US	NH	US3300710090	1	800	1	800
6	US	NH	US3300710090	1	804	1	804
7	US	KS	US2013368025	2	1020	1	862
8	US	KS	US2013368025	2	1020	2	1020
9	US	NV	US3200331900	3	785	3	785
10	US	NV	US3200340000	3	787	3	787
11	US	LA	US22051A0001	4	3452	1	2585
12	US	LA	US2205338355	4	3465	2	2749
13	US	AL	US0100103220	2	1274	5	1550
14	US	AR	US0500316240	1	5760	5	5761
15	US	HI	US1500332700	1	7658	5	7660
16	US	TX	US4846975428	1	41	5	43

Geocode, TaxAuthType, TaxAuthId, ReportType and ReportTo need to be connected to the appropriate code tables to be translated into their descriptions~~ The TaxAuthType and ReportType are not represented in the Detail or Matrix tables, but they can be found through their connection to the Taxing Authority.

Note: Rows 3 through 10 indicate the TaxAuthType and the ReportType are the same. In row 11, a tax district (4) reports to the state taxing authority (1). In row 12 (in the same state) a different tax district (4) reports to the county taxing authority (2). Rows 13 through 16 indicate either a county taxing authority (2) or a state taxing authority (1) using an external reporting agency (5).

² is a line number for purposes of illustration. It is not a column in the Detail (i.e., Rates) table.

³ Report Type is not a column in any of the tables. However, when joining the ReportTo column to TaxAuthority, the specific ReportType can be determined for a ReportTo identifier.

Geography

General

Accurately connecting each tax to a location is the most important factor. The database has two tables to make that connection: the Geocode table and the ZIP-Plus4 table.

The United States Postal Service®(USPS) ZIP Code™ system was set up for mail delivery. It is not related to taxing authority boundaries. While ZIP code information can be helpful, it is not an accurate way to determine the taxing authority for a sale. The only consistently accurate way to identify the taxing authority at any specific location is by a geocode

A geocode is a 12-character alphanumeric string that defines a specific country, state, county, locality and, where necessary, a unique combination of local taxes. The country is always represented by two characters (for example ‘US’ or ‘GB’).The rest of the code reads as: state = 2 characters, county = 3 characters, locality = 5 characters. For example, the city of Wichita, Kansas is located in Sedgwick County. Its geocode is US2017379000 where US is the country, 20 is the state code for Kansas, 173 is the county code for Sedgwick County, KS, and 79000 is the city code for Wichita. Special districts generally have an embedded ‘A’ at the beginning of the city portion of the geocode (e.g., US20173A0001, which represents the area covered by the Wichita Riverwalk Redevelopment District). The Plus4 table links specific ZIP Code/ZIP + 4 combinations to specific geocodes.

To trace a location’s taxes and taxing authorities, identify the specific ZIP Code and ZIP + 4 combination (in the Plus4 table). Then link it to the geocode table (using the geocode in the Plus4 table), or directly to the Detail table with the geocode. The Detail table contains the TaxAuth and ReportTo codes which, when connected back to TaxAuthority, provide the Authority and Reporting information.

The example below shows how the link between ZIP Codes, taxing authorities, and geocodes are inter-related.

1st: Plus4

Zip	Plus4Lo	Plus4Hi	State	Geocode
66775	4051	4051	KS	US2013368025

2nd: Plus4 to Geocode for Zip Code 66775-4051

Country	State	County	City	TaxDistrict	Geocode
US	KS	NEOSHO	STARK		US2013368025

3rd: Segments of the rate records from Detail via the Geocode (from Plus4 or Geocode)

Geocode	TaxType	TaxCat	TaxAuth	ReportTo
US2013368025	02	01	1020	862
US2013368025	u2	01	1020	862

4th: The TaxAuthority records for the above Detail records showing the TaxingAuthority (TaxAuth) and the ReportingAuthority (ReportTo)

TaxAuth	Country	State	AuthorityName	TaxAuthType
1020	US	KS	NEOSHO, COUNTY OF	2
862	US	KS	KANSAS DEPARTMENT OF REVENUE	1

From the Plus4 table, the ZIP Code 66775 connects to geocode US2013368025 which represents a portion of Stark, KS. Connecting the TaxAuth and the ReportTo codes to TaxingAuthority gives you the following information: both of these taxes are imposed by the County of Neosho (the TaxAuthType for the imposing authority is a county) – and both taxes are reported to the Kansas Department of Revenue. In the same set of illustrations, the TaxDistrict column in the geocode table is empty because the locality identified by the geocode is not itself sub-divided by any special tax districts.

Once you have made the connection between a particular ZIP Code/ZIP+4 combination to the Detail table, all relevant taxes can be retrieved for use in a transaction.

The database also includes Geographic Names Information System (GNIS) codes as part of the geocode table. GNIS codes are provided because they are a convenient standard reference for geography. These codes were designed for use by the U.S. Board on Geographic Names as a standardized index of named places. Do not use this information for determining taxability or tax rates. Since a named place may include more than one area with a unique combination of taxes, rates, reporting requirements, etc., the GNIS code is not specific enough to use in determining tax liability, proper reporting, or tax computation.

The Plus4 Concept

The ZIP+4 additions to the USPS ZIP Code system were added to make the original ZIP code system more specific. This level of specificity is critical to determine which geocode should be used.

There are six data elements in the Plus4 table: Zip, Plus4Lo, Plus4Hi, State, Geocode and MainRange.

The complete range of Plus4s for any ZIP Code is 0000 to 9999. In some cases, there may be only a single geocode associated with a ZIP Code. Other Zip codes may have several associated geocodes. When there is only one geocode, the Plus4 table will contain a single record for that ZIP Code.

Example 10 (ZipCode with a single Plus4 record)

[Plus4]

Zip	Plus4Lo	Plus4Hi	State	Geocode	MainRange
50265	0000	9999	IA	US1915383910	1

[Geocode]

Country	State	County	City	TaxDistrict	Geocode
US	IA	POLK	WEST DES MOINES		US1915383910

In the above example, all ZIP+4 codes for the ZIP Code 50265 point to the same geocode.

In the other cases, where different ZIP+4 codes within the ZIP Code point to different geocodes, you must use the specific ZIP+4 code to determine which geocode applies. The Plus4Lo and Plus4Hi columns indicate the particular range of ZIP+4 codes that point to a specific geocode. If none of the Plus4Lo and Plus4Hi ranges matches the specific ZIP+4 code for the transaction, then the geocode of the first record of the set (i.e., the one with the 0000-9999 range) is used. This default value record is referred to in the database as the “main range” record.

The geocode that can be related to the greatest number of ZIP+4 codes within a ZIP Code coverage area is assigned to the main range record. This geocode is treated as the default location value for locations within that ZIP Code. The other records for ZIP+4 ranges within that ZIP Code represent exceptions to that default location.

Example 11

#	Zip	Plus4Lo	Plus4Hi	State	Geocode	MainRange
1	51246	0000	9999	IA	US1911968160	Y
2	51246	2061	2064	IA	US1911900000	N
3	51246	2092	2092	IA	US1911900000	N
4	51246	7500	7572	IA	US1911900000	N
5	51246	7574	7605	IA	US1911900000	N
6	51246	7611	7615	IA	US1911900000	N
7	51246	7700	7707	IA	US1911900000	N
8	51246	7708	7709	IA	US1911944670	N
9	51246	7710	7797	IA	US1911900000	N
10	51246	7808	7808	IA	US1911900000	N

NPANXX: Landline Telephone Connection to Geography

In addition to ZIP code and their Plus-4 extensions, a third table, NPA-NXX, connects to the geocode through telephone area codes and exchange prefixes. However, using the NPA-NXX to identify appropriate geocodes has limitations. It can only be applied to landline phones using the Public Switch Telephone Network. VOIP, cell phones, pagers, and other personal electronic devices with phone function cannot be used in conjunction with NPA-NXX for geocode referencing.

The NPA-NXX table contains four pieces of information: the three-character NPA (Numbering Plan Area code), the three character NXX (or Central Office or Exchange Code), the three character LATA (Local Access and Transport Area) and the geocode mapped to it.

Beyond its telecommunications industry applications, this table can help you find an appropriate geocode for a customer even if the only information in your database is their land line telephone number. For example, if you had a customer living in Peabody, MA tied only to a phone number in your database, you could identify the appropriate geocode by querying the NPA-NXX table for (978) 954-1234.

NPA	NXX	LATA	GEOCODE
978	954	128	US25000952490

Note About Canadian Geocodes

Canadian taxes are tracked at the provincial level; therefore each Canadian province is assigned only one geocode. Users of the data can locate the appropriate geocode for the province in the Geocode table by searching using the province abbreviation in the State field.

Taxability

General

Once the transaction location is determined, TaxType and TaxCat are used to determine taxability. Taxability defines how a tax is applied to some combination of Groups, Items, Customers, Providers and Transaction Types. The terms Group and Item refer to goods and services provided. Customer, Provider and Transaction Type refer to the nature of the transaction and the parties engaged in it. Data for taxability issues is maintained in the Matrix table. The terms TaxType and TaxCat refer to the most general definition of a class of taxes. The first few TaxTypes are generally locational. The TaxCat says something about a kind of tax related to a given TaxType. For example, TaxCat ‘01’ represents general sales taxes.

Before reviewing examples of the fundamental kinds of taxability data, it is important to understand the components mentioned above.

Example 12 (the most basic Sales Tax Tax Types)

TaxType	TaxCat	General Description
01	01	State level sales tax
02	01	County level sales tax
03	01	County-Local level sales tax
04	01	City level sales tax
05	01	Local (Special District) level sales tax

“Group” refers to categories of charges. “Item” refers to the specific type of charge related to a line item. You should map your invoice codes to the ProductGroup and ProductItem codes.

Example 12 (items contained in Group '0003' : CLOTHING-GENERAL)

Group	Group Description	Item	Item Description
0003	CLOTHING-GENERAL	001	GENERAL CLOTHING/FOOTWEAR
"	"	002	CLOTHING (FUR REPRESENTS MORE THAN 50% OF THE GARMENT-S VALUE)
"	"	003	FORMAL WEAR
"	"	004	NOTIONS
"	"	005	BELT BUCKLES
"	"	006	CHILDREN-S COSTUMES
"	"	007	ADULT COSTUMES
"	"	008	HAIRBOWS
"	"	009	HANDKERCHIEFS
"	"	010	SYNTHETIC FUR
"	"	011	RECEIVING BLANKETS
"	"	012	CRIB BLANKETS
"	"	013	CHILDRENS CLOTHING

“Customer” refers to the categories of purchasers of taxable goods and services. “Provider” refers to the categories of goods and services providers.

Note: The examples here are from the full Group and Item code tables

Example 13 (Customers)⁷

Customer	Description
00	RESIDENTIAL
08	RETAIL
99	DEFAULT

Note: The examples here are from the full Customer and Provider code tables

Example 14 (Providers)

Provider	Description
00	LEC - REGULATED
70	RETAIL
99	DEFAULT

Unless there is a specific exception, both Customer and Provider utilize the ‘99’ code to indicate the default values for all Customers or all Providers for a specific Group and Item.

In the database, there are currently two Transaction Types: Sales or Purchase. In the Matrix table, these are referred to simply as Transaction.

Example 15 (Transaction Types)

Transaction Type	Description
01	SALES
02	PURCHASE

Taxability for goods and services varies from jurisdiction to jurisdiction. So, to make sure the taxability calculation is correct, codes identify situations where taxability varies by location or situation.

To be efficient, matrix taxability data is organized downward. Most decisions for a given TaxType- TaxCat-Group-Item-Customer-Provider-TransactionType are represented at the state level, the highest level of taxability representation, and are said to be “State specific.”

If a given TaxType-TaxCat-Group-Item-Customer-Provider-TransactionType combination (TTGICPT) has no exceptions at any lower level (for example at the County level or at the City level), the taxability decision for that combination will be represented by a single record in the Matrix table. Exceptions to this are said to be “authority-specific” when a given Taxing Authority will have a different taxability decision for same TTGICPT. Similarly, exceptions based on differences of location (i.e., geocode) are said to be “geocode-specific.” Exceptions can also happen on the Customer-Provider portion of the record at all levels of taxability.

Taxability is indicated in the Taxable column of the Matrix table. It uses two values: 0, meaning not taxable, and 1 meaning taxable.

The three remaining data items in the Matrix table are Effective, PerTaxableType, and PercentTaxable. Effective indicates the date when a particular Taxability decision became effective. PercentTaxable indicates the “rate of the rate,” that is what percentage of the rate is used for calculating the tax for the given Group and Item. In most cases, its value is 1.000, indicating that the rates are applied at 100%. PerTaxableType is reserved for future development.

Illustrations of Taxability

The hypothetical examples given below serve to illustrate the various kinds of defaults and exceptions you’ll typically encounter in the Matrix table. The data points themselves are not found in the Matrix table.

Example 16 (State Level default taxability)

Geocode	TaxAuthId	Group	Item	Customer	Provider	Transaction	Taxable	TaxType	TaxCat
US36000000000	[NULL]	0001	001	99	99	01	1	01	01

This is a state level taxability record. Note that only the country and the state codes are given in the Geocode column. The values for the county and city portions of the geocode are zeroes. Similarly, the Customer and Provider are 99s, indicating that the taxability for the TTGICPT applies to all customers and all providers by default. Note that the TaxAuthId column is NULL, indicating that State level taxability records are the default condition for the entire state no matter which specific taxing authority imposes this class of tax.

Deriving a specific taxability decision for a specific taxing authority from state level default records in the Matrix table is discussed in Chapter 7. It involves the relationship between Matrix and Detail (TaxAuthId is a key piece of information in the Detail table).

Example 17 (Taxing Authority Level exception to a State Level default)

Geocode	TaxAuthId	Group	Item	Customer	Provider	Transaction	Taxable	TaxType	TaxCat
US36000000000	[NULL]	0001	001	99	99	01	1	02	01
US36000000000	1234	0001	001	99	99	01	0	02	01

Here we have a taxing authority level exception to the default state record. Note that the Geocode has the same content as the state default record. This is because, as the state default record covers everything for the state (jurisdictionally), a lower level taxing authority covers everything for itself. In this case, the overriding taxing authority has imposed a decision of NO taxability for the same TTGICPT.

Example 18 (Geocode Level exception to an Authority Level default)

Geocode	TaxAuthId	Group	Item	Customer	Provider	Transaction	Taxable	TaxType	TaxCat
US36000000000	[NULL]	0001	001	99	99	01	1	02	01
US36000000000	1234	0001	001	99	99	01	0	02	01
US36001000001	1234	0001	001	99	99	01	1	02	01

This is a geocode exception to the taxing authority exception to the state level default. Although the given taxing authority overrode the state taxability decision, the geocode-level taxability decision reverts back to the state level decision for the same TTGICPT.In this example, the locality with the exception is associated with TaxAuthId as well as Geocode.

Example 19 (Geocode Level exception based on Customer difference)

Geocode	TaxAuthId	Group	Item	Customer	Provider	Transaction	Taxable	TaxType	TaxCat
US36000000000	[NULL]	0001	001	99	99	01	1	02	01
US36000000000	1234	0001	001	99	99	01	0	02	01
US36001000001	1234	0001	001	99	99	01	1	02	01
US36001000001	1234	0001	001	01	99	01	0	02	01

In this example, the geocode-level taxability (“Yes”) applies to all customers except customer ‘01.'

Example 20 (Geocode Level exception based on Provider difference)

Geocode	TaxAuthId	Group	Item	Customer	Provider	Transaction	Taxable	TaxType	TaxCat
US36000000000	[NULL]	0001	001	99	99	01	1	02	01
US36000000000	1234	0001	001	99	99	01	0	02	01
US36001000001	1234	0001	001	99	99	01	1	02	01
US36001000001	1234	0001	001	99	01	01	0	02	01

In this example, the geocode-level taxability (“Yes”) applies to all providers except provider ‘01’

Example 21 (Percent Taxable and PerTaxableType)

Geocode	TaxAuthId	Group	Item	Transaction	Taxable	TaxType	TaxCat	PerTaxableType	PercentTaxable
US36000000000	1234	0001	001	01	1	02	01	01	1.000000
US36000000000	1234	0001	002	01	1	02	01	01	0.500000
US36000000000	1234	0001	003	01	1	02	01	01	0.850000

Example 21 contains three illustrations of PercentTaxable. When there is no deviation from the stated rate, the value in PercentTaxable is 1.000000. The second row of the example shows a PercentTaxable (that is, the “rate of the rate”) at 50%, meaning that the liable tax rate is 50% of the normal rate for the given tax. The third row shows a PercentTaxable of 85%, meaning that the liable tax rate is 85% of the normal rate for the given tax. There is currently only one value for PerTaxableType, ’01,’ which is defined simply as “Percent of Receipts Taxable.”

The possible scenarios are limited only by the imagination of the legislators for the various taxing authorities. They alter or impose taxability for taxes as they deem appropriate in their jurisdictions. The method for extracting the exact taxability decision for a given TTGICPT is discussed in chapter 7.

Note: For specific effective rates for a given group and item, legislators may impose rates that are functions of the rate for the given tax. For example, if the rate is .03500 (i.e., 3.5%) and the Percent Taxable value is 1.000 then there is no reduced rate. If, however the rate is .03500 and the Percent Taxable value is 0.7500, then the rate is calculated at 75% if its value [.035000 * .75] or 0.02625.

Rates

General

Rates describe the parts of the rate structure and how they are applied. Rate information is contained in the Detail table. Rate information is far more complex than taxability.

A given group-item-customer-provider-transaction type is either taxable or not taxable in a given place.Rates, however, include not only a set number of “core” values used in calculating the taxes charged for the given taxable goods or services, but also include other kinds of data that give specific meaning to one or more of the core elements. These are “definers.” Further, there are additional data elements called “peripherals” that describe specific rules about how the tax is executed, charged and reported. This combination of factors makes determining a rate more complicated than determining taxability.

The five elements that determine rates, and their corresponding columns in the Detail table, are:

Geography

Geocode geographic identifier for unique combinations of tax jurisdictions. For state level uses in Matrix, Detail and TaxOnTax, the Geocode contains the country and state codes followed by 8 trailing zeroes.

The tax itself


TaxType	General classification of tax
TaxCat	More specific differentiation under the corresponding TaxType code
TaxAuth	Numeric identifier for taxing authorities (which is blank for state level records)

Core Values


TaxRate	Tax Rate applied to specific Tier (0, 1, 2, etc.)
Fee	Fee applied per unit for specific Tier
Tier	Sequence identifier used in calculating tax or fee thresholds
MinTaxBase	Minimum dollar amount for specific Tier
MaxTaxBase	Maximum dollar amount for specific Tier
MinUnitBase	Minimum unit count for specific Tier
MaxUnitBase	Maximum unit count for specific Tier
MaxTax	Maximum permissible collectable tax

Definers


UnitType	UnitType code (i.e. “per what”)
MaxType	MaxType code (i.e. how aggregates are applied)
ThreshType	ThreshType code (i.e. “split” or “spread” for aggregation)
UnitAndOrTax	reserved

Peripherals


PassFlag	Can the tax be passed on to the consumer
PassType	How a tax is represented on an invoice
BaseType	Is this tax imposed on the seller or the buyer
Effective	Effective date
DateFlag	Reserved
Description	General description of the tax
Rounding	Rounding level code
Location	Code used on tax reporting forms
ReportTo	Tax Authority ID to which tax is reported
Formula	General method concepts for tax computation

Geocode, TaxType, TaxCat and TaxAuth are already familiar from descriptions in the chapters of this manual dealing with Geography, Tax Authorities and Taxability.

Core Values and their Associated Definers

The two primary vehicles for computing taxes due are tax rates and fees. Tax rates are based on the sale price of the goods or services. Fees, however, are flat (i.e., static) amounts that are generally unrelated to the sale price. To calculate fees, the thing being taxed is computed on a unit basis. Tax rates generally cover anything that is not specifically unit based.

If we say that a tax is a 5¢ fee the real question is, “What exactly is being taxed?” Similarly, if a TaxRate is defined as 5% for the first $100, the question, “Per what?” applies. The expression “for the first $100” implies that a given tax can have more than one value for its rate or fee. This introduces the concept of “aggregation” which plays a major role in many rates and fees. Aggregation requires different kinds of qualification both in terms of “what” and “how.”

For fees, the “per what?” question initially can be answered in terms of its associated definer, UnitType:

Example 22 (selected Unit Types associated with Fees and Rates)

UnitType	UnitTypeDescription
00	ACCESS LINE
02	ACCOUNT
03	kWh
04	THERM
99	N/A

For example, a 5¢ fee with UnitType value of 03 means that the 5¢ is applied to each kWh billed. In the case of rates, the "per what" question is generally applicable when aggregation is involved. It is initially answered by its definer, MaxType which clarifies how aggregates are applied.

Example 23 (selected MaxTypes associated with Rates)

MaxType	MaxTypeDescription
00	TOTAL TAXABLE RECEIPTS PER INVOICE
01	TOTAL TAXABLE RECEIPTS PER SINGLE ITEM
03	ACCUMULATED TOTAL OF CHARGES IN A UTILITY TAX YEAR (JULY/1 - JUNE/30)
09	MONTHLY TAXABLE RECEIPTS PER LOCATION
15	ACCUMULATED TOTAL OF UNITS PER MONTH
99	N/A

Both UnitType and MaxType have a ‘99’ code, which is defined as Not Applicable (n/a). ‘99’ in this case means that nothing needs to be defined in terms of Units or aggregation. Rates which invoke aggregation methods require UnitType Definers when the MaxType value calls for unit level aggregation.

There is a complication, however. Some kinds of services can be measured in different ways, leading to potential discrepancies between the measure used on a billing invoice and that represented in the detail table. For example an invoice might calculate billing in terms of individual kWh, but the state regulation in the Detail table might specify the use of multiples of kWh. To reconcile these differences, the CCH database provides a cross reference table, UnitTypeXRef, which helps clarify potential discrepancies.

The UnitTypeXRef table contains four values for each record – the UnitType (as used in the Detail table), the SuppliedUnitType (that is, the UnitType as defined on the invoice that may use a different measure than the Detail table), the Operation (the mathematical operator defining how UnitType and SuppliedUnitType are reconciled) and factor (the mathematical constant used in conjunction with operation).

Example 24a (two pairs of related UnitTypes)

UnitType	UnitTypeDesc
00	ACCESS LINE
17	SET OF 10 ACCESS LINES
03	kWh
09	mWh (1000 kWh)

Example 24b shows UnitTypes 00 and 17 are variations of each other – although they describe essentially the same thing, access lines, the number that would appear in MinUnitBase and MaxUnitBase would mean quite different things if the invoice uses the ‘17’ definition and the tax rule uses the ‘00’ code. A similar potential problem exists between 03 and 09, Kilowatt Hours and MegaWatt Hours.

Example 24b (the resolution as found in UnitTypeXRef)

#	UnitType	SuppliedUnitType	Operation	Factor
1	00	17	*	10.000000000
2	17	00	/	10.000000000
3	03	09	*	1000.000000000
4	09	03	/	1000.000000000

Example 24b.1 illustrates a situation in which the invoice uses UnitType 17 and the Detail table represents the tax with UnitType 00. In this case, to correctly calculate the tax, the number of units must be multiplied by a factor of ten. Example 24b.2 illustrates the opposite condition: to correctly calculate the units must be divided by 10.

Often, a tax rate or fee changes when larger numbers of units are purchased or the price is higher. When a given tax has more than one TaxRate or Fee, we say it is “tiered.”

Example 25 (Fee based tiers utilizing MinUnitBase and MaxUnitBase):

Tier	UnitType	Fee	MinUnitBase	MaxUnitBase
0	03	0.00000000	0.00000	1000.0000
1	03	0.00441000	1000.00000	0.00000

Example 25 illustrates a set of tiers for a given fee based tax when there is more than one blanket fee. In this case, the fee for this tax when the aggregate amount is <= 1000 units is $0.00. That is indicated by the MinUnitBase and MaxUnitBase values with the Fee for that range. The initial row in the set of tiers, or base tier, for the given tax is always indicated ‘0’.

When the number of units is greater than 1000, the fee is then $0.00441000 per unit. This second row is always referred to as ’1’ – the second tier above the base tier is tier ‘2’, etc. There is no maximum number of units in this example, as indicated by the MaxUnitBase in the first tier (since, in this case, it is also the only tier in the sequence).

Example 26 (Fee based tiers utilizing MinUnitBase, MaxUnitBase and MaxType)

Tier	UnitType	Fee	MinUnitBase	MaxUnitBase	MaxType
0	03	0.00000000	0.00000	1000.0000	15
1	03	0.00441000	1000.00000	0.00000	15

Example 26 expands the same set of data as in Example 24 to include a defining MaxType. In this case, the example demostrates a set of tiers where the UnitType, Kilowatt Hours (kWh) are aggregated on the basis (MaxType) of accumulated total number of units per month.

Example 27 (Rate based tiers utilizing MinTaxBase and MaxTaxBase)

Tier	MaxType	TaxRate	MinUnitBase	MaxUnitBase
0	01	0.000000000000	00000000.00	100.00
1	01	0.010000000000	100.00	400.00
2	01	0.005000000000	400.00	0.00

Example 27 illustrates a hierarchy (or set of tiers) for a given rate based tax when there is more than one blanket rate. In this case, when the amount of the sale is <= $100.00 the rate for this tax is 0%. That is indicated by the MinTaxBase and MaxTaxBase values along with the TaxRate. The base tier, is always called “0”. The TaxRate goes up to 1% when the price exceeds $100.00 but is not greater than $400.00. This is tier “1”. When the price exceeds $400.00, the rate of 0.5% applies. This is tier “2”. There is no maximum value in the second tier of this example indicating that at price points above $400.00 the tax rate is 0.5%.

The MaxType provides the “per what” information, which gives the relationship between TaxRate, MinTaxBase and MaxTaxBase for each of the tiers a specific meaning. In this case, MaxType ‘01’ means Total Taxable Receipts Per Single Item. Without this clarification it would be impossible to know the basis or how to aggregate the applicable threshold.

Rates as a function of Units, Fees aggregated on MinTaxBase/MaxTaxBase

The scenarios described above account for approximately 95% of the data contained in the Detail table. The remaining 5% relies on three additional scenarios in which the question of rates and fees and their various definers cross over each other. Some rates are calculated as a function of units, with their fees aggregated on a “tax base” rather than a “unit base”. There are rates aggregated on both taxbase and unitbase as well as fees aggregated on taxbase and unitbase.

Example 28 (Rates calculated as a function units)

tier	Tax Rate	Max Tax	Max Type	Unit Type	MinTax Base	MaxTax Base	fee	MinUnit Base	MaxUnit Base
0	0.00	0.00	15	03	0.00	0.00	0.00	0.000	750.000
0	0.05	0.00	15	03	0.00	0.00	0.00	750.000	0.000

Example 28, a utility tax whose MaxType indicates that it is computed on the basis of accumulated total units per month has a UnitType of kWh (Kilowatt Hours). In this instance there is no tax liability while the number of units <= 750 per month. Once the number of units exceeds 750, the applicable rate is 5%.

Example 29 (Fees aggregated on MinTaxBase/MaxTaxBase)

#	tier	MaxTax	UnitType	MaxType	TaxRate	MinTaxBase	MaxTaxBase	fee	MinUnitBase	MaxUnitBase
28a	0	0.00	03	06	0.00	0.00	3.00	0.01562600	0.00	117.11316
28b	0	0.00	03	06	0.00	0.00	200.00	0.01476600	0.00	9937.93303

Example 29 has two different utility taxes, both of which have a defined UnitType of kWh (Kilowatt Hours) and a MaxType indicating that billing is computed on the monthly taxable receipts per invoice per utility. The interesting twist to these two taxes is that there are two operators which determine the maximum number of units and the maximum tax. In the first example (28a) the maximum sales price that can be taxed is $3.00, while the maximum number of units (kWh) is 117.11316. This means that the actual number of units subject to taxation in any given transaction is a function of the price per unit charged.

Using the data given in Row 28a, if we have 100 units and 10 of these have a total sale price = $3.00. This means only these 10 are subject to the .01562600 fee per unit (in this case, 10). The remaining units are not subject to the tax indicated (because the 11th would bring the sale price over the $3.00 MaxTaxBase).

Similarly, if the number of units sold is 150 and the sale price for these 150 units is <= $3.00, only 117.11316 of them are subject to the tax because of the MaxUnitBase limit of 117.11316.

Example 30 (Rates aggregated on both MinTaxBase/MaxTaxBase and unitbase)

#	tier	UnitType	MaxType	TaxRate	MinTaxBase	MaxTaxBase	MinUnitBase	MaxUnitBase
30a	0	00	00	0.05	31.40	0.00	0.00	100.00
30b	0	03	16	0.1	0.00	4000.00	200.00	0.00
30b	1	03	16	0.02	4000.00	6000.00	200.00	0.00
30b	2	03	16	0.01	6000.00	0.00	200.00	0.00

Example 30 shows two different instances of rates aggregated both on MinTaxBase/MaxTaxBase and unitbase.

The first, 30a, a telecom tax whose UnitType is ACCESS LINE and whose MaxType is TOTAL TAXABLE RECEIPTS PER INVOICE is a single rate based tax (5%) that does not become applicable until the sales price is > $31.40. Only those charges for the first 100 access lines are subject to taxation.

The second, 30b, a utility tax computed in kWh, requires that there be more than 200 billable kWhs before any rate can be applied. The first 200 billable kWhs are not subject to taxation.

Assuming that the number of kWhs is > 200, then the base tier indicates the rate is 10% for any sale amount up to $4,000. The first tier above the base says a rate of 2% applies on that portion of the sale that is > $4,000 but <= $6,000. The second tier above the base says a rate of 1% applies on any sale amount above $6,000.

If we assume the minimum number of kWhs meets the stated criteria, and the total sale price for those kWhs exceeding 200 is $7,000, then the first $4,000 is taxed at 10%, the next $2,000 is taxed at 2%, and the remaining $1,000 is taxed at 1%.

Moving from Tier to Tier

What is not described by the various combinations of UnitType, Fee, MinUnitBase, MaxUnitBase, MaxType, TaxRate, MinTaxBase and MaxTaxBase is how you move from tier to tier. That is, how the rate or the fee of the next tier is applied when the price charged or units “consumed” exceed the limits of the previous tier. There are two methods used to determine the threshold where a subsequent tier supersedes a previous tier. These are referenced by the value in the ThreshType column of the Detail record.

The first category, the split method, takes the Fee, MinUnitBase, MaxUnitBase, TaxRate, MinTaxBase and MaxTaxBase values and applies them in order. Successive tiers of a given tax are applied to the marginal sales price or units consumed, represented by the range of the given tier. For example, if tier X of a given tax has a range of 500 – 1000, only the amount exceeding 500 and <= 1000 is subject to the rate for that tier.

The second category, the spread method, determines which tier applies to the total calculated amount (either sales price or unit volume or any applicable combination of both) and then applies only the values in that tier to the entire amount. Chapter 5.5 of this guide discusses Calculation Formulas and more precisely illustrates how these methods work in a number of scenarios.

By default, those tiers following the split method will have values of ‘0’ or ‘9’, while spread type thresholds will be indicated by ‘2.’ The following table shows the threshold types currently in use:

Example 31 (ThreshTypes)

ThreshType	ThreshTypeDesc
0	TAXRATE AND/OR FEE APPLIES TO THE PORTION OF THE AMOUNT < OR = TO MAXTAXBASE AND/OR MAXUNITBASE
1	RESERVED
2	TAXRATE AND/OR FEE APPLIES WHEN THE ENTIRE AMOUNT IS < OR = TO MAXTAXBASE AND/OR MAXUNITBASE
3	RESERVED
9	N/A

Example 32 (Rate based tiers utilizing MinTaxBase and MaxTaxBase and ThreshType)

Tier	MaxType	TaxRate	MinTaxBase	MaxTaxBase	ThreshType
00	01	0.000000000000	000000000.00	100.00	?
01	01	0.010000000000	100.00	400.00	?
02	01	0.005000000000	400.00	0.00	?

Example 32 is a rate based tax utilizing MinTaxBase and MaxTaxBase. From the perspective of ThreshType ‘0,’ the split method, if the total value of the taxable line item (i.e., MaxType ‘01’) were $500, anything up to $100 would not be subject to a tax since it has no associated rate. Only that part of the line item between $100 and $400 (that is, the next $300) is subject to a 1% rate. And everything greater than $400 up to $500 (that is, the remaining $100) is subject to the 0.5% rate.

If we consider the same example from the perspective of ThreshType 2, the spread method, then if the total value of the taxable line item were $500 only tier ‘02’ would be applicable to the entire amount. If the total value of the taxable line item were <= $400 but > $100, then tier ‘01’ would apply to the entire amount. If the total value of the taxable line item were $100 or less, then no tax would apply.

Example 33 (Fee based tiers utilizing MinUnitBase, MaxUnitBase, MaxType and ThreshType)

Tier	UnitType	Fee	MinUnitBase	MaxUnitBase	MaxType	ThreshType
0	03	0.00000000	0.00000	1000.0000	15	?
1	03	0.00441000	1000.00000	2000.00000	15	?
2	03	0.00330330	2000.00000	5000.00000	15	?

If we consider Example 33 from the perspective of ThreshType ‘0,’ the split method, when the unit count is <= 1000, no tax applies. If there are 6000 billable units, then the first 1000 above the initial 1000 (which are not subject to tax) would be subject to the 0.00441000 fee per unit. The next 3000 would be subject to the 0.00330330 fee per unit. The remaining 1000 units would not be subject to any tax.

Considered from the perspective of the ThreshType = 2, the spread method, there is no applicable tax since the total number of units billed, 6000, exceeds the MaxUnitBase value, 5000, in the highest tier listed for this tax. If the total unit count were greater than 2000 but <= 5000, then the fee in tier 02 would apply to the entire line item count. If the unit count were > 1000 and <= 2000, then tier 01 would apply to the entire item count.

The last of the core values is MaxTax. When a value > 0 is present in the MaxTax column, then an absolute cap has been placed on the calculated amount of the tax despite any other condition. MaxTax operates on the tax level and is not tier specific. However, the MaxType needs to be consulted in order to determine if the MaxTax is calculated on the line item or invoice level.

Example 34 (MaxTax)

MaxTax	UnitType	MaxType	ThreshType	tier	fee	MinUnitBase	MaxUnitBase
14.55	12	15	9	0	0.04450000	0.00000	1000.00000
14.55	12	15	9	1	0.04370000	1000.00000	0.00000

Example 34 illustrates a tiered, fee based utility tax with a MaxTax. The fee is based on the number of CCFs (100 Cubic Feet) (UnitType), and the tier thresholds are applied on the basis of the accumulated total of units per month (MaxType). If the number of CCFs is <= 1000, the rate is $0.0445 per CCF. If the number of CCFs is > 1000, the fee is $0.0437 per CCF exceeding 1000. However, the maximum amount of tax is limited to $14.55 despite the fee schedule established by the tier structure itself. Remember that although the MaxTax value appears in every tier, it is applicable on the tax level only.

An additional definer works with ThreshType, MinTaxBase/MaxTaxBase and MinUnitBaseBase/ MaxUnitBase. This definer, UnitAndOrTax, specifies criteria in recognizing thresholds applicable to taxes that have both a revenue and a unit determinant. This element is reserved and is not used at this time.

Peripherals

The fifth element in the description of rates and fees is a series of ten additional code fields which, with one exception, deal with ancillary aspects of the rate/fee.

PassFlag: Taxing authorities regulate whether sellers are permitted to pass a tax through to purchasers, and the form of that pass through. Sales tax, for example, is generally imposed on purchasers with the seller acting as collector for the tax authority. The seller is required to pass the tax on to the customer via a separate line item on the bill, using approved wording. Excise tax on the other hand, is ordinarily imposed on sellers, but sellers may have the option of including it in their rates or passing it on to consumers as a separate line item. Some taxes are prohibited from being passed on to consumers. The code indicating if a given tax can be passed along to the consumer is found in the PassFlag column.

Example 35 (PassFlag Codes)

PassFlag	PassFlagDesc
0	Optional
1	Required
2	Prohibited

PassType: Different taxing authorities have varying requirements defining how their taxes are to appear on invoices. The code indicating how a given tax is represented on an invoice is found in the PassType column.

Example 36 (PassType Codes)

PassType	PassTypeDesc
00	Separate Line Item as tax
01	Separate Line Item as surcharge
02	Surcharge - but not as separate item
03	Include in Base Rate

BaseType: Seller imposed taxes may be based on a variety of elements, such as a gross receipts, gross operating revenue, or receipts subject to income tax. Seller imposed taxes cannot always be directly passed on to the consumer. The codes that define the different BaseTypes (as a function of the tax as “belonging” to the consumer or the seller) are found in the BaseType table. A few examples are presented in Example 37 below.

Example 37 (Illustration of different BaseTypes)

BaseType	BaseTypeDesc
00	Consumer - Sales Price/Service Charge
01	Seller - Gross Receipts
03	Seller - Minutes Carried
06	Consumer - Access Line
23	Consumer - Per Unit Billed

Effective: The date when either a new tax or a revised tax becomes effective. The database does not distinguish between new and revised taxes. Updates to the database will indicate whether rows are expired or inserted, but inserts and expirations do not necessarily indicate a newly established tax, or a defunct one.

DateFlag: This field is currently reserved for future development.

Description: The description provides the name of the tax, or some descriptive phrase used to identify the tax. It is often used on invoices as the line item descriptor for the tax.

RoundingLevel: As with other Peripherals and Definers, the required method for rounding values is present in the detail table. The code’s value is found in the RoundingLevel table. At present, as in Example 39 below, there are two values for determining how rounding is accomplished.

Example 39 (Rounding Levels)

RoundingLevel	RoundingDesc
01	Round on item level
02	Round on total

Location: Some taxing authorities require a location code on forms used to report revenue and applicable taxes. These codes are authority-specific, and they are not necessary for calculations. There is no table to summarize or define them. They simply need to be present on whatever reporting forms are required by the given taxing authority.

ReportTo: Usually taxes are reported to the imposing authority. Sometimes, however, taxes are reported to another taxing authority or to an agency acting on behalf of the taxing authority. The ReportTo code points to the TaxAuth column in the TaxAuthority table. It gives the Country, State, AuthorityName and TaxAuthType of the Reporting Authority.

Calculation Formulas

Example 40 (Current Formulas)

calcid	Calcdesc
01	Standard Rate, No Aggregation, Maxtype = 99
02	TaxRate with split aggregation per invocie based on MinTaxBase for each single item, MaxType = 01 / ThreshType 2
03	TaxRate with split aggregation per invoice based on MinTaxBase and MaxTaxBase, MaxType 01, ThreshType 2
04	TaxRate with split aggregation for each single item based on MinTaxBase, MaxTaxBase, and invoice level split aggregation based on MinUnitBase, MaxUnitBase, MaxType = 20, ThreshType 2
05	Standad Fee, No aggregation, Maxtype = 99
06	Fee with split aggregation per invoice based on MinUnitBase and MaxUnitBase, MaxType Varies, ThreshType 2
07	Fee with spread aggregation per invoice based on MinUnitBase and MaxUnitBase, MaxType Varies, ThreshType 2
08	TaxRate with spread aggregation per invoice based on MinUnitBase and MaxUnitBase, MaxType Varies, ThreshType 2
09	TaxRate with split aggregation per invoice based on MinUnitBase and MaxUnitBase, MaxType Varies, ThreshType 2
10	TaxRate with split aggregation based on MinTaxBase and MaxTaxBase, MinUnitBase, MaxUnitBase per invoice, MaxType Varies, ThreshType 2
11	TaxRate with spread aggregation based on MinTaxBase and MaxTaxBase for each single item, MaxType = 01, ThreshType = 2
12	TaxRate with spread aggregation per invoice based on MinTaxBase and MaxTaxBase, MaxType 01, ThreshType = 2
99	N/A

As you use the database, your taxability determination will be refined as you combine the information in the database with the information you gather while doing business. Calculation Formulas provide the basic framework that describes the general methods used to calculate a particular tax. The formulae described below become even more effective as you learn to apply them to your business’s unique situation.

Formula 01 – describes a standard rate based tax where no aggregation or tier processing occurs on either the dollar amount of the sale or the number of units sold. The method to calculate the tax is rate * sale amount.

Formula 02 – is applied to rate based taxes where aggregation or tier processing is based on the sale amount per single item. In this case, either the MinTaxBase, or the MaxTaxBase or both can be > 0 (no MinUnitBase or MaxUnitBase), MaxType = 01 (total taxable receipts per single item) and ThreshType 2. Together, these indicate either a single rate with a specific threshold, or a set of tiers that operate using the split method (all tiers are applied as necessary). The definition of a single item can be understood from the UnitType value or, where UnitType = 99 from common usage. For example, is the single unit a single battery, or is it a single pack of batteries? That is determined by your coding.

Formula 03 – is applied to rate based taxes. It is similar to Formula 02. Due to its particular MaxTypes, it aggregates on the invoice level model. The MaxTypes indicate “per invoice” rather than “per item.”

Formula 04 – indicates a rate based tax which also has a unit count requirement. Aggregation or tier processing is required based on the sale price for each single item. Invoice level aggregation is applied based on the total units sold.

Formula 05 – is the fee based analogue to Formula 01. It is a flat fee based tax calculated solely on the basis of fee multiplied by the number of units sold.

Formula 06 – is a fee based tax where invoice level aggregation is required, depending on the number of units sold. It uses the split method of tiered calculations. Generally speaking, formula 06 deals with invoice based aggregation. However as with Formula 03, the database assigns Formula 06 to tax records where periodic aggregation is a factor.

Formula 07 – is similar to Formula 06 EXCEPT that tiers are calculated according to the spread method.

Formula 08 – is an aggregated rate based, invoice level tax, based on the number of units sold where the requirements of MinUnitBase and MaxUnitBase determine if the tax is applicable. It is aggregated according to the spread method.

Formula 09 – is similar to Formula 08 EXCEPT that the split method of aggregation is used.

Formula 10 – indicates an invoice level rate based tax which also has a unit count requirement. The aggregation or tier processing is based on the total units sold as well as the sale price per invoice.

Formula 11 – is a rate based tax that parallels Formula 02 (single item). The difference is its tiers are calculated using the spread method (in which a single tier applies to the whole transaction).

Formula 12 – is a rate based tax that parallels Formula 03 (invoice level). However, its tiers are calculated using the spread method (in which a single tier applies to the whole transaction).

Rates only package

The Rates Only Package includes only sales and/or use tax rates, without information on taxability.

Four primary tables (and their associated code tables) are used to determine rates without determining taxability: Plus4, Detail, Geocode, TaxOnTax, and TaxAuthority. CusomerType, ProviderType, TransactionType, ProductGroup, or ProductItem are taxability tables, so are not used in rates-only calculations. In the Detail (i.e. Rates) table, use taxes are identified with the letter “U” in the first character of the taxtype column.

Note: When you use the rates only table, calculations are made based on the assumption that your item or service is taxable. It is up to you to determine if the taxability assumption is correct.

You can integrate the rates only data into your accounting system by writing your own functionality to calculate taxes based solely on the database itself. Or, you can use calculations in your accounting package to define how the Rates Only data is used.

The two most common integration methods are:

Operate on a flat schedule basis. Combined rates are maintained inside the accounting package based on specific geography (i.e. by geocode). Taxes are derived from the Detail table by combining tax rates by geocode.
Use separate schedules on the basis of state, county, city and local level tax rates and query the Detail table by using taxauthtype.

See Chapter 6 if you need to calculate Tax On Tax.

Tax-On-Tax

Consumerbased and Providerbased Taxes

The discussion of the concept of tax-on-tax must begin by describing the differences between consumer-based and provider-based taxes. Generally speaking, consumer-based taxes assign the provider the role of agent for the authority imposing the tax. Any funds collected for consumer- based taxes are not considered to be part of a provider’s gross receipts. The money is not acquired as a function of the provider’s having added to the base amount of the billed sale. The money is collected to meet legislative requirements.

On the other hand, there are instances where the provider may have the option to pass on consumer taxes that are imposed on the provider. If your business passes the tax along to the consumer, the funds representing the passed through tax are added to the base amount of the invoice and are treated as taxable gross receipts to your business. They then become subject to various consumer- and provider-based taxes. This particular situation is the basis for tax-on-tax.

When applied in practice, a given TTGICPT is potentially subject to multiple taxes. Some of those taxes can be passed on to the customer. All of the taxes have to be evaluated against each other so the appropriate tax-on-tax calculations can be applied.

The table in the CCH database that provides tax-on-tax data is called TaxOnTax.This table contains seven elements: Geocode, TaxingTaxType, TaxingTaxCat, Taxable, TaxType, TaxCat and Effective. All TaxOnTax decisions are geocode based. That means, as with Detail and Matrix, the starting point for identifying TaxOnTax data is the geocode where the tax liability is under consideration. TaxType and TaxCat represent the base tax that potentially can be passed through to the customer. TaxingTaxType and TaxingTaxCat represent the tax that is imposed on the funds collected as the base tax and passed through to the customer. As in Matrix, the taxable column indicates the taxability decision.

When permitted, there are two kinds of rules that govern tax-on-tax – state level rules and local level rules. Given that tax-on-tax is geocode-specific, state level rules can be easily identified since the geocode for a state level rule will have only the country and state code with trailing zeros (US1000000000). Local level rules will have the full geocode with county and locality specified in addition to the state code (US1001000001).

Example 41 (Illustrations of TaxOnTax)

#	Geocode	TaxingTaxType	TaxingTaxCat	Taxable	TaxType	TaxCat	Effective
41a	US1000000000	42	01	Y	40	01	20000101
41b	US1000000001	42	01	N	40	01	20000101

Example 41 illustrates two instances of tax-on-tax. 41a demonstrates a state based rule. 41b demonstrates a local rule. In 41a, the provider is allowed to pass the tax defined as TaxType 40, TaxCat 01 in geocode US1000000000 to the customer. In doing so, however, the provider becomes liable for that pass through as a function of TaxingTaxType 42 and TaxingTaxCat 01. 41b represents a local override to the state rule for the taxing jurisdiction US1001000001. Even though the taxes represented by TaxType/TaxCat 40/01 are passed through to the consumer, the pass through amount does not become subject to TaxType/TaxCat 42/01.

To calculate tax-on-tax, the above can be summarized in three steps:

1. The user needs to capture all taxes imposed for each line item on an invoice (i.e., the applicable TaxTypes and TaxCats). For purposes of discussion let us say these taxes are placed in a table called YourLIST as follows:

Example 42 (illustration of applicable taxes for a given invoice’s items)

TaxType	TaxCate
01	01
02	01
03	01

Then cross join this table to itself in order to derive all the potential combinations of TaxType and TaxCat as follows:

Example 43 (joining a table to itself to produce all the possible permutations)

Select distinct  
a. TaxType TaxingTaxType,
a. TaxCat TaxingTaxCat
b. TaxType
b. TaxCat
fromYourList a Cross join YourList b

Example 44 (results of Example 43's query based on Example 42)

TaxingTaxType	TaxingTaxCat	TaxType	TaxCat
01	01	01	01
01	01	02	01
01	01	03	01
02	01	01	01
02	01	02	01
02	01	03	01
03	01	01	01
03	01	02	01
03	01	03	01

2 -Query the TaxOnTax table to find records that match the cross joined list for the transaction location. Keep in mind that you have to account for potential overrides.

3 -Subject each matched record in YourLIST to calculation routines that capture the appropriate TaxOnTax amount.

The number of iterations of tax-on-tax processing is, by definition, a business decision that will depend on transaction size and the degree of precision required. As your taxes are calculated as percentages of a smaller base percentage, the tax amount will approach zero. For most transactions, the zero point is reached at the third or fourth iteration. Larger transactions and higher rates will require more iterations to reach zero.

Putting It All Together

Overview

Now that you understand the database components and the basics of taxability and rate determinations, you are better prepared to use the database accurately and efficiently. This section will guide you through learning how to put the components together. Sales and use tax is complicated so we can’t cover every possible situation, but the examples below were chosen to guide you through what you will probably encounter in the course of business. Once you learn the fundamentals you can adapt them to your particular business need.

Basic Assumptions

The old saying “garbage in/garbage out” is never more true than in working with a relational database. The only way you will get accurate taxability and rate determinations is by ensuring the business information you apply with this database is complete and correct. For example, accurate customer addresses are essential to the geocode-based process. You also need to know what type of customer is being billed, by what kind of provider. Before you start applying the database you’ll also need to map your existing goods and services codes to the CCH database. Taking time to set up all of the necessary information will mean your taxability and rate determinations are easy and accurate. To complete your calculations for an invoice, you’ll also need the price per unit charged for each of your goods or services, the total price for each line invoice line item, and the number of units sold.

Taxes for an invoice may require aggregation methods that cover more than one line item on the invoice. Depending on the kind of product or service sold and its applicable tax (or taxes), a single line item may require some form of aggregation. Keep in mind, however, this discussion is not concerned with aggregation over multiple line items.

There is a complication, however, in that some kinds of services can be measured in different ways, leading to potential discrepancies between the measure used on a billing invoice and that represented in the detail table. For example an invoice might calculate billing in terms of individual kWh while the state regulation might specify using multiples of kWh. To reconcile these differences, the CCH database provides a cross reference table, UnitTypeXRef, which helps clarify potential discrepancies.

For example, a line item might say 10 computers for a total price of $5000 (in which case the total price divided by the number of units gives the individual unit cost of $500), OR, 10 computers at $500 per computer (in which case the total price is the amount per computer multiplied by the number of computers, or $5000).

Some of the illustrations here involve sample SQL queries. The CCH database is standardized on TSQL for Microsoft SQL Server 2005. You should adjust your queries to the specific syntax of the version of SQL you are using.

Since the CCH Database is organized around specific geography, the first stage involves the use of geocodes, associated ZIP Codes and the specific ZIP+4 code applicable for the invoice and customer. From this point you can determine the list of applicable taxes for the geocode. For each applicable tax associated with the geocode you then determine taxability for the given group, item, customer and provider for the invoiced items, as listed in the Matrix table. For each taxable tax you then calculate the tax based on the data in the Detail table. Finally, you calculate tax-on- tax if appropriate. The steps are described in detail below.

The method for extracting data is given here as model SQL code. We will assign certain generic names to represent data elements specific to the seller’s side of the process to illustrate the various steps common to the broad scope of the process.

STEP ONE: GEOGRAPHY

Because the CCH Database is geographically oriented, the point of entry for the tax calculation process is a specific location (i.e., ZIP+4). The underlying assumption is that tax sourcing and nexus issues have been properly resolved by the vendor outside of this process. The examples that follow assume a tax jurisdiction sourcing of the ship-to address of the customer. First, then, you need @YOURZipCode and @YOURPlus4, which will provide @YOURGeocode.

Example 45 (determining Geocode with @YOURZipCode and @YOURPlus4)

set @YOURGeocode = 
(select top 1 Geocode from Plus4 where zip = @YOURZipCode
and Plus4Lo <=@YOURPlus4 and Plus4Hi >=@YOURPlus4  order by MainRange)

The result of the query using @YOURZipCode = ‘10952’ and @YOURPlus4 = ‘1122’ is:

Geocode
US3608779174

STEP TWO: FINDING APPLICABLE TAXES (in general)

Before you can isolate specific applicable taxes for invoiced items, you must first determine which taxes in the detail table are applicable to YOURGeocode in question. Initially, we will put this information into a table called YOURSHORTLIST:

Example 46 (isolating potentially applicable taxes from Detail)

Select distinct TaxType, TaxCat,TaxAuthId  
Into YOURSHORTLIST 
From Detail 
Where Geocode = @YOURGeocode

The results of this query will need to be reduced to a more specific list. To do that we will need more information. We need to know which TaxTypes, TaxCats and TaxingAuthorities exist in the geocode.

Example 47 (potential sales taxes based on @YOURGeocode)

TaxType	TaxCat	TaxAuthId
01	00	1468
01	01	1468
02	01	1782
03	00	3032
03	01	3032

STEP THREE: FINDING THE TAXABILITY DECISIONS

Since taxability is specifically a function of the items being taxed, you will need to have the codes representing invoice items mapped to groups and items in the CCH database. These are @YOURGroup and @YOURItem. The customer type will need to be associated with a specific code in the database, @YOURCustomer. The vendor will need to be mapped to the specific provider type which, @YOURVendor. The vendor must also determine the kind of transaction, @YOURTransType.

You’ll need to process each item to find any applicable taxes. Any items in YOURSHORTLIST that are not applicable should be deleted from the list or otherwise marked inapplicable. Since taxability, if it exists at all, is identified at the local level, the tax authority level or the state level, you may have to test for each of these separately.

Local Level Matching

Find TaxType, TaxCat, TaxAuthId and @YOURGeocode in Matrix where group = @YOURGroup, item = @YOURItem, customer = @YOURCustomer or ‘99’, provider = @YOURProvider or ‘99’, transtype = @YOURTranstype. If taxability is 0 (i.e., ‘N’) then the tax (as represented by the item in YOURSHORTLIST) is not applicable. It should be deleted or flagged.

If taxable = 1 (‘Y’) taxability has been established and so it is not necessary to continue testing this record. You can move on to the next item.

Authority Level Matching

If you did NOT make a match for your TaxType, TaxCat, taxauthority and Geocode then you have to attempt authority level matching.

Find TaxType, TaxCat, TaxAuthId and state level Geocode [that is substring(@YOURGeocode,1,4)+’00000000’] in Matrix where group = @YOURGroup, item = @YOURItem, customer = @YOURCustomer or ‘99’, provider = @YOURProvider or ‘99’, transtype = YOURTranstype. If taxability is 0 (i.e., ‘N’) then the tax (as represented by the item in YOURSHORTLIST) is not applicable, and should be deleted or marked accordingly.

If taxable = 1 (‘Y’) taxability has been established and so it is not necessary to continue testing this record. Move on to the next one.

State Level Matching

If you did NOT make a match for your TaxType, TaxCat, taxauthority and state level Geocode then you have to attempt state level matching. Find TaxType, TaxCat and state level Geocode [that is substring(@YOURGeocode,1,4)+’00000000’] in Matrix where TaxAuthId is blank (or NULL, depending on your database structure) and where group = @YOURGroup, item = @YOURItem, customer = @YOURCustomer or ‘99’, provider = @YOURProvider or ‘99’, transtype = @YOURTranstype. If taxability is 0 (i.e., ‘N’) then the tax (as represented by the item in YOURSHORTLIST) is not applicable. It should be deleted or marked accordingly.

If taxable = 1 (‘Y’) taxability has been established and so it is not necessary to continue testing this record. You can move on to the next item.

If not found altogether

If the record in YOURSHORTLIST fails all of these tests, there is no taxability for the TaxType- TaxCat-TaxAuthId-Geocode. This record is DELETED or marked accordingly. Then you move on to the next record.

STEP FOUR: FINDING APPLICABLE TAXES (specific)

The sales invoice for general merchandise below demonstrates how to apply the process.

Keeping in mind the various mapping issues and other pre-tax calculation decisions, we will establish the list of accountable elements and their associated SQL prototype variables as follows:

Example 48 (table of suggested variables for tax and taxability computation)

Variable	Value(s)	Comment
@YOURZipCode	10952
@YOURPlus4	1122
@YOURGeocode	US3608779174
@YOURTaxType	01, 02 or 03	(see Example 44)
@YOURTaxCat	00, 01	(see Example 44)
@YOURTaxAuthId	1468, 1782, 3032	(see Example 44)
@YOURGroup	0000	(general, unspecified merchandise)
@YOURItem	000	(general, unspecified merchandise)
@YOURCustomer	08	(retail customer)
@YOURProvider	70	(retail)
@YOURTransType	70	(sale)
@YOURTransDate	08/12/2008	invoice date

For each potential tax listed in Example 47, we need to determine if taxability applies. For each row in YOURSHORTLIST the following SQL code returns taxable decisions (and PercentTaxable). Those whose taxable value is 1 (i.e., Y) remain in YOURSHORTLIST. Those whose taxable value is 0 (i.e., N), or NULL are deleted from the YOURSHORTLIST.

Example 49 (determining taxability row by row from YOURSHORTLIST)

Select top 1 taxable, PercentTaxable From Matrix 
Where Geocode in (@YOURGeocode, substring(@YOURGeocode,1,4)+’00000000’) 
and (TaxAuthId = @YOURTaxAuthId or TaxAuthId is NULL) 
and TaxType = @YOURTaxType and TaxCat = @YOURTaxCat and [Group] = @YOURGroup 
and Item = @YOURItem and Customer in (@YOURCustomer, ‘99’) 
and Provider in (@YOURProvider, ‘99’) 
and [Transaction] in (@YOURTranstype, ‘99’) 
and @YOURTransDate >= effective Order by Geocode desc, TaxAuthId desc, customer, provider

Example 50 (result of processing the query in Example 46)

Row	TaxType	TaxCat	TaxAuthId	Taxable	PercentTaxable
1	01	00	1468	NULL	NULL
2	01	01	1468	1	1.00000
3	02	01	1782	1	1.00000
4	03	00	3032	NULL	NULL
5	03	01	3032	1	1.00000

After processing the query in Example 50 through the five potential taxes (01/00, 01/01, 02/01, 03/00 and 03/01), only three prove to be taxable. The taxes in rows 1 and 4 did not return any values and should be deleted from the list. Now we can go back to the Detail table and extract the rate information for each of the items in the invoice. Assuming for the moment that YOURSHORTLIST is a SQL table, you can derive the applicable taxes with a simple join between YOURSHORTLIST and Detail as follows:

Example 51 (extracting rate information from Detail by way of YOURSHORTLIST)

select distinct *  
from Detail a 
join YOURSHORTLIST b 
on  
a.TaxType = b.TaxType and  a.TaxCat = b.TaxCat and  a.TaxAuthId = b.TaxAuthId  
where 
a.Geocode = @YOURGeocode and
@YOURTransDate >= effective

The query in Example 51 produces the following information from Detail:

Example 52 (rate information derived from Detail by the code in Example 48)


Geocode	US3608779174	US3608779174	US3608779174
TaxType	01	02	03
TaxCat	01	01	01
TaxAuthId	1468	1782	3032
Effective	2005-06-01	2007-03-01	2005-06-01
Description	SALES TAX	CPIMTU SA;ES TAX	METRO COMMUTER TRANS. DISTRICT
PassFlag	1	1	1
PassType	00	00	00
BaseType	00	00	00
DateFlag	02	02	02
Rounding	02	02	02
Location		RO 3921	RO 3921
ReportTo	1468	1468	1468
MaxTax	0.00	0.00	0.00
UnitType	99	99	99
MaxType	99	99	99
ThreshType	9	9	9
UnitAndOrTax
Formula	01	01	01
Tier	0	0	0
TaxRate	0.04000	0.04000	0.00375
MinTaxBase	0.00	0.00	0.00
MaxTaxBase	0.00	0.00	0.00
Fee	0.00000	0.00000	0.00000
MinUnitBase	0.00000	0.00000	0.00000
MaxUnitBase	0.00000	0.00000	0.00000

Reading these results vertically, the three taxes (01/01, 02/01 and 03/01) can be read as:

The three different taxing authorities point to a state level tax, a county level tax and a special district level tax. All three of the taxes became effective before the invoice date of our example and are, therefore, in force. The PassFlag = 1 indicates all three taxes are required. The PassType indicates that the invoice must list the tax as a separate line item. The BaseType indicates that this is a consumer-sale price computation. The DateFlag indicates that the tax is charged as of the invoice date. Rounding indicates that the total tax, once computed, is rounded on the sum of computed taxes, and not on a line item basis. The ReportTo code is the same as the state taxing authority code, indicating these taxes are reported to the state taxing authority. The remaining data points collectively indicate these taxes are simple sales taxes based on the sale price of the taxable items and nothing else.

Each item on the invoice has three components – the state sales tax of 4%, the county sales tax of 4% and the special district tax of .375%. The items on our invoice have the following taxes associated with them:

Item	Cost	State tax (4%)	Count tax (4%)	Special District tax (.375%)	Total tax
6 Green Metallic Bridge Chair	$143.88	$5.7552	$5.7552	$0.53955	$12.04995
8" Plastic Stools	$13.98	$0.5592	$0.5592	$0.052425	$1.170825
	$157.86			Subtotal for computed taxes	$13.220775
					============
				Rounded tax	$13.22

Our original invoice, when completed after the computation of the applicable taxes, now looks like this:

ASCII Files Table Layouts

General

This chapter lists the tables in the CCH Database. The list shows how the data is delivered, as opposed to how it would be represented in a SQL database. See Chapter 9 for sample SQL code for the creation of a SQL database for data presented in flat ASCII format.

Text Files

The ASCII (TXT) records provided each month are delimited with carriage return/line feed (CR/LF) characters. The following is a list of all text files.

The text files consist of six primary data tables and twenty-one code reference tables. Detailed descriptions of all the tables, their column by column definitions and functionality, as well as their uses and methods are found in the main documentation.

Those customers receiving the data in “Delta” format will find all tables containing an additional column. That column is “action,” a single character column indicating an Insert (“I”) or a Delete (“D”).

Primary Data Tables

1. Plus4 2. Matrix 3. Detail 4. Geocode 5. TaxOnTax 6. TaxAuthority 7. NPANXX **8.**TaxAuthType 9. TaxType 10. TaxCat 11. PassFlag 12 PassType 13. BaseType 14. DateFlag 15. RoundingLevel 16. UnitType 17. MaxType 18. ThreshType 19. CustomerType 20. ProviderType 21. TransactionType 22. PercentTaxableType. 23. UnitTypeXRef 24. ProductGroup 25. ProductItem 26. Country 27. State 28. CalculationFormula

1‐ PLUS4 Record Layout

	Field Name	Pos	Len	Type	Field Description
1	ZIP	1	5	Char	Postal ZipCode
2	Plus4Lo	6	4	Char	Low range for ZipCode Plus4 extension
3	Plus4Hi	10	4	Char	High range for ZipCode Plus4 extension
4	State	14	2	Char	State abbreviation
5	Geocode	16	12	Char	Geographic Identifier for unique combinations of tax juridictions.
6	MainRange	28	1	Int	PLUS4 range for zipcode (1=Y, 0=N) flag

2‐ Matrix Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Geocode	1	12	Char	Geographic Identifier for unique combinations of tax juridictions. For state level taxes: country+state+8 zeroes
2	TaxAuthId	13	9	Int	Numeric identifier for taxing authorities (blank for state level records)
3	Group	22	4	Char	Product group code
4	Item	26	3	Char	Product item code
5	Customer	29	2	Char	Customer type code
6	Provider	31	2	Char	Provider type code
7	Transaction	33	2	Char	Transaction type code
8	Taxable	35	1	Int	Taxable fla g (1=Yes, 0=No)
9	TaxType	36	2	Char	TaxType code
10	TaxCat	38	2	Char	TaxCat Code
11	Effective	40	8	Date	Effective Date
12	PerTaxableType	48	2	Char	Percent taxable type code
13	PercentTaxable	50	8	Num	% of Rate or Fee applicable

3‐ Detail Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Geocode	1	12	Char	Geographic Identifier for unique combinations of tax juridictions
2	TaxType	13	2	Char	TaxType code
3	TaxCat	15	2	Char	TaxCat code
4	TaxAuthId	17	9	Int	Numeric identifier for taxing authorities (blank for state level records)
5	Effective	26	8	Date	effective date
6	Description	34	100	Varchar	Tax description
7	PassFlag	134	1	Char	PassFlag code
8	PassType	135	2	Char	PassType code
9	BaseType	137	2	Char	BaseType code
10	DateFlag	139	2	Char	DateFlag Code
11	Rounding	141	2	Char	Rounding Level code
12	Location	143	15	Char	Code on tax forms for reporting purposes
13	ReportTo	158	9	Int	Tax Authority ID to which tax is reported
14	MaxTax	167	8	Num	Maximum tax amount
15	UnitType	175	2	Char	UnitType code
16	MaxType	177	2	Char	MaxType code
17	ThreshType	179	1	Char	ThreshType code
18	UnitAndOrTax	180	1	Char	Reserved
19	Formula	181	2	Char	Calculating formula code
20	Tier	183	2	Int	Sequence identifier used in calculating tax or fee thresholds. ‘0’ is always the default when no tiers are present. When tiers are present, ‘0’ represents the base tier (0 followed by 1, 2, 3, etc.)
21	TaxRate	185	14	Num	Tax Rate applied to a specific tier
22	MinTaxBase	199	11	Num	Minimum dollar amount for a specific tier
23	MaxTaxBase	210	11	Num	Maximum dollar amount for a specific tier
24	Fee	221	12	Num	Fee applied per unit for a specific tier
25	MinUnitBase	233	15	Num	Minimum unit count for a specific tier
26	MaxUnitBase	248	15	Num	Maximum unit count for a specific tier

4‐ Geocode Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Country	1	2	Char	Country code
2	State	3	2	Char	State or Province code
3	County	5	75	Varchar	County name
4	City	80	255	Varchar	City or place name
5	TaxDistrict	335	255	Varchar	Special Tax district description (where applicable)
6	Geocode	590	12	Char	Geographic Identifier for uniqu combinations of tax juridictions
7	GNIS	602	10	Varchar	GNIS code

5‐ TaxOnTax Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Geocode	1	12	Char	Geographic Identifier for unique combinations of tax juridictions. For state level records: country+state+8 zeroes.
2	TaxingTaxType	13	2	Char	Taxing TaxType code
3	TaxingTaxCat	15	2	Char	Taxing TaxCat code
4	Taxable	17	1	Int	Taxable decision (1= Yes, 0 = No)
5	TaxType	18	2	Char	TaxType subject to taxing tax
6	TaxCat	20	2	Char	TaxCat subject to taxing tax
7	Effective	22	8	Date	Legal effective date of the rule

6‐ TaxAuthority Record Layout

	Field Name	Pos	Len	Type	Field Description
1	TaxAuthId	1	9	Int	Numeric identifier for taxing authorities (blank for state level records)
2	Country	10	2	Char	Country code
3	State	12	2	Char	State code
4	AuthorityName	14	200	Varchar	Tax AuthorityName
5	TaxAuthType	214	1	Char	Tax Authority Type code

7‐ NPA‐NXX Record Layout

	Pos	Len	Type	Field Description
NPA	1	3	char	Numbering Plan Area Code
NXX	4	3	char	Central Office or Exchange Code
LATA	7	4	char	Local Access Transport Area Code
Geocode	11	12	char	Geocode

8‐ TaxAuthType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	TaxAuthType	1	1	Char	Tax Authority Type code
2	TaxAuthTypeDesc	2	100	Varchar	Tax Authority Type description

9‐ TaxType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	TaxType	1	2	Char	TaxType code
2	TaxTypeDesc	3	100	Varchar	TaxType description

10‐ TaxCat Record Layout

	Field Name	Pos	Len	Type	Field Description
1	TaxCat	1	2	Char	TaxCat code
2	TaxCatDesc	3	100	Varchar	TaxCat description

11‐ PassFlag Record Layout

	Field Name	Pos	Len	Type	Field Description
1	PassFlag	1	1	Char	PassFlag code
2	PassFlagDesc	2	100	Varchar	PassFlag description

12‐ PassType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	PassType	1	2	Char	PassType code
2	PassTypeDesc	3	100	Varchar	PassType description

13‐ BaseType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	BaseType	1	2	Char	BaseType code
2	BaseTypeDesc	3	100	Varchar	Base Type description

14‐ DateFlag Record Layout

	Field Name	Pos	Len	Type	Field Description
1	DateFlag	1	2	Char	DateFlag code
2	DateFlagDesc	3	100	Varchar	DateFlag description– currently reserved

15‐ RoundingLevel Record Layout

	Field Name	Pos	Len	Type	Field Description
1	RoundingLevel	1	2	Char	RoundingLevel code
2	RoundingLevelDesc	3	100	Varchar	RoundingLevel description

16‐ Unit Type Record Layout

	Field Name	Pos	Len	Type	Field Description
1	UnitType	1	2	Char	UnitType code
2	UnitTypeDesc	3	100	Varchar	UnitType description

17‐ MaxType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	MaxType	1	2	Char	MaxType code
2	MaxTypeDesc	3	100	Varchar	MaxType description

18‐ ThreshType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	ThreshType	1	1	Char	ThreshType code
2	ThreshTypeDesc	2	100	Varchar	ThreshType description

19‐ CustomerType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	CustomerType	1	2	Char	CustomerType code
2	CustomerTypeDesc	3	100	Varchar	CustomerType description

20‐ ProviderType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	ProviderType	1	2	Char	ProviderType code
2	ProviderTypeDesc	3	100	Varchar	ProviderType description

21‐ TransactionType Record Layout

	Field Name	Pos	Len	Type	Field Description
1	TransactionType	1	2	Char	TransactionType Code
2	Description	3	100	Varchar	TransactionType Description

22‐ PercentTaxable Type Record Layout

	Field Name	Pos	Len	Type	Field Description
1	PercentTaxable	1	2	Char	Code
2	Description	3	100	Varchar	PercentTaxableType description

23‐ UnitTypeXRef Record Layout

	Field Name	Pos	Len	Type	Field Description
1	UnitType	1	2	Char	UnitType code referencing Detail
2	SuppliedUnitType	3	2	Char	UnitType code provided by user
3	Operation	5	1	Char	Reconcilliation of UnitType and SuppliedUnitType
4	Factor	6	21	Numeric	Mathematical value applied in operation

24‐ ProductGroup Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Group	1	4	Char	Group Code
2	Description	5	30	Varchar	General category of goods and services

25‐ ProductItem Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Group	1	4	Char	Product Code for item
2	Item	5	3	Char	Item code
3	Description	8	100	Varchar	Specific goods or servicesunder general group category

26‐ Country Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Country	1	2	Char	Alphabetical code for the country
2	Name	3	70	Varchar	Name of the country

27‐ State Record Layout

	Field Name	Pos	Len	Type	Field Description
1	Country	1	2	Char	Alphabetical Country code
2	State	3	2	Char	Alphabetical code for state or province
3	Name	5	70	Varchar	Name of the state or province

28‐ CalculationFormula

	Field Name	Pos	Len	Type	Field Description
1	Formula	1	2	Char	Calculation Formula ID
2	Description	3	200	Varchar	Calculation Formula Description

Establishing SQL Tables

Some column names are considered reserved words in T-SQL. They must be encased in square brackets ([…]) when creating the tables that use them, and when those columns are addressed in queries. To ensure accurate representation of floating point numeric values those columns are given using SQL standard precision.

Plus4

create table Plus4
(Zip		char(5),
Plus4Lo		char(4),
Plus4Hi		char(4),
State		char(2),
Geocode		char(12),
MainRange	int )

Matrix

create table Matrix
(Geocode		char(12),  
TaxAuthId		int,  
[Group]			char(4),  
Item			char(3), 
Customer		char(2), 
Provider		char(2),  
[Transaction]		char(2), 
Taxable			int,  
TaxType			char(2),  
TaxCat			char(2),  
Effective		datetime, 
PerTaxableType		char(2),  
PercentTaxable		numeric(7,6) )

Detail

create table Detail
(Geocode		char(12), 
TaxType			char(2),
TaxCat			char(2),  
TaxAuthId		int,  
Effective		datetime, 
Description		varchar(100), 
PassFlag		char(1), 
PassType		char(2), 
BaseType		char(2), 
DateFlag		char(2),  
Rounding		char(2),  
Location		char(15),  
ReportTo		int, 
MaxTax			numeric(7,2),  
UnitType		char(2),  
MaxType			char(2),  
ThreshType		char(1), 
UnitAndOrTax		char(1), 
Formula			char(2),  
Tier			int, 
TaxRate			numeric(13,12), 
MinTaxBase		numeric(10,2),  
MaxTaxBase		numeric(10,2), 
Fee			numeric(11,8), 
MinUnitBase		numeric(14,5), 
MaxUnitBase		numeric(14,5) )

Geocode

create table Geocode
(Country		char(2),  
State			char(2), 
County			varchar(75), 
City			varchar(255),  
TaxDistrict		varchar(255), 
Geocode			char(12), 
GNIS			varchar(10) )

TaxOnTax

create table TaxOnTax
(Geocode		char(12), 
TaxingTaxType		char(2), 
TaxingTaxCat		char(2), 
Taxable			int, 
TaxType			char(2), 
TaxCat			char(2), 
Effective		datetime )

TaxAuthority

create table TaxAuthority
(TaxAuthId			int, 
Country				char(2),
State				char(2), 
AuthorityName			varchar(200), 
TaxAuthType			char(1) )

NPANXX

create table NPANXX
(NPA		char (3), 
NXX		char (3), 
LATA		char (4), 
GEOCODE		char (12) )

TaxAuthType

create table TaxAuthType
(TaxAuthType		char(1), 
TaxAuthTypeDesc		varchar(100) )

UnitType

create table UnitType
(UnitType		char(2),  
UnitTypeDesc		varchar(100) )

MaxType

create table MaxType
(MaxType		char(2), 
MaxTypeDesc		varchar(100) )

ThreshType

create table ThreshType
(ThreshType		char(1), 
ThreshTypeDesc		varchar(100) )

Customer

create table Customer
(CustomerType		char(2), 
CustomerTypeDesc	varchar(100) )

Provider

create table Provider
(ProviderType		char(2),
ProviderTypeDesc	varchar(100) )

TransactionType

create table TransactionType
(TransactionType	char(2), 
Description		varchar(100) )

Geocode

create table Geocode
(Country		char(2),  
State			char(2),  
County			char(75),  
City			varchar(255),  
TaxDistrict		varchar(255), 
Geocode			char(12),  
GNIS			varchar(10) )

TaxType

create table TaxType
(TaxType		char(2),
TaxTypeDesc		varchar(100) )

TaxCat

create table TaxCat
(TaxCat			char(2), 
TaxCatDesc		varchar(100) )

PassFlag

create table PassFlag
(PassFlag		char(1), 
PassFlagDesc		varchar(100) )

PassType

create table PassType
(PassType		char(2), 
PassTypeDesc		varchar(100) )

BaseType

create table BaseType
(BaseType		char(2), 
BaseTypeDesc		varchar(100) )

DateFlag

create table DateFlag
(DateFlag		char(2), 
DateFlagDesc		varchar(100) )

RoundingLevel

create table RoundingLevel
(RoundingLevel		char(2), 
RoundingLevelDesc	varchar(100) )

PercentTaxableType

create table PercentTaxableType
(PercentTaxable		char(2), 
Description		varchar(100) )

UnitTypeXRef

create table UnitTypeXRef
(UnitType			char(2), 
SuppliedUnitType		char(2), 
Operation			char(1), 
Factor				numeric(20,9) )

ProductGroup

create table ProductGroup
([group]		char(4), 
Description		varchar(30) )

ProductItem

create table ProductItem
([group]		char(4),
Item			char(3), 
Description		varchar(100) )

Country

create table Country
(Country	char(2), 
[Name]		varchar(70) )

State

create table State
(Country	char(2), 
State		char(2), 
[Name]		varchar(70) )

calculationFormula

create table calculationFormula
(CalculationFormulaId		char(2), 
CalculationFormulaDesc		varchar(200))

Current Database vs. Legacy

General Observations

Users of the legacy CCH Sales and Use Tax Database will immediately notice many differences when comparing the older format and content of the data to its present form. The current database includes a number of enhancements as well as several key conceptual changes. This chapter introduces and explains those changes to users of the previous system.

The most obvious difference between the two databases can be seen in the greater number of tables¹ in the current² version. The older version had seven files: Plus4, ZIP, NPA/NXX, TXMatrix, Geocode, Detail and Code. The new version has four times as many tables. Of the original seven tables, only Plus4, TXMatrix (renamed Matrix), Geocode, Detail and NPA/NXX remain, although they have been changed. The older single Code table is now presented as a sequence of individual tables specific to the given code types which are referenced elsewhere in the database.

The most basic change from the older to the newer system is the organization of the data around Taxing Authority. Geocodes, which previously only generally described where a tax was in force, are now specifically associated with given taxing authorities. The TaxAuthID code now specifically describes a taxing authority, and has its corollary in the ReportTo code (the reporting or collecting agency for the given taxing authority). This allows the database structure to display individual instances of each tax for each authority.

¹ ASCII flat source files which are ingested into whatever SQL database format the user of the data uses to house and process them.

²“New version” and “Current Version” are used interchangeably.

Geographical information is now contained in the Plus4 and Geocode tables. In the new structure both INOUTCITY and INOUTLOCAL are no longer needed and have been dropped. The new system with its granularized view of geographically bounded areas also provides references to GNIS³ codes. As mentioned in Chapter 3, however, the GNIS codes alone are not specific enough to use in determining tax liability, proper reporting, or tax computation.

To clarify the issue of national identity throughout the database, the geocode table now includes a country code (US = United States, CA = Canada, etc.). This will prove especially useful in distinguishing Canada, the country (i.e., CA) from California, the US State (i.e., CA).

TaxOnTax information was previously embedded in the TXMATRIX (i.e., Matrix) table. It is now contained in its own table.

The RECTYPE⁴ data point in the older TXMATRIX table is no longer present in the new Matrix table.

Use taxes are now specifically represented in Detail and Matrix by unique taxtypes and taxcats. Use taxes are now indicated simply by the letter ‘U’ appearing as the first character of a use taxtype. It is better, however, to reference the transaction type in Matrix to determine the appropriate taxes for a given transaction type.

Rate data includes a number of new items, including the taxing authority, the reporting authority, rounding factor, calculation formula (i.e., method) description, and aggregate thresholds for both rate and fee based taxes. Tiered taxes, which are handled in a completely different matter, are discussed below.

Tiered tax rates are now handled specifically rather than implicitly (see Chapter 5). The new system eliminates some of the previous data points and specifically indicates the base tier (i.e., tier=0) and all that flows from it (tier=1,2,3, etc.) when applied in the split or spread manner.

A group of rate rows for a given taxing authority-taxtype-taxcat now encompasses as many records as will fully and unambiguously describe a rate with all its tiers. Since all rates have at least one record, all initiating records for a given tax are always indicated as tier 0. This eliminates the need for the older Detail data points, and a number of taxcats, which generically implied the concept of aggregation.

Taxability data is now tax authority and geocode specific. Previously, a three-tiered representation of taxability was organized by City, County and State; whereas the present version, while maintaining the three part organization, reorganizes it around City (with specific geocode), Taxauthority (without specific geocode other than its state code) and State (with only the state specific geocode). Sales and Use taxes are listed separately. TaxOnTax now occupies its own table and has been removed from Matrix. Additionally, there are new markers for the transaction type, the percent taxable type, and the percent taxable.

³Geographic Names Information System

⁴Definition of rule, i.e., Group record, Item record, taxability Rule record, TaxOnTax record – taxability is now specifically and inextricably bound up in its relationship to rates as well as the division of all taxability into State, TaxJurisidiction and Local governance.

Comparison of the tables in both current and older versions

The following tables list all available data points in both versions of the four common tables. Where a field of the older version of the database is no longer available, or the meaning and/or content has changed, the column in the charts below labeled Current will have the value n/a. Where the field in the newer version is a new item, or when it has the same name as an older counterpart but the meaning has changed, the Previous column is marked n/a. New items are explicitly marked new which indicates data points not previously available in the older version.

Comparison of previous version of Detail vs. current version of Detail

#	Column	Previous	InUse	Current
1	GEOCODE	Geographic Code	Y	Geographic identifier for unique combination of tax jurisdictions
2	INOUTCITY	In or out of city code	N	n/a [see taxjurisdiction/geocode]
3	INOUTLOCAL	In or out of local boundary code	N	n/a [see taxjurisdiction/geocode]
4	TAXRATE	Primary Tax Rate	Y	Tax Rate applied to specific tier
5	LOCATION	Location code for filling	Y	Code on tax forms for reporting purposes
6	TAXBASE	Tax revenue base (or threshold)	N	n/a [see mintaxbase, maxtaxbase]
7	MAXTAX	Maximum tax amount (cap)	Y	Maximum tax amount (cap)
8	EXCESSRATE	Second revenue tier tax rate	N	n/a [see tier, min and max taxbase, min and max unitbase]
9	EFFDATE	Effective Date	Y	now EFFECTIVE - Effective Date
10	TAXAUTH	Authority Level Code	Y	Now TaxAuthId - points to specific taxing authority ID
11	TAXTYPE	TaxType code	Y	TaxType code
12	TAXCAT	TaxCat code	Y	TaxCat code
13	DESC	Tax Description	Y	now DESCRIPTION - Tax Description
14	USETAXRATE	Primary use tax rate	N	n/a [see use taxtypes and detail]
15	UEXCESSRATE	Second tier use tax rate	N	n/a [see use taxtype and detail]
16	FEE	Primary flat fee per unit	Y	Fee applied per unit for a specific tier
17	UNITTYPE	Unit type - what is counted	Y	Unit type code - what and how it is counted
18	MAXUNIT	Maximum number of units taxed	N	n/a see [minunitbase/maxunitbase]
19	MAXTYPE	Level at which revenue	Y	How aggregates are applied
20	PASSFLAG	Pass Flag Code	Y	Can the tax base be passed on to the consumer
21	PASSTYPE	Pass Type Code	Y	How is the tax represented on an invoice
22	BASETYPE	Base Type Code	Y	Is the tax imposed on the seller or the buyer
23	UNITBASE	Unit base (or threshold)	N	n/a [see minunitbase/maxunitbase]
24	EXCESSFEE	Second unit tier flat fee per unit	N	n/a [see minunitbase/maxunitbase]
25	THRESHTYPE	Threshold Type	Y	Determining "split" or "spread" aggregation
26	UNITANDORTAX	Unitbase and/or taxbase	Y	n/a [presently reserved]
27	DATEFLAG	n/a	Y	n/a [presently reserved]
28	REPORTTO	n/a	Y	NEW: the reporting authority
29	ROUNDING	n/a	Y	NEW: rounding level code
30	FORMULA	n/a	Y	NEW: calculation formula code
31	TIER	n/a	Y	NEW: specific indicator of rate or fee sequence in aggregated rate taxes
32	MINTAXBASE	n/a	Y	NEW: Minimum dollar amount for specific tier
33	MAXTAXBASE	n/a	Y	NEW: Maximum dollar amount for specific tier
34	MINUNITBASE	n/a	Y	NEW: Minimum unit count for a specific tier
35	MAXUNITBASE	n/a	Y	NEW: Maximum unit count for specific tier

Comparison of the previous version of Matrix vs. the current version of Matrix

#	Column	Previous	InUse	Current
1	CITY	Place Name	N	n/a [see Geocode]
2	COUNTY	County Name	N	n/a [see Geocode]
3	STATE	Postal State abbrevation	N	n/a [see Geocode]
4	LOCAL	Local tax district flag	N	n/a [see Geocode]
5	GEOCODE	Geographic code	Y	Geografic identifier for unique combination of tax jurisdictions
6	GROUP	Product Group Code	Y	Product Group Code
7	GROUPDESC	Product Group description	N	n/a [see ProductGroup code table]
8	ITEM	Product Item code	Y	Product Item code
9	ITEMDESC	Product Item description	N	n/a [see ProductItem code table]
10	PROVIDER	Provider Type Code	Y	Provider Type Code
11	CUSTOMER	Customer Type Code	Y	Customer Type Code
12	TaxTypeT	TaxOnTax Base Tax Type	N	n/a [see TaxOnTax table]
13	TaxCatT	TaxOnTax Base Tax Category	N	n/a [see TaxOnTax table]
14	Taxable	Taxable Flag [char=Y or N]	Y	Taxable Flag [numeric 1=Yes, 0=No]
15	TaxType	TaxType code	Y	TaxType Code
16	TaxCat	TaxCat code	Y	TaxCat code
17	EFFDATE	Effective Date	Y	Now EFFECTIVE - effective date
18	RECTYPE	Record Category	N	n/a
19	TAXAUTHID	n/a	Y	NEW: Specific Taxing Authority for Tax
20	TRANSACTION	n/a	Y	NEW: Specific Taxing Authority for Tax
21	PERTAXABLETYPE	n/a	Y	NEW: percent taxable type
22	PERCENTTAXABLE	n/a	Y	NEW: actual percent of rate or fee applicable

Comparison of the previous version of Plus4 vs. the current version of Plus4

Note: Both versions of the data points in these two tables remain unchanged, although the order of the columns has been changed.

#	Column	Previous	InUse	Present
1	CITY	Place Name	Y	City or Place Name
2	COUNTY	County Name	Y	County Name
3	STATE	State abbrevation	Y	State or Province Code
4	GEOCODE	Geographic code	Y	Geographic identifier for unique combination of tax jurisdictions
5	COUNTRY	n/a	Y	New: Country abbrevation
6	TAXDISTRICT	n/a	Y	New: Name of Special Tax District (as appropriate - see Geocodes with embedded 'A')
7	GNIS	n/a	Y	New: Geographic Name Information System Code

Assumptions and Requirements

General disclaimers

All illustrations in this User’s Guide are intended solely as examples.
The illustrations in this User’s Guide are only a small fraction of the total number of potential permutations. They are not intended to be exhaustive.
The user is solely responsible for any aspect of any outcome of any calculation or quotation from the database.
CCH does not provide, nor does it engage in any form of tax advice.
CCH assumes no liability whatsoever for the outcome of calculations using the data.
All uses of the data are subject to the terms of the licensing agreement.
Use CCH database detailed guides for directions on how to map your business’s invoicing information to this database. Such issues may involve tax sourcing and dynamic transaction mapping of groups and items. Customers are expected to consult these guides as appropriate for the application of the data, as well as to understand the nature of the infrastructure, aside from the basic infrastructure needed to process the database’s data.
Further, as stated previously, customers are solely responsible for the appropriate and accurate use of the data provided by the CCH database. All uses of the data should be tested prior to implementation for accuracy and acceptability. Clearly, the need for customers to seek the advice of tax counsel cannot be underestimated.
In line with this, it is understood that dynamic mapping issues (for example, the 2 out of 3 rule in Interstate Telecom taxation) and other apportioning issues are not addressed in this User’s Guide.

Flat database concept

The single most important assumption about the data is that it is NOT historical. Each monthly feed of the database presents a “flat view” of the current state of the data as of the date of its production. Any data points which expire between one feed and the next are simply not present in the newer release (feed) of the data. Also, there is no forward history. That means any data that will become active at some point beyond the date of production of the current release are not present in the current release. It is, therefore, incumbent upon the user of the CCH database to track and maintain changes in the data from month to month.

In lieu of full data feeds, the CCH database is also supplied in a “delta” format in which only changes to the data are present. There are two categories of data for each table in the database – additions and deletions – those rows in the various tables that are added or deleted as a function of expiration, modification or instantiation. The rows no longer present due to rate or taxability expiration or replacement by virtue of update are marked as “D” [deleted]. The rows that are new to the data, or are updates to earlier versions are marked as “I” [insert].

Internal mapping

The proper use of the data also assumes that users will create and maintain all necessary internal mappings to the various code types that exist throughout the database. All points of information maintained by the CCH database that are applicable and/or relevant to the user will need to be mapped to the user’s own proprietary code identifiers.

Nexus and Tax Sourcing

The user of the CCH database will have to establish and maintain Nexus and Tax Sourcing outside of any process that uses the data. These are primary points of reference to any potential tax liabilities and responsibilities of the data user.

ASCII format of the Data

The CCH database is supplied as a series of ASCII files. ASCII (or American Standard Code for Information Interchange) is the most universal encoding scheme for binary representation of symbolic data on computers. Because its fundamental format is not specific to any particular Relational Data Base Management System or data storage paradigm, it is accessible to all. As the table layouts in Chapter 9 indicate, each record (or row) in each supplied table occupies one carriage-return/line-feed (hex 0d0a) terminated line in the given file. Using the table layouts, you devise the appropriate algorithm to parse each line of each file. You can then load the individually parsed items into the tables and columns you designate for the storage and use of the CCH supplied data.

Those users who receive only the change files (the “Deltas”) will have the same set of tables as those who receive the full load. (“Rates Only” customers are the exception. They will not have the Matrix table). The naming convention for change files and full files are identical. The only structural difference is an additional column, “action,” added at the end of each row. A row that is new or which represents an edit are marked ‘I’ – a row that is to be deleted is marked ‘D’. Any table which has no new data is delivered as an empty file (i.e., 0 byte length).

TaxingTaxType	TaxingTaxCat	TaxType	TaxCat
01	01	01	01
01	01	02	01
01	01	03	01
02	01	01	01
02	01	02	01
02	01	03	01
03	01	01	01
03	01	02	01
03	01	03	01

TaxingTaxType	TaxingTaxCat	TaxType	TaxCat
01	01	01	01
01	01	02	01
01	01	03	01
02	01	01	01
02	01	02	01
02	01	03	01
03	01	01	01
03	01	02	01
03	01	03	01

Sales Tax Rates & Taxability Database

Overview

Taxing Authorities

Taxing Authorities

Geography

General

The Plus4 Concept

NPA­NXX: Landline Telephone Connection to Geography

Note About Canadian Geocodes

Taxability

General

Illustrations of Taxability

Rates

General

Geography

Core Values and their Associated Definers

Rates as a function of Units, Fees aggregated on MinTaxBase/MaxTaxBase

Moving from Tier to Tier

Calculation Formulas

Rates only package

Tax-On-Tax

Consumer­based and Provider­based Taxes

Putting It All Together

Overview

Basic Assumptions

ASCII Files Table Layouts

General

Text Files

1‐ PLUS4 Record Layout

2‐ Matrix Record Layout

3‐ Detail Record Layout

4‐ Geocode Record Layout

5‐ TaxOnTax Record Layout

6‐ TaxAuthority Record Layout

7‐ NPA‐NXX Record Layout

8‐ TaxAuthType Record Layout

9‐ TaxType Record Layout

10‐ TaxCat Record Layout

11‐ PassFlag Record Layout

12‐ PassType Record Layout

13‐ BaseType Record Layout

14‐ DateFlag Record Layout

15‐ RoundingLevel Record Layout

16‐ Unit Type Record Layout

17‐ MaxType Record Layout

18‐ ThreshType Record Layout

19‐ CustomerType Record Layout

20‐ ProviderType Record Layout

21‐ TransactionType Record Layout

22‐ PercentTaxable Type Record Layout

23‐ UnitTypeXRef Record Layout

24‐ ProductGroup Record Layout

25‐ ProductItem Record Layout

26‐ Country Record Layout

27‐ State Record Layout

28‐ CalculationFormula

Establishing SQL Tables

Current Database vs. Legacy

General Observations

Comparison of the tables in both current and older versions

Assumptions and Requirements

General disclaimers

Flat database concept

Internal mapping

Nexus and Tax Sourcing

ASCII format of the Data

NPANXX: Landline Telephone Connection to Geography

Consumerbased and Providerbased Taxes

TaxingTaxType	TaxingTaxCat	TaxType	TaxCat
01	01	01	01
01	01	02	01
01	01	03	01
02	01	01	01
02	01	02	01
02	01	03	01
03	01	01	01
03	01	02	01
03	01	03	01