Members, Enrollment & Eligibility

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

election_coverages

Used by modules: base, client, clmshare, cobra, cobradw, cobrarpt, drugplan, enrolaud, enroll, enrollutils, enrolrpt, funding, homersys, provider, stoploss, system, um_main, um_maint, um_ur

Column	Declared type
audit_eff_dt	datetime
benefit_coverage_cd	char(15)
benefit_coverage_type_cd	char(10)
benefitcoveragecd	char(15)
benefitcoveragetypecd	char(10)
coveragelevelcd	char(0)
create_dt	datetime
create_user_id	—
dep_member_sys_key	decimal(0)
depmembersyskey	decimal(0)
eceffdt	datetime
ecexpdt	datetime
effdt	datetime
elect_seq_no	decimal(0)
expdt	datetime
me_eff_dt	datetime
member_add_eff_dt	datetime
member_dental_eff_dt	datetime
member_depadd_eff_dt	datetime
member_depcare_eff_dt	datetime
member_deplife_eff_dt	datetime
member_drug_eff_dt	datetime
member_elect_eff_dt	datetime
member_elect_exp_cd	char(10)
member_elect_exp_dt	datetime
member_elect_status_cd	char(10)
member_life_eff_dt	datetime
member_medical_eff_dt	datetime
member_medreim_eff_dt	datetime
member_spoadd_eff_dt	datetime
member_spolife_eff_dt	datetime
member_supadd_eff_dt	datetime
member_suplife_eff_dt	datetime
member_vision_eff_dt	datetime
min_audit_dt	datetime
qmcso_order_no	decimal(0)
rownum	number
sub_member_sys_key	decimal(0)
submembersyskey	decimal(0)

election_riders

Used by modules: enroll

Column	Declared type
sub_member_sys_key	—

elections

Used by modules: base, billfunc, billing, client, clmsproc, clmutils, cobra, cobradw, cobrarpt, drugplan, enrolaud, enroll, enrollutils, enrolrpt, funding, homersys, idcard, provider, stoploss, system, um_main, um_maint

Column	Declared type
addamt	decimal(2)
audit_eff_dt	datetime
beneficiary_name	char(100), char(50)
benefit_coverage_cd	char(15)
benefit_coverage_type_cd	char(10)
benefitcoveragetypecd	char(10)
cobra_flg	char(1)
copies	char(0)
coverage	char(10)
coverage_benefit_amt	decimal(2)
coverage_level_cd	char(10)
coveragebenefitamt	decimal(2)
coveragelevelcd	char(10)
create_dt	datetime
create_user_id	—
credits	number
current_coverage_cd	char(15)
current_level_cd	char(10)
dentallevel	char(10)
dentalplan	char(15)
druglevel	char(10)
drugplan	char(15)
edit_mode	number
ee_add_beneficiary	char(100)
ee_add_coverage_amt	decimal(2)
ee_add_eff_dt	datetime
ee_dental_coverage_level	char(10)
ee_dental_eff_dt	datetime
ee_depadd_beneficiary	char(100)
ee_depadd_coverage_amt	decimal(2)
ee_depadd_eff_dt	datetime
ee_depcare_beneficiary	char(100)
ee_depcare_coverage_amt	decimal(2)
ee_depcare_eff_dt	datetime
ee_deplife_beneficiary	char(100)
ee_deplife_coverage_amt	decimal(2)
ee_deplife_eff_dt	datetime
ee_drug_coverage_level	char(10)
ee_drug_eff_dt	datetime
ee_life_beneficiary	char(100)
ee_life_coverage_amt	decimal(2)
ee_life_eff_dt	datetime
ee_medical_coverage_level	char(10)
ee_medical_eff_dt	datetime
ee_medreim_beneficiary	char(100)
ee_medreim_coverage_amt	decimal(2)
ee_medreim_eff_dt	datetime
ee_spoadd_beneficiary	char(100)
ee_spoadd_coverage_amt	decimal(2)
ee_spoadd_eff_dt	datetime
ee_spolife_beneficiary	char(100)
ee_spolife_coverage_amt	decimal(2)
ee_spolife_eff_dt	datetime
ee_supadd_beneficiary	char(100)
ee_supadd_coverage_amt	decimal(2)
ee_supadd_eff_dt	datetime
ee_suplife_beneficiary	char(100)
ee_suplife_coverage_amt	decimal(2)
ee_suplife_eff_dt	datetime
ee_vision_coverage_level	char(10)
ee_vision_eff_dt	datetime
elect_eff_dt	datetime
elect_exp_cd	char(10)
elect_exp_dt	datetime
elect_seq_no	decimal(0)
elect_status_cd	char(10)
hipaa_print_flg	char(1)
level	char(10)
lifeamt	decimal(2)
medlevel	char(10)
medplan	char(15)
member_cat_val_no	number
min_audit_dt	datetime
null	char(0)
paid	number
rownum	number
sub_member_sys_key	decimal(0)
suplife	decimal(2)
total_spent	number

family

Used by modules: base, claims, client, clmforms, clmrpt, clmsproc, cob, cobra, cobrarpt, drugplan, enrolaud, enroll, enrollutils, enrolrpt, homersys, preex, stoploss, svclog, system, um_main, um_maint

Column	Declared type
dep_member_sys_key	decimal(0), number
sfpi_ee_sys_key	decimal(0)
sfpi_member_sys_key	decimal(0)
sub_member_sys_key	decimal(0), number

member

Used by modules: claims, enrolrpt

(referenced in SQL; no column details captured)

member_accum_credits

Used by modules: claims, clmrpt, clmsproc, clmutils, enrollutils, homersys

Column	Declared type
accm_sys_key	decimal(0)
carryover_consider_amt	decimal(2)
carryover_qty	decimal(0)
carryover_retain_amt	decimal(2)
consider_amt	decimal(2)
create_dt	datetime
create_user_id	char(8)
credit_comment	char(2000)
credit_dt	datetime
dep_member_sys_key	decimal(0)
member_accum_credit_sys_key	decimal(0)
qty	decimal(0)
retain_amt	decimal(2)
sub_member_sys_key	decimal(0)
updt_dt	datetime
updt_user_id	char(8)

member_accums

Used by modules: claims, clmrpt, clmsproc, clmutils, homersys

Column	Declared type
accum_sys_key	number
carryover_consider_amt	decimal(2)
carryover_qty	decimal(0)
carryover_retain_amt	decimal(2)
carryover_retain_qty	decimal(2)
consider_amt	decimal(2)
credit_carryover_consider_amt	number
credit_carryover_qty	number
credit_carryover_retain_amt	number
credit_carryover_retain_qty	number
credit_consider_amt	number
credit_consider_in	number
credit_qty	number
credit_qty_in	number
credit_retain_amt	number
credit_retain_in	number
credit_retain_qty	number
credit_retain_qty_in	number
dep_member_sys_key	number
from_dt	datetime
member_accum_sys_key	number
qty	decimal(0)
retain_amt	decimal(2)
retain_qty	decimal(2)
sub_member_sys_key	number
thru_dt	datetime

member_cat_val

Used by modules: base, billfunc, claims, client, clmrpt, clmshare, clmsproc, clmutils, cobra, cobrarpt, drugplan, enrolaud, enroll, enrolrpt, funding, homersys, provider, sales, stoploss, um_cm, um_main, um_maint

Column	Declared type
audit_eff_dt	datetime
create_dt	datetime
create_user_id	—
dep_member_sys_key	decimal(0)
mbr_cat_val_eff_dt	datetime
mbr_cat_val_exp_dt	datetime
member_cat_cd	—
member_cat_val_cd	char(10)
member_cat_val_char	char(50)
member_cat_val_dt	datetime
member_cat_val_no	—
min_audit_dt	datetime
relation	char(10)
rownum	number
sub_member_sys_key	decimal(0)
updt_dt	—
updt_user_id	—

member_eligibilities

Used by modules: base, billfunc, billing, client, clmrpt, clmsproc, clmutils, cobra, cobradw, cobrarpt, drugplan, enrolaud, enroll, enrollutils, enrolrpt, funding, homersys, idcard, preex, provider, stoploss, system, um_cm, um_main, um_maint, um_ur

Column	Declared type
'0'	char(1)
audit_eff_dt	datetime
cobra_rights_sent_dt	datetime
create_dt	datetime
create_user_id	—
dep_exp_cd	char(10)
dep_member_sys_key	decimal(0), number
depmembersyskey	decimal(0)
division	char(15)
edit_mode	number
eff_dt	datetime
elig_group_id	char(15)
eligibility_from_dt	datetime
eligibility_thru_dt	datetime
exp_dt	datetime
group_id	char(15)
meeffdt	datetime
meexpdt	datetime
megroupid	char(15)
member_eligibility_eff_dt	datetime
min_audit_dt	datetime
qmcso_flg	char(1)
rownum	number
sub_member_sys_key	decimal(0), number
submembersyskey	decimal(0)

member_pcps

Used by modules: clmsproc, enroll, enrolrpt, idcard

Column	Declared type
addr_no	number
benefit_coverage	char(10)
benefit_coverage_type_cd	char(10)
dep_member_sys_key	—
eff_dt	datetime
exp_dt	datetime
member_pcp_key	number
member_pcp_sys_key	number
pcp_type_cd	char(10)
provider_sys_key	number
sub_member_sys_key	number

member_waiting_periods

Used by modules: enrolaud, enroll, homersys, system

Column	Declared type
audit_eff_dt	datetime
create_dt	datetime
create_user_id	—
edit_mode	number
eff_dt	datetime
exp_dt	datetime
member_sys_key	decimal(0)
min_audit_dt	datetime
rownum	number
waiting_period_start_dt	datetime

members

Used by modules: base, billfunc, billing, chkrundw, claims, client, clmentry, clmforms, clmprint, clmrpt, clmsproc, clmutils, cob, cobra, cobradw, cobrarpt, drugplan, enrolaud, enroll, enrollutils, enrolrpt, funding, homersys, idcard, mailobj, preex, provider, reprint, sales, stoploss, svclog, system, um_cm, um_main, um_maint, um_ur

Column	Declared type
'0'	char(1)
addr_no	decimal(0)
audit_eff_dt	datetime
bill_date	datetime
birth_dt	datetime
create_dt	datetime
create_user_id	char(8)
date1	datetime
dep_f_name	char(25)
dep_first_name	char(25)
dep_l_name	char(25)
dep_last_name	char(25)
dep_m_name	char(25)
dep_member_id	char(15)
dep_member_sys_key	decimal(0)
dep_middle_name	char(25)
dep_soc_sec_no	char(9)
edit_mode	number
ee_dob	datetime
ee_f_name	char(25)
ee_gender	char(10)
ee_l_name	char(25)
ee_m_name	char(25)
ee_phone	char(13)
ee_ssn	char(9)
ext_member_id	char(20)
f_name	char(25)
f_name_cap	char(25)
fname	char(25)
gender_cd	char(10)
group_id	char(15)
hire_date	datetime
hire_dt	datetime
l_name	char(25)
l_name_cap	char(25)
life_status_cd	char(10)
lname	char(25)
m_name	char(25)
m_name_cap	char(25)
master_group	char(15)
master_group_id	char(15)
mbr_term_dt	datetime
member_cd	number
member_dob	datetime
member_f_name	char(25)
member_gender	char(10)
member_id	char(15)
member_key	decimal(0)
member_l_name	char(25)
member_m_name	char(25)
member_member_id	char(15)
member_phone	char(13)
member_ssn	char(9)
member_ssn_d	char(9)
member_sys_key	decimal(0), number
memberdep	decimal(0)
memberid	char(15)
membersub	decimal(0)
memsyskey	decimal(0)
min_audit_dt	datetime
other_master_group_id	char(15)
other_member_id	char(15)
other_member_sys_key	number
pat_name	char(25)
patient_f_name	char(25)
patient_gender	char(10)
patient_l_name	char(25)
patient_m_name	char(25)
patient_member_id	char(15)
patient_soc_sec_no	char(9)
phone_no	char(13)
rate	number
rownum	number
sfpi_ee_member_id	char(15)
show_total	number
soc_sec_no	char(9)
social_security_no	char(9)
social_security_no_d	char(9)
ssn_alt_id_number	char(9)
ssno	char(9)
sub_f_name	char(25)
sub_first_name	char(25)
sub_id	char(15)
sub_l_name	char(25)
sub_last_name	char(25)
sub_m_name	char(25)
sub_member_id	char(15)
sub_member_sys_key	decimal(0)
sub_middle_name	char(25)
sub_soc_sec_no	char(9)
subscriber_f_name	char(25)
subscriber_key	decimal(0)
subscriber_l_name	char(25)
subscriber_lname	char(25)
subscriber_m_name	char(25)
subscriber_master_group_id	char(15)
subscriber_member_id	char(15)
subscriber_soc_sec_no	char(9)
sys_key	decimal(0), number
tpamemberid	char(15)
updt_dt	datetime
updt_user_id	char(8)
usa_flag	number

members_included

Used by modules: homersys

Column	Declared type
member_sys_key	—

mmo_enrollment_work

Used by modules: provider

Column	Declared type
action_cd	char(1)
addr1	char(40)
addr2	char(40)
birth_dt	datetime
city	char(40)
contract_type	char(2)
coverage_level_cd	char(10)
dep_member_id	char(15)
dep_member_sys_key	number
dep_ssnumber	char(9)
eff_dt	datetime
exp_cd	char(10)
exp_dt	datetime
f_name	char(25)
gender	char(10)
hire_dt	datetime
homer_group_id	char(15)
l_name	char(25)
m_name	char(25)
marital_status	char(1)
plan_type	char(1)
ppo_group	char(6)
ppo_group_section	char(3)
record_type	char(1)
relationship_cd	char(10)
run_seq_no	number
state	char(2)
sub_member_id	char(15)
sub_member_sys_key	number
sub_ssnumber	char(9)
zip	char(9)

ppo_enrollment_work

Used by modules: provider

Column	Declared type
action_cd	char(1)
addr1	char(40)
addr2	char(40)
birth_dt	datetime
city	char(40)
contract_type	char(2)
coverage_level_cd	char(10)
dep_member_id	char(15)
dep_member_sys_key	number
dep_ssnumber	char(9)
eff_dt	datetime
exp_dt	datetime
f_name	char(25)
gender	char(10)
hire_dt	datetime
homer_group_id	char(15)
l_name	char(25)
m_name	char(25)
marital_status	char(1)
plan_type	char(1)
ppo_group	char(6)
ppo_group_section	char(3)
record_type	char(1)
relationship_cd	char(10)
run_seq_no	number
state	char(2)
sub_member_id	char(15)
sub_member_sys_key	number
sub_ssnumber	char(9)
zip	char(9)

students

Used by modules: clmutils, drugplan, enrolaud, enroll, homersys

Column	Declared type
addr_no	decimal(0)
audit_eff_dt	datetime
create_dt	datetime
create_user_id	—
edit_mode	number
eff_dt	datetime
exp_dt	datetime
member_sys_key	decimal(0)
min_audit_dt	datetime
other_insurance_ind	char(1)
phone_ext	char(5)
phone_no	char(13)
rownum	number
school_name	char(50)
student_spouse_ind	char(10)
student_status_cd	char(10)