Drug 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.

Tables

caremark_edi_table

Used by modules: clmutils

Column	Declared type
adjustment_reason_cd	number
affected_claim_id	char(17)
caremark_edi_sys_key	number
date_of_service	datetime
days_supply	number
fill_number	number
generic_name	—
group_id	char(15)
matching_claim_id	char(17)
net_amount_due	number
patient_dob	—
patient_f_name	—
patient_gender	—
patient_id	char(15)
patient_l_name	—
patient_m_name	—
patient_pay_amount	number
patient_relationship	char(2)
pharmacy_name	char(35)
prescriber_f_name	—
prescriber_id	char(15)
prescriber_id_type	char(2)
prescriber_l_name	—
proc_cd	char(19)
proc_cd_type	char(2)
processed_dt	datetime
processed_ind	char(1)
product_name	—
quantity_dispensed	number
record_status_cd	char(1)
rx_number	char(20)
service_provider_id	char(15)
sub_alt_id	—
sub_dob	—
sub_f_name	—
sub_id	—
sub_l_name	—
sub_m_name	—
total_amount_paid	number
updt_dt	—

caremark_report_audit

Used by modules: provider

Column	Declared type
add_user	char(16)
end_dt	datetime
run_dt	datetime
start_dt	datetime

drug_card_groups

Used by modules: funding

Column	Declared type
cash_card_fees	number
check_auth_ind	char(1)
claims_fees	number
drug_card_sys_key	decimal(0)
fees	number
fund_page_sys_key	decimal(0)
group_id	char(15)

drug_cards

Used by modules: funding

Column	Declared type
drug_card_name	char(50)
drug_card_sys_key	decimal(0)
vendor_sys_key	decimal(0)

drug_covr_exp_trans

Used by modules: drugplan

Column	Declared type
create_dt	datetime
dep_member_sys_key	decimal(0)
exp_address1	char(40)
exp_card_request_id	char(1)
exp_city	char(15)
exp_dependent_coverage	char(2)
exp_dob	datetime
exp_eff_date	datetime
exp_first_name	char(15)
exp_gender_code	char(1)
exp_group_number	char(15)
exp_last_name	char(20)
exp_master_carrier	char(4)
exp_member_id	char(18)
exp_member_number	char(2)
exp_mi	char(1)
exp_misc_value	char(10)
exp_overage_dependent	char(1)
exp_record_type	char(1)
exp_relationship_cd	char(2)
exp_remarks	char(29)
exp_state	char(2)
exp_student_ind	char(1)
exp_subcarrier	char(4)
exp_term_date	datetime
exp_zip9	char(9)
sub_member_sys_key	decimal(0)

drug_covr_pcs_rxb_trans

Used by modules: drugplan

Column	Declared type
addr1	char(40)
addr2	char(40)
city	char(40)
coverage_level_cd	char(10)
create_dt	datetime
dep_member_sys_key	decimal(0)
pcs_act_code	char(1)
pcs_birth_date	datetime
pcs_carrier	char(4)
pcs_dep_code	char(3)
pcs_dep_type	char(2)
pcs_eff_date	datetime
pcs_exp_date	datetime
pcs_first_name	char(15)
pcs_gp	char(10), char(15)
pcs_group	char(8)
pcs_initial	char(1)
pcs_last_name	char(15)
pcs_remarks	char(30)
pcs_sex	char(1)
pcs_ssnumber	char(9)
state	char(2)
sub_member_sys_key	decimal(0)
zip	char(9)

drug_covr_pcs_std_trans

Used by modules: base, drugplan

Column	Declared type
addr1	char(40)
addr2	char(40)
city	char(40)
coverage_level_cd	char(10)
create_dt	datetime
dep_member_sys_key	decimal(0)
pcs_act_code	char(1)
pcs_birth_date	datetime
pcs_carrier	char(4)
pcs_dep_code	char(3)
pcs_dep_type	char(2)
pcs_eff_date	datetime
pcs_exp_date	datetime
pcs_first_name	char(15)
pcs_gp	char(10), char(15)
pcs_group	char(4), char(8)
pcs_initial	char(1)
pcs_last_name	char(15)
pcs_remarks	char(30)
pcs_sex	char(1)
pcs_ssnumber	char(9)
state	char(2)
sub_member_sys_key	decimal(0)
zip	char(9)

drug_covr_pcs_trans

Used by modules: drugplan

Column	Declared type
addr1	char(40)
addr2	char(40)
city	char(40)
coverage_level_cd	char(10)
create_dt	datetime
dep_member_sys_key	decimal(0)
pcs_act_code	char(1)
pcs_alternate_id	char(9)
pcs_birth_date	datetime
pcs_carrier	char(4)
pcs_dep_code	char(3)
pcs_dep_type	char(2)
pcs_eff_date	datetime
pcs_exp_date	datetime
pcs_first_name	char(15)
pcs_gp	char(15)
pcs_group	char(4), char(8)
pcs_initial	char(1)
pcs_last_name	char(15)
pcs_remarks	char(30)
pcs_sex	char(1)
pcs_ssnumber	char(9)
state	char(2)
sub_member_sys_key	decimal(0)
twins_ind	char(1)
zip	char(9)

drug_covr_restat_trans

Used by modules: drugplan

Column	Declared type
addr1	char(30)
addr2	char(25)
alternate_member_id	char(18)
anniversary_dt	datetime
birth_dt	datetime
city	char(20)
client_id	char(15)
copay_override	char(10)
copay_override_flg	char(1)
customer_id	char(10)
dep_member_sys_key	decimal(0)
eff_dt	datetime
eligibility_validation_data	char(10)
eligiblity_type	char(1)
f_name	char(12)
gender	char(1)
group_id	char(15)
l_name	char(15)
member_id	char(18)
patient_id_number	char(18)
person_code	char(3)
pharmacy_nabp	char(12)
phone_no	decimal(0)
primary_care_phys	char(10)
primary_cov_insurance	char(15)
relationship_code	char(1)
state	char(2)
status	char(1)
sub_member_sys_key	decimal(0)
term_dt	datetime
zip	char(10)

drug_mbr_exts

Used by modules: drugplan, homersys

(referenced in SQL; no column details captured)

drug_plan_exts

Used by modules: base, drugplan, enroll, enrolrpt, idcard, system

Column	Declared type
account_d	char(15)
age_dep_ends_non_student	number
age_dep_ends_student	number
benefit_coverage_cd	char(15)
benefit_coverage_type_cd	char(10)
carrier_d	char(4)
drug_plan_ext_type_cd	char(10)
exp_plan_id	char(15)
group_d	char(4)
named_dep_flg	char(1)
pcs_account_number	char(15)
pcs_carrier_cd	char(4)
pcs_eff_dt	datetime
pcs_plan_id	char(13), char(8)
restat_client_id	char(15)
restat_customer_id	char(10)