Groups, Clients & Benefit Plans

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

benefit_coverage_type_cd

Used by modules: base, funding

(referenced in SQL; no column details captured)

benefit_def_diags

Used by modules: benplans, clmsproc

Column	Declared type
benefit_def_sys_key	decimal(0), number
diag_cd_type	char(10)
diag_criterion	char(200), char(2000)

benefit_def_procs

Used by modules: benplans, clmsproc

Column	Declared type
benefit_def_sys_key	decimal(0), number
proc_cd_type	char(10)
proc_criterion	char(200), char(2000)

benefit_defs

Used by modules: benplans, bensupport, chkrundw, claims, clmentry, clmrpt, clmsproc, clmutils, pricing, sales, stoploss, um_cm, um_main, um_ur

Column	Declared type
benefit_def_cd	char(15)
benefit_def_criterion	char(2000)
benefit_def_sys_key	decimal(0), number
create_dt	datetime
create_user_id	char(8)
description	char(40)
diag_group_criterion	char(2000)
inpat_outpat_indicator	char(1)
modifier_criterion	char(2000)
pos_criterion	char(2000)
proc_group_criterion	char(2000)
revenue_criterion	char(2000)
short_desc	char(15)
specialty_criterion	char(2000)
updt_dt	datetime
updt_user_id	char(8)

benefit_pkgs

Used by modules: benplans, bensupport, claims, clmsproc

Column	Declared type
benefit_coverage_type_cd	char(10)
benefit_pkg_cd	char(10), char(15)
benefit_pkg_sys_key	decimal(0), number
create_dt	datetime
create_user_id	char(8)
default_benefit_def_sys_key	decimal(0), number
description	char(40)
extraction_flg	char(1)
pricing_plan_sys_key	char(15), decimal(0)
procedure_flg	char(1)
second_opinion_sys_key	decimal(0), number
unassigned_action_cd	char(10)
unassigned_reason_sys_key	decimal(0), number
updt_dt	datetime
updt_user_id	char(8)

benefit_plans

Used by modules: base, benplans, bensupport, billing, clmprint, clmrpt, clmsproc, enrollutils, enrolrpt, um_main, um_ur

Column	Declared type
appeal_period_length	decimal(0), number
appeal_period_type_cd	char(10)
auto_assignment_ind	char(1)
benefit_coverage_cd	char(15)
benefit_coverage_type_cd	char(10)
cob_method_cd	char(10)
create_dt	datetime
create_user_id	char(8)
dep_age_non_student	decimal(0)
dep_age_student	decimal(0)
description	char(40)
filing_period_length	decimal(0), number
filing_period_type_cd	char(10)
lifetime_max_amt	decimal(2)
pkg_logic_cd	char(10)
plan_status_cd	char(10)
pre_ex_dep_elapsed_length	decimal(0)
pre_ex_dep_elapsed_type_cd	char(10)
pre_ex_dep_prior_length	decimal(0)
pre_ex_dep_prior_type_cd	char(10)
pre_ex_sub_elapsed_length	decimal(0)
pre_ex_sub_elapsed_type_cd	char(10)
pre_ex_sub_prior_length	decimal(0)
pre_ex_sub_prior_type_cd	char(10)
updt_dt	datetime
updt_user_id	char(8)
wdi_pmt_sched_cd	char(10)

benefit_rule_tooth_types

Used by modules: benplans, bensupport

Column	Declared type
benefit_rule_sys_key	number
tooth_type_sys_key	number

benefit_rules

Used by modules: benplans, bensupport, chkrundw, clmentry, clmprint, clmrpt, clmshare, clmsproc, clmutils, provider, sales, stoploss

Column	Declared type
accident_indicator	char(1)
accum_cd	char(10)
accum_sys_key	decimal(0), number
accum_type_cd	char(10)
benefit_rule_cd	char(15)
benefit_rule_sys_key	decimal(0), number
carryover_rule_sys_key	decimal(0), number
consider_pct	decimal(2), number
contingency_ind	char(1)
contingency_period_length	decimal(0)
contingency_period_type_cd	char(10)
create_dt	datetime
create_user_id	char(8)
days_of_week_cd	char(10)
description	char(40)
family_max_val	decimal(2), number
individual_max_val	decimal(2), number
level_sched_flg	char(1)
level_sched_sys_key	decimal(0), number
max_age_period_length	decimal(0), number
max_age_period_type_cd	char(10)
max_consider_amt	decimal(2), number
max_individuals_met	decimal(0), number
max_units	decimal(0), number
min_age_period_length	decimal(0), number
min_age_period_type_cd	char(10)
min_consider_amt	decimal(2), number
min_units	decimal(0), number
narrative	char(2000)
per_day_cd	char(10)
per_unit_flg	char(1)
qty_ratio	number
qty_type_cd	char(10)
reason_sys_key	decimal(0), number
retain_val	decimal(2), number
rule_period_length	decimal(0), number
rule_period_type_cd	char(10)
rule_type_cd	char(10)
updt_dt	datetime
updt_user_id	char(8)

benefits

Used by modules: client, system

Column	Declared type
benefit_coverage_cd	—
benefit_coverage_desc	—
benefit_coverage_type_cd	—

client_segment_groups

Used by modules: client, clmshare, provider

Column	Declared type
client_segment	char(10)
edit_mode	number
group_id	char(15)

client_segment_users

Used by modules: provider, um_main

Column	Declared type
client_segment	char(10)
name	char(16)

client_segments

Used by modules: client, provider, sales, um_main

Column	Declared type
client_segment	char(10)
client_segment_name	char(50)

coverage_level_cd

Used by modules: base, funding

(referenced in SQL; no column details captured)

coverages

Used by modules: benplans, billfunc, billing, client, cobra, enroll, enrollutils, enrolrpt, funding, stoploss, system

Column	Declared type
benefit_coverage_order	decimal(0)
benefit_coverage_type_cd	char(10)
benefit_coverage_type_desc	char(40)
cobra_elig_flg	char(1)
coverage	char(10)
coverage_flg	char(1)
coverage_level_benefit_flg	char(1)
selected	number

group_ancillary_coverages

Used by modules: client

Column	Declared type
base_dollar_amt	number
benefit_coverage_type_cd	char(10)
commission_pct	number
group_id	char(15)
rate_amt	number
start_dt	datetime

group_ancillary_parms

Used by modules: client

Column	Declared type
carrier_sys_key	number
end_dt	datetime
group_id	char(15)
start_dt	datetime
termination_reason	char(10)

group_contacts

Used by modules: billfunc, client, enroll, homersys, mailobj, stoploss, system, um_cm, um_main

Column	Declared type
addr_no	decimal(0)
col1	char(15)
col2	char(40)
col3	char(25)
col4	char(25)
col5	char(25)
comments	char(100)
confidential_ind	char(1)
contact_cat_cd	char(10)
contact_no	number
email_addr	char(50)
f_name	char(25)
group_id	char(15)
inquiries	char(50)
l_name	char(25)
m_name	char(25)
mail_addr1	char(40)
phone_ext_no	char(6)
phone_no	char(13)
position	char(40)
title_cd	char(10)
update_flg	number

group_coverages

Used by modules: bensupport, billfunc, client, clmrpt, clmsproc, cobra, cobrarpt, drugplan, enroll, enrollutils, enrolrpt, funding, homersys, provider, sales, stoploss, system, um_cm, um_main

Column	Declared type
applied_to_members_dt	datetime
benefit_coverage_cd	char(15)
benefit_coverage_desc	char(50)
benefit_coverage_order	decimal(0)
benefit_coverage_type_cd	char(10)
cobra_only_flg	—
cobra_type_cd	char(10)
coverage_benefit_default_amt	decimal(2)
coverage_benefit_maximum_amt	decimal(2)
coverage_benefit_minimum_amt	decimal(2)
coverage_exp_cd	char(10)
default_election_flg	char(1)
edit_mode	number
eff_dt	datetime
exp_dt	datetime
gc_eff	datetime
gc_exp	datetime
group	char(15)
group_coverage_seq_no	decimal(0)
group_id	char(15)
id_form_no_all	char(10)
id_form_no_back	char(10)
id_form_no_front	char(10)
mbr_cov_applied_cd	char(10)
mbr_cov_reqd_flg	char(1)
pogh_required_flg	—
print_id_card_flg	char(1)
round_to_amt	decimal(2)
rounding_type_cd	char(10)
run_out_exp_dt	datetime
salary_increment_cd	char(10)
salary_multiplier_amt	decimal(2)
tax_status	char(1)
unapplied_chg_flg	char(1)
waiting_period_cd	char(10)
waiting_period_dt_from_cd	—
waiting_period_no	number
waiting_period_type_cd	char(10)

group_enroll_opts

Used by modules: base, client, clmprint, cobra, enroll, um_maint

Column	Declared type
edit_mode	number
eff_dt	datetime
enroll_opt_cd	char(10)
exp_dt	datetime
group_id	char(15)

group_member_cat

Used by modules: billfunc, billing, client, enroll, enrolrpt, homersys

Column	Declared type
applied_to_members_dt	datetime
create_dt	datetime
create_user_id	char(8)
edit_mode	number
group_id	char(15)
mbr_cat_applied_cd	char(10)
mbr_cat_eff_dt	datetime
mbr_cat_exp_dt	datetime
mbr_cat_reqd_flg	char(1)
member_cat_cd	char(10)
number_val	number
selected	number
unapplied_chg_flg	char(1)
updt_dt	datetime
updt_user_id	char(8)
val_saved_as_cd	char(10)

group_option_vals

Used by modules: base, client, enroll, enrolrpt, funding, idcard, provider, um_maint

Column	Declared type
accountnumber	char(50)
eff_dt	datetime
exp_dt	datetime
fromdate	char(10), datetime
group_id	char(15)
group_option_cd	char(10)
option_val_cd	char(10)
option_val_char	char(50)
option_val_dt	datetime
option_val_no	number
plancode	char(50)
thrudate	char(10), datetime

group_options

Used by modules: billfunc, billing, client, homersys

Column	Declared type
code_key	char(10)
group_option_cd	char(10)
group_type	char(10)
number_val	number
selected	number
val_saved_as_cd	char(10)

group_periods

Used by modules: billfunc, client, clmsproc, enrollutils

Column	Declared type
eff_dt	datetime
exp_dt	datetime
extended_year_flg	char(1)
group_id	char(15)
period_beg_day	decimal(0)
period_beg_month	decimal(0)
period_end_day	decimal(0)
period_end_month	decimal(0)
period_type_cd	char(10)

group_print_distribution

Used by modules: claims, reprint

Column	Declared type
group_id	char(15)
option_cd	char(10)
option_cd_type	char(10)

group_stoploss_coverage_rates

Used by modules: client

Column	Declared type
benefit_coverage_type_cd	char(10)
coverage_level_cd	char(10)
group_id	char(15)
rate_amt	number
start_dt	datetime
stoploss_type	char(10)

group_stoploss_coverages

Used by modules: client, stoploss

Column	Declared type
aggr_factor_composite	decimal(2)
aggr_factor_family	decimal(2)
aggr_factor_single	decimal(2)
aggr_factor_tier3	decimal(2)
aggr_factor_tier4	decimal(2)
benefit_coverage_type_cd	char(10)
commission_pct	number
group_id	char(15)
gsc_election_tier3_desc	char(10)
gsc_election_tier4_desc	char(10)
start_dt	datetime
stoploss_type	char(10)
tlo_factor_composite	decimal(2)
tlo_factor_family	decimal(2)
tlo_factor_single	decimal(2)
tlo_factor_tier3	decimal(2)
tlo_factor_tier4	decimal(2)

group_stoploss_parms

Used by modules: client, stoploss, um_main

Column	Declared type
aggr_carrier	number
aggr_contract_inc	number
aggr_contract_paid	number
aggr_min_attchment_pt	decimal(2)
aggr_tlo_ind	char(1)
aggr_tlo_mm	number
agspec_amt	decimal(2)
agspec_ind	char(1)
comp_factor_composite	decimal(2)
comp_factor_family	decimal(2)
comp_factor_single	decimal(2)
comp_factor_tier3	decimal(2)
comp_factor_tier4	decimal(2)
comp_tlo_factor_composite	decimal(2)
comp_tlo_factor_family	decimal(2)
comp_tlo_factor_single	decimal(2)
comp_tlo_factor_tier3	decimal(2)
comp_tlo_factor_tier4	decimal(2)
ded_amt	number
end_dt	datetime
group_id	char(15)
gsp_election_tier3_desc	char(10)
gsp_election_tier4_desc	char(10)
spec_carrier	decimal(0), number
spec_contract_inc	decimal(0), number
spec_contract_paid	decimal(0), number
spec_family_limit	decimal(2)
spec_individual_limit	decimal(2)
spec_tlo_ind	char(1)
spec_tlo_mm	number
start_dt	datetime
termination_reason	char(10)

groups

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

Column	Declared type
'0'	char(1)
addr_no	decimal(0), number
create_dt	—
create_user_id	—
division_name	char(50)
edit_mode	number
eff_dt	datetime
elig_group_name	char(50)
elig_waiting_period_cd	char(10)
elig_waiting_period_dt_from_cd	—
elig_waiting_period_no	number
elig_waiting_period_type_cd	char(10)
exp_dt	datetime
ext_account	char(15)
ext_account_name	char(50)
ext_control	char(15)
ext_elr	char(15)
ext_plan_number	char(15)
ext_suffix	char(15)
fax_phone_no	char(13)
funding_parent_group_id	char(15)
group	char(15)
group_change_ind	number
group_id	char(15)
group_name	char(50)
group_name_cap	char(50)
group_table_id	char(15)
group_type_cd	char(10)
groupid	char(15)
groupname	char(50)
grp_exp_cd	char(10)
grp_info_flg	char(1)
level	number
master_group_flg	char(1)
parent_group	char(15)
parent_group_id	char(15)
parentid	char(15)
parentname	char(50)
payee_sys_key	number
primary_phone_no	char(13)
register_paid_amt	number
register_payable_amt	number
rownum	number
rule_line	number
sfpi_group_id	char(15)
sysdate	datetime
tax_id	char(9)
union_divider	number
updt_dt	—
updt_user_id	—
usa_flag	number

groups_excluded

Used by modules: client

Column	Declared type
group_id	—

plan_pkgs

Used by modules: base, benplans, bensupport, billfunc, claims, clmentry, clmprint, clmrpt, clmshare, clmsproc, enroll, enrolrpt, funding, provider, sales, um_main, um_maint, um_ur

Column	Declared type
benefit_coverage_cd	char(15)
benefit_coverage_type_cd	char(10)
benefit_pkg_sys_key	decimal(0), number
cm_firm_sys_key	decimal(0), number
description	char(40)
eff_dt	datetime
exp_dt	datetime
hcn_sys_key	decimal(0), number
plan	char(15)
plan_pkg_sys_key	decimal(0), number
pricing_logic	char(10)
pricing_logic_cd	char(10)
pricing_plan_sys_key	decimal(0)
search_order_seq	decimal(0)
ur_firm_sys_key	decimal(0), number

rate_groups

Used by modules: billing

Column	Declared type
bill_brk_level_cd	—
bill_destination_cd	—
bill_frequency_cd	—
billing_plan_cd	—
group_id	—
rate_from_dt	—
rate_thru_dt	—
sort_order_cd	—