Skip to main content
Avalara Help Center

AvaTax for Communications SaaS Standard file format guidelines

AvaTax for Communications SaaS Standard allows you to transfer files to and from Avalara for processing by the AFC server. This guide details the file format requirements. See the Avalara AvaTax for Communications SaaS Standard User Manual for information on how to access the FTP and import these files.

Input File

The columns in the Excel and CSV file formats are the same in each format. Column headers are the first row in each file that describe the data in that particular field or column.

Note: Do not surround variables, column names, etc, in the input file with quotation marks (" "). Quotes can cause the file to not be read properly and could result in a failure. 

Field

Required

Value List

Reference

Request Type

Required

CalcTaxes

CalcAdj

CalcIncl

CalcInclAdj

Specifying Request Type

BillTo PCode

Origination PCode

Termination PCode

At least one taxing jurisdiction required *

 

Specifying Jurisdiction PCode

BillTo Country

BillTo State

BillTo County

BillTo Locality

BillTo ZipCode

BillTo ZipP4

Origination Country 

Origination State

Origination County

Origination Locality

Origination ZipCode

Origination ZipP4

Termination Country

Termination State

Termination County

Termination Locality

Termination ZipCode

Termination ZipP4

At least one taxing jurisdiction required *

 

Specifying Jurisdiction Address

BillTo Fips Code

Origination Fips Code

Termination Fips Code

At least one taxing jurisdiction required *

 

Specifying Jurisdiction Fips Code

BillTo NpaNxx

Origination NpaNxx

Termination NpaNxx

At least one taxing jurisdiction required *

 

Specifying Jurisdiction NpaNxx

Transaction Type

Required

 

Specifying Transaction / Service Pair

Service Type

Required

 

Specifying Transaction / Service Pair

Date

Required

YYYYMMDD

Specifying Transaction Date

Charge

Required

 

Specifying Transaction Charge

Customer Type

Strongly Recommended
(Default, if applicable) **

Business (default)

Residential

Industrial

Senior Citizen

Specifying Customer Type

Lines

Optional
(Default, if applicable) **

Integer (default is 0)

Specifying Lines

Sale

Optional
(Default, if applicable) **

Sale (default)

Resale

Specifying Sale Type

Incorporated

Optional
(Default, if applicable) **

True (default)

False

Specifying Incorporated

Federal Exempt

Federal PCode

State Exempt

State PCode

County Exempt

County PCode

Local Exempt

Local PCode

Optional
(Default, if applicable) **

 

Specifying Level Exemptions

Regulated

Optional
(Default, if applicable) **

True

False (default)

Specifying Regulated Flag

Minutes

Optional
(Default, if applicable) **

Number (default is 0)

Specifying Minutes

Debit

Optional
(Default, if applicable) **

True

False (default)

Specifying Debit Flag

Service Class

Optional
(Default, if applicable) **

Local (default)

Long Distance

Specifying Service Class

Lifeline

Optional
(Default, if applicable) **

True

False (default)

Specifying Lifeline Flag

Facilities

Optional
(Default, if applicable) **

True (default)

False

Specifying Facilities Flag

Franchise

Optional
(Default, if applicable) **

True (default)

False

Specifying Franchise Flag

Business Class

Optional
(Default, if applicable) **

CLEC (default)

ILEC

Specifying Business Class

Company Identifier

Optional
(Default, if applicable) **

 

Specifying Company Identifier

Customer Number

Optional
(Default, if applicable) **

 

Specifying Customer Number

Invoice Number

Optional
(Default, if applicable) **

 

Specifying Invoice Number

Discount Type

Optional
(Default, if applicable) **

None (Default)

Retail Product

Manufacturer Product

Account Level

Subsidized

Goodwill

Specifying Discount Type

Exemption Type

Optional
(Default, if applicable) **
 

Specifying Exemption Type

Adjustment Method

Optional
(Default, if applicable) **

Default (Default)

Least

Most

Specifying Adjustment Method

IsPrivateLine

PrivateLineSplit

Optional
(Default, if applicable) **

Percentage allocation specified as a decimal in the range 0 to 1.

Specifying Private Line or Point-to-Point

IsProRated

ProRatedPct

Optional
(Default, if applicable) **

Percentage allocation specified as a decimal in the range 0 to 1.

Specifying Proration

Optional

Optional4

Optional5

Optional6

Optional7

Optional8

Optional9

Optional10

OptionalAlpha1

Optional
(Default, if applicable) **

 

Specifying Optional Reporting Fields

Key:{header}

Optional
(No Default)

 

Specifying Extended Optional Reporting Fields

VoIP Old (Original Fed TAM)

Optional
(No Default)

TAM value specified as decimal between 0 and 1.

Specifying Safe Harbor Overrides for Traffic Studies

VoIP New (Override Fed TAM)

Cellular Old (Original Fed TAM)

Optional
(No Default)

TAM value specified as decimal between 0 and 1.

Specifying Safe Harbor Overrides for Traffic Studies

Cellular New (Override Fed TAM)

Paging Old (Original Fed TAM)

Optional
(No Default)

TAM value specified as decimal between 0 and 1.

Specifying Safe Harbor Overrides for Traffic Studies

Paging New (Override Fed TAM)

* At least one taxing jurisdiction must be specified by providing a PCode, Address, FIPs code, or NpaNxx. This can be a BillTo, Origination, or Termination taxing jurisdiction. If you send information for more than one taxing jurisdiction (such as sending BillTo, Origination, and Termination PCodes), AvaTax for Communications determines the appropriate taxing jurisdiction. See the Specifying a Tax Jurisdiction section for more information.

** Please see Transaction Default Values for additional information on default parameters.

Notes:

  • Spacing is ignored in a field name. For example, [Request Type] and [RequestType] would be considered identical.
  • Matching will ignore additional fields or characters beyond the listed value. Example: [Lines] and [Lines Count] would be considered identical. [Line Count] however would not be considered a valid column identifier, as it is missing the character [s].
  • Column headers are required and must be included in Excel and CSV format.

Exemption File

The exemption file is an optional input file that identifies tax type exemptions, category exemptions, or both. The name of the file is user defined, but the extension must be .exm.

Note: While both a tax type and category exemption can be applied to the same transaction within the input file, the separate exemptions must be specified on individual rows within the exemption file.

Please refer to Handling of Optional Files for additional information.

Exemption File Updates

New Fields – Effective September 2018

Scope – union, user supplied

  • One or more of the set { Federal, State, County, Local }

Include Nonbillable – flag, user supplied

  • True – nonbillable taxes can be exempted
  • False – nonbillable taxes should not be included in exemption

Domain – discrete, user supplied

  • Defines the level the taxing jurisdiction must match exemption jurisdiction
  • One of the set { Federal, State, County, Local }
  • Does not need to correlate to the tax level of the tax being exempted

Ignore Level Exemptible - flag, user supplied

  • True (Default) - Tax type wildcard exemptions will exempt taxes tagged not level exemptible
  • False - Tax type wildcard exemptions will exclude taxes tagged not level exemptible from consideration

The exemption handling has been redesigned to allow more flexibility in how they are used. Wherever possible backwards compatibility was maintained with the following exception:

Category exemptions and wildcard tax type exemptions by default will no longer exempt non- billable taxes. The new flag ExemptNB must be set to true if a client wishes to include non- billable taxes. This change was made to protect clients from applying exemptions beyond the scope of the exemption certificate(s) for which the customer qualifies. If you have additional questions on why Avalara changed the default, please submit a support case.

Overview of Exemptions

Setting up exemptions allows for exempting clients from taxes that would normally apply for a given product for either a specific jurisdiction or a jurisdictional region.

Checklist for creating an exemption

  • What - Is the exemption for a specific tax or for a category of taxes?
    • For more information, see AFC Tax Types and Category Definitions.
    • An array of exemptions can be created if multiple tax types and/or categories apply.
    • Values of 0 or All defines wildcard for both tax type and categories.
  • Where - Does the exemption apply to a specific jurisdiction or to a jurisdiction region?
    • This defines the Scope. Regions are Federal (Country), State/Province, County, City.
    • You can define multiple regions for Scope such as (State + County + Local)
  • When - At what level must the exemption match the taxing jurisdiction?
    • This defines the Domain. Domain can be one of (Federal, State, County, Local)
    • For example, if you wish to exempt Federal taxes only if the taxing jurisdiction is in Puerto Rico, then the Domain would be State and the Exemption would be defined for Puerto Rico. The federal taxes would then only be exempt when the taxing jurisdiction was in Puerto Rico.
  • Who – Define the cross-reference for who the exemption will be applied to
    • Exemptions can be tied to transactions by one of the following:
      • Customer Number
      • Company Identifier
      • Optional Alpha1
      • Exemption Type

Exemption Fields

Field

Value List

Reference

[Cross Reference Key]

 

Specifying Exemption Cross Reference Key

[Jurisdiction]

 

Specifying Exemption Jurisdiction

TaxType

Numeric Tax Type ID Or “All”

Specifying Exemption Tax Type

TaxCategory

Numeric Category ID Or “All”

Specifying Exemption Category

Scope

Numeric Scope Value

-or-

Text Scope Value(s)

Specifying Exemption Scope

Domain

Numeric Tax Level ID

-or-

Text Domain Value

Specifying Exemption Domain

ExemptNB

Flag – Boolean

Specifying Exemption Exempt Non-billable Flag

ExemptLvl

Flag – Boolean

Specifying Exemption Exempt Non-level-exemptible Flag

 

Tax Type Exemption Sample File

The samples provided show tax type exemption files processed and properly cross-referenced by the following key columns:

  • Using Company Identifier and PCode

    The data within lines 2 – 3 of the previous input file are associated with the sample exemption file provided below.

    Company Identifier

    PCode

    Tax Type

    Domain

    Scope

    Company A

    540000

    18

    0

    Federal

    Company A

    540000

    64

    1

    Federal+State

  • Using Customer Number and LinkTo

    The data within lines 2 – 3 of the previous input file are associated with the sample exemption file provided below.

    Customer Number

    LinkTo

    Tax Type

    Domain

    Customer 1

    BillTo

    18

    0

    Customer 1

    Origination

    64

    1

    Customer 1

    Termination

    64

    3

  • Using Customer Number and Address

    The data within lines 2 – 5 of the previous input file are associated with the sample exemption file provided below.

    Customer Number

    Country

    State

    County

    Locality

    Zip Code

    Tax Type

    Domain

    Customer 1

    USA

     

     

     

     

    18

    0

    Customer 1

     

    FL

     

     

     

    64

    1

    Customer 1

    USA

    FL

    Brevard

    Cocoa Beach

    32931

    64

    3

    Customer 2

    USA

     

     

     

     

    169

    Federal

    Customer 2

     

    CA

     

     

     

    18

    Federal

    Customer 2

    USA

    CA

    San Francisco

    San Francisco

    94102

    16

    Local

Application of Jurisdiction for Exemptions

The exemption jurisdiction should be defined at least to the Domain level specified. If it is not, the Domain will automatically shift upward to the level the exemption jurisdiction is defined at. So for example, if the Domain level is specified at city level, but the exemption jurisdiction provided is for the state of Colorado, the Domain level will shift to State and produce a match if the transaction taxing jurisdiction is in Colorado.

Exemption Default Values

Tax Type defaults (where applicable)

Field

Default

Special Condition

Include Nonbillable

True

False

Valid tax type specified

Wildcard for tax type used

Scope

Tax Level or Domain

 

Domain

Tax Level

One or the other is required for Tax Type exemptions.

Domain overrides tax level if both are provided

Force Lvl Exempt

True

Wildcard for tax type used

 

Category defaults (where applicable)

Field

Default

Special Condition

Include Nonbillable

False

 

Scope

State+County+City

All

If State or lower level jurisdiction

If Country level jurisdiction

Domain

State

Federal

If State or lower level jurisdiction

If Country level jurisdiction

Exemption Use Case Scenarios

Scenario 001 - apply exemption to all cities within a state - billable only

Client wants all tax categories of type 1 (Sales) exempt at local level for the state of Colorado (billable only).

Scope local will exempt only local taxes. Domain state will match jurisdiction at the state level (CO). Non- billable taxes will be excluded from consideration.

TaxCategory

Jurisdiction

Scope

ExemptNB

Domain

ExLvl

1

USA, CO, Boulder, Boulder

1024 (Local)

false

1 (State)

n/a

 

Scenario 002 - apply exemption to all states, counties and cities within a country - include non- billable

Client wants all tax categories of type 5 (Connectivity Charges) exempt at state, county and local level for all states in the USA, billable or non-billable.

Scope will exempt State, County and Local taxes. Domain Federal will match jurisdiction at the Federal level (USA). Non-billable taxes will be subject to exemption.

TaxCategory

Jurisdiction

Scope

ExemptNB

Domain

ExLvl

5

USA, TX

1792 (State+County+Local)

true

0 (Federal)

n/a

 

Scenario 003 - apply multiple exemptions to all locations within a state

Client wants billable Sales tax (1) and Use tax (49) exempt at state, county and city level for Washington.

Scope will exempt State, County and Local taxes. Domain State will match jurisdiction at the State level (WA).

TaxType

Jurisdiction

Scope

ExemptNB

Domain

ExemptLvl

1

USA, WA

1792 (State+County+Local)

n/a

1 (State)

n/a

49

USA, WA

1792 (State+County+Local)

n/a

1 (State)

n/a

 

Scenario 004 - exempt Federal tax only if taxing jurisdiction is within a state

Client wants FUSF tax (18) exempt at Federal level but only if transaction is in Puerto Rico. Scope will exempt Federal taxes. Domain State will match jurisdiction at the State level (PR).

TaxType

Jurisdiction

Scope

ExemptNB

Domain

ExemptLvl

18

USA, PR

128 (Federal)

n/a

1 (State)

n/a

 

Scenario 005 - exempt all taxes at Federal level except non-level exemptible

Client wants all Federal taxes exempt at Federal level except those marked non-level exemptible. Scope will exempt Federal taxes. Domain will match at Federal level. Non-billable will be included. Taxes that are not level-exemptible will be excluded from consideration.

TaxType

Jurisdiction

Scope

ExemptNB

Domain

ExemptLvl

0

USA, PR

128 (Federal)

true

0 (Federal)

false

Transaction Default Values

Clients that use the Excel or CSV format for the input file have the option of including a default file that will be used to specify what default values should be used. The file must be named Default.{ext} with the extension being either a valid Excel extension or csv for comma delimited. The default value for each column specified will be used when the input file does not include the column.

If a customer-provided Default file is not uploaded, the following default values will be used.

 

Field

Default

Customer Type

Business

Lines

0

Sale

TRUE

Incorporated

TRUE

Federal Exempt

FALSE

State Exempt

FALSE

County Exempt

FALSE

Local Exempt

FALSE

Regulated

FALSE

Minutes

0

Debit

FALSE

Service Class

Local

Lifeline

FALSE

Facilities

TRUE

Franchise

TRUE

Business Class

CLEC

Transaction File Sample

The order of columns is not important, but column headers are required.

Request Type

Origination PCode

Customer Type

Date

Charge

Lines

Transaction Type

Service Type

Sale

Incorpor ated

Customer Number

CalcTaxes

540000

Business

20150715

100

0

19

6

True

True

Customer 1

CalcTaxes

540000

Business

20150715

100

0

19

30

True

True

Customer 1

CalcTaxes

540000

Business

20150715

100

0

19

37

True

True

Customer 1

CalcTaxes

540000

Business

20150715

100

0

19

577

True

True

Customer 1

CalcTaxes

446400

Business

20150715

100

0

19

6

True

True

Customer 2

CalcTaxes

446400

Business

20150715

100

0

19

30

True

True

Customer 2

CalcTaxes

446400

Business

20150715

100

0

19

37

True

True

Customer 2

CalcTaxes

446400

Business

20150715

100

0

19

577

True

True

Customer 2

Default File Sample

The order of columns is not important, but column headers are required. There should only be a single line of data indicated what default values to use. You need only define default values for columns that are not otherwise defined or that are different from the default values used by the service.

Regulated

Debit

Service Class

Lifeline

Facilities Based

Franchise

Business Class

True

False

Local

False

False

False

CLEC

Specifying a Tax Jurisdiction

For AFC to calculate taxes for transactions correctly, it must first determine the taxing jurisdiction for the transaction in question. The tax laws of various jurisdictions complicate this. AFC currently supports multiple unique rules for determination of the correct tax jurisdiction. Jurisdiction determination is usually based upon three inputs to AFC:

  1. The Service Address or the Bill To Number (BTN).
  2. The termination location of the transaction (for telephone calls only) to be taxed. The number called, also known as the "To Number" or the "Termination number" usually specifies this.
  3. The origination location of the transaction to be taxed. For telephone calls this is usually specified by the number called from, also known as the “From Number” or Origination number.

The jurisdiction, for many telecommunications taxes applied by AFC, is determined by the Goldberg or "2 out of 3" rule. With this particular rule, the three jurisdiction inputs pointed out above are compared. If 2 of the 3 jurisdictions supplied match, that is the jurisdictions for which taxes are generated. It is slightly more complicated since there are four authority levels for which jurisdictions determination must be made (i.e. federal, state, county, and local), however; this brief discussion illustrates the concept.  Remember, the Goldberg Rule although the most common, is only one of 10 rules specified for jurisdiction determination by taxing jurisdictions in the United States.

Jurisdiction information can be supplied to the system in different ways. It can be supplied using a permanent jurisdiction code (PCode), using a FIPS Code, using an NPANXX, or using zip code and address information. AFC allows the user to identify the jurisdictions by any of these methods, thereby providing maximum flexibility. AFC allows the specification of jurisdiction information using any combination of these methods during the same session.

Jurisdiction Hierarchy

Clients have the option of specifying one, two or all three jurisdictions for a single transaction. If only one jurisdiction is specified, it will be used for all three. If two jurisdictions are specified, the following hierarchy will be used for determining which jurisdiction will be used to populate the missing jurisdiction.

High Level Jurisdiction Order of Preference (by Transaction):

  1. BillToPCode
  2. Origination
  3. Termination

The following table of populating missing jurisdiction from jurisdictions is provided.

Missing Jurisdiction

Hierarchy Application

Resulting Jurisdictions Used

BillTo

Uses Origination

Orig, Orig, Term

Origination

Uses BillTo

BillTo, BillTo, Term

Termination

Uses BillTo

BillTo, Orig, BillTo

Jurisdiction Data Types

PCode

PCodes are permanent jurisdiction codes which Avalara provides and allow AFC software users to populate their databases with jurisdiction information. With PCodes, AFC clients can populate their customer records with jurisdiction information and never worry about changes of jurisdiction codes. If a jurisdiction code changes, AFC re-maps the PCodes so clients are not affected. This allows AFC clients to populate client records with a PCode.

For more information refer to Specifying Jurisdiction PCode.

Address

The accuracy of the ZIP code method depends upon the amount of data provided for the address as well as the user’s ability to choose the correct taxing jurisdiction zip code and address. AFC databases contain numerous duplicate zip codes that cross not only taxing jurisdiction boundaries, but boundaries of localities as well. Providing a complete address along with the zip code insures the best match possible. When address information is missing, AFC returns taxes based upon the first match of the provided input information.

It is appropriate to use the zip code interface or PCode methods for transactions where the jurisdiction can be positively identified by the calling application. Examples of this type of transaction are product sales and Internet usage.

For more information refer to Specifying Jurisdiction Address.

FIPS Code

FIPS Codes are issued by the National Institute of Standards and Technology (NIST). AFC provides internal translation tables from FIPS Codes to PCodes, so that using FIPS Codes is almost as fast and accurate as using PCodes. Some special taxing districts are not identified in separate FIPS codes, so there is some loss of accuracy, but the majority of transactions will produce the same tax results with FIP Codes as with PCodes.

For more information refer to Specifying Jurisdiction Fips Code.

NPANXX

The NpaNxx is the first 6 digits of a phone number.

For more information refer to Specifying Jurisdiction NpaNxx.

Jurisdiction Data Type Hierarchy

If a client supplies more than one data type for a single jurisdiction, the following hierarchy will be used to determine which jurisdiction data type is used for that jurisdiction.

Jurisdiction Data Type Order of Preference (by Jurisdiction):

  1. PCode
  2. Address
  3. FIPs Code
  4. NpaNxx

For example, if a transaction has both an OriginationPCode and an OriginationNpaNxx specified, the OriginationPCode value will be used for the Origination Jurisdiction.

Getting the Right Tax Jurisdiction for Local Taxation

It is important to get the end users location correct for local taxation. People tend to know what they are charged for local taxes. These taxes also have a tendency to change from one neighborhood to the next.

Avalara provides a comma delimited ASCII file to aid with entry of customers in your system and to help insure the correct taxing jurisdiction is setup. The file is "all_adr.txt". This comma delimited ASCII files is provided to allow Avalara clients to populate database tables in their system with this information. The all address (all_adr.txt) file is a cross-reference of locations to PCodes. The format of the files is illustrated below.

The "all_adr.txt" file format and example follows:

Pcode,P/A,Country,State,County,City,"Zip code range start","Zip code range end"

3346800,0,USA,PA,LANCASTER,BART,17503,17503

3346900,0,USA,PA,LANCASTER,BAUSMAN,17504,17504

3347000,0,USA,PA,LANCASTER,BIRD IN HAND,17505,17505

3347100,0,USA,PA,LANCASTER,BOWMANSVILLE,17507,17507

3347200,0,USA,PA,LANCASTER,CHRISTIANA,17509,17509

3347300,0,USA,PA,LANCASTER,CHURCHTOWN,17555,17555

3347400,0,USA,PA,LANCASTER,CONESTOGA,17516,17516

3347500,0,USA,PA,LANCASTER,DRUMORE,17518,17518

3347600,0,USA,PA,LANCASTER,EAST EARL,17519,17519

3347700,0,USA,PA,LANCASTER,ELM,17521,17521

3347800,0,USA,PA,LANCASTER,GAP,17527,17527

Transaction Specifications

Specifying Request Type

The request type tells the engine what type of calculation should be performed on the transaction data. The request type is independent of the jurisdiction type, and supports all jurisdiction specifications.

Please refer to the table below to view the request types which are supported.

Request Type

Value

Description

CalcTaxes

Perform a standard tax calculation.

CalcAdj

Calculate adjustment activities such as discounts, refunds, changing a customer’s bill, or writing off uncollectible accounts with a standard tax calculation. All transaction data, including charge and transaction date, should be identical to the standard calculation.

Supports Specifying Adjustment Method and Specifying Discount Type.

CalcIncl

Perform a tax inclusive calculation. The desired total (charge + taxes) should be placed in the “Charge” column. The tax calculation method will determine the “base charge” that is needed such that (base charge + taxes calculated = desired total).

Notes:

  • The desired total must be a positive value sufficiently large to cover any fixed taxes that may apply.
  • The calculated base charge will be stored in the optional field as an unsigned long where: optional = (unsigned long)(base charge * 100)
  • Clients using tax inclusive calculations should not use the optional field or the data in the optional field will be overwritten.
  • The standard TSR report uses the calculated base charge. To add the original charge passed in, a request needs to be made to receive the RTR (Reconciliation Transaction/Service Report).

CalcInclAdj

Calculates an adjustment for a tax inclusive calculation. All transaction data, including charge and transaction date, should be identical to the tax inclusive calculation. Refer to CalcIncl Request Type for more details on tax inclusive calculations.

Supports Specifying Adjustment Method and Specifying Discount Type.

Specifying Jurisdiction PCode

A PCode is a persisted numeric identifier for a taxing jurisdiction. It can represent anything from a country to a special taxing jurisdiction within a local taxing district, such as the Salado Public Library District in Bell, Texas.

The value can be obtained from either using our Avalara Geo for Communications (AFC Geo) product or from parsing the PCode from the all_adr.txt file. Getting the Right Tax Jurisdiction for Local Taxation has more information on the format and contents of this file.

Specifying Jurisdiction Address

When using an address to specify the taxing jurisdiction, the more complete the information provided the more accurate the lookup will be. For most foreign nations, other than Canada and Brazil, the country is sufficient for taxation purposes. For USA and Canada, at a minimum the Country, State and Zip Code must be provided.

If the address is for a USA Territory, it can be handled the same as a USA state by using the provided two byte state abbreviation.

Supported USA Territory abbreviations are provided in the following table.

Code

Territory

Special Handling Notes

AS

American Samoa

 

FM

Fed St of Micronesia

 

GU

Guam

Leave County blank

MH

Marshall Islands

 

MP

Northern Mariana Islands

 

PR

Puerto Rico

Leave County blank

PW

Palau

 

VI

US Virgin Islands

 

For Canada, you may populate the 6 digit Postal Code in the Zip Code field (with or without a dash or space). Zip plus 4 (ZipP4) is for USA only.

Supported Canadian Province and Territory abbreviations are provided in the following table.

Code

Territory

AB

Alberta

BC

British Columbia

MB

Manitoba

NB

New Brunswick

NL or NF

Newfoundland

AB

Alberta

NS

Nova Scotia

NT

Northwest Territories

NU

Nunavut

ON

Ontario

PE

Prince Edward Island

QC or PQ

Quebec

SK

Saskatchewan

YT

Yukon Territory

A breakdown of the address fields are below. The Column Name should be prefixed with either BillTo, Origination or Termination (as applicable).

ZipAddress

Column Name

Description

Country

3 character ISO abbreviation

State

State Abbreviation

County

County name

Locality

City name

ZipCode

ZIP Code (5 digit USA, 6 digit Canada)

ZipP4

ZIP Code extension (4 digit)

The all_adr.txt file can be used to verify zip address data. Getting the Right Tax Jurisdiction for Local Taxation has more information on the format and contents of this file.

Optionally, the Viewer in the AFC Manager can also be used for manual verification.

Note: The country will default to USA if no country is specified.

Specifying Jurisdiction FIPS Code

“The Federal Information Processing Standards codes (FIPS codes) are a standardized set of numeric or alphabetic codes issued by the National Institute of Standards and Technology (NIST) to ensure uniform identification of geographic entities through all federal government agencies. The entities covered include: states and statistically equivalent entities, counties and statistically equivalent entities, named populated and related location entities (such as, places and county subdivisions), and American Indian and Alaska Native areas”.

A FIPS Code may be used to identify a jurisdiction, but must follow the 10-digit numeric code with the following format:

FIPS CODE FORMAT

SSCCCPPPPP

SS

FIPS State Code

CCC

FIPS County Code

PPPPP

FIPS Place Code

Specifying Jurisdiction NPANXX

NPANXX 6 digit numbers may be used for specifying a jurisdiction. There are issues to be considered with using the NPANXX.

  1. The first six digits of a toll free number do not constitute an NPANXX. Rather, these numbers are associated with a “ring to” number which is the number that is actually reached when the toll free number is dialed. The NPANXX of the “ring to” number should be used as the NPANXX for the toll free number. This replacement must be made before the information is passed to the AFC system.
  2. Switches are capable of producing CDRs with account codes in place of a “Bill To” number, which is not related to an actual NPANXX. The user is required to convert this number to the actual NPANXX using data from the billing system before interfacing with AFC.
  3. Use of NPANXX for determination of a taxing jurisdiction is generally considered not sufficient for local service. There is often an unacceptable rate of foreign exchanges that incorrectly map customers to the wrong local tax jurisdiction resulting in the wrong local taxes being calculated.

NPANXXs exist for the U.S., Barbados, Canada, Guam, Mexico, Northern Mariana Islands, Puerto Rico, Trinidad & Tobago and the US Virgin Islands. These countries are on the North American dialing plan.

Specifying Transaction / Service Pair

AFC stipulates a unique pair of numbers for each Transaction and Service Type. The first number defines the Transaction Type and the second number defines the Service Type. Transaction Types and Service Types are combined (or “paired”) to uniquely describe a Valid Transaction / Service Pair for a transaction.

Refer to TM_00115_AFC Transaction and Service Types for a complete list of all T/S pair mappings.

Specifying Transaction Date

The Date field is normally populated with the bill date, invoice date or call date (as applicable) and may appear in any of the Standard Date and Time Format Strings for .NET Framework shown in the table below.

Date Format

Sample

yyyymmdd

20160601

mm/dd/yyyy

06/01/2016

m/d/yyyy

6/1/2016

mm-dd-yyyy

06-01-2016

m-d-yyyy

6-1-2016

yyyy-mm-dd

2016-06-01

yyyy-m-d

2016-6-1

yyyy-mm-ddThh:MM:ss

2016-06-01T13:45:30

yyyy-m-dThh:MM:ss

2016-6-1T13:45:30

AFC compares this date to the effective date of each tax that applies to the transaction. Historical rates and effective dates are maintained and updated within AFC and it will return the correct tax information based upon the transaction date. The monthly updates assure that the rates and effective dates are current.

Note: The invoice date passed to the server in the transaction by default is preserved as is. For example, if the date 1/1/2017 is passed, Avalara does not convert the time and will use 1/1/2017. It is recommended that clients not use modifiers on the invoice date.

Specifying Transaction Charge

For standard calculations, the Charge field specifies the amount of the transaction to be taxed and it may appear in any of the numeric or currency formats supported in Microsoft Excel. Please refer to the table shown below for specific examples of positive charges or negative credit/refund amounts.

Charge

Examples

1500

1500.00

1,500.00

$1,500

$1,500.00

-1500

-1500.00

-1,500.00

(1500.00)

(1,500.00)

($1,500.00)

For Tax Inclusive calculations, this field specifies the desired total (base charge + taxes). Please refer to Specifying Request Type for additional details on adjustments or tax inclusive calculations.

Specifying Customer Type

This field is used to specify the type of customer involved in the transaction. The customer type is selected from one of the following four Customer Types.

Customer Type

ID

Value

Description

0

Residential

When transactions are made by a customer for home use.

1

Business

When transactions are made at a place of business

2

Senior Citizen

When transactions are made by a customer who meets the jurisdiction requirements to be considered a senior citizen and qualify for senior citizen tax breaks.

3

Industrial

When transactions are made at an industrial business.

Specifying Lines

When local service is provided, a transaction should be generated with the Lines field populated with the number of lines the customer subscribes to. AFC uses this information for generation of per line taxes usually associated with local E911 charges and local telecommunications relay service taxes and other assorted taxes.

Transaction Fields

Column Name

Description

Lines

Numeric : integer >= 0 (rounded to nearest integer using the half round up method)

Specifying Sale Type

Sale should be used for retail transactions. Resale should be used for wholesale transactions. Telecommunication companies are taxed on transactions made by their clients, which in some cases can be passed on or “resold” to their customers in part or in total. Resale is used to indicate whether the product or service transaction is final or if it is to be resold.

To have exempt taxes available for reporting, exemption type 3 (Sales For Resale) should be used in combination with Resale.

Sale Type

ID

Value

Description

0

Resale

Only Resale/Wholesale taxes apply

1

Sale (Default)

Standard taxes apply

Specifying Incorporated

The Incorp field is used to specify whether the customer involved in this transaction is inside or outside of the Local level designated as their location. The tax may or may not be affected by this designator depending upon whether or not the local level has taxes which would apply to the transaction/service type pair. Marking transactions as Incorporated = False will usually result in no local taxes returned. If not sure, the value True is the recommended setting to be used.

Incorporated Flag

Value

Description

True (Default)

Jurisdiction is inside an incorporated location.

False

Jurisdiction is outside an incorporated location.

Specifying Level Exemptions

The tax logic has a flag that indicates whether it is level exemptible or not. Only those taxes which have been flagged to be level exemptible can be exempted through specifying a level exemption.

The exemption PCode is the PCode associated with the jurisdictional level of the taxing authority that defines the tax. It is used to exempt all federal, state, county and / or local taxes. If the exemption PCode fields are not specified then all taxes are exempt at that level.

The Exempt Level field to be used (set to True) should be chosen based upon the level of the tax to be exempted.

Examples:

If the desire is to exempt all New York state taxes, but not New Jersey (or other states), then the following would be used:

Field

Value

State Exempt

True

State PCode

2502500

 

If the desire is to exempt all state taxes, then the following would be used:

Field

Value

State Exempt

True

State PCode

0

Specifying the Regulated Flag

The Regulated Flag is used to specify whether the telecommunication company and its services are regulated by the regulatory commission in the state of the service. Unless the seller is registered with the state regulatory commission as a rate-regulated, incumbent provider, all transactions should be Regulated = False.

Field

Value

Description

Regulated Flag

True

Company is rate-regulated

False (Default)

Company is not rate-regulated

Specifying Minutes

The Minutes (Call Duration) Field specifies the length of phone call in minutes, with one tenth of a minute precision capability. AFC uses this field for generation of taxes that are specified as per minute flat fees in some taxing jurisdictions and stores the value in the AFC log database. The number is a double so any seconds added should be in decimal format. For example, 20 minutes and 30 seconds would be 20.5 minutes.

Transaction Fields

Column Name

Description

Minutes

Numeric : floating point >= 0.0

Specifying Debit Flag

Setting the value to True triggers a special prepaid taxing routine.

In general, unless you are a prepaid seller and understand the impact of setting the debit flag, the value of False should be used. Contact Avalara Support for more information if you are not sure if setting this flag to True is applicable to your business.

Debit Flag

Value

Description

True

Perform a debit call tax calculation.

False (Default)

Debit does not apply.

Specifying Service Class

The Service Class Indicator is provided to delineate the Primary activity of an organization as either Long Distance or Local Service. The service class only impacts Telecom transactions in certain jurisdictions, such as New York.

Service Class

ID

Value

Description

0

Local (Default)

Primary Local Service providers are carriers vending their services with over 50% of the gross business activities in Local Service revenue.

1

Long Distance

Primary Long Distance providers are carriers vending their services with over 50% of the gross business activities in Long Distance revenue.

Specifying Lifeline Flag

The Lifeline flag is used to indicate if a customer is a Lifeline participant.

Lifeline Flag

Value

Description

True

Has the effect of turning off the calculation of taxes that are not collected from Lifeline recipients.

False (Default)

Collects all taxes.

Specifying Facilities Flag

The Facilities flag specifies the tangible facilities used by a company to deliver the service. This designation applies to Seller.

If the carrier delivering the service uses company-owned facilities to provide the service, then it is facilities-based. If the carrier does not own the facilities, it is non-facilities based.

Facilities Flag

Value

Description

True (Default)

Seller is facilities based.

False

Seller is not facilities based.

Specifying Franchise Flag

The Franchise flag indicates that the company provides services sold pursuant to a franchise agreement between the carrier and the jurisdiction. This designation applies to the Seller.

Note: Marking the transaction as Franchise = False will often result in no franchise fees or taxes being returned.

Franchise Flag

Value

Description

True (Default)

Seller has a franchise agreement with jurisdiction.

False

Franchise fees and taxes do not apply to seller.

Specifying Business Class

The Business Class Indicator field is used to specify whether the business making the transaction is an Incumbent Local Exchange Company (ILEC) or a Competitive Local Exchange Company (CLEC). This designation applies to the Customer.

This setting can also be considered as, “You are ILEC” or, “You are not ILEC.” If neither class applies, use the default value CLEC.

The business class only impacts Telecom transactions in certain jurisdictions, such as Oregon.

Business Class

ID

Value

Description

0

ILEC

Customer is an ILEC company.

1

CLEC (Default)

Customer is not an ILEC company.

Specifying Company Identifier

The company identifier does not impact taxation, but is useful in reporting. Normally this field would only be used if you need to distinguish transactions for different companies.

Reporting Fields

Column Name

Description

Customer Identifier

Alpha-numeric field. Supports up to 20 bytes.

Specifying Customer Number

The customer number does not impact taxation, but is useful in reporting. Normally this field would only be used if you need to distinguish transactions for different customers.

Reporting Fields

Column Name

Description

Customer Number

Alpha-numeric field. Supports up to 20 bytes.

Specifying Invoice Number

The invoice number does not impact taxation, but is useful in reporting. Normally this field is only used if you need to distinguish transactions for different invoices.

Reporting Fields

Column Name

Description

Invoice Number

Unsigned long value. Should be >= 0. The max value is different for different platforms, but in general supports values at least up to 4294967295 (232 -1).

Note: All numerical values entered into fields, such as Invoice Number, are considered integers and cannot be greater than the maximum value previously noted in this table. Any numbers larger than this value cannot be passed in AFC SaaS Standard and will produce an error message indicating the value is too large.

Specifying Discount Type

AFC has an additional table that stores discount types by state with an “allow ability” indicator. The adjustment functions have arguments for the discount type which look up the discount type from the table to determine whether to apply taxes or not.

Discounts may or may not be taxed within each state. When a discount is taxed, the customer receives a tax benefit commensurate with the amount of the discount (i.e., if the customer gets $5 off on a transaction subject to a 5% tax, the customer pays $0.25 less in tax than they would have). When a discount is not taxed, the customer receives non tax benefit from the discount. Whether a discount is taxed or not depends on the type of discount and the rules in a particular tax jurisdiction.

Discount types only apply to adjustment transactions. Refer to Section 4.1.7 Discount Adjustments in TM_00101_AFC User Manual_Telecom for more information on calculating adjustments.

Discount Type

ID

Value

Description

0

None (Default)

Discount Type not applicable.

1

Retail Product

An amount subtracted from the original price to arrive at a lower price.

2

Manufacturer Product

A credit applied to the total amount reimbursed to either the retailer or the customer by the manufacturer.

3

Account Level

A stand-alone discount that is not applied against any service but instead as a stand-alone product.

4

Subsidized

A credit for telephone service where the telephone provider provides a service to a lifeline eligible customer. The credit will be applied to the subscriber line charge.

5

Goodwill

A credit applied to customer invoices for the purpose of engendering customer goodwill. For example, compensation for a service outage.

Specifying Exemption Type

  • Specifying the exemption type is for reporting purposes only and does not impact tax calculations.
  • Spaces and capitalization do not matter in Exemption Type value.
  • The bolded text is sufficient, additional characters are ignored.
  • The optional numeric ID can be used. For example:
    • Non Profit Sales = nonprofitsales = nonprofit = 6
    • Fed Coupons WIC Vouchers = Fed Coupons = FEDCOUPONS = 4

Exemption Type

ID

Value

Description

0

None (Default)

Exemption Type not applicable.

1

Fed Sales Supremacy

Sales to the Federal government exempt based on supremacy.

2

State Local Gov Sales

Sales to the State or Local government in the state that you operate in.

3

Sales for Resale

Sales that are designated for resale.

4

Fed Coupons WIC Vouchers

Sales at retail for which Federal government coupons or vouchers under the supplemental feeding for women, infants and children program are used as payment.

5

Reduced Food Rate

Tax imposed on food that is taxed at a reduced rate.

6

Non Profit Sales

Sales made by or to not-for-profit civic, social, service or fraternal organizations.

7

Public School Sales

Sales made by or to all elementary and secondary schools operated at public expense in their educational functions and activities.

8

Religious Charitable Sales

Sales made by or to religious and charitable organizations and institutions in their religious, charitable or educational functions and activities.

9

Prescription Drug Sales

Drugs which may be legally dispensed by a licensed pharmacist only upon a lawful prescription of a practitioner licensed to administer those items.

10

Prostho Ortho Devices

Prosthetic or orthopedic devices as defined on January 1, 1980, by the Federal Medicare program pursuant to Title XVIII of the Social Security Act of 1965.

11

Insulin Sales

All sales of insulin.

12

Interstate Foreign Sales

Sales in commerce between various states or between any foreign country (Export Sales).

13

Ingredient Component Parts

Sale of ingredient or component parts.

Specifying Adjustment Method

In certain cases, when the rate of a tax will change based upon the charge or the number of lines in the transaction, clients may wish to make an adjustment at a different rate than would normally apply for the transaction.

For example, if the client purchased 20 lines at a rate of $0.67 per line and the credit or adjustment is for 5 lines at a rate of $1.25 per line, then usage of LEAST for the adjustment method would refund the 5 lines at the least favorable rate for the brackets making sure the client was not over compensated.

If unsure, use the value “Default”.

Adjustment Method

Value

Description

Default (Default)

Standard Tax Brackets applied for transaction.

Least Favorable Rate

Tax brackets applied to produce smallest tax refund.

Most Favorable Rate

Tax brackets applied to produce largest tax refund.

Specifying Private Line (Point-to-Point)

Private line, or Point-to-Point (PTP) as it is often referred to, allows a transaction to be split between the A point and Z point. If “IsPrivateLine” is set to true, then the “PrivateLineSplit” must also be provided. If all transactions have the same split, the default file can be used to set the PrivateLineSplit.

Private Line or Point-to-Point

Column Name

Description

IsPrivateLine

True/False

PrivateLineSplit

Percentage allocation specified as a decimal in the range 0 to 1.

Example: For a 40% - 60% split, the value 0.4 would be specified. 40% would be applied to the A point, and 60% to the Z point.

The Origination Jurisdiction will be used for the A point, and the Termination Jurisdiction will be used for the Z point.

Note: This feature is only supported with processing of tax calculations and adjustments which are not Tax Inclusive. It is not supported for use with Tax Inclusive calculations.

Specifying Proration

The Proration feature provides clients with the ability to calculate prorated taxes on a transaction that represents a partial month of service. Percentage-based taxes are prorated by way of the charge amount passed. However, fixed and per line taxes are subject to special prorating rules and procedures. Some tax authorities allow sellers to pro-rate fixed and per line taxes for partial months, but many insist on receiving the full amount. AFC will apply these rules automatically if the prorating feature is used. . A number between 0 and 1 is passed to specify the percentage of the month the service was active. The prorating functionality checks the logic of each tax in the appropriate jurisdiction to determine if prorating is allowed. If prorating is not allowed, the full amount is taxed. If prorating is allowed, the fixed or per line tax applicable to the service will be returned multiplied by the fraction supplied.

If the proration is being used for an adjustment credit rather than a partial charge, the ratio of the percentage applied should reflect the portion of the month in which the service was not active.

Proration

Column Name

Description

IsProRated

True/False

ProRatedPct

Percentage allocation specified as a decimal in the range 0 to 1.

If IsProRated is true and a prorated percentage is not provided, the default value of 1 will be applied.

Specifying Default Optional Reporting Fields

AFC SaaS Standard provides up to 9 Default Optional Fields. The optional fields do not impact taxation, but can be useful in reporting. As noted in Specifying Request Type, the field Optional should not be used when performing tax inclusive calculations.

Reporting Fields

Column Name

Description

Optional

Optional4

Optional5

Optional6

Optional7

Optional8

Optional9

Optional10

Unsigned long value. Should be >= 0. The max value is different for different platforms, but in general supports values at least up to 4294967295 (232 -1).

OptionalAlpha1

Alpha-numeric field. Supports up to 20 bytes.

Specifying Extended Optional Reporting Fields

AFC SaaS Standard provides up to 10 Extended Optional Fields. The optional fields do not impact taxation, but can be useful in reporting. As noted in Specifying Request Type, the field Optional should not be used when performing tax inclusive calculations.

The general guidelines adding Extended Optional Fields in AFC SaaS Standard are as follows:

  1. Add the Extended Optional Fields by assigning values to each in the input file.
  2. The column header for each field must be entered in the format of Key:[header].

Note: Only up to 10 Extended Optional Fields may be added and the value for each field can be no longer than 150 characters.

Sample Input File (only showing the optional fields)

image

For the error handling, invalid columns are ignored. In the sample input file provided, Column P and R are invalid and Column Z is ignored.

Note: The data below is the output of the sample input file and can be found in the status report. Only the valid columns of data will be shown in the detail log report.

Image_StatusFile.png

Specifying Exemption Cross Reference Key

The exemption file requires the name of the first column to be the one of the appropriate cross reference keys that ties the exemption to the applicable transactions. The value of the cross reference key should match exactly the value of the applicable transaction column in the input file. The following table shows valid options for the column header names which may are used to represent the cross reference key. (Please note that only one option for column name may be selected.)

Cross Reference Key

Column Name

Description

Customer Number

Ties exemption(s) to input file transactions with matching Customer Number. Refer to Specifying Customer Number for more details on the field.

Company Identifier

Ties exemption(s) to input file transactions with matching Company Identifier. Refer to Specifying Company Identifier for more details on the field.

OptionalAlpha1

Ties exemption(s) to input file transactions with matching OptionalAlpha1.  Refer to Specifying Optional Reporting Fields for more details on the field.

Exemption Type

Ties exemption(s) to input file transactions with matching Exemption Type.  Refer to Specifying Exemption Type for more details on the field.

Specifying Exemption Jurisdiction

The exemption jurisdiction can be specified through any of the jurisdiction types in the Exemption Jurisdiction Table. Only one of the jurisdiction options should be chosen for a given exemption.

Exemption Jurisdiction Table

Column Header(s)

Values List

Description

Link To

BillTo

Origination

Termination

Linked jurisdictions are useful for when the jurisdiction of the taxes to be exempted can be defined by one of the input transaction jurisdictions. When the tax calculation occurs, the exemption will be updated with the correct corresponding jurisdiction from the transaction.

If all three jurisdictions in the input transaction are the same, the linked exemption would always apply.

PCode

Numeric

The PCode should be the same level or lower than the targeted tax level. Refer to Specifying Jurisdiction PCode for more details on the field.

Country

State

County

Locality

ZipCode

ZipP4

Address

The address can be Country Only, State Only (for USA), or any valid address. Refer to Specifying Jurisdiction Address for more details on the address fields.

Fips Code

10 digit number

The Fips Code should be the same level or lower than the targeted tax level. Refer to Specifying Jurisdiction Fips Code for more details on the field.

NpaNxx

6 digit number

Refer to Specifying Jurisdiction NpaNxx for more details on the field.

Specifying Exemption Domain

Domain determines the jurisdiction level at which the exemption jurisdiction must match the taxing jurisdiction. The domain will automatically shift upwards if the exemption jurisdiction is defined at a higher level than the domain.

The domain value can be specified either by numeric id or the equivalent text representation. Capitalization is ignored (FEDERAL = Federal = 0).

Exemption Type

ID

Value

Description

0

Federal

Matches at the Federal or Country level.

1

State

Matches at the State, Province or Territory level.

2

County

Matches at the County level.

3

Local

Matches at the Local or City level.

Specifying Exemption Scope

Exemptions are designed to be applied for one or more specific tax types. The tax type is required.

Exemption Scope

Value

Description

Numeric value

Numeric mask representing summarized scope values.

Text value

Text representing combined scope values.

 

Description of values

Scope Base Numeric Value

Scope Base Text Value

128

Federal

256

State

512

County

1024

Local

 

To get combinations, add up the numeric values or combine the text values with + as the separator. Examples:

Scope Numeric Value

Scope Text Value Options

 

1920

All

Federal+State+County+Local

F+S+C+L

 

1792

State+County+Local

S+C+L

 

384

Federal+State

F+S

Specifying Exemption Tax Level (Deprecated – Use Domain instead)

The tax level can be specified either by numeric id or the equivalent text representation. Capitalization is ignored (FEDERAL = Federal = 0).

Exemption Type

ID

Value

Description

0

Federal

Exemption applies to Federal taxes.

1

State

Exemption applies to State taxes.

2

County

Exemption applies to County taxes.

3

Local

Exemption applies to Local taxes.

Note: Most Federal taxes are only exempted when selling to a reseller who is registered, reporting, and remitting to the regulating agency. For this reason, a wholesale exemption or a tax type exemption must be used to exempt taxes at the Federal level.

Specifying Exemption Category

Exemptions may also be applied by tax category in AFC SaaS Standard. Clients may enter the appropriate tax category ID instead of a tax type ID.

See Category Definitions for a current list of tax categories in AFC SaaS Standard.

Specifying Exemption Exempt Non-billable Flag

The Exempt non-billable flag indicates whether non-billable taxes should be included as candidates for exemption.

This is not required if a specific tax type is being exempted, but only for Category exemptions and wild- card exemptions.

Exempt Non-billable

Value

Description

True

Include non-billable taxes as candidates for exemption.

False (Default)

Exclude non-billable taxes as candidates for exemption.

Specifying Exemption Exempt Non-level-exemptible Flag

The exempt non-level exemptible flag indicates whether taxes tagged as non-level exemptible should be included as candidates for exemption.

This is not required if a specific tax type is being exempted or if it is a category exemption, but only for tax type wild-card exemptions.

Exempt Non-level exemptible

Value

Description

True (Default)

Include non-level exemptible taxes as candidates for exemption.

False

Exclude non-level exemptible taxes as candidates for exemption.

Specifying Safe Harbor Overrides for Traffic Studies

The results of percentages from a traffic study can be applied by administering traffic study TAM overrides. The TAM values for Cellular, VoIP and Paging are adjusted to reflect the percentages in the traffic study.

Please submit a support case in order to set the override at the account level versus transaction level.

  1. Cellular Examples

    Original Cellular Safe Harbor: 37.1% Federal / 62.9% State

    State

    City

    Zip

    Charge

    Products

    Tax Type

    Level

    Rate

    TM

    Tax

    Charge

    Exempt

    NY

    Manhattan

    10001

    100

    Cellular/Access Charge

    Fed USF Cellular

    Federal

    0.174

    37.1

    6.4554

    100

    62.9

     

    Cellular Traffic Study Override: 15.0% Federal / 85.0% State

    State

    City

    Zip

    Charge

    Products

    Tax Type

    Level

    Rate

    TM

    Tax

    Charge

    Exempt

    NY

    Manhattan

    10001

    100

    Cellular/Access Charge

    Fed USF Cellular

    Federal

    0.174

    15

    2.61

    100

    85

  2. VoIP Examples

    Original VoIP Safe Harbor: 64.9% Federal / 35.1% State

    State

    Zip

    Charge

    Products

    Tax Type

    Level

    Rate

    TM

    Tax

    Charge

    Exempt

    CA

    90001

    100

    VoIP/Access Charge

    Universal Lifeline Telephone Service Charge (VoIP)

    State

    0.055

    35.1

    1.9305

    100

    64.9

    CA

    90001

    100

    VoIP/Access Charge

    CASF (VoIP)

    State

    0.00464

    35.1

    0.16286

    100

    64.9

    CA

    90001

    100

    VoIP/Access Charge

    CA Teleconnect Fund (VoIP)

    State

    0.0108

    35.1

    0.37908

    100

    64.9

    CA

    90001

    100

    VoIP/Access Charge

    CA High Cost Fund A (VoIP)

    State

    0.0035

    35.1

    0.12285

    100

    64.9

    CA

    90001

    100

    VoIP/Access Charge

    TRS (VoIP)

    State

    0.005

    35.1

    0.1755

    100

    64.9

    CA

    90001

    100

    VoIP/Access Charge

    E911 (VoIP)

    State

    0.0075

    35.1

    0.26325

    100

    64.9

    CA

    90001

    100

    VoIP/Access Charge

    FUSF (VoIP)

    Federal

    0.174

    64.9

    11.2926

    100

    35.1

    CA

    90001

    100

    VoIP/Access Charge

    FCC Regulatory Fee (VoIP)

    Federal

    0.00371

    64.9

    0.24078

    100

    35.1

     

    VoIP Traffic Study Override: 25.0% Federal / 75.0% State

    State

    Zip

    Charge

    Products

    Tax Type

    Level

    Rate

    TM

    Tax

    Charge

    Exempt

    CA

    90001

    100

    VoIP/Access Charge

    Universal Lifeline Telephone Service Charge (VoIP)

    State

    0.055

    75

    4.125

    100

    25

    CA

    90001

    100

    VoIP/Access Charge

    CASF (VoIP)

    State

    0.00464

    75

    0.348

    100

    25

    CA

    90001

    100

    VoIP/Access Charge

    CA Teleconnect Fund (VoIP)

    State

    0.0108

    75

    0.81

    100

    25

    CA

    90001

    100

    VoIP/Access Charge

    CA High Cost Fund A (VoIP)

    State

    0.0035

    75

    0.2625

    100

    25

    CA

    90001

    100

    VoIP/Access Charge

    TRS (VoIP)

    State

    0.005

    75

    0.375

    100

    25

    CA

    90001

    100

    VoIP/Access Charge

    E911 (VoIP)

    State

    0.0075

    75

    0.5625

    100

    25

    CA

    90001

    100

    VoIP/Access Charge

    FUSF (VoIP)

    Federal

    0.174

    25

    4.35

    100

    75

    CA

    90001

    100

    VoIP/Access Charge

    FCC Regulatory Fee (VoIP)

    Federal

    0.00371

    25

    0.09275

    100

    75

Zip Lookup Requests

Zip Lookup Input File

The columns in the Excel and CSV file formats are the same in each format. Column headers are the first row in each file which describe the data in that particular field or column.

Field

*Required

Value List

Reference

Request Type

R

ZipLookup

Specifying Request Type

Country

State

County

Locality

ZipCode

ZipP4

OR

 

Specifying Jurisdiction Address

Best Match

D

True

False (default)

Specifying Best Match Flag

Limit Results

D

Number (default is 100)

Specifying Limit Results Value

Key:{header}

O

 

Specifying Extended Optional Reporting Fields

*Required:

  • R = Required
  • OR = Only One Required (Remaining optional)
  • SR = Strongly Recommended (Default, if applicable)
  • O = Optional (No Default)
  • D = Optional (Default, if applicable)

Notes:

  • Spacing is ignored in a field name. For example, [Request Type] and [RequestType] would be considered identical.
  • Matching will ignore additional fields or characters beyond the listed value. Example: [Country] and [Country] would be considered identical. [Zip] however would not be considered a valid column identifier, as it must be either [ZipCode] or [ZipP4] to distinguish which value applies.
  • Column headers are required and must be included in Excel and CSV format.
  • The key column should be used to tie input data to the results.

Zip Lookup Results

The following zip files would be produced if a file with the base name KML150117AA is uploaded.

KMLR150117AA.zip - Report Files

Zip Lookup Report Files

File Name

Description

KML161015AA.csv

Request in comma delimited format.

KML161015AA_ziplookup.csv

Results in comma delimited format.

KML161015AA_conversion.rpt

Conversion report. Generated if input file is in Excel format. Convert excel to comma delimited.

KML161015AA_error.rpt

Processing error report. Produced if errors occur during processing. Report captures the specific error message and the input line that failed processing.

KML161015AA_summary.rpt

Processing summary report.

KMLE161015AA.zip - Status Files

Extended and Fixed Format Status Files

File Name

Description

Read_err.sta

Reports problems encountered while reading the input file. Problems might relate to invalid transaction/service type pairings or an invalid input file.

KML161015AA_converter.sta

Applies to excel input files. If an error occurs during conversion from Excel to csv format, this file will be generated with the error message.

KML161015AA_batch.sta

Status from batch processor. Outputs column mappings and any informational, warning or error level messages.

Zip Lookup Specific Transaction Specifications

Specifying Best Match Flag

Best Match Flag

Value

Description

True

Will attempt to locate the best match for the data provided

False (Default)

All data provided must match exactly to the values in the address file

By default, the Zip Lookup algorithm requires an exact match of all fields provided with the following considerations:

  1. Punctuation in city names will be ignored
  2. Whitespace will be ignored

    For example, the city name LAND O' LAKES will match LAND O LAKES or LANDOLAKES as both the punctuation and spacing will be ignored for purposes of matching.

If Best Match is set to true, some limited variable search algorithms will be used to find the best match for the data provided.

Examples using Manhattan in New York are as follows:

  1. Sample Request with one exact match

    Request 1 - Match exact on Country, State, County, City and Zip Code

    Request Type

    Country

    State

    County

    Locality

    ZipCode

    Best Match

    ZipLookup

    USA

    NY

    New York

    Manhattan

    10001

    FALSE

     

    Result

    PCode

    Country

    State

    County

    City

    2604100

    USA

    NY

    NEW YORK

    MANHATTAN

  2. Sample Request with multiple exact matches

    Request 1 – Match exact on Country, State, County and Zip Code

    Request Type

    Country

    State

    County

    Locality

    ZipCode

    Best Match

    ZipLookup

    USA

    NY

    New York

     

    10001

    FALSE

     

    Request 2 – Match exact on Country and Zip Code (Blank Country defaults to USA)

    Request Type

    Country

    State

    County

    Locality

    ZipCode

    Best Match

    ZipLookup

     

     

     

     

    10001

    FALSE

     

    Result - Both match 5 addresses (All have same Country, State, County and Zip)

    PCode

    Country

    State

    County

    City

    2604100

    USA

    NY

    NEW YORK

    EMPIRE STATE

    2604100

    USA

    NY

    NEW YORK

    GREELEY SQUARE

    2604100

    USA

    NY

    NEW YORK

    ONE HUNDRED THIRTY EIGHTH

    2604100

    USA

    NY

    NEW YORK

    MANHATTAN

    2604100

    USA

    NY

    NEW YORK

    NEW YORK

  3. Sample Request – Match exact on invalid address

    Request 1 – Match exact on Country, State, County, City and Zip Code

    Request Type

    Country

    State

    County

    Locality

    ZipCode

    Best Match

    ZipLookup

    USA

    NY

    Westchester

    Manhattan

    10001

    FALSE

     

    Result – No matches, not a valid address

    PCode

    Country

    State

    County

    City

     

     

     

     

     

  4. Sample Request – Best match on invalid address

    Request 1 – Best match on Country, State, County, City and Zip Code

    Request Type

    Country

    State

    County

    Locality

    ZipCode

    Best Match

    ZipLookup

    USA

    NY

    Westchester

    Manhattan

    10001

    FALSE

     

    Result – County was disregarded

    PCode

    Country

    State

    County

    City

    2604100

    USA

    NY

    NEW YORK

    MANHATTAN

Specifying Limit Results Value

The limit results field is used to limit the number of matches returned.

Limit Results

Column Name

Description

Limit Results

Numeric : integer >= 0

  • Default = 100, Maximum Limit = 1000
  • If zero is entered as the value, the default limit will be used.
  • If the value entered is greater than the maximum, the maximum limit will be used.

Bridge Conference File

The Bridge Conferencing feature is currently in development at this time. Please refrain from using this feature as well as the proposed solutions and files until further notice.

  • Was this article helpful?