EDI & Interfaces

Provisional schema

Reconstructed from application binaries (DataWindow bindings and embedded SQL), not from Oracle DDL. Column types are the application's declared types; constraints, indexes, triggers and exact server types are unknown until an Oracle DDL export is reconciled.

Key relationships (inferred from join usage)

Cardinality is indicative only; verify against DDL.

Tables

edi_in_repricing

Used by modules: clmutils, moon

Column	Declared type
claim_id	char(17)
claim_seq	number
claimed_amt	number
from_dt	datetime
line_pricing_amt	number
proc_cd	char(13)
processed_ind	char(1)
reject_cd	char(10)
trading_partner_cd	char(10)
whole_claim_pricing_amt	number

edi_submitter_table

Used by modules: homersys

Column	Declared type
claim_form_type	char(20)
filename	char(25)
first_claim_id	char(17)
last_claim_id	char(17)
load_dt	datetime
number_of_claims	number
process_dt	datetime

hipaa_transactions

Used by modules: enroll

Column	Declared type
dep_member_sys_key	decimal(0)
lock_cd	—
sub_member_sys_key	decimal(0)

outbound_edi_claim_table

Used by modules: clmutils

Column	Declared type
accept_assignment_ind	char(1)
amount_prepaid	decimal(2)
attachment_ind	char(1)
auto_accident_desc	char(80)
auto_accident_dt	datetime
auto_accident_ind	char(1)
auto_accident_state	char(2)
balance_due	decimal(2)
claim_id	char(17)
comments	char(400)
curr_cond_start_dt	datetime
days_allowed	decimal(0)
diag_cd_1	char(7)
diag_cd_2	char(7)
diag_cd_3	char(7)
diag_cd_4	char(7)
diag_cd_desc_1	char(50)
diag_cd_desc_2	char(50)
diag_cd_desc_3	char(50)
diag_cd_desc_4	char(50)
discount_pct	decimal(0)
discount_pct_2	decimal(0)
drg_code	char(3)
employment_accident_desc	char(80)
employment_accident_dt	datetime
employment_accident_ind	char(1)
employment_accident_ind_2	char(1)
first_consult_dt	datetime
from_dt	datetime
grand_total_allow_amt	decimal(2)
grand_total_disc_amt	decimal(2)
hcn_enter_dt	datetime
hcn_print_dt	datetime
hcn_recv_dt	datetime
increase_over_perdiem	decimal(2)
inpat_end_dt	datetime
inpat_start_dt	datetime
max_discount_pct	decimal(2)
mcaid_resub_code	char(20)
mcaid_resub_ref	char(20)
member_not_on_file_ind	char(1)
member_not_on_file_ind_2	char(1)
operative_notes_ind	char(1)
other_accident_date	datetime
other_accident_desc	char(80)
other_accident_ind	char(1)
other_accident_ind_2	char(1)
other_insurance_by_cat	char(25)
outside_lab_chgs	decimal(2)
outside_lab_chgs_2	decimal(2)
outside_lab_ind	char(1)
partially_disabled_from_dt	datetime
partially_disabled_thru_dt	datetime
patient_acct	char(20)
patient_owes_amt	decimal(2)
patient_signature	char(1)
patient_under_care_ind	char(1)
payer_owes_amt	decimal(2)
perdiem_allowed_other	decimal(2)
perdiem_allowed_room	decimal(2)
perdiem_due_from_payer	decimal(2)
perdiem_not_allowed	decimal(2)
perdiem_per_day	decimal(2)
perdiem_total	decimal(2)
pregnancy_dt	datetime
pregnancy_ind	char(1)
prior_authorization_no	char(20)
provider_out_of_network_ind	char(1)
provider_out_of_network_ind_2	char(1)
repriced_amt	decimal(2)
repricing_method	char(10)
resp_pay	char(240)
return_to_work_dt	datetime
return_to_work_dt_2	datetime
returned_to_work_dt	datetime
returned_to_work_ind	char(1)
similar_cond_ind	char(1)
similar_cond_start_desc	char(80)
similar_cond_start_dt	datetime
spouse_employed_ind	char(1)
spouse_employer_addr1	char(40)
spouse_employer_addr2	char(40)
spouse_employer_city	char(40)
spouse_employer_name	char(50)
spouse_employer_state	char(2)
spouse_employer_zip	char(9)
spouse_last_employed_dt	datetime
spouse_work_phone	char(13)
sub_signature	char(1)
thru_dt	datetime
total_allow_amt	decimal(2)
total_charges	decimal(2)
total_chg_not_covered	decimal(2)
total_disc_amt	decimal(2)
total_hosp	decimal(2)
unavailable_to_work_from_dt	datetime
unavailable_to_work_thru_dt	datetime
wdi_ind	char(1)

outbound_edi_clearinghouses

Used by modules: provider

Column	Declared type
clearinghouse_id	char(10)
default_data_file_location	char(40)
name	char(50)
status_cd	char(10)

outbound_edi_conditions_table

Used by modules: clmutils

Column	Declared type
claim_id	char(17)
condition_cd	char(6)
seq_no	decimal(0)

outbound_edi_dental_table

Used by modules: clmutils

Column	Declared type
carrier_addr1	char(40)
carrier_addr2	char(40)
carrier_city	char(40)
carrier_name	char(50)
carrier_state	char(2)
carrier_zip	char(9)
claim_id	char(17)
employee_signature	char(1)
first_appliance_dt	datetime
initial_placement_ind	char(1)
number_of_xrays	number
orthodontic_purposes_ind	char(1)
replacement_proc_cd	char(13)
replacement_proc_cd_dt	datetime
replacement_reason	char(80)
submission_reason	char(10)
total_fees	decimal(2)
treatment_from_dt	datetime
treatment_remaining_months	number
treatment_thru_dt	datetime
xray_included_ind	char(1)

outbound_edi_line_table

Used by modules: clmutils

Column	Declared type
allowed_amt	decimal(2)
claim_id	char(17)
claim_seq	number
claimant_age	number
claimant_f_name	char(25)
claimant_l_name	char(25)
claimant_m_name	char(25)
claimed_amt	decimal(2)
days_supply	number
dea_number	char(15)
diagnosis_ref	char(20)
directions	char(50)
dosage	char(10)
emerg_room_treatment	char(10)
epsdt_plan	char(5)
form	char(10)
from_dt	datetime
medicare_prov_id	char(20)
mfg_name	char(50)
non_cov_chg	decimal(2)
place_of_service_cd	char(2)
proc_cd	char(13)
proc_cd_type	char(10)
proc_desc	char(60)
proc_modifier1	char(2)
proc_modifier2	char(2)
provider	char(40)
provider_tin	char(9)
rate_charged_amt	decimal(2)
refill_ind	char(1)
reprice_allow_extended	decimal(2)
reprice_allow_per_unit	decimal(2)
reprice_discount_amt	decimal(2)
revenue_cd	char(6)
rx_number	char(20)
strength	char(10)
surface	char(5)
thru_dt	datetime
tooth_from	char(2)
tooth_thru	char(2)
type_of_service_cd	char(1)
units	decimal(2)
unlabeled_49	char(20)

outbound_edi_occurrences_table

Used by modules: clmutils

Column	Declared type
claim_id	char(17)
occurrence_cd_1	char(6)
occurrence_cd_2	char(6)
occurrence_cd_3	char(6)
occurrence_cd_4	char(6)
occurrence_cd_dt_1	datetime
occurrence_cd_dt_2	datetime
occurrence_cd_dt_3	datetime
occurrence_cd_dt_4	datetime
occurrence_span_cd	char(6)
occurrence_span_from_dt	datetime
occurrence_span_to_dt	datetime
seq_no	decimal(0)

outbound_edi_patient_table

Used by modules: clmutils

Column	Declared type
addr1	char(40)
addr2	char(40)
age	number
birth_dt	datetime
city	char(40)
claim_id	char(17)
dep_member_sys_key	decimal(0)
employment_status	char(10)
f_name	char(25)
gender	char(1)
l_name	char(25)
m_name	char(25)
maritial_status	char(10)
phone	char(13)
relationship	char(10)
school_addr1	char(40)
school_addr2	char(40)
school_city	char(40)
school_name	char(50)
school_zip	char(9)
scool_state	char(2)
state	char(2)
student_status	char(10)
zip	char(9)

outbound_edi_procedures_table

Used by modules: clmutils

Column	Declared type
claim_id	char(17)
principal_cd_ind	char(1)
proc_cd	char(6)
proc_cd_dt	datetime
seq_no	decimal(0)

outbound_edi_provider_table

Used by modules: clmutils

Column	Declared type
attending_provider_addr1	char(40)
attending_provider_addr2	char(40)
attending_provider_city	char(40)
attending_provider_degree	char(10)
attending_provider_f_name	char(25)
attending_provider_l_name	char(25)
attending_provider_m_name	char(25)
attending_provider_phone	char(13)
attending_provider_state	char(2)
attending_provider_tin	char(9)
attending_provider_tin_type	char(10)
attending_provider_zip	char(9)
bill_provider_addr1	char(40)
bill_provider_addr2	char(40)
bill_provider_city	char(40)
bill_provider_degree	char(10)
bill_provider_f_name	char(25)
bill_provider_facility_name	char(50)
bill_provider_l_name	char(25)
bill_provider_m_name	char(25)
bill_provider_phone	char(13)
bill_provider_pin	char(20)
bill_provider_state	char(2)
bill_provider_sys_key	decimal(0)
bill_provider_tin	char(9)
bill_provider_tin_type	char(10)
bill_provider_zip	char(9)
claim_id	char(17)
perf_provider_degree	char(10)
perf_provider_f_name	char(40)
perf_provider_l_name	char(40)
perf_provider_m_name	char(40)
ref_provider_addr1	char(40)
ref_provider_addr2	char(40)
ref_provider_city	char(40)
ref_provider_degree	char(10)
ref_provider_f_name	char(25)
ref_provider_l_name	char(25)
ref_provider_m_name	char(25)
ref_provider_state	char(2)
ref_provider_tin	char(9)
ref_provider_tin_type	char(10)
ref_provider_zip	char(9)

outbound_edi_recipients

Used by modules: clmutils

Column	Declared type
id	number
recipient_id	number

outbound_edi_subscriber_table

Used by modules: clmutils

Column	Declared type
claim_id	char(17)
sub_addr1	char(40)
sub_addr2	char(40)
sub_city	char(40)
sub_dob	datetime
sub_employer_addr1	char(40)
sub_employer_addr2	char(40)
sub_employer_city	char(40)
sub_employer_name	char(50)
sub_employer_state	char(2)
sub_employer_zip	char(9)
sub_f_name	char(25)
sub_gender	char(1)
sub_l_name	char(25)
sub_m_name	char(25)
sub_member_sys_key	decimal(0)
sub_phone	char(13)
sub_state	char(2)
sub_zip	char(9)

outbound_edi_trading_partners

Used by modules: claims, moon, provider

Column	Declared type
allowance_days	number
clearinghouse_id	char(10)
name	char(50)
no_reply_action_cd	char(10)
partner_type_cd	char(10)
pay_by_days	number
retention_days	number
routing_number	char(20)
status_cd	char(10)
trading_partner_id	char(10)

outbound_edi_transactions

Used by modules: claims, clmrpt, clmutils

Column	Declared type
claim_form_type	char(20)
claim_id	char(17)
create date	datetime
create_dt	datetime
create_user_id	char(8)
file_created	char(1)
hcn_sys_key	number
processed_dt	datetime
trading_partner_id	char(10)

outbound_edi_ub92_table

Used by modules: clmutils

Column	Declared type
admit_diag_code	char(7)
admit_hr	number
admit_src	char(1)
admit_type	char(1)
bill_type	number
claim_end_dt	datetime
claim_id	char(17)
claim_start_dt	datetime
coins_days	number
cov_days	number
date_send	datetime
diag_cd_5	char(6)
diag_cd_6	char(6)
diag_cd_7	char(6)
diag_cd_8	char(6)
diag_cd_9	char(6)
diag_e_code	char(7)
disch_hr	number
disch_status	char(10)
document_control_no	number
lifersrv_days	number
medical_record_number	char(17)
non_cov_days	number
other_phys_f_name_1	char(25)
other_phys_f_name_2	char(25)
other_phys_id_1	char(20)
other_phys_id_2	char(20)
other_phys_l_name_1	char(25)
other_phys_l_name_2	char(25)
other_phys_m_name_1	char(25)
other_phys_m_name_2	char(25)
proc_code_type	char(10)
prov_rep	char(30)
remarks	char(192)
unlabeled_11	char(20)
unlabeled_2	char(20)
unlabeled_31	char(20)
unlabeled_56	char(20)
unlabeled_57	char(20)
unlabeled_78	char(20)

outbound_edi_values_table

Used by modules: clmutils

Column	Declared type
claim_id	char(17)
seq_no	decimal(0)
value_amt	number
value_cd	char(3)