Table of Contents
Table of Contents |
---|
Introduction
NetSmart-eVV provides the module Ad Hoc Reporting as a tool for self-service personalized reporting with drag-and-drop elements. Users of any skill level can build reports and get answers to custom questions - all on their own. Users are not required to create queries.
Functional Overview
The user launches Payer Dashboard and accesses ‘Adhoc Report’ per the top Table of Contents
Table of Contents |
---|
Introduction
NetSmart-eVV provides the module Ad Hoc Reporting as a tool for self-service personalized reporting with drag-and-drop elements. Users of any skill level can build reports and get answers to custom questions - all on their own. Users are not required to create queries.
Functional Overview
The user launches Payer Dashboard and accesses ‘Adhoc Report’ per the top blue ribbon locate ‘Reports' icon where a drop list of reports by name appears. Selecting the “Adhoc Report” to launch a new window.
...
Sample Data - a small load of data will generate and display. This option is set as default and utilized as adhoc reports are being develops.
Full Data - a full load of data will generate and display. This option is used only when a report has been developed utilizing the ‘small’ sample data whereby data resource are allocated efficiently.
No Data - used to show images by URL or file path, however, no data. (file name of image cannot contain special characters)
...
Insert Data - layout band and filters
Fields, which can be added to a layout as columns, rows, and as filters.
Measures, which are specialized fields that contain data value that can be added to a layout columns, rows, and as filters.
...
Example:
Fields - select under ‘provider’ - ‘Provider Name’, drag and drop on to layout band Rows.
Measure - select under ‘claim_invoice’ - ‘Amount’, drag and drop on to layout band Rows.
Fields - select under ‘claim_invoice’ - ‘Hcpcs Code’, drag and drop on to layout band Columns.
Filters - refine dataset
...
Provider Name - from left side panel select under ‘provider’, drag and drop in right side panel
Refine filter drop list = * start with (e.g. ‘Tellus’)
Start Datetime - from left side panel select under ‘claim_invoice’, drag and drop in right side panel
Refine filter drop list = * is on or after (select from calendar desired start date and time)
End Datetime - from left side panel select under ‘claim_invoice’, drag and drop in right side panel
Refine filter drop list = is on or before (select from calendar desired end date and time)
...
AdHoc Reporting - continuity
...
Toggle between design and display modes
...
Generate, review and export
...
Save Ad Hoc View, Save as, and Create Report
...
Create title for the report - ‘Click to add a title’
...
AdHoc Report Data Dictionaries
The NetSmart-eVV Ad Hoc Reporting tool uses a payer-dedicated database as it’s source. The key tables of the database are as follows:
Recipient - Recipient data received from payer.
Provider - Provider data received from payer.
Authorization - Authorization data received from payer.
Claim Invoice - Visit and Claim Data intended for submission to the payer.
Claim Invoice Error - Completed Visit adjudicated resulting in Claim Invoice Error Data
Recipient
...
Field Name
...
Data Type
...
Definition
...
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
Provider
...
Field Name
...
Data Type
...
Definition
...
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
...
...
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
Authorization
...
Field Name
...
Data Type
...
Definition
...
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
Claim Invoice
...
Field Name
...
Data Type
...
Definition
...
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)
...
no data. (file name of image cannot contain special characters)
...
Insert Data - layout band and filters
Fields, which can be added to a layout as columns, rows, and as filters.
Measures, which are specialized fields that contain data value that can be added to a layout columns, rows, and as filters.
...
Example:
Fields - select under ‘provider’ - ‘Provider Name’, drag and drop on to layout band Rows.
Measure - select under ‘claim_invoice’ - ‘Amount’, drag and drop on to layout band Rows.
Fields - select under ‘claim_invoice’ - ‘Hcpcs Code’, drag and drop on to layout band Columns.
Filters - refine dataset
...
Provider Name - from left side panel select under ‘provider’, drag and drop in right side panel
Refine filter drop list = * start with (e.g. ‘Tellus’)
Start Datetime - from left side panel select under ‘claim_invoice’, drag and drop in right side panel
Refine filter drop list = * is on or after (select from calendar desired start date and time)
End Datetime - from left side panel select under ‘claim_invoice’, drag and drop in right side panel
Refine filter drop list = is on or before (select from calendar desired end date and time)
...
AdHoc Reporting - continuity
...
Toggle between design and display modes
...
Generate, review and export
...
Save Ad Hoc View, Save as, and Create Report
...
Create title for the report - ‘Click to add a title’
...
AdHoc Report Data Dictionaries
The NetSmart-eVV Ad Hoc Reporting tool uses a payer-dedicated database as it’s source. The key tables of the database are as follows:
Recipient - Recipient data received from payer.
Provider - Provider data received from payer.
Authorization - Authorization data received from payer.
Claim Invoice - Visit and Claim Data intended for submission to the payer.
Claim Invoice Error - Completed Visit adjudicated resulting in Claim Invoice Error Data
Recipient
Field Name | Data Type | Definition |
---|---|---|
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 |
Provider
Field Name | Data Type | Definition |
---|---|---|
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 |
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 |
Authorization
Field Name | Data Type | Definition |
---|---|---|
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(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
last_modified_by
varchar(255)
users' login name (username) whom last modified the claim record
last_update_by
varchar(250)
users' login name (username) whom last updated the claim record
refresh_status
varchar(64)
NTSTeVV internal integration Status (Completed, In-progress…)
payer_approval_status
varchar(25)
currently not used
visit_internal_id (future)
varchar(255)
NTSTeVV internal visit id utilized in UI Provider/Payer Portals as ‘Visit ID’
ref_phys_first_name
varchar(255)
Recipients' Referring Physians first / (given) name
ref_phys_last_name
varchar(255)
Recipients' Referring Physians last / (family) name
ref_phys_npi
varchar(255)
Recipients' Referring Physians
ref_physician_npi_taxonomy
varchar(255)
Recipients' Referring Physians
tpl_dollars_paid
double
Billing 3rd Party Liability payment amount
non_bill_minutes
int(11)
eight or more minutes are left over, bill for an additional unit, if seven or fewer minutes remain, non bill minute
invoice_number
varchar(255)
unique number generated by a business issuing a ‘debtor’ notes of financial obligation.
employee_id
varchar(255)
Caregiver assigned employee ID per Provider
actual_start_phone
varchar(255)
IVR Phone number for start of service
actual_end_phone
varchar(255)
IVR Phone number for end of service
received_date_277
datetime UTC
Date of 277 EDI file - Claims Status Inquiry & Response
response_277
varchar(1000)
Response of 277 EDI Claims Status Inquiry
adj_void_status
varchar(20)
Re-submitted Claims Status (Void, Adjusted)
adj_void_date
datetime UTC
Date Time of Re-submitted Claim to Payer
interchange_control_no
varchar(45)
ICN
units_adjustment
double
manual adjustment of visit record units, per 3rd Party eVV data
amount_adjustment
double
manual adjustment of visit record amount, per 3rd Party eVV data
evv_provider_id
varchar(255)
Provider Identification, per 3rd Party eVV data
npi
varchar(10)
National Provider Identification, per 3rd Party eVV data
npi_zipcode
varchar(25)
related to NPI, Provider place of business zip code, per 3rd Party eVV data
npi_taxonomy
varchar(25)
related to NPI, generally 10-character code that designates classification and specialization per provider per 3rd Party eVV data
soc_dollars_paid
double(10,2)
Share of Cost paid by Recipient
pa_number
varchar(255)
prior authorization number, per 3rd Party eVV data
ci_record_id
varchar(255)
Payer’s Case Information record identifier
pa_record_id
varchar(255)
Payer’s Prior Authorization record identifier
provider_record_id
varchar(255)
Payer’s Provider record identifier
recipient_record_id
varchar(255)
Payer’s Recipient record identifier
vrs_authorization_id
int(11)
Visit Recipient Service Authorization ID, which is table: Authorization and field ‘ID’
evv_vrs_id
varchar(255)
eVV database table (Visit Recipient Service) internal GUID
Claim Invoice Error (future)
Field Name
Data Type
Definition
id
varchar(40) PK
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 |
Claim Invoice
Field Name | Data Type | Definition |
---|---|---|
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 |
Claim Invoice Error
Field Name | Data Type | Definition | |||
---|---|---|---|---|---|
created_datetime | timestamp UTC | Date/Time Error was created | |||
updated_datetime | timestamp UTC | Date/Time Error was modified | claim_invoice_id | varchar(40) | ID of Claim Invoice, unique per each service delivered |
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 |
...