Sample Scenario: Tax Rate API

Loading a Custom Rate File

The following describes some key information and restrictions regarding the loading of a custom rate file:

Record Types

Record Types
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 File Attributes Definitions

Content File Attributes Definitions
  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.

Record Layouts

Geography Records
  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
Zip Range Record
  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
Tax Rate Record (Sales, Use, Rental, Lease)
  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
Possible Values of Attributes
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

Examples of Content File Records

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.

Geography Record
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.

Tax Rate Record
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.

Tax Rate Change and Error Correction

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.