ERS SQL Table Reference

Last updated
Save as PDF

The following is a listing of all SQL tables that myAvatar ERS uses to generate compiles. Each compile is separated into its corresponding SQL tables. Each SQL table details all the SQL fields in that table, a description of the data that field is accessing and the data source of the obtained information.

ERS Trial Balance

ERS.billing_tb_detail

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_tx_history table.
LAST_TRAN_DATE	The last transaction date	The latest transaction date entered in any cash posting form, as obtained from the billing_pay_adj_history table.
LAST_PAYMENT_DATE	The last payment date	The latest posting date entered in any cash posting form, as obtained from the billing_pay_adj_history table.
GUARANTOR_NUMBER	The guarantor ID associated with the program	The guarantor code associated with the client/transaction, as obtained from the billing_pay_adj_history table.
guarantor_name	The guarantor name associated with the client transaction	The guarantor name associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_guarantor_table table.
GUARANTOR_BALANCE	The guarantor remaining liability	A calculated field that subtracts the total posted amount associated to the guarantor (billing_pay_adj_history) from the total cost for all services rendered to the client for which that guarantor is responsible (billing_tx_history).
financial_class_value	The financial class value of the guarantor in any cash posting form	The financial class value for the guarantor associated with the client/transaction (billing_pay_adj_history) as obtained from the billing_guar_table table.
financial_class	The financial class code of the guarantor in any cash posting form	The financial class code for the guarantor associated with the client/transaction (billing_pay_adj_history) as obtained from the billing_guar_table table.
episode_status	The current client episode status	A calculated field that details the client episode status associated with the program (billing_tx_history). A client is listed as discharged when the client discharge date, as obtained from the discharge_data field, is equal to or earlier than the compile date. All other clients are active.
episode_rrg_value	The revenue reporting group name associated with the client episode	The RRG value associated with the PATID and episode number and program the service(s) were posted to, as obtained from the billing_tx_history table.
episode_rrg_code	The revenue reporting group code associated with the client episode	The RRG code associated with the PATID and episode number and program the service(s) were posted to, as obtained from the billing_tx_history table.
episode_program_value	The program value associated with the client episode	The program value associated with the PATID and episode number that the service(s) were posted to, as obtained from the billing_tx_history table.
episode_program_code	The program code associated with the client episode	The program code associated with the PATID and episode number that the service(s) were posted to, as obtained from the billing_tx_history table.
EPISODENUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The client episode number associated with the posted service(s) as obtained from the billing_tx_history table.
COMPILE_ID	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Trial Balance form.
COMPILE_DESC	Used for ERS compile tracking	The compile description assigned to the selected compile through the ERS Trial Balance form.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Trial Balance form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Trial Balance form.
client_name	The client name	The client name associated with the PATID and episode number. This information was obtained from the billing_tx_history table.
cat8_balance	The balance for aging category 8	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the eighth number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
cat7_balance	The balance for aging category 7	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the seventh number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
cat6_balance	The balance for aging category 6	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the sixth number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
cat5_balance	The balance for aging category 5	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the fifth number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
cat4_balance	The balance for aging category 4	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the fourth number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
cat3_balance	The balance for aging category 3	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the third number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
cat2_balance	The balance for aging category 2	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the second number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
cat1_balance	The balance for aging category 1	A calculated field that details the remaining balance, where the number of days that balance existed is less than or equal to the first number listed in the Aging Category field of the ERS Trial Balance form as aged using the difference between the Effective Date and the Age From Date as entered in the ERS Trial Balance form.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the billing_tx_history table.

ERS.billing_tb_detail_params

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code associated with the compile selected in the ERS Trial Balance Compile field in the ERS Trial Balance Report form.
COMPILE_ID	Used for ERS compile tracking	The ERS compile batch number associated with the compile selected in the ERS Trial Balance Compile field the ERS Trial Balance Report form.
age_from_date_code	The code of the start date used for aging criteria in the compile	The age from date code associated with the aging criteria selected in the Age From Date field in the ERS Trial Balance form.
age_from_date_value	The value of the start date used for aging criteria in the compile	The age from date value associated with the aging criteria selected in the Age From Date field in the ERS Trial Balance form.
aging_catgegory	The aging categories associated with the compile	The aging categories entered in the Aging Category field in the ERS Trial Balance form.
date_of_payments	The last posting date for payment inclusion in the compile	The date entered in the Date of Payments field in the ERS Trial Balance form.
effective_date	The end date for the compile	The date entered in the Effective Date field in the ERS Trial Balance form.
service_billing_status_code	The code for service billing status inclusion in the compile	The code associated with the service billing status selected in the Service Billing Status field in the ERS Trial Balance form.
service_billing_status_value	The value for service billing status inclusion in the compile	The value associated with the service billing status selected in the Service Billing Status field in the ERS Trial Balance form.

ERS Payment And Adjustments

ERS.billing_pay_adjust

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The client episode number associated with the posted service(s) as obtained from the billing_pay_adj_history table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_pay_adj_history table.
GUARANTOR_ID	The guarantor ID associated with the client transaction	The guarantor ID associated with the client/transaction, as obtained from the billing_pay_adj_history table.
JOIN_TO_TX_HISTORY	A SQL join to another table	A SQL join to the billing_tx_history table.
PATID	The client ID	The client ID associated with the episode number that services were posted to, as obtained from the billing_pay_adj_history table.
PROVIDER_ID	The practitioner ID of the client service	The practitioner ID associated with the client/transaction, as obtained from the billing_pay_adj_history table.
acct_period	The accounting period closure date	The accounting period closure date, as obtained from the billing_tx_history table.
amount	The dollar amount entered for client payment, adjustment or transfer transaction	The payment amount of the client transaction, as obtained from the billing_pay_adj_history table.
chg_cat_code	The insurance charge category code associated with the client service in any cash posting form	The insurance charge category code associated with the service, as obtained from the billing_tx_history table.
chg_cat_value	The insurance charge category value associated with the client service in any cash posting form	The insurance charge category value associated with the service, as obtained from the billing_tx_history table.
client_name	The client name	The client name associated with the PATID.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Payment And Adjustments form.
compile_desc	Used for ERS compile tracking	The compile description assigned to the selected compile through the ERS Payment And Adjustments form.
compile_id	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Payment And Adjustments form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Payment And Adjustments form.
ers_posting_cat_code	The posting code entered in any cash posting form	(This SQL field is associated with the ERS Posting Code Category field in the Posting / Adjustment Codes Definition form.) The ERS posting category code associated with the transaction (billing_pay_adj_history), as obtained from the billing_posting_codes table.
ers_posting_cat_value	The posting code name entered in any cash posting form	(This SQL field is associated with the ERS Posting Code Category field in the Posting / Adjustment Codes Definition form.) The ERS posting category value associated with the transaction (billing_pay_adj_history), as obtained from the billing_posting_codes table.
financial_class_code	The financial class code of the guarantor in any cash posting form	The financial class code for the guarantor associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_guar_table.
financial_class_value	The financial class name of the guarantor in any cash posting form	The financial class value for the guarantor associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_guar_table.
group_code	The group service code from any cash posting form	The group code for the service associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_tx_history table.
group_code_value	The group service code value from any cash posting form	The group code for the service associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_tx_master_table.
guarantor_name	The guarantor name associated with the client transaction	The guarantor name associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_guar_table table.
posting_code	The posting code entered in any cash posting form	The posting code of the client transaction, as obtained from the billing_pay_adj_history table.
posting_code_value	The posting code value entered in any cash posting form	The posting code value associated with the transaction (billing_pay_adj_history), as obtained from the billing_posting_codes table.
program_code	The program code associated with any cash posting form	The program code associated with client/transaction that the service(s) were posted to, as obtained from the billing_pay_adj_history table.
program_value	The program value associated with any cash posting form	The program value associated with client/transaction that the service(s) were posted to, as obtained from the billing_pay_adj_history table.
provider_name	The practitioner name of the client service	The practitioner name associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_tx_history table.
rrg_code	The revenue reporting group associated with the program in any cash posting form	The RRG code of the program that the service(s) were posted to, as obtained from the billing_pay_adj_history table.
rrg_value	The revenue reporting group value associated with the program in any cash posting form	The RRG value of the program that the service(s) were posted to, as obtained from the billing_pay_adj_history table.
service_code	The service code associated with the client service in any cash posting form	The service code of the service, as obtained from the billing_pay_adj_history table.
service_desc	The service code description associated with the client service in any cash posting form	The service code description of the service, as obtained from the billing_pay_adj_history table.
who_posted	The user who posted the payment / adjustment in any cash posting form.	The user associated with the posting to, as obtained from the billing_pay_adj_history table.

ERS.billing_pay_adjust

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile
FACILITY	The facility ID	The system code associated with the compile selected in the ERS Payments and Adjustments Compile field in the ERS Payments and Adjustments Report form.
compile_id	Used for ERS compile tracking	The ERS compile batch number associated with the compile selected in the ERS Payments and Adjustments Compile field in the ERS Payments and Adjustments Report form.
pay_adjustments_from_date	The first date for payments and adjustments inclusion in the compile	The date entered in the Include Payments/Adjustments From field in the ERS Payments and Adjustments form.
pay_adjustments_to_date	The last date for payments and adjustments inclusion in the compile	The date entered in the Include Payments/Adjustments Thru field in the ERS Payments and Adjustments form.

ERS Program Statistics

ERS.pgm_statistics_adm_dis

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
ADM_uniqueid	An unique SQL row admission ID	An automatically assigned number associated with the PATID and episode number that corresponds to a single program admission, as obtained from the admission_data table.
DIS_uniqueid	An unique SQL row discharge ID	An automatically assigned number associated with the PATID and episode number that corresponds to a single program discharge, as obtained from the discharge_data table.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The client episode number, as obtained from the episode_history table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the episode_history table.
PATID	The client ID	The client ID associated with the episode number, as obtained from the episode_history table.
admission_date	The client admission date	The admission date of the program associated with the PATID and episode number, as obtained from the episode_history table.
ama_discharge	The number of client discharges against medical advice	A calculated field that details the number of client discharges defined as against medical advice from a program, as obtained from the discharge_data table.
client_name	The client name	The client name associated with the PATID and episode number, as obtained from the episode_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Program Statistics form.
compile_desc	Used for ERS compile tracking	The compile description assigned to the selected compile through the ERS Program Statistics form.
compile_id	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Program Statistics form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Program Statistics form.
discharge_date	The client discharge date	The client discharge date of a program, as obtained from the episode_history table.
length_of_stay	The client length of stay	The client length of stay of a program, as obtained from the discharge_data table.
program_code	The program code associated with any cash posting	The program code, as obtained from the episode_history table.
program_value	The program value associated with any cash posting	The program value, as obtained from the episode_history table.

ERS.pgm_statistics_claim

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
CLAIM_NUMBER	The claim number created by a bill generation form	The claim number associated with the PATID and Episode Number (billing_tx_history), as obtained from the billing_tx_charge_detail table.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The client episode number associated with the posted service(s), as obtained from the billing_tx_history table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_tx_history table.
PATID	The client ID	The client ID associated with the episode number that services were posted to, as obtained from the billing_tx_history table.
client_name	The client name	The client name associated with the PATID and episode number, as obtained from the billing_tx_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Program Statistics form.
compile_desc	Used for ERS compile tracking	The compile description assigned to the selected compile through the ERS Program Statistics form.
compile_id	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Program Statistics form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Program Statistics form.
program_code	The program code associated with any cash posting	The program code associated with the posted service(s), as obtained from the billing_tx_history table.
program_value	The program value associated with any cash posting	The program value associated with the posted service(s), as obtained from the billing_tx_history table.

ERS.pgm_statistics_data

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the episode_history table.
admissions	The number of client admissions	A calculated field that details the number of admissions to a program, as obtained from the admission_data table.
ama_discharges	The number of client discharges against medical advice	A calculated field that details the number of client discharges defined as against medical advice from a program, as obtained from the discharge_data table.
average_length_of_stay	The client average length of stay	A calculated field that details the average client length of stay in a program, as obtained from the discharge_data table.
claims	The number of claims	A calculated field that details the total number of claim postings (episode_history), as obtained from the billing_claim_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Program Statistics form.
compile_desc	Used for ERS compile tracking	The compile description assigned to the selected compile through the ERS Program Statistics form.
compile_id	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Program Statistics form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Program Statistics form.
discharges	The number of client discharges	A calculated field that details the number of discharges from a program, as obtained from the discharge_data table.
median_length_of_stay	The middle number of all the different client lengths of stays	A calculated field that details the median client length of stay in a program, as obtained from the discharge_data table.
program_code	The program code	The program code, as obtained from the episode_history table.
program_value	The program value	The program value, as obtained from the episode_history table.
transfer_in	The number of program transfers in to a program	The number of program transfers in to a program, as obtained from the history_program_transfer table.
transfer_out	The number of program transfers out of a program	The number of program transfers out of a program, as obtained from the history_program_transfer table.
visits	The total number of client visits filed in a staff posting form	A calculated field that details the total number of client services defined as visits (episode_history) that occurred during the reporting period, as obtained from the billing_tx_master_table table.
ytd_admissions	The year to compile date client admissions	A calculated field that details the number of admissions to a program. This information is obtained from the admission_data table for the year, as aged from the date entered in the First Date In YTD Calculation field in the ERS Program Statistics form to the period end date.
ytd_discharges	The year to compile date client discharges	A calculated field that details the number of discharges from a program. This information is obtained from the discharge_data table for the year, as aged from the date entered in the First Date In YTD Calculation field in the ERS Program Statistics form to the period end date.
ytd_transfer_in	The year to compile date transfers in to a program	A calculated field that details the number of transfers in to a program. This information is obtained from the history_program_transfer table for the year, as aged from the date entered in the First Date In YTD Calculation field in the ERS Program Statistics form to the period end date.
ytd_transfer_out	The year to compile date transfers out of a program	A calculated field that details the number of transfers out of a program. This information is obtained from the history_program_transfer table for the year, as aged from the date entered in the First Date In YTD Calculation field in the ERS Program Statistics form to the period end date.

ERS.pgm_statistics_tra

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The client episode number associated with the posted service(s), as obtained from the history_program_transfer table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the history_program_transfer table.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the history_program_transfer table.
TRAuniqueid	An unique SQL row transfer ID	An automatically assigned number that corresponds to a single program transfer associated with a PATID and episode number, as obtained from the history_program_transfer table.
client_name	The client name	The client name associated with the PATID and episode number, as obtained from the episode_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Program Statistics form.
compile_desc	Used for ERS compile tracking	The Compile Description assigned to the selected compile through the ERS Program Statistics form.
compile_id	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Program Statistics form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Program Statistics form.
program_from_code	The program code of the program transferred from	The program code of the program transferred from that the service(s) were posted to, as obtained from the history_program_transfer table.
program_from_value	The program value of the program transferred from	The program value of the program transferred from that the service(s) were posted to, as obtained from the history_program_transfer table.
program_to_code	The program code of the program transferred to	The program code of the program transferred to that the service(s) were posted to, as obtained from the history_program_transfer table.
program_to_value	The program value of the program transferred to	The program value of the program transferred to that the service(s) were posted to, as obtained from the history_program_transfer table.

ERS.pgm_statistics_tx

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The episode number associated with the posted service(s), as obtained from the billing_tx_history table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_tx_history table.
JOIN_TO_TX_HISTORY	A SQL join to another table.	A SQL join to the billing_tx_history table.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the billing_tx_history table.
client_name	The client name	The client name associated with the PATID and episode number, as obtained from the billing_tx_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Program Statistics form.
compile_desc	Used for ERS compile tracking	The compile description assigned to the selected compile through the ERS Program Statistics form.
compile_id	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Program Statistics form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Program Statistics form.
program_code	The program code associated with any cash posting form	The program code associated with the PATID and episode number, as obtained from the billing_tx_history table.
program_value	The program value associated with any cash posting form	The program value associated with the PATID and episode number, as obtained from the billing_tx_history table.

ERS.pgm_statistics_params

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code associated with the compile selected in the Select Program Statistics Compile field in the selected ERS Program Statistics Report form.
compile_id	Used for ERS compile tracking	The ERS compile batch number associated with the compile selected in the Select Program Statistics Compile field in the selected ERS Program Statistics Report form.
compile_programs	The programs associated with the compile	The programs selected in the Compile Programs field in the ERS Program Statistics form.
end_date	The end date of program activity included in the compile	The date entered in the Period End Date field in the ERS Program Statistics form.
first_date_in_YTD	The date from which the Year to Date calculations are generated for the compile	The date entered in the First Date in YTD Calculation field in the ERS Program Statistics form.
start_date	The start date of program activity included in the program statistics compile	The date entered in the Period Start Date field in the ERS Program Statistics form.

ERS Client Subset Index

ERS.client_subset_inde

SQL Field Name	Description	Data Source
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_tx_history table.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the billing_tx_history table.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The episode number associated with the posted service(s), as obtained from the billing_tx_history table.
days_since_last_visit	The number of days since the last client visit	A calculated field that details the number of days since the last client visit to the compile date, as obtained from the billing_tx_history table.
length_of_stay	The length of the client stay in a program	The client length of stay of the program associated with the PATID and episode number, as obtained from the discharge_data table.
date_of_last_visit	The date of the last client visit	The latest client service date entered in any cash posting form, as obtained from the billing_tx_history table.
COMPILE_ID	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Client Subset Index form.
compile_description	Used for ERS compile tracking	The compile description assigned to the selected compile through the ERS Client Subset Index form.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Client Subset Index form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Client Subset Index form.
ADM_uniqueid	An unique SQL row admission ID	An automatically assigned number that corresponds to a single program admission, as obtained from the admission_data table.
DIS_uniqueid	An unique SQL row discharge ID	An automatically assigned number that corresponds to a single program discharge, as obtained from the discharge_data table.

ERS.client_subset_index_params

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code associated with the compile selected in the ERS Client Subset Index Compile ID field in the selected ERS Quality of Care Report form.
COMPILE_ID	Used for ERS compile tracking	The ERS compile batch number associated with the compile selected in the ERS Client Subset Index Compile ID field in the selected ERS Quality of Care Report form.
active_programs	The active programs associated with the compile	The programs selected in the Active in What programs field in the ERS Client Subset Index form.
discharged_programs	The discharged programs associated with the compile	The programs selected in the Discharged From programs field in the ERS Client Subset Index form.
disch_from_date	The earliest discharge date included in the compile	The date entered in the Discharge From Date field in the ERS Client Subset Index form.
disch_min_los_for_inclusion	The minimum length of stay required for client episode inclusion in the compile.	The value entered in the Minimum LOS Required For Inclusion field in the ERS Client Subset Index form.
disch_restrict_on_los_code	The code for restricting client episode inclusion in the compile based on length of stay	The selection made in the Restrict Episode Inclusion Based on LOS field in the ERS Client Subset Index form.
disch_restrict_on_los_value	The value for restricting client episode inclusion in the compile based on length of stay	The selection made in the Restrict Episode Inclusion Based on LOS field in the ERS Client Subset Index form.
disch_thru_date	The latest discharge date included in the compile	The date entered in the Discharge Through Date field in the ERS Client Subset Index form.
include_no_svc_in_epid_code	The code for restricting client episode inclusion in the compile based on services having been rendered	The selection made in the Include Client If He/She Has No Services In Episode field in the ERS Client Subset Index form.
include_no_svc_in_epid_value	The value for restricting client episode inclusion in the compile based on services having been rendered	The selection made in the Include Client If He/She Has No Services In Episode field in the ERS Client Subset Index form.
include_no_svc_isince_date	The earliest date of service for client episode inclusion in the compile	The date entered in the Only Include Clients Who Have Not Had A Service Since field in the ERS Client Subset Index form.
index_type_code	The Client Subset Index Type code	The selection made in the Client Subset Index Type field in the ERS Client Subset Index form.
index_type_value	The Client Subset Index Type value	The selection made in the Client Subset Index Type field in the ERS Client Subset Index form.
minimum_loss_thru_today	The minimum length of stay required for client episode inclusion in the compile	The value entered in the Minimum LOS Required Through Today field in the ERS Client Subset Index form.
restrict_epid_on_los_code	The code for restricting client episode inclusion in the compile based on a specified length of stay	The selection made in the Restrict Episode Inclusion Based On LOS field in the ERS Client Subset Index form.
restrict_epid_on_los_value	The value for restricting client episode inclusion in the compile based on a specified length of stay	The selection made in the Restrict Episode Inclusion Based On LOS field in the ERS Client Subset Index form.
restrict_epid_svcdate_code	The code for restricting client episode inclusion in the compile based on services rendered on or after a specified date	The selection made in the Restrict Episode Inclusion Based On Last Service Date field in the ERS Client Subset Index form.
restrict_epid_svcdate_value	The value for restricting client episode inclusion in the compile based on services rendered on or after a specified date	The selection made in the Restrict Episode Inclusion Based On Last Service Date field in the ERS Client Subset Index form.
service_programs	The programs in which clients have had services that are included in the compile	The programs selected in the Service in programs field in the ERS Client Subset Index form.
services_included	The rendered client services that are included in the compile	The services selected in the Specify Service(s) To Include field in the ERS Client Subset Index form.
svc_from_date	The earliest date for client services included in the compile	The date entered in the Services From Date field in the ERS Client Subset Index form.
svc_thru_date	The latest date for client services included in the compile	The date entered in the Services Through Date field in the ERS Client Subset Index form.

ERS Earned Income

ERS.billing_earned_income

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_tx_history table.
accounting_period	The accounting period for the billing transaction	The date associated with accounting period, as obtained from the billing_tx_history table.
chg_category_code	The insurance charge category code associated with the client service.	The insurance charge category code associated with the client service, as obtained from the billing_tx_history table.
chg_category_value	The insurance charge category value associated with the client service.	The insurance charge category value associated with the client service, as obtained from the billing_tx_history table.
financial_class_code	The financial class code of the guarantor.	The financial class code for the guarantor associated with the client/transaction (billing_tx_history), as obtained from the billing_tx_charge_detail table.
financial_class_value	The financial class value of the guarantor.	The financial class value for the guarantor associated with the client/transaction (billing_tx_history), as obtained from the billing_tx_charge_detail table.
group_code	The group code associated with the client service.	The group code associated with the posted service, as obtained from the billing_tx_master_table table.
group_code_value	The group code value associated with the client service.	The group value associated with the posted service, as obtained from the billing_tx_master_table table.
GUARANTOR_ID	The guarantor ID associated with the billing transaction	The guarantor ID associated with the PATID and episode number, as obtained from the billing_tx_charge_detail table.
guarantor_liability	The guarantor liability associated with the billing transaction	The guarantor liability associated with the PATID and episode number, as obtained from the billing_tx_charge_detail table.
guarantor_name	The guarantor name associated with the billing transaction	The guarantor name associated with the client/transaction (billing_tx_history), as obtained from the billing_guar_table table.
program_code	The program code associated with the billing transaction	The program code of the client/episode associated with the posted service, as obtained from the billing_tx_history table.
program_value	The program value associated with the billing transaction	The program value of the client/episode associated with the posted service, as obtained from the billing_tx_history table.
rrg_code	The revenue reporting group code associated with the client service	The RRG code associated with the PATID/episode number and program the service(s) were posted to, as obtained from the table_program_definition table.
rrg_value	The revenue reporting group value associated with the client service	The RRG value associated with the PATID/episode number and program the service(s) were posted to, as obtained from the table_program_definition table.
SERVICE_CODE	The service code of the client service	The service code associated with the service, as obtained from the billing_tx_history table.
service_description	The service code description of the client service	The service code description associated with the service, as obtained from the billing_tx_history table.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the billing_tx_history table.
client_name	The client name	The client name associated with the PATID.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The episode number associated with the posted service(s), as obtained from the billing_tx_history table.
PROVIDER_ID	The practitioner ID of the client service	The practitioner ID associated with the client and service, as obtained from the billing_tx_history table.
provider_name	The practitioner name of the client service	The practitioner name associated with the client and service, as obtained from the billing_tx_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Earned Income form.
compile_start_month	Used for ERS compile tracking	The month entered in the From Month / Year field in the ERS Earned Income form, that is used as the earliest month and year to include in the compile.
compile_end_month	Used for ERS compile tracking	The month entered in the Through Month / Year field in the ERS Earned Income form, that is used as the latest month and year to include in the compile.
compile_description	Used for ERS compile tracking	The Compile Description assigned to the selected compile through the ERS Earned Income form.
COMPILEID	Used for ERS compile tracking	The ERS compile batch number assigned to the selected compile through the ERS Earned Income form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Earned Income form.

ERS.billing_earned_income

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code associated with the compile selected in the ERS Earned Income Compile field in the ERS Earned Income Report form.
COMPILE_ID	Used for ERS compile tracking	The ERS compile batch number associated with the compile selected in the ERS Earned Income Compile field in the selected ERS Earned Income Report form.
from_month_year	The earliest month and year included in the compile	The date entered in the From Month/Year field in the ERS Earned Income form.
through_month_year	The latest month and year included in the compile	The date entered in the Through Month/Year field in the ERS Earned Income form.

ERS Daily Summary

The ERS Daily Summary compiles information for the previous 24 hours prior to compile generation.

ERS.daily_summary_coll

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_pay_adj_history table.
adjustment_amount	The amount entered in any cash posting form for contractual adjustment posting codes	The adjustment amount as obtained from the billing_pay_adj_history table.
chg_rev_amount	The amount entered in any cash posting form for reversal posting codes	The charge reversal amount as obtained from the billing_pay_adj_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Daily Summary form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Daily Summary form.
contr_allow_amount	The amount entered in any cash posting form for contractual adjustment posting codes	The contractual allowance amount as obtained from the billing_pay_adj_history table.
effective_date	The effective date of the compile	The effective date of the compile, as obtained from the facility’s system.
payment_amount	The amount entered in any cash posting form	The submitted payment for the service, as obtained from the billing_pay_adj_history table.
program_code	The program code associated with the client transaction	The program code associated with the posted service(s) as obtained from the billing_pay_adj_history table.
program_value	The program value associated with the client transaction	The program value associated with the posted service(s) as obtained from the billing_pay_adj_history table.
transfer_amount	The amount entered in any cash posting form for transfer posting codes	The transfer amount as obtained from the billing_pay_adj_history table.

ERS.daily_summary_mvmt

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the episode_history table.
active_count	The number of clients active in an episode on the compile date	A calculated field that details the number of active clients associated with the PATID and episode number in a program (episode_history) on the compile date. The clients are active when there is no discharged date or the effective date obtained from the facility’s system is earlier the discharge date obtained in the discharge_data table.
admit_count	The number of clients admitted into a program on the compile date	The number of clients admitted in to a program that day, as obtained from the admission_data table.
ama_discharge_count	The number of client discharges against medical advice on the compile date	The client discharges against medical advice, as obtained from the discharge_data table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Daily Summary form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Daily Summary form.
discharge_count	The number of clients discharged from a program on the compile date	The number of clients discharged from a program, as obtained from the discharge_data table.
effective_date	The effective date of the compile	The effective date of the compile, as obtained from the facility’s system.
late_admit_count	The number of clients with late admissions on the compile run date	A calculated field that details the number of clients where the date entry date is after the entered admission date, as obtained from the admission_data table.
late_discharge_count	The number of clients discharged late from a program on the compile run date	A calculated field that details the number of clients where the date entry date is after the entered discharge date, as obtained from the discharge_data table.
program_code	The program code associated with the client transaction	The program code associated with the PATID and episode number, as obtained from the episode_history table.
program_value	The program value associated with the client transaction	The program value associated with the PATID and episode number, as obtained from the episode_history table.
transfer_in_count	The number of client transfers in to a program	The number of client transfers in to a program, as obtained from the history_program_transfer table.
transfer_out_count	The number of client transfers out of a program	The number of client transfers out of a program, as obtained from the history_program_transfer table.

ERS.daily_summary_visit

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_tx_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Daily Summary form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Daily Summary form.
cost_of_service	The cost of the client service entered in any cash posting form	A calculated field that details the total cost for all client services for a given program that occurred during the reporting period, as obtained from the billing_tx_history table.
effective_date	The effective date of the compile	The effective date of the compile, as obtained from the facility’s system.
program_code	The program code associated with the client transaction	The program code associated with the posted service, as obtained from the billing_tx_history table.
program_value	The program value associated with client transaction	The program value associated with the posted service, as obtained from the billing_tx_history table.
visit_count	The total number of client visits for the compile run date	A calculated field that details the total number of client services defined as visits (billing_tx_history) that occurred during the reporting period, as obtained from the billing_tx_master_table table.

Daily Detail Compiles

The ERS Daily Summary compiles compile information for the previous 24 hours prior to compile generation.

ERS.daily_detail_visit

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The episode number associated with the posted service(s), as obtained from the billing_tx_history table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_tx_history table.
JOIN_TO_TX_HISTORY	A SQL join to another table.	A SQL join to the billing_tx_history table.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the billing_tx_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Daily Summary form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Daily Summary form.
cost_of_service	The cost of the client service entered in any cash posting form	The cost of the client service, as obtained from the billing_tx_history table.
effective_date	The effective date of the compile	The effective date of the compile, as obtained from the facility’s system.
program_code	The program code associated with the client transaction	The program code associated with the posted service, as obtained from the billing_tx_history table.
program_value	The program value associated with client transaction	The program value associated with the posted service, as obtained from the billing_tx_history table.

ERS.daily_detail_coll

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The episode number associated with the posted service(s), as obtained from the billing_pay_adj_history table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the billing_pay_adj_history table.
GUARANTOR_ID	The guarantor ID associated with the client transaction	The guarantor ID associated with the client/transaction, as obtained from the billing_pay_adj_history table.
GUARANTOR_ID_transfer_to	The guarantor ID associated with the program transferred to	The guarantor ID associated with the program transferred to, as obtained from the billing_pay_adj_history table.
JOIN_TO_TX_HISTORY	A SQL join to another table.	A SQL join to the billing_tx_history table.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the billing_pay_adj_history table.
amount	The amount posted	The submitted payment for the service, as obtained from the billing_pay_adj_history table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Daily Summary form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Daily Summary form.
effective_date	The effective date of the compile	The effective date of the compile, as obtained from the facility’s system.
ers_category_code	The ERS posting code ID	(This SQL field is associated with the ERS Posting Code Category field in the Posting / Adjustment Codes Definition form.) The ERS posting category code associated with the posting code, as obtained from the billing_posting_codes table.
ers_category_value	The ERS posting code value	(This SQL field is associated with the ERS Posting Code Category field in the Posting / Adjustment Codes Definition form.) The ERS posting category value associated with the posting code, as obtained from the billing_posting_codes table.
financial_class_code	The financial class code of the guarantor in any cash posting form	The financial class code for the guarantor associated with the client/transaction (billing_pay_adj_history) as obtained from the billing_guar_table.
financial_class_value	The financial class value of the guarantor in any cash posting form	The financial class value for the guarantor associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_guar_table.
guarantor_name	The guarantor name associated with the client transaction	The guarantor name associated with the client/transaction (billing_pay_adj_history), as obtained from the billing_guar_table table.
guarantor_name_trans_to	The guarantor name associated with the program transferred to	The guarantor name associated with the program transferred to (billing_tx_history), as obtained from the billing_guar_table table.
payment_type_code	The payment type code associated with any cash posting form	The submitted payment posting code for the transaction, as obtained from the billing_pay_adj_history table.
payment_type_value	The payment type name associated with any cash posting form	The submitted payment posting code value for the transaction, as obtained from the billing_pay_adj_history table.
program_code	The program code associated with the client transaction	The program code of the transaction that the service(s) were posted to, as obtained from the billing_pay_adj_history table.
program_value	The program value associated with the client transaction	The program value of the transaction that the service(s) were posted to, as obtained from the billing_pay_adj_history table.
summary_category	The summary of ERS posting code category codes associated with the cash posting	The summary of the posting codes defined as ERS posting code category codes (billing_pay_adj_history) that occurred during the reporting period, as obtained from the billing_posting_codes table. (ERS posting code category codes are associated with the ERS Posting Code Category field in the Posting / Adjustment Codes Definition form.)

ERS.daily_detail_coll_tra

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The client episode number associated with the posted service(s) as obtained from the history_program_transfer table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the history_program_transfer table.
JOIN_TO_TRAuniqueid	A SQL join to another table.	A SQL join to the TRAuniqueid SQL field in the history_program_transfer SQL table.
PATID	The client ID	The client ID associated with the episode number that the service(s) were posted to, as obtained from the history_program_transfer table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Daily Summary form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch was created through the ERS Daily Summary form.
effective_date	The effective date of the compile	The effective date of the compile, as obtained from the facility’s system.
from_program_code	The program code of the program transferred from	The program code of the program transferred from, as obtained from the history_program_transfer table.
from_program_value	The program value of program transferred from	The program value of the program transferred from, as obtained from the history_program_transfer table.
to_program_code	The program code of program transferred to	The program code of the program transferred to, as obtained from the history_program_transfer table.
to_program_value	The program value of program transferred to	The program value of the program transferred to, as obtained from the history_program_transfer table.

ERS.daily_detail_mvmt

SQL Field Name	Description	Data Source
ID	An unique SQL row ID	An automatically assigned number that corresponds to a single compile.
EPISODE_NUMBER	The client episode number assigned in the Pre Admit, Admission or Admission (Outpatient) form	The client episode number associated with the program, as obtained from the episode_history table.
FACILITY	The facility ID	The system code where the compiled data was obtained, from the episode_history table.
JOIN_TO_ADMLINK	A SQL join to another table.	A SQL join to the ADM_LINK SQL field in the admission_data_other SQL table.
JOIN_DISuniqueid	A SQL join to another table.	A SQL join to the DIS_uniqueid SQL field in the discharge_data SQL table.
PATID	The client ID	The client ID associated with the episode number and program, as obtained from the episode_history table.
active	The number of active clients in an episode on the date of the compile	A calculated field that details the number of active clients associated with the PATID and episode number in a program (episode_history) on the compile date. The clients are active when there is no discharged date or the effective date obtained from the facility’s system is earlier the discharge date obtained in the discharge_data table.
compile_date	Used for ERS compile tracking	The date that the selected ERS compile batch was created through the ERS Daily Summary form.
compile_time	Used for ERS compile tracking	The time that the selected ERS compile batch compile was created through the ERS Daily Summary form.
effective_date	The effective date of the compile	The effective date of the compile, as obtained from the facility’s system.
program_code	The program code associated with the client transaction	The program code associated with the PATID and episode number, as obtained from the episode_history table.
program_value	The program value associated with client transaction	The program value associated with the PATID and episode number, as obtained from the episode_history table.