Att 3 Database Workflow Process RFP.pdf

EPRE RFP 47QTCB21R0002

Database Workflow Process Attachment 3

Database Workflow Process

Attachment 3

General Services Administration i

Table of Contents

1.0 Overview 1

2.0 Workflow Process 2

3.0 TAX Interface Validation Rules 4

3.1 General Business Rules 4

3.2 Data Element Validation Rules 5

3.3 ATAX Reference tables 8

Attachment 3

General Services Administration 1

1.0 Overview

The EPRE contractor conducts research on telecommunications taxes, fees, and surcharges (TFSs) consistent with GSA provided location data. GSA approves TFSs based on the research provided by the EPRE contractor.

The EPRE contractor submits the approved tax rules (allowable or unallowable) to Conexus via a web service interface. Conexus stores tax rules and formats TFS data into a pipe-delimited data reference file for distribution to the GSA Enterprise Infrastructure Solutions (EIS) contractors.

Conexus uses TFS data to validate tax rule and tax amount values submitted on EIS invoices.

Discrepancies in the validation process may be submitted to the EPRE contractor for further investigation. If the EIS contractor disputes the tax rule or rate, the issue is resolved in accordance with Clauses H.14 and 23 of the EIS Contract.

Attachment 3

General Services Administration 2

2.0 How Data Flows from EPRE Contractor to Conexus

The following diagram illustrates the flow of the tax rule information and the tax rule issue resolution process.

Attachment 3

General Services Administration 3

1. The EPRE contractor provides new TFS allowability rules and revisions for upload to Conexus. A validated ALLTAX file is reloaded quarterly to ensure the EPRE contractor’s TFS allowability database is aligned with Conexus.

a. The EPRE contractor ensures that tax record locations are consistent with GSA provided location data.

b. Conexus validates TFS allowability data as outlined in Database Workflow Process (Section 3, “TAX Interface Validation Rules”), Attachment 3.

c. All tax record updates are live in-production as soon as they are saved in the Conexus database.

2. A nightly script will check Conexus for daily TFS database additions and/or changes.

Additions or changes are distributed to all EIS contractors via two pipe-delimited files:

a. “ALLTAX_<date>” -- a file containing TFS allowability data as required in the EIS contract.

b. “NTAX_<date>” -- a file containing non-allowable tax data

Files are posted for 30 days, then deleted.

3. EIS contractors may challenge TFS allowability rules per the resolution processes outlined in Clauses H.14 and H.23 of the EIS contract. Final TFS allowability determinations are made by the EIS Contracting Officer.

4. The EPRE contractor ensures the identified and structured revisions described in EPRE PWS 3.3.3.2.3 are updated in the TFS database and forwarded for upload into Conexus.

Attachment 3

General Services Administration 4

3.0 TAX Interface Validation Rules

The following validation rules are implemented as part of step 1 in the workflow described in section 2.

3.1 General Business Rules

1. All fields marked as required are mandatory fields. If any of these fields are not provided, the record will be rejected.

2. If the Tax Authority is "Federal" the EPRE contractor system is required to provide only the ISO 3166-2 defined Country code, and the remaining fields (State, County Name, Municipality Name) should be sent as blank fields in the ATAX web service. If State, County Name, Municipality Name are not blank then the values are stored as sent.

3. If the Tax Authority is "State" the EPRE contractor system is required to provide only the ISO 3166-2 defined Country and State codes, and the remaining fields (County Name, Municipality Name) should be sent as blank fields in the ATAX web service. If County Name, Municipality Name are not blank then store the values as sent.

4. If the Tax Authority is "County," the EPRE contractor shall provide the ISO 3166-2 defined Country, State, and County Name. The Municipality Name field may be blank if the tax rate of that county is the same for all municipalities under it. "NA" shall not be used as the value for the County Name field when the tax authority is County. If Municipality Name is not blank, then store the value as sent.

5. If the Tax Authority is "County" in unincorporated areas (as opposed to an incorporated city/town/village), the EPRE contractor system is required to provide a value of "Unincorporated" for the Municipality Name field.

6. If the Tax Authority is "School District" or "Other" the EPRE contractor system is required to provide Country, State, County Name, and Municipality Name.

7. An existing record (Item number + version number combination) can only be inserted and can never be updated, except to logically delete the record.

a. If the version number and item number for the tax record already exist in the Conexus system and the remove flag is “N” then Conexus system will reject the Web service message.

b. If the version number and item number for the tax record already exist in the Conexus system and the remove flag is “Y” then Conexus system will mark the record for removal.

8. A logically deleted (remove status = Y) item number can never be reused.

9. A remove status of “Y” will take precedence over an allowable flag change or any other change in the record.

10. On receiving a Remove request for ATAX record, Conexus will mark all the versions of that record as Removed. If the Remove value provided is "Y" or "y", and the Remove field value in Conexus is "N" or "n" for the same item number and Version number, set the Remove Value to "Y" in Conexus on all records (versions) with the provided item number.

Attachment 3

General Services Administration 5

3.2 Data Element Validation Rules

Sr. No Data Element R/O Field

Length Allowable Values Content Level Validations Error Messages

1 Data_Transaction_Sequence_ Num Y 20 Numeric Numeric Validation is performed in the API

Gateway.

2 Data_Transaction_Code Y 10 Alphanumeric Should always be “ATAX”. Content validation is required and performed in the API Gateway.

3 Data_Transaction_File_Date Y 10 Date If the provided Data Transaction File Date is off by more than 1 day compared to the system date send an error 20004.

20004 Error: Value for Data Transaction File Date is invalid

4 Item_Number Y 20 Alphanumeric Case 1- If the Item Number Exists in the Conexus system and is already marked for removal send Error 20004.

20004 Error: Value for Item Number is invalid.

5 Version Y 5 Decimal

NN.NN

Format validation is performed in the API Gateway. No content level validations required.

6 Description Y 4 Integer If the description does not exist in the reference table send Error 20001.

20001 Error: Description does not exist

7 Country Y 2 alpha If the provided Country code does not exist in the ISO country code standards list send Error 20001.

20001 Error: Country does not exist

8 State O 2 alpha

Case 1 - If the State code does not exist in the ISO State code standards list send Error 20001.

Case 2 - If the State code is not within the provided Country code send Error 20002.

Case 3 - If the tax authority code is "ST" and the State is not provided send Error 20003.

Case 4 - If the tax authority is "CO" and the State is not provided send Error 20003.

Case 1 - 20001 Error: State does not exist Case 2 - 20002 Error: State does not match information provided in Country Case 3 - 20003 Error: Value for State is not provided

Case 4 - 20003 Error: Value for State is not provided

Attachment 3

General Services Administration 6

9 County_Name O 50 alphanumeric

Case 1 - If the tax authority code is "CO" and the County Name is not provided send Error 20003.

Case 2 - If the tax authority code is "CO" and if the County Name is provided as "NA" send Error 20004.

Case 1 - 20003 Error: Value for County Name is not provided Case 2 - 20004 Error: Value for County name is invalid

10 Municipality_Name O 50 alphanumeric

Case 1 - If the tax authority code is "MU" and the Municipality Name is not provided send an Error 20003.

Case 2 - If the tax authority is "MU" and the State, County Name are not provided send Error 20003. (They send the county name as NA in case county does not exist.)

Case 3 - If the tax authority is "SD" or "O" and if the Country, State, County Name, and Municipality Name are not provided send Error 20003.

Case 1 - 20003 Error: Value for Municipality Name is not provided Case 2 - Please use applicable message:

(a) 20003 Error: Value for State is not provided

(b) 20003 Error: Value for County Name is not provided Case 3 - Please use applicable message:

(a) 20003 Error: Value for State is not provided

(b) 20003 Error: Value for County Name is not provided

(c) 20003 Error: Value for Municipality Name is not provided

11 Tax_Authority Y 3 Varchar If the Tax Authority value does not exist in the reference table send Error 20001.

20001 Error: Tax Authority does not exist

12 Tax_Basis Y 4 Integer If the Tax Basis does not exist in the reference table send Error 20001. 20001 Error: Tax Basis does not exist

13 Percent_Taxable_of_Charge O 10 Decimal

NNN.NNNNNN

If the Percent Taxable of Charge value is less than zero or greater than 100 send Error 20004.

20004 Error: Value for Percent Taxable of Charge is invalid

14 Voice_or_Data Y 4 Integer If the Voice or Data value does not exist in the reference table send Error 20001.

20001 Error: Voice or Data does not exist

15 Jur_Revenue Y 4 Integer If the Jurisdiction Revenue value does not exist in the reference table send Error 20001.

20001 Error: Jurisdiction Revenue does not exist

16 Rate_Application_Fixed_Perc ent Y 4 Integer

If the Rate Application Fixed Percent value does not exist in the reference table send Error 20001.

20001 Error: Rate Application Fixed Percent does not exist

17 Percent_Rate O 10 Decimal

NNN.NNNNNN

If value for Percent Rate is less than zero or greater than 100 send an error message.

20004 Error: Value for Percent Rate is invalid

Attachment 3

General Services Administration 7

18 Fixed_Rate O 23

Decimal

NNNNNNNNNNNNN

NNN.NNNNNN

The API Gateway will validate the data format and type of this field.

19 Citation_Type Y 4 Integer If the Citation Type value does not exist in the reference table send Error 20001.

20001 Error: Citation Type does not exist

20 Citation Y 255 Varchar The API Gateway will validate the data format and type of this field.

21 Effective_Date Y 3 Date If the Effective Date is greater than the provided End Date send Error 20004.

20004 Error: Value for Effective Date is invalid

22 End_Date O 3 Date No content level validation is required. See Effective Date validation.

23 Max_Allowed_By O 4 int If Max Allowed By value does not exist in the reference table send Error 20001.

20001 Error: Max Allowed By does not exist

24 Max_Percent_Rate O 10 Decimal

NNN.NNNNNN

If the Max Percent Rate value is less than zero or greater than 100 send Error 20004.

20004 Error: Value for Max Percent Rate is invalid

25 Max_Fixed_Rate O 23 Decimal

NNNNNNNNNNNNN

NNN.NNNNNN

The API Gateway will validate the format of this field.

26 Remove Y 1 Alpha

Case 1 - If the Remove value is not "Y", "y", "N", or "n" send Error 20004.

Case 2 - If the Remove value provided is "Y" or "y", and the Remove value in Conexus is "Y" or "y" for the same item number and Version number send Error 20004.

Case 3 - If the Remove value is "Y" or "y", and the combination of Item number and Version number does not exist in Conexus send Error 20001.

Case 4- If the combination of Item number and Version number already exist in Conexus and the Remove value provided is "N" or "n" , but the remove field value in Conexus is

Case 1 - 20004 Error: Value for Remove is invalid

Case 2 - 20004

Error: Value for Item Number is invalid.

Case 3 - 20001

Error: Item Number does not exist

Case 4 (a) - 20005

Error: Identical value for Item Number already exists

Attachment 3

General Services Administration 8

(a) "N" send Error 20005.

(b) "Y" send Error 20004

Case 4 (b) - 20004

Error: Value for Item Number is invalid.

27 Allowable Y 1 Alpha If the Allowable value is not "Y", "y", "N", or "n" send Error 20004.

20004 Error: Value for Allowable is invalid

3.3 ATAX Reference tables

TAX_AUTHORITY TABLE

TAX_AUTHORITY_ID ID TYPE

70001 MU Municipality 70002 CO County 70003 FED Federal 70004 ST State

70005 O OTHER

70006 SD SCHDIST

TAX_BASIS TABLE

TAX_BASIS_ID ID TYPE

70101 1 Service 70102 2 Equipment 70103 3 Service_and_Equipment 70104 4 Account 70105 5 Line 70106 6 Other

TAX_CITATION_TYPE TABLE

TAX_CITATION_TYPE_ID ID TYPE

70201 1 Statute 70202 2 DR Rule 70203 3 County Code 70204 4 Muni Code 70205 5 PUC Rule 70206 6 Other

TAX_DESCRIPTION TABLE

Attachment 3

General Services Administration 9

TAX_DESCRIPTION_ID ID CATEGORY NAME

70301 1 Gross Receipts Gross Receipts 70302 2 Business Business 70303 3 Utility Utility 70304 4 STax Sales Tax 70305 5 FUSF Federal Universal Service Fund 70306 6 Infra Infrastructure 70307 7 PUC Public Utility Commission 70308 8 LNP Number Portability 70309 9 StateTRS State Telecommunications Relay Service 70310 10 StateUSF State Universal Service Fund 70311 11 FRS Federal Relay Service 70312 12 Other Other 70313 13 911 911

TAX_JUR_REVENUE TABLE

TAX_JUR_REVENUE_ID ID TYPE

70401 1 Interstate 70402 2 Intrastate 70403 3 Both 70404 4 Not applicable

TAX_MAX_ALLOWED TABLE

TAX_MAX_ALLOWED_ID ID TYPE NAME

70501 1 SCC State County City 70502 2 CC County City 70503 3 SC State City 70504 4 CS County State

TAX_RATE_APPL_FIXED_PERC TABLE

TAX_RATE_APPL_FIXED_PERC_ID ID TYPE

70601 1 Fixed 70602 2 Percent 70603 3 Both

TAX_VOICE_OR_DATA TABLE

Attachment 3

General Services Administration 10

TAX_VOICE_OR_DATA_ID ID TYPE

70701 1 Voice 70702 2 Data 70703 3 Both 70704 4 Not applicable