The following describes some key information and restrictions regarding the loading of a custom rate file:
There are no limitations in tax content data upload for other tax partners. Tax content data upload functions the same for Vertex and Taxware.
This scenario pertains only to the US.
Loading a custom rate file creates the tax to rate setup under an established regime, however you should manually create the taxes in order to properly set the accounting and exemption handling methods.
You should not use this methodology to maintain an upgraded regime. However, you can use it to maintain a manually created regime.
You cannot upload the tax content data for two different regimes. The regime that you use to upload the data first time should be used each time.
Record Type | Description |
00 | Country |
01 | State Geography Record |
03 | County Geography Record |
06 | City Geography Record |
08 | Postal Code Record |
09 | Tax Rate Record |
Detailed examples of the records in a content file are given in the Example of Content File Records section that follows.
Content Data Type | Data Type | Size | Description | |
1 | Country Code | NUMBER | 3 | This mandatory attribute identifies a country. It should be leading zeros filled if not of 3 digits. |
2 | State Jurisdiction Code | NUMBER | 2 | This mandatory attribute identifies a state geography code and/ or state tax jurisdiction code. It should be leading zero filled if a state jurisdiction code is of one digit. |
3 | County Jurisdiction Code | NUMBER | 3 | This mandatory attribute in a county or city record identifies a county geography code and/ or county tax jurisdiction code. It should be leading zeros filled if a county jurisdiction code is of less than three digits. |
4 | City Jurisdiction Code | CHARACTER | 23 | This mandatory attribute in a city record identifies a city geography code and/ or city tax jurisdiction code. It should be trailing spaces filled. |
5 | Effective From | DATE | 8 | This mandatory attribute is the effective from date. The date should be provided in YYYYMMDD format. |
6 | Effective To | DATE | 8 | This is the effective to date. This attribute should be filled with SPACES if there is no effective to date for a jurisdiction. The date should be provided in YYYYMMDD format. |
7 | Creation Version | NUMBER | 5 | This mandatory attribute identifies the file version in which this record was first introduced. |
8 | Last Updated Version | NUMBER | 5 | This mandatory attribute identifies the file version in which this record was last updated. |
9 | Country/State Abbreviation | CHARACTER | 2 | This mandatory attribute gives the two characters alphabetic abbreviation for a country or state. |
10 | Geography Name | CHARACTER | 30 | This is a mandatory attribute. In a state record it should have the state name, in a county record it should have the county name and in a city record it should have the city name. This field is trailing spaces filled. |
11 | Tax Authority Level | CHARACTER | 6 | This mandatory attribute in a tax rate record identifies if the given tax rate is for STATE, COUNTY or CITY tax. |
12 | Jurisdiction Serial Number | NUMBER | 1 | This attribute can have only two values 0 or 1 These values represent outside and inside city limits respectively. |
13 | Multiple Parent Flag | CHARACTER | 1 | This optional attribute identifies geography with multiple parents such as a city in two counties. At present Multiple Parents are not supported and user should pass N only. |
14 | Zip Begin | CHARACTER | 5 | This mandatory attribute is available only in zip range record. |
15 | Zip End | CHARACTER | 5 | This is a mandatory attribute. If there is a discrete zip code and not a range, same value should be given in both Zip Begin and Zip End. |
16 | Tax Rate | NUMBER | 8 | This mandatory attribute should have eight digits with no decimal point. First three digits are considered before decimal and the last five are considered after decimal. This field is zero filled, for example, a rate of 2.5 should be given as 00250000. |
17 | Tax Rate Active Flag | CHARACTER | 1 | This mandatory attribute is used for inactivating a tax rate. It is used in the situations when a wrong rate got published that needs to be disabled. |
18 | Primary City Flag | CHARACTER | 1 | This attribute is mandatory for city geography records and used for identifying the primary city and alternate city names. This attribute should be passed only for City Geography records. Primary City Flag = Y for Primary City and = N for Alternate City Names. |
Content File Column Name | Size | Positions | Update Allowed? | |
1 | Record Type | 2 | 1-2 | N |
2 | Country Code | 3 | 3-5 | N |
3 | State Jurisdiction Code | 2 | 6-7 | N |
4 | County Jurisdiction Code | 3 | 8-10 | N |
5 | City Jurisdiction Code | 23 | 11-33 | N |
6 | Effective From | 8 | 34-41 | N |
7 | Effective To | 8 | 41-49 | Y (Change is only allowed from a blank date to an actual effective to date). At present, geography end-dating is not supported in the Tax Content Upload Program |
8 | Creation Version | 5 | 50-54 | N |
9 | Last Updated Version | 5 | 55-59 | Y (Can update to a higher version only) |
10 | Country/State Abbreviation | 2 | 60-61 | N |
11 | Geography Name | 30 | 62-91 | N |
12 | Multiple Parent Flag | 1 | 92-92 | N |
13 | Jurisdiction Serial Number | 1 | 93-93 | N |
14 | Primary City Flag | 1 | 94-94 | N |
Content File Column Name | Size | Positions | Update Allowed | |
1 | Record Type | 2 | 1-2 | N |
2 | Country Code | 3 | 3-5 | N |
3 | State Jurisdiction Code | 2 | 6-7 | N |
4 | County Jurisdiction Code | 3 | 8-10 | N |
5 | City Jurisdiction Code | 23 | 11-33 | N |
6 | Effective From | 8 | 34-41 | N |
7 | Effective To | 8 | 42-49 | Y (Change is only allowed from a blank date to an actual effective to date) |
8 | Creation Version | 5 | 50-54 | N |
9 | Last Update Version | 5 | 55-59 | Y (Can update to a higher version only) |
10 | Zip Begin | 5 | 60-64 | N |
11 | Zip End | 5 | 65-69 | N |
12 | Geography Name | 30 | 70-99 | N |
Content File Column Name | Size | Positions | Update Allowed? | |
1 | Record Type | 2 | 1-2 | N |
2 | Country Code | 3 | 3-5 | N |
3 | State Jurisdiction Code | 2 | 6-7 | N |
4 | County Jurisdiction Code | 3 | 8-10 | N |
5 | City Jurisdiction Code | 23 | 11-33 | N |
6 | Effective From | 8 | 34-41 | N |
7 | Effective To | 8 | 42-49 | Y (Change is only allowed from a blank date to an actual effective to date) |
8 | Creation Version | 5 | 50-54 | N |
9 | Last Updated Version | 5 | 55-59 | Y (Can update to a higher version only) |
10 | Tax Rate | 8 | 60-67 | N |
11 | Tax Rate Active Flag | 1 | 68-68 | Y (Can only update the flag from A to N) |
12 | Tax Authority Level | 6 | 69-74 | N |
Attribute Name | Possible Values |
Tax Authority Level | STATE, COUNTY, CITY |
Tax Rate Active Flag | A (Active), N (In Active) |
Multiple Parent Flag | Y (Yes), N (No) |
Primary City Flag | Y (Yes), N (No) |
Jurisdiction Serial Number | 0, 1 |
Note: Dates are in format YYYYMMDD. Creation Version and Last Updated Versions are both 1 as the records are being first introduced in version 1 of the file and have not yet been updated in any subsequent versions.
Content File Column Name | Position | Country Record | State Record | County Record | Primary City Record | Alternate City Record |
Record Type | 1-2 | 00 | 01 | 03 | 06 | 06 |
Country Code | 3-5 | 001 | 001 | 001 | 001 | 001 |
State Jurisdiction Code | 6-7 | 05 | 05 | 03 | 03 | |
County Jurisdiction Code | 8-10 | 081 | 081 | 081 | ||
City Jurisdiction Code | 11-33 | 2790 | 2790 | |||
Effective From | 34-41 | 19900101 | 19900101 | 19900101 | 19900101 | 19900101 |
Effective To | 42-49 | |||||
Creation Version | 50-54 | 1 | 1 | 1 | 1 | 1 |
Last Updated Version | 55-59 | 1 | 1 | 1 | 1 | 1 |
Country/State Abbreviation | 60-61 | US | CA | CA | CA | CA |
Geography Name | 62-91 | United States | California | San Mateo | Redwood City | Redwood Shores |
Multiple Parent Flag | 92-92 | N | N | N | N | N |
Jurisdiction Serial Number | 93-93 | 1 | 1 | 1 | 1 | 1 |
Primary City Flag | 94-94 | Y | N |
The preceding table explains the country, state, county, primary city and alternate city name records in the content file.
For a Country Record, the record type is 00. Country Code = 001 for United States. State, County and City jurisdiction code columns will have no values. Effective from is 01-Jan-1990 and Effective to is Null. Country/State Abbreviation = US.
For a State Record, the record type is 01. Country Code = 001 and State Jurisdiction Code = 05 for California i.e. State California under Country US. County and City jurisdiction code columns will have no values. Effective from is 01-Jan-1990 and Effective to is Null. Country/State Abbreviation = CA.
For a County Record, the record type is 03. Country Code = 001, State Jurisdiction Code = 05 and County Jurisdiction Code = 081 for San Mateo, such as County San Mateo under State California under Country US. City jurisdiction code column will have no value. Effective from is 01-Jan-1990 and Effective to is Null. Country/State Abbreviation = CA.
For a Primary City Record, the record type is 06. Country Code = 001, State Jurisdiction Code = 05, County Jurisdiction Code = 081 and City Jurisdiction Code = 2790 for Redwood City, such as City Redwood City under County San Mateo under State California under Country US. Effective from is 01-Jan-1990 and Effective to is Null. Country/State Abbreviation = CA. The Primary City Flag is Y.
For an Alternate City Name Record, the values of columns record type, Country Code, State Jurisdiction Code, County Jurisdiction Code, City Jurisdiction Code, and Country/State Abbreviation are same as the Primary City record but the Primary City Flag is N. Also see that the Geography Name is different Redwood Shores (for example, the city Redwood City is also known as Redwood Shores).
Note: Geography end dating is not supported by the Tax Content Upload Program.
Content File Column Name | Position | Primary City Record | Alternate City Name Record | Content File Column Name | Position | Primary City Zip Range | Alternate City Zip Range |
Record Type | 1-2 | 06 | 06 | Record Type | 1-2 | 08 | 08 |
Country Code | 3-5 | 001 | 001 | Country Code | 3-5 | 001 | 001 |
State Jurisdiction Code | 6-7 | 03 | 03 | State Jurisdiction Code | 6-7 | 030 | 03 |
County Jurisdiction Code | 8-10 | 081 | 081 | County Jurisdiction Code | 8-10 | 081 | 081 |
City Jurisdiction Code | 11-33 | 2790 | 2790 | City Jurisdiction Code | 11-33 | 2790 | 2790 |
Effective From | 34-41 | 19900101 | 19900101 | Effective From | 34-41 | 19900101 | 19900101 |
Effective To | 42-49 | Effective To | 42-49 | ||||
Creation Version | 50-54 | 1 | 1 | Creation Version | 50-54 | 1 | 1 |
Last Updated Version | 55-59 | 1 | 1 | Last Updated Version | 55-59 | 1 | 1 |
Country/ State Abbreviation | 60-61 | CA | CA | Zip Begin | 60-64 | 94061 | 64065 |
Geography Name | 62-91 | Redwood City | Redwood Shores | Zip End | 65-69 | 64065 | 94065 |
Multiple Parent Flag | 92-92 | N | N | Geography Name | 77-99 | Redwood City | Redwood Shores |
Jurisdiction Serial Number | 93-93 | 1 | 1 | ||||
Primary City Flag | 94-94 | Y | N |
The preceding table explains the postal code records for the primary city and alternate city record in the content file.
For a Postal Code Record, the record type is 08. The value of columns Country Code, State Jurisdiction Code, County Jurisdiction Code, City Jurisdiction Code and Geography Name are same as the corresponding City Geography Record. Effective from is 01-Jan-1990 and Effective to is Null. Zip Begin is the Start Postal Code and Zip End is the End Postal Code of the postal range of the city. If the city has only one postal code for it then both Zip Begin and Zip End will be same. For example, Postal Code Range for Redwood City is 94061 to 94065 and Postal Code for the Alternate City Redwood Shores is 94065.
Content File Column Name | Position | State Tax Rate Record | County Tax Rate Record | City Tax Rate Record |
Record Type | 1-2 | 09 | 09 | 09 |
Country Code | 3-5 | 001 | 001 | 001 |
State Jurisdiction Code | 6-7 | 05 | 05 | 05 |
County Jurisdiction Code | 8-10 | 017 | 017 | |
City Jurisdiction Code | 11-33 | 7600 | ||
Effective From | 34-41 | 20040101 | 20040101 | 20040101 |
Effective To | 42-49 | |||
Creation Version | 50-54 | 1 | 1 | 1 |
Last Update Version | 55-59 | 1 | 1 | 1 |
Tax Rate | 60-67 | 00625000 | 001000000 | 000500000 |
Tax Rate Active Flag | 68-68 | A | A | A |
Tax Authority Level | 69-74 | STATE | COUNTY | CITY |
The preceding table explains the tax rate records for the state, county and city record in the content file.
The record type for the Tax Rate record is 09.
For a State Tax Rate Record, the Tax Authority Level is STATE. The values of columns Country Code and State Jurisdiction Code are same as State Geography Code record. County and City jurisdiction code columns will have no values. Effective from is 01-Jan-2004 and Effective to is Null. Tax Rate = 00625000 or 6.25% and Active Flag is A or Yes.
For a County Tax Rate Record, the Tax Authority Level is COUNTY. The values of columns Country Code, State Jurisdiction Code and County Jurisdiction Code are same as County Geography Code record. City jurisdiction code column will have no value. Effective from is 01-Jan-2004 and Effective to is Null. Tax Rate = 00100000 or 1.0% and Active Flag is A or Yes.
For a City Tax Rate Record, the Tax Authority Level is CITY. The values of columns Country Code, State Jurisdiction Code, County Jurisdiction Code and City Jurisdiction Code are same as City Geography Code record. Effective from is 01-Jan-2004 and Effective to is Null. Tax Rate = 00050000 or 0.5% and Active Flag is A or Yes.
Tax Authority level can be used to override the tax rate of a particular geography.
Content File Column Name | Position | County Tax Rate Record | City Tax Rate Record |
Record Type | 1-2 | 09 | 09 |
Country Code | 3-5 | 001 | 001 |
State Jurisdiction Code | 6-7 | 48 | 48 |
County Jurisdiction Code | 8-10 | 044 | 044 |
City Jurisdiction Code | 11-33 | 1922 | |
Effective From | 34-41 | 20040701 | 20040701 |
Effective To | 42-49 | ||
Creation Version | 50-54 | 1 | 1 |
Last Update Version | 55-59 | 1 | 1 |
Tax Rate | 60-67 | 00100000 | 000500000 |
Tax Rate Active Flag | 68-68 | A | A |
Tax Authority Level | 69-74 | COUNTY | COUNTY |
The preceding table explains the tax rate records with City Overriding the County Tax Rate record in the content file.
The County tax rate record shows that the County 044 of State 48 has Tax-authority COUNTY and an active tax rate of 1%.
In the City tax rate record, State, County and City Jurisdiction Codes are given but the tax authority level is specified as COUNTY. So the record identifies a city but the tax rate is of county tax. This means for the City 1922 of County 044 and State 48, the tax rate will be 0.5% for the county 044 in place of 1.0%.
Similarly, we can have a County tax rate record with Tax-Authority STATE to override state tax rate and City tax rate record with Tax-Authority STATE to override state tax rate.
Example 1: State Tax Rate Change
Assume that the state tax rate of California (State Jurisdiction Code: 05) was changed to 7.25 effective from 1-Jul-2004. The existing record of California state tax rate is updated and a new tax rate record is added in the following table. Notice the values in bold.
Content File Column Name | Position | State Tax Rate Record | State Tax Rate Record |
Record Type | 1-2 | 09 | 09 |
Country Code | 3-5 | 001 | 001 |
State Jurisdiction Code | 6-7 | 05 | 05 |
County Jurisdiction Code | 8-10 | ||
City Jurisdiction Code | 11-33 | ||
Effective From | 34-41 | 20040101 | 20040701 |
Effective To | 42-49 | 20040630 | |
Creation Version | 50-54 | 1 | 2 |
Last Updated Version | 55-59 | 2 | 2 |
Tax Rate | 60-67 | 00625000 | 00725000 |
Tax Rate Active Flag | 68-68 | A | A |
Tax Authority Level | 69-74 | STATE | STATE |
In the first record, the Last Updated Version has been updated with 2 indicating the record has changed in file version 2. The Effective to has been changed to 30-Jun-2004. The second record has both Creation Version and Last Updated Version as 2 since this record is created in file version 2 and has not been updated since then. This record has been created with Effective From 01-Jul-2004 and Tax Rate 7.25%.
Example 2: County Tax Rate Change with Data Error
To extend the preceding example, assume that the county tax rate has changed to 1.25% effective 1-Jul-1990. However, the content in the file shows 1.5% instead of 1.25%.
Content File Column Name | Position | County Tax Rate Record | County Tax Rate Record |
Record Type | 1-2 | 09 | 09 |
Country Code | 3-5 | 001 | 001 |
State Jurisdiction Code | 6-7 | 05 | 05 |
County Jurisdiction Code | 8-10 | 053 | 053 |
City Jurisdiction Code | 11-33 | ||
Effective From | 34-41 | 19900101 | 19900701 |
Effective To | 42-49 | 19900630 | |
Creation Version | 50-54 | 1 | 2 |
Last Updated Version | 55-59 | 2 | 2 |
Tax Rate | 60-67 | 1 | 1.5 |
Tax Rate Active Flag | 68-68 | A | A |
Tax Authority Level | 69-74 | COUNTY | COUNTY |
Example 3: Data Error Correction
The incorrect county tax rate of 1.5% is corrected to 1.25% by inactivating the wrong data record and by providing a new record. Oracle does not provide any mechanism for partners to indicate deletion of a record. If the wrong rate has already been used, it can't be updated. Therefore, partners must make existing record inactive and provide a new record with correct tax rate.
Content File Column Name | Position | State Tax Rate Record | State Tax Rate Record |
Record Type | 1-2 | 09 | 09 |
Country Code | 3-5 | 001 | 001 |
State Jurisdiction Code | 6-7 | 05 | 05 |
County Jurisdiction Code | 8-10 | 053 | 053 |
City Jurisdiction Code | 11-33 | ||
Effective From | 34-41 | 19900701 | 20040701 |
Effective To | 42-49 | ||
Creation Version | 50-54 | 2 | 3 |
Last Updated Version | 55-59 | 3 | 3 |
Tax Rate | 60-67 | 1.5 | 1.25 |
Tax Rate Active Flag | 68-68 | N | A |
Tax Authority Level | 69-74 | COUNTY | COUNTY |
The Last Updated Version of the record introduced in version 2 has been updated with 3 indicating the record has changed in file version 3. The Active Flag has been changed to N. The second record has both Creation Version and Last Updated Version as 3 since this record is created in file version 3 and has not been updated since then. This record has been created with correct Tax rate 1.25%.
In the preceding examples, state and county have been taken as an example to show data change and error correction. All these changes and error corrections apply to the state, county and city tax. This change affects only the tax rate record.