Utilization & Case Management

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

cm_billing

Used by modules: um_cm, um_ur

Column	Declared type
billing_status_cd	char(10)
billing_type_cd	char(10)
case_status_cd	char(10)
case_sys_key	char(16)
cm_billing_sys_key	decimal(0)
create_user_id	char(8)
elapsed_time	decimal(0)
start_dt	datetime

cm_case_involvements

Used by modules: um_cm

Column	Declared type
case_sys_key	char(16)
cm_involvement_cd	char(10)

cm_involvements

Used by modules: um_cm

Column	Declared type
cm_involvement_cd	—
description	char(50)

cm_potentials

Used by modules: um_cm, um_ur

Column	Declared type
cm_case_sys_key	char(16)
cm_potential_sys_key	decimal(0), number
cm_record_link	decimal(0)
comments	char(2000)
create_dt	datetime
create_user_id	char(8)
entity_generator_cd	char(10)
entity_sys_key	decimal(0)
followed_up_by	char(8)
followed_up_dt	datetime
generator_link	char(25)
generator_user_id	char(8)
procedure_status_cd	char(10)
report_dt	datetime
resolution_cd	char(10)
resolution_dt	datetime
resolution_user_id	char(8)
resolved_by	char(50)
status_cd	char(10)
total_claims_paid_amt	decimal(2)
updt_dt	datetime
updt_user_id	char(8)

cm_trigger_diag_codes

Used by modules: um_maint

Column	Declared type
cm_trigger_diag_sys_key	decimal(0)
cm_trigger_sys_key	decimal(0)
diag_cd	char(7)
dollar_threshold	decimal(2)
inpat_outpat	char(1)
network_affiliation	char(1)

cm_trigger_proc_codes

Used by modules: um_maint

Column	Declared type
cm_trigger_proc_sys_key	decimal(0)
cm_trigger_sys_key	decimal(0)
dollar_threshold	decimal(2)
inpat_outpat	char(1)
network_affiliation	char(1)
proc_cd	char(13)
proc_cd_type	char(10)

cm_triggers

Used by modules: um_main, um_maint

Column	Declared type
cm_trigger_sys_key	decimal(0)
description	char(200)
name	char(20)

um_appealer_types

Used by modules: um_main

Column	Declared type
appealer_type_cd	char(10)
description	char(50)

um_benefit_definitions

Used by modules: clmsproc, um_cm, um_main, um_ur

Column	Declared type
benefit_def_sys_key	decimal(0)
service_sys_key	char(16)

um_benefit_holds

Used by modules: um_cm, um_ur

Column	Declared type
benefit_def_sys_key	decimal(0)
um_hold_rule_sys_key	decimal(0)

um_case_dispositions

Used by modules: um_main

Column	Declared type
um_case_disposition_cd	char(10)
um_case_disposition_desc	char(50)

um_case_mgt_claims

Used by modules: claims, clmshare, clmsproc

Column	Declared type
case_sys_key	char(16)
claim_id	char(17)
claim_seq	number
from_dt	datetime
payable_amt	decimal(2)
service_sys_key	char(16)
thru_dt	datetime
um_rule_sys_key	number
units	decimal(2)
units_used	decimal(2)

um_cases

Used by modules: clmsproc, svclog, um_cm, um_main, um_ur

Column	Declared type
apply_to_adjudication_ind	char(1)
benefit_coverage_type_cd	char(10)
case_disposition_cd	char(10)
case_manager_user_id	char(8)
case_sys_key	char(16)
cc_names	char(50)
coinsurance_after_limit_pct	decimal(0)
coinsurance_limit_amt	decimal(2)
coinsurance_pct	decimal(0)
create_dt	datetime
create_user_id	char(8)
deductible_amt	decimal(2)
deductible_type_cd	char(10)
from_dt	datetime
hard_savings	number
late_notification_ind	char(1)
lifetime_maximum_amt	decimal(2)
lock_dt	datetime
lock_seq_no	decimal(0)
lock_user_id	char(8)
motor_vehicle_accident_ind	char(1)
notified_by_cd	char(10)
notified_by_name	char(50)
patient_sys_key	decimal(0)
requestor_addr_no	decimal(0)
requestor_member_ind	char(1)
requestor_name	char(50)
requestor_phone_no	char(13)
response_printed_dt	datetime
soft_savings	number
subscriber_sys_key	decimal(0)
thru_dt	datetime
um_category_cd	char(10)
um_record_status_cd	char(10)
unassigned_birth_dt	datetime
unassigned_f_name	char(25)
unassigned_ind	char(1)
unassigned_l_name	char(25)
unassigned_m_name	char(25)
updt_dt	datetime
updt_user_id	char(8)
workers_compensation_sw	char(1)
yearly_maximum_amt	decimal(2)

um_categories

Used by modules: um_main

Column	Declared type
um_category_cd	char(10)
um_category_name	char(50)

um_cm_possible_case_claim

Used by modules: um_main

Column	Declared type
claim_id	—
claim_seq	—
create_dt	—
message_seq_no	number

um_cm_serv_excl_proc_cd

Used by modules: bensupport, clmsproc

Column	Declared type
benefit_def_sys_key	number
proc_cd	char(13)
proc_cd_type	char(10)

um_diagnosis_code_holds

Used by modules: um_cm, um_ur

Column	Declared type
diag_cd	char(7)
um_hold_rule_sys_key	decimal(0)

um_diagnosis_codes

Used by modules: clmsproc, um_cm, um_main, um_ur

Column	Declared type
create_dt	datetime
create_user_id	char(8)
diag_cd	char(7)
diag_order_no	decimal(0), number
service_sys_key	char(16)
updt_dt	datetime
updt_user_id	char(8)

um_diagnosis_groups

Used by modules: clmsproc, um_cm, um_ur

Column	Declared type
create_dt	datetime
create_user_id	char(8)
diag_group_sys_key	decimal(0)
service_sys_key	char(16)
updt_dt	datetime
updt_user_id	char(8)

um_entities

Used by modules: benplans, mailobj, svclog, um_cm, um_main, um_maint, um_ur

Column	Declared type
addr_no	decimal(0)
cm_trigger_sys_key	decimal(0)
contact_f_name	char(25)
contact_l_name	char(25)
contact_m_name	char(25)
display_name	char(50)
e_mail_addr	char(40)
entity_display_name	char(50)
entity_name	char(20)
fax_no	char(13)
name	char(20)
phone_no	char(13)
suppress_warning_ind	char(1)
um_entity_sys_key	decimal(0), number
um_entity_type_cd	char(10)

um_entity_medical_directors

Used by modules: um_maint

Column	Declared type
medical_director_sys_key	decimal(0)
um_entity_sys_key	decimal(0)

um_entity_warnings

Used by modules: um_main, um_maint

Column	Declared type
um_entity_sys_key	decimal(0)
warning_sys_key	decimal(0)

um_hold_messages

Used by modules: um_main

Column	Declared type
description	char(50)
um_hold_message_cd	char(10)

um_hold_rules

Used by modules: um_cm, um_ur

Column	Declared type
always_hold_claim_ind	char(1)
benefit_holds	number
claims_from_dt	datetime
claims_thru_dt	datetime
diagnosis_holds	number
hold_claimed_amt_less_than	decimal(2)
hold_claimed_amt_more_than	decimal(2)
procedure_holds	number
provider_holds	number
service_sys_key	char(16)
specialty_holds	number
um_hold_message_cd	char(10)
um_hold_rule_sys_key	decimal(0)

um_medical_reviews

Used by modules: um_cm, um_main, um_ur

Column	Declared type
appealer_type_cd	char(10)
create_dt	datetime
create_user_id	char(8)
medical_director_sys_key	decimal(0)
request_dt	datetime
response_dt	datetime
review_outcome_cd	char(10)
um_review_sys_key	decimal(0)
um_review_type_cd	char(10)
um_rule_sys_key	decimal(0)
updt_dt	datetime
updt_user_id	char(8)

um_notification_types

Used by modules: um_main

Column	Declared type
description	char(50)
um_notification_type_cd	char(10)

um_notified_by

Used by modules: um_main

Column	Declared type
um_notified_by_cd	char(10)
um_notified_by_desc	char(50)

um_physician_types

Used by modules: um_main

Column	Declared type
description	char(50)
um_physician_type_cd	char(10)

um_physicians

Used by modules: um_cm, um_main, um_ur

Column	Declared type
case_sys_key	char(16)
create_dt	datetime
create_user_id	char(8)
name	char(50)
phone_no	char(13)
physician_order_no	decimal(0)
specialty	char(10)
um_physician_type_cd	char(10)
updt_dt	datetime
updt_user_id	char(8)

um_procedure_code_holds

Used by modules: um_cm, um_ur

Column	Declared type
proc_cd	char(13)
proc_cd_type	char(10)
um_hold_rule_sys_key	decimal(0)

um_procedure_codes

Used by modules: clmsproc, um_cm, um_main, um_ur

Column	Declared type
billed_amt	decimal(2)
create_dt	datetime
create_user_id	char(8)
proc_cd	char(13)
proc_cd_type	char(10)
procedure_order_no	decimal(0)
procedure_resolution	char(10)
second_opinion_needed_ind	char(1)
service_sys_key	char(16)
updt_dt	datetime
updt_user_id	char(8)

um_procedure_groups

Used by modules: clmsproc, um_cm, um_ur

Column	Declared type
proc_group_sys_key	decimal(0)
procedure_group_resolution_cd	char(10)
service_sys_key	char(16)

um_provider_contacts

Used by modules: um_cm, um_main, um_ur

Column	Declared type
contact_type_cd	char(10)
create_dt	datetime
create_user_id	char(8)
e_mail_addr	char(40)
fax_no	char(13)
name	char(50)
phone_no_1	char(13)
phone_no_2	char(13)
service_sys_key	char(16)
um_provider_contact_sys_key	decimal(0)
updt_dt	datetime
updt_user_id	char(8)

um_provider_holds

Used by modules: um_cm, um_ur

Column	Declared type
provider_sys_key	decimal(0)
um_hold_rule_sys_key	decimal(0)

um_record_statuses

Used by modules: um_main

Column	Declared type
description	char(50)
um_record_status_cd	char(10)

um_review_outcomes

Used by modules: um_main

Column	Declared type
description	char(50)
review_outcome_cd	char(10)

um_review_types

Used by modules: um_main

Column	Declared type
description	char(50)
um_review_type_cd	char(10)

um_rules

Used by modules: claims, clmshare, clmsproc, um_cm, um_main, um_ur

Column	Declared type
amt_toward_purchase	decimal(2)
apply_excess_units_to_stoploss	char(1)
create_dt	datetime
create_user_id	char(8)
date_override_ind	char(1)
exceed_lifetime_max_ind	char(1)
exceed_plan_limits_ind	char(1)
hard_savings	decimal(2), number
list_price	decimal(2), number
max_units_approved	decimal(0)
min_units_approved	decimal(0)
no_alt_pricing_ind	char(1)
pricing_plan_sys_key	decimal(0)
purchase_price	decimal(2)
reviews	number
rule_from_dt	datetime
rule_no	number
rule_thru_dt	datetime
service_sys_key	char(16)
soft_savings	decimal(2), number
um_rule_sys_key	decimal(0), number
units_requested	decimal(0), number
units_used	decimal(0), number
updt_dt	datetime
updt_user_id	char(8)

um_service_types

Used by modules: um_main

Column	Declared type
description	char(50)
service_type_cd	char(10)

um_services

Used by modules: clmsproc, um_cm, um_main, um_ur

Column	Declared type
actual_days_stayed	decimal(0), number
approved_service_from_dt	datetime
approved_service_thru_dt	datetime
case_sys_key	char(16)
claim_type_cd	char(10)
cm_potential_sys_key	decimal(0)
create_dt	datetime
create_user_id	char(8)
criteria_guideline_units	decimal(0)
date_override_ind	char(1)
discharge_confirmed_by_cd	char(10)
discharge_dt	datetime
estimated_last_day_in_hospital	datetime
follow_up_done_ind	char(1)
hcn_name	char(50)
network_affiliation	decimal(0)
next_follow_up_dt	datetime
notification_type_cd	char(10)
provider_addr_no	decimal(0)
provider_sys_key	decimal(0)
referring_provider_sys_key	decimal(0)
referring_provider_type_cd	char(10)
request_service_from_dt	datetime
request_service_thru_dt	datetime
rownum	number
service_provider_tin	decimal(0)
service_provider_tin_type	char(10)
service_resolution_cd	char(10)
service_sys_key	char(16)
service_type_cd	char(10)
total_hard_savings	number
total_soft_savings	number
total_unit_allowed	number
total_unit_requested	number
total_unit_used	number
unit_requested_type_cd	char(10)
updt_dt	datetime
updt_user_id	char(8)

um_specialty_holds

Used by modules: um_ur

Column	Declared type
specialty	char(10)
um_hold_rule_sys_key	decimal(0)

um_warnings

Used by modules: um_main, um_maint

Column	Declared type
message	char(2000)
warning	char(2000)
warning_sys_key	decimal(0)
warning_type_cd	char(10)