US Sales and Use Tax API

This data originates from: Tax Rate API: How to Load a Custom Rate and Geography File for US Locations in R12 E-Business Tax (Doc ID 1072665.1)

We have looked at this data and managed to create our own version of a data file that can be used instead of Vertex, Taxware and the likes. What it gives you is an option to load in all the geographies and manually maintained rates. This means is that instead of paying the year on year subscription to get a rates file from a 3rd party, you can manually maintain these rates but still get a tax solution that works within Oracle.

This is great if you are not yet ready to go live and don’t want to have license costs starting too early or if you are a school or local authority that does not make sales and only needs to register for a few locations then our solution will work for you.

File Format for Non-Partner data loading for E-Business Tax in release 12
12.1.1: Patch 9462557 (comes seeded in 12.1.2+)
12.0: Patch 7598326

FAQ’s

Q. Are there any limitations on which tax partner data you can upload?
A. You can upload US data in Tax Content Data Upload for Other Tax Partners. It will work the same way as it does when loading US data for Oracle tax partners: Vertex and Taxware. However I have found that loading the tax data as other as apposed to Taxware changes the way the data is created within Oracle. For example, the naming convention used in the jurisdiction creation in completely different. So whilst the API can be used for custom loads (we have our own custom load) the way the data is loaded in is very different indeed. Taxware also creates a whole bunch of tax zones based on the zip code which are not created if using the source as ‘other’.

Q. Is this for US only or can it be used for any country?
A. This is for US only. ER 10203257 – ENABLE TAX CONTENT DATA UPLOAD API FOR CANADA & Other Countries.

Q. Will this work like it does for tax partners and create the entire Tax to rate setup under an established regime?
A. Yes. It will create the Tax to Rate setup under an established regime however we recommend customers manually create the taxes so that they can properly set the Accounting and Exemption handling methods. Create the regime and the tax but no need for the status or any rates. Documentation says to create a default status and rate but we have found that this can cause issues.

Q. Can it be used to maintain an upgraded regime?
A. No

Q. Can it be used to maintain a regime created manually?
A. Yes however users cannot upload the Tax Content Data for two different regimes. The regime that user will use to upload the data first time should be used everytime.

1.Record Types

Record Type Description
0 Country
0 State Geography Record
3 County Geography Record
6 City Geography Record
8 Postal Code Record
9 Tax Rate Record – Sales
10 Tax Rate Record – Rental
11 Tax Rate Record – Use
12 Tax Rate Record – Lease
15 School District Records
16 Township Records
17 File Version

The detailed examples of the records in a Content File are given in Section 4. There are actually more record types than this. Depending on the Data supplier but 18,19 and 20 are used for Canadian Provinces, Cities and Postal Codes respectively from the Vertex Oramast file.

2. Content File Attributes Definitions

Content File Name Data Type Size Description
1 Country Code NUMBER 3 This is a mandatory attribute identifying a country. It should be leading zeros filled if not of 3 digits.
2 State Jurisdiction Code NUMBER 2 This is a mandatory attribute identifying 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.  Every State should have unique 2 digit numeric code
3 County Jurisdiction Code NUMBER 3 This is a mandatory attribute in a county or city record identifying 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.  Every County under a State should have unique 3 digit
4 City Jurisdiction Code NUMBER 23 This is a mandatory attribute in a city record identifying a city geography code and/or city tax jurisdiction code. It should be trailing spaces filled.  First 4 digits of every City jurisdiction code under a County and a State should be unique.
5 Effective From DATE 8 Effective from date. The date should be provided in YYYYMMDD format. It is a mandatory attribute.
6 Effective To DATE 8 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 attributes identifies the file version in which this record was first introduced. It is a mandatory attribute.
8 Last Updated Version NUMBER 5 This attributes identifies the file version in which this record was last updated. It is a mandatory attribute.
9 Country/State Abbreviation CHARACTER 2 This is a mandatory attribute giving the two characters alphabetic abbreviation for a country or state.
10 Geography Name CHARACTER 30 This is also a mandatory attribute. In a state record it should have the statename, 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 is a mandatory attribute in a tax rate record. It 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 is an optional attribute. It is used for identifying geography with multiple parents e.g. a city in two counties. At present Multiple Parents are not supported and user should pass N only.
14 Zip Begin CHARACTER 5 This attribute is available only in zip range record. This is a mandatory attribute.
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 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 e.g. a rate of 2.5 should be given as 00250000. It is a mandatory attribute.
17 Tax Rate Active Flag CHARACTER 1 This 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. It is a mandatory attribute.
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.

3. Record Layouts

a. 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 Tax Content Upload Program
8 Creation Version 5 50--54 N
9 Last Updated Version 5 55--59 Y (Can update to only a higher version)
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

b. Zip Range 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 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 only a higher version)
10 Zip Begin 5 60--64 N
11 Zip End 5 65--69 N
12 Geography Name 30 70--99 N

c. Tax Rate Records (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 only a higher version)
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

d. Possible Value of Some 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

4. Examples of Content File Records

The detailed examples of the records in a Content File are given in below:

Note:

– All the dates are in format YYYYMMDD.
– Please note that 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.

a. 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 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 i.e. 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 i.e. 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 (i.e. the city Redwood City is also known as Redwood Shores).

Note: Please note that 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 30 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 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. E.g. Postal Code Range for Redwood City is 94061 to 94065 and Postal Code for the Alternate City Redwood Shores is 94065.

b. 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 625000 1000000 500000
Tax Rate Active Flag 68--68 A A A
Tax Authority Level 69--74 STATE COUNTY CITY

The 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 i.e. 6.25% and Active Flag is A i.e. 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 i.e. 1.0% and Active Flag is A i.e. 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 i.e. 0.5% and Active Flag is A i.e. 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 100000 500000
Tax Rate Active Flag 68-68 A A
Tax Authority Level 69-74 COUNTY COUNTY

The 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.

5. Tax Rate Change and Error Correction

Example 1: State Tax Rate Change.

Let us 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 table below. Please 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 625000 725000
Tax Rate Active Flag 68--68 A A
Tax Authority Level 69--74 STATE STATE

Please note that 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 above example, let us 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. Please note that 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 examples explained above, 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 rate. Please note that this change affects only tax rate record.