id

VARCHAR(100)

Primary Key (PK) unique identification per recipient

created_datetime

TIMESTAMP UTC

the date/time the recipient was created in NTSTeVV

updated_datetime

TIMESTAMP UTC

the date/time the recipient file was modified in NTSTeVV

source_system

VARCHAR(4)

System of Reference per Payer database where the providers dataset is captured and stored

jurisdiction

VARCHAR(4)

4 character acronym, generally the state per the recipient primary residence

payer_id

VARCHAR(10)

unique identifier per Payer database establishing recipient profile and Payer database

plan

VARCHAR(4)

recipients health plan related supported by related Payer

program

VARCHAR(4)

recipients health insurance program issued by related Payer

delivery_system

VARCHAR(4)

acronym for Medicaid managed care delivery of health benefits and primary care services arrange between States and MCOs

first_name

VARCHAR(100)

recipient given (first) name

last_name

VARCHAR(100)

recipient family (last) name

gender

VARCHAR(1)

1 character reference (m) male or (f) female per recipient biological sex

ssn

VARCHAR(10)

Recipients' Social Security Number

date_of_birth

DATE

recipient date of birth

address

VARCHAR(100)

recipient permanent address, Street,

adddress2

VARCHAR(100)

recipient permanent address,

city

VARCHAR(100)

recipient permanent city

state

VARCHAR(2)

recipient permanent state

zip

VARCHAR(10)

recipient permanent zip

phone_home

VARCHAR(10)

recipient contact phone number

phone_mobile

VARCHAR(10)

recipient contact cell/mobile number

medicaid_id

VARCHAR(16)

recipient Medicaid Identification number

eligibility_effective_date

DATE

recipient active date of Medicaid eligibility

eligibility_end_date

DATE

recipient termination of Medicaid eligibility

member_id

VARCHAR(100)

unique alphanumeric assigned by Payer to identify recipient other then Medicaid id

batch_id

VARCHAR(255)

unique identifier for a set of data, generally 834 EDI recipient ‘RECP’ file

address_latitude

VARCHAR(45)

global coordinate point, measured in decimal degrees north/south of the equator per recipient primary address

address_longitude

VARCHAR(45)

global coordinate point, measured in decimal degrees east/west of the prime meridian per recipient primary address

phone_home_ivr

TINYINT(1)

1 character identifying home phone is IVR, (1) active (0) not active

phone_mobile_ivr

TINYINT(1)

1 character identifying mobile phone is IVR, (1) active (0) not active

id

VARCHAR(100) (PK)

Primary Key (PK) unique identification per provider

batch_id

VARCHAR(100)

unique identifier for a set of data, generally provider profile TXT file

created_datetime

DATETIME UTC

Date/time of provider setup in NTST-eVV database

updated_datetime

DATETIME UTC

Date/time the last modification to providers profile

source_system

VARCHAR(4)

System of Reference per Payer database where the providers dataset is captured and stored

payer

VARCHAR(45)

unique identifier per Payer database establishing Providers' profile captured and stored Payer database

name

VARCHAR(255)

Provider DBA (Doing Business As) name

type

VARCHAR(255)

provider association identification type (G) Group, (I) Independent

tax_id

VARCHAR(10)

Employer Identification, Tax Identification number

npi

VARCHAR(10)

National Provider Identification

npi_taxonomy

VARCHAR(25)

related to NPI, stardard 10-character code that designates classification and specialization per provider type of service

npi_zipcode

VARCHAR(25)

providers zip code related to National Provider Identification

address

VARCHAR(60)

providers place of business address

address2

VARCHAR(60)

providers place of business address 2nd line

county

VARCHAR(255)

providers' county per place of business

city

VARCHAR(30)

providers' city per place of business

state

VARCHAR(45)

providers' state per place of business

zip

VARCHAR(9)

providers' zip per place of business

phone

VARCHAR(10)

providers' phone number per place of business

email

VARCHAR(255)

providers' email per place of business

effective_date

DATE

providers' effective service date per Payer

expiration_date

DATE

providers' expiration service date per Payer

medicaid_id

VARCHAR(12)

providers' assigned Medicaid Identification number

id

int(11) AI PK

Primary Key (PK) unique identification per authorization related to recipient healthcare service

batch_id

varchar(100)

unique identifier for a set of data, generally authorization TXT file

created_datetime

timestamp UTC

the date/time the authorization was created in NTSTeVV

updated_datetime

timestamp UTC

the date/time the authorization file was modified in NTSTeVV

source_system

varchar(4)

System of Reference per Payer database where the providers dataset is captured and stored

jurisdiction

varchar(4)

Payer Jurisdiction (State of Operation)

payer_id

varchar(4)

NTSTeVV-Assigned Identifier for the Payer.

plan

varchar(255)

Payer Product identifier.

provider_name

varchar(100)

Provider business name.   If DBA name is separate from business name then concatenate like "BusinessName DBA DbaName"

provider_id

varchar(100)

Payer-Assigned Provider Identifier, or Provider Medicaid ID.  Used by NTSTeVV to create a relationship between prior authorization and provider database records. (Payer_Provider_id)

provider_ein

varchar(45)

Provider employee identification number (EIN) for entities or Social Security Number (SSN) for individuals, also referred to as tax ID number.

provider_npi

varchar(45)

Provider National Provider ID (NPI), send null for atypical providers that do not have an NPI number.

recipient_first_name

varchar(45)

Recipient First (Given) Name

recipient_last_name

varchar(45)

Recipient Last (Family) Name

recipient_id

varchar(100)

Medicaid ID of Service Recipient.  Used by NTSTeVV to create a relationship between prior authorization and recipient database records.

payer_recipient_member_id

varchar(45)

Payer-Assigned Member ID.   Typically used by MCO's only, may be equal to Medicaid ID.

setting

varchar(3)

Setting indicator (home health, ALF, SNF, etc.)

hcpcs_code

varchar(5)

Valid HCPCS Procedure Code, Service Code, or Revenue Code.

hcpcs_modifier1

varchar(5)

Valid HCPCS, Revenue Code, or Service Code Modifier #1

hcpcs_modifier2

varchar(5)

Valid HCPCS, Revenue Code, or Service Code Modifier #2

hcpcs_modifier3

varchar(5)

Valid HCPCS, Revenue Code, or Service Code Modifier #3

hcpcs_modifier4

varchar(5)

Valid HCPCS, Revenue Code, or Service Code Modifier #4

pa_number

varchar(255)

Payer-Assigned Authorization Number

auth_type

varchar(1)

The factor used to convert visit duration of time to billing units.  H (1 Hour = 1 Unit), Q (15 Minutes = 1 Unit), etc.

units_used

double

currently not used

total_units

double

Total number of units authorized.

limit_type

varchar(1)

If there is a limit in the number of units that can be used per day, week, month, or year for this authorization then use this field to specify the type of limit (daily, weekly,…).

limit_sun

int(11)

Used to capture auth limit Sunday, generally apportioned by Payer

limit_mon

int(11)

Used to capture service auth limit on Monday, generally apportioned by Payer

limit_tue

int(11)

Used to capture service auth limit on Tuesday, generally apportioned by Payer

limit_wed

int(11)

Used to capture service auth limit on Wednesday, generally apportioned by Payer

limit_thu

int(11)

Used to capture service auth limit on Thursday, generally apportioned by Payer

limit_fri

int(11)

Used to capture service auth limit on Friday, generally apportioned by Payer

limit_sat

int(11)

Used to capture service auth limit on Saturday, generally apportioned by Payer

note

varchar(100)

Free-form authorization Notes

timezone

varchar(255)

time zone of service/recipient/caregiver set by device (America/New_York) PC, (America/Chicago) Mobile

care_type

varchar(255)

Collection of all care types (ANTHINT, LTC, OTHER, etc) that are allowed to perform this service.  Comma-separate the list for flat files.

program

varchar(255)

Payer Program identifier (SLTC- Senior Long Term Care)

recipient_date_of_birth

date

Members Date of Birth (DOB)

contract

varchar(255)

Payer defined

service_group

varchar(255)

Collection of all service group (HHS, PCS, etc.) that are allowed to perform this service.  Comma-separate the list for flat files.

service_code

varchar(255)

additional HCPCS, Revenue Code, or Service Code Modifier

alt_contract

varchar(255)

Payer defined

id

varchar(40) PK

Primary Key (PK) unique identifier per claim service visit record

created_timestamp

timestamp UTC

date time completed visit was created as a claim record in NTSTeVV

updated_timestamp

timestamp UTC

date time claim record was modified and or updated the status per workflow in NTSTeVV

status_code

varchar(255)

current workflow level within in NTSTeVV

visit_source_type_id

varchar(4)

source identifier per the originating ‘completed visit service transaction’

visit_id

varchar(255)

(GUID) identifier per the originating ‘completed visit service transaction’

hcpcs_code

varchar(5)

Valid HCPCS Procedure Code, Service Code, or Revenue Code.

hcpcs_modifier1

varchar(5)

Valid HCPCS, Revenue Code, or Service Code Modifier #1

hcpcs_modifier2

varchar(5)

Valid HCPCS, Revenue Code, or Service Code Modifier #2

hcpcs_modifier3

varchar(255)

Valid HCPCS, Revenue Code, or Service Code Modifier #3

hcpcs_modifier4

varchar(255)

Valid HCPCS, Revenue Code, or Service Code Modifier #4

start_datetime

datetime UTC

Actual Date Time Service Started

end_datetime

datetime UTC

Actual Date Time Service Ended

timezone

varchar(25)

time zone of service/recipient/caregiver per the device, PC (America/New_York), Mobile (America/Chicago)

duration

bigint(20)

calculated field per end_datetime less start_datetime in seconds

start_address1

varchar(255)

Address 1, Starting place of service

start_address2

varchar(255)

Address 2, Starting place of service

start_city

varchar(255)

City, Starting place of service

start_state

varchar(255)

State, Starting place of service

start_zip

varchar(255)

Zip, Starting place of service

start_verification_method

varchar(45)

Method type of Verification, Starting place of service

start_latitude

double

GPS latitude coordinates for Starting place of service

start_longitude

double

GPS longitude coordinates (-) for Starting place of service

end_address1

varchar(255)

Address 1 for Ending place of service

end_address2

varchar(255)

Address 2 for Ending place of service

end_city

varchar(255)

City for Ending place of service

end_state

varchar(255)

State for Ending place of service

end_zip

varchar(255)

Zip for Ending place of service

end_verification_method

varchar(45)

Verified Type for Ending place of service

end_latitude

double

GPS latitude coordinate for Ending place of service

end_longitude

double

GPS longitude coordinate (-) for Ending place of service

provider_id

varchar(100)

Payer-Assigned Provider Identifier, or Provider Medicaid ID.  Used by NTSTeVV to create a relationship between prior authorization and provider database records. (Payer_Provider_id)

user_id

varchar(255)

identifier for NTSTeVV, GUID Caregiver

username

varchar(255)

Caregiver user credential name, created by Caregiver

user_medicaid_id

varchar(32)

Caregiver CMS Medicaid Identifier #

user_first_name

varchar(255)

Caregiver first name (Given name)

user_last_name

varchar(255)

Caregiver Last name (Family name)

recipient_id

varchar(100)

Assigned Member ID.  May be equal to /or combination of Medicaid ID & Payer assigned ID

recipient_diagnosis_codes

varchar(1024)

Comma separated list of recipients ICD-10 recipient Diagnosis Codes.   Note: In future release, NTSTeVV will add product feature to differentiate between primary and additional diagnosis codes ( reference AATES-6218); send the primary diagnosis code as the first value in the comma-separated list.

hidden

bit(1)

currently not used

flattened

bit(1)

currently not used

authorization_id

int(11)

NTSTeVV system generated unique identifier linking to authorization recipient file

units

double

claims units, system calculated (end_datetime less start_datetime)

amount

double

claims amount, system calculated (end_datetime less start_datetime) * service rate

invoice_created_datetime

datetime UTC

Date Time stamp of visit record when completed and recognized as claim_invoice record

invoice_updated_datetime

datetime UTC

Date Time stamp where an update occurred to a claim invoice record, e.g. recalculate units/rate, status updated

parent_id

varchar(40)

currently not used

trx_control_no

varchar(255)

NTSTeVV assigned trx control number where nomenclature is related to Transmitted Date Time of 837P e.g. (8) yyyymmdd + (6) hhmmss +(3) claim count

icn

varchar(255)

Payers control number per an adjudicated claim

note

text

Free-form Visit/Claim Notes

archived

bit(1)

identifier if claim is archived (1,0)

unit_type

varchar(1)

The factor used to convert visit time to billing units.  H (1 Hour = 1 Unit), Q (15 Minutes = 1 Unit), etc.

scheduled_start_longitude

double

GPS Longitude for Start Scheduled Service

scheduled_start_latitude

double

GPS Latitude for Start Scheduled Service

scheduled_end_longitude

double

GPS Longitude for End Scheduled Service

scheduled_end_latitude

double

GPS Latitude for End Scheduled Service

payer_id

varchar(4)

NTSTeVV/MCO assign acronym from payer name

service_recipient_current

int(11)

*NTSTeVV identifier or user defined

service_recipient_total

int(11)

*NTSTeVV identifier or user defined

batch_id

varchar(255)

integrated file assigned batch identification

transmitted_date_837P

datetime UTC

Date & time claim submitted per EDI 837P

claim_type

varchar(45)

currently not used - user defined

source_system

varchar(25)

Acronym of originating system for transaction (EVVW = NTSTeVV, 3rd Party)

jurisdiction

varchar(25)

Payer Define

plan

varchar(25)

Payer Define

program

varchar(25)

Payer Define

delivery_system

varchar(25)

3rd Party data integration system type (FFPS, MCOR, FFFS,)

recipient_member_id

varchar(50)

Payer assigned Member ID to recipient

thrd_visit_id

varchar(50)

3rd Party eVV identifier per service visit

scheduled_start_datetime

datetime UTC

Service Visit Start Scheduled Date Time

scheduled_end_datetime

datetime UTC

Service Visit End Scheduled Date Time

userfield1

varchar(255)

User defined field 1

userfield2

varchar(255)

User defined field 2

userfield3

varchar(255)

User defined field 3

reasoncode1

varchar(100)

Reason code 1

reasoncode2

varchar(100)

Reason code 2

reasoncode3

varchar(100)

Reason code 3

reasoncode4

varchar(100)

Reason code 4

visit_note

varchar(1000)

Notes per recipient service

visit_status

varchar(25)

Status of Service Visit

missed_visit_reason

varchar(25)

Reason for missed service visit

missed_visit_action_taken

varchar(25)

Action taken for missed service visit

provider_type

varchar(25)

Type of Service Provider performs

provider_code

varchar(25)

Payer assigned Provider Codes

recipient_ssn

varchar(25)

Recipient Social Security Number

recipient_gender

varchar(25)

Gender of recipient

source

varchar(25)

3rd Party Name (acronym), generally 4 character

file_name_837P

varchar(255)

batch name of 837P EDI file

amount_paid

double

payment amount per claim e.g. 835 file

received_date_999

datetime UTC

date of 999 EDI file confirming file was Accepted or Rejected submitted claims batch

response_999

varchar(1000)

999 EDI response per claim batch (no errors, rejected)

received_date_835

datetime UTC

Date of inbound 835 EDI file, Explanation of Payment and Remittance Advice

response_835

varchar(1000)

835 EDI response to inbound file received status type (no errors, rejected)

care_direction_type

varchar(45)

Self Directed or Provider Directed Care instructions (PROV, SELF)

amount_billable

double

Supersedes system calculated claim ‘amount’, Adjustment make to ‘claim_invoice.amount’, billable to Payer

units_billable

double

Supersedes system calculated ‘units’. Adjustment make to ‘claim_invoice.units’, new billable units

start_datetime_billable

datetime UTC

Superseds system stored ‘start_datetime’, adjustment made to ‘claim_invoice.start_datetime, new billable start datetime.

end_datetime_billable

datetime UTC

Superseds system stored ‘end_datetime’, adjustment made to ‘claim_invoice.start_datetime, new billable end datetime.

visit_start_datetime

datetime UTC

3rd Party Scheduled Service Start Date/Time, integrated data

visit_end_datetime

datetime UTC

3rd Party Scheduled Service End Date/Time, integrated data

start_address_type

varchar(255)

Scheduled Start Address Type (Home, Civic Center)

end_address_type

varchar(255)

Scheduled End Address Type (Home, Civic Center)

contract

varchar(255)

Subcontractor per Self Direction

alt_contract

varchar(255)

Alternative Subcontractor per Self Direction

business_type

varchar(255)

 Different Types of Business Entities for Healthcare Professionals

service_group

varchar(255)

Healthcare Services Group with experience and expertise

service_code

varchar(255)

Place of Service Codes are two-digit codes 

procedure_code_qualifier

varchar(255)

seventh character code provides specificity regarding an additional attribute of the procedure

caregiver_hours_today

double

Caregiver # of hours worked (admin + service)

recipient_hours_today

double

Caregiver # of hours servicing Recipients

recip_overlapping_caregivers

tinyint(1)

Flag identifying Overlap Caregiver(s) service to a Recipient, (1) Suspect, (0) Reviewed

caregiver_overlapping_recips

tinyint(1)

Flag identifying Overlap Recipient(s) Service by a Caregiver, (1) Suspect, (0) Reviewed

provider_record_id

varchar(255)

Payer’s Provider record identifier

created_datetime

 timestamp UTC

Date/Time Error was created

updated_datetime

 timestamp UTC

Date/Time Error was modified

matching_error_code 

varchar(255) 

Adjudicated/matching Error Code

matching_error

varchar(1024) 

Description of Error Code

type 

varchar(32) 

Type of Error

reason_code  

varchar(16)

code for reason to clear ‘Error’

reason_code_setter 

varchar(16) 

process type to create/clear Error

reason_code_description

varchar(1024) 

description of reason code

modified_date

timestamp UTC

date Reason Code was applied

modified_by_first_name 

varchar(255) 

First name of person made changes

modified_by_last_name

varchar(255) 

First name of person made changes

modified_old_new_values

varchar(1024) 

original value > new value

note

 text 

Note per user