Providers & Repricing

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

fee_schedule_details

Used by modules: pricing

Column	Declared type
eff_dt	datetime
exp_dt	datetime
fee_amt	decimal(2)
fee_sched_sys_key	number
modifier	char(2)
proc_cd	char(13)
proc_cd_type	char(10)
specialty	char(10)
state	char(2)
zip_code	char(9)

fee_schedules

Used by modules: pricing

Column	Declared type
create_dt	datetime
fee_sched_sys_key	decimal(0)
schedule_name	char(50)
updt_dt	datetime

moon_negotiations

Used by modules: claims, moon

Column	Declared type
bill_claim_id	char(17)
bill_dt	datetime
bill_status_cd	char(10)
claim_id	char(17)
discount_amt	number
examiner_id	char(8)
group_id	char(15)
information	char(255)
negotiated_amt	number
negotiator_id	char(8)
orig_claim_amt	number
phone_no	char(13)
ppo_field	char(20)
provider_name	char(50)
sent_to_examiner_dt	datetime
specialty_cd	char(10)
status	char(255)
submitted_by	char(10)

ppo_census_ids

Used by modules: provider

Column	Declared type
hcn_sys_key	number
run_seq_no	number

ppo_group_work

Used by modules: provider

Column	Declared type
action_cd	char(1)
addr1	char(40)
addr2	char(40)
city	char(40)
eff_dt	datetime
exp_dt	datetime
group_id	char(15)
group_name	char(50)
link_no	char(6)
run_seq_no	number
state	char(2)
tax_id	char(10)
zip	char(9)

pricing_methods

Used by modules: pricing

Column	Declared type
allowed_reason	decimal(0)
default_drg_cap_rate	decimal(2)
default_drg_multiplier	decimal(4)
detail_ind	char(1)
from_dt	datetime
method	char(10)
pricing_method_sys_key	number
pricing_plan_sys_key	number
pricing_savings_type_cd	char(10)
rollover_ind	char(1)
seq_no	decimal(0), number
thru_dt	datetime

pricing_plans

Used by modules: benplans, pricing, um_cm, um_maint, um_ur

Column	Declared type
name	char(50)
pricing_plan_sys_key	decimal(0)
selected_pricing_plan_name	char(50)
selected_pricing_plan_sys_key	number

pricing_rule_diags

Used by modules: pricing

Column	Declared type
diag_cd_type	char(10)
diag_criterion	char(2000)
pricing_rule_sys_key	decimal(0)

pricing_rule_procs

Used by modules: pricing

Column	Declared type
pricing_rule_sys_key	decimal(0)
proc_cd_type	char(10)
proc_criterion	char(2000)

pricing_rules

Used by modules: clmshare, pricing

Column	Declared type
allow_additional_amt	decimal(2)
allow_pct	decimal(2)
anesthesia_ind	char(1)
apply_allow_pct_cd	char(10)
benefit_def_sys_key	number
case_rate_amt	decimal(2)
day_from	decimal(0)
day_thru	decimal(0)
description	char(50)
diag_group_criterion	char(2000)
drg_code_criterion	char(2000)
inpat_outpat_criterion	char(10)
los_criterion	char(2000)
per_diem_amt	decimal(2)
per_diem_type_cd	char(10)
pos_code_criterion	char(2000)
pricing_method_sys_key	number
pricing_rule_sys_key	number
proc_cd	char(13)
proc_cd_type	char(10)
proc_group_criterion	char(2000)
replace_with_claimed_ind	char(1)
revenue_criterion	char(2000)
rule_type_cd	char(10)
schedule_sys_key	number
seq_no	decimal(0)
specialty_criterion	char(2000)

provider_add_log

Used by modules: clmshare

Column	Declared type
claim_id	char(17)
provider_sys_key	decimal(0)
secondary_id	number
type_cd	char(25)

provider_address_vendors

Used by modules: base, clmshare, clmsproc, provider

Column	Declared type
addr_no	number
client_segment	char(10)
eff_dt	datetime
exp_dt	datetime
exp_reason	char(10)
provider_sys_key	number
vendor_sys_key	decimal(0), number

provider_addresses

Used by modules: clmshare, enroll, homersys, idcard, mailobj, moon, provider, svclog

Column	Declared type
addr_no	decimal(0), number
client_segment	char(10)
contact_no	number
provider_sys_key	number
rownum	number

provider_group_holds

Used by modules: provider

Column	Declared type
end_dt	datetime
group_id	char(15)
hold_reason	char(10)
provider_sys_key	decimal(0)
start_dt	datetime

provider_holds

Used by modules: provider

Column	Declared type
end_dt	datetime
end_dt_type	char(10)
hold_reason	char(10)
provider_sys_key	number
start_dt	datetime

provider_medicaid_ids

Used by modules: provider

Column	Declared type
medicaid_id	char(9)
medicaid_state	char(2)
provider_sys_key	number

provider_npis

Used by modules: provider

Column	Declared type
npi	char(10)
provider_sys_key	number

provider_panels

Used by modules: provider

Column	Declared type
eff_dt	datetime
exp_dt	datetime
exp_reason	char(10)
panel_sys_key	number
provider_sys_key	number

provider_specialties

Used by modules: provider

Column	Declared type
provider_sys_key	number
specialty	char(10)

provider_tins

Used by modules: clmshare, enroll, enrolrpt, homersys, provider, svclog, um_cm, um_main, um_ur

Column	Declared type
provider_sys_key	number
tin	decimal(0), number
tin_type	char(10)

provider_types

Used by modules: provider

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

providers

Used by modules: base, chkrundw, claims, clmforms, clmprint, clmrpt, clmshare, clmsproc, clmutils, enroll, enrolrpt, funding, homersys, idcard, mailobj, moon, provider, reprint, sales, stoploss, svclog, um_cm, um_main, um_maint, um_ur

Column	Declared type
after_hours	char(320)
birth_dt	datetime
clearinghouse_number	char(20)
client_segment	char(10)
company_name	char(50)
company_name_upper	char(50)
covering_provider	decimal(0)
default_vendor_sys_key	decimal(0)
educational_degree	char(5)
f_name	char(25)
f_name_upper	char(25)
l_name	char(25)
l_name_upper	char(25)
language_1	char(10)
language_2	char(10)
language_3	char(10)
look_up_name	char(25)
m_name	char(25)
m_name_upper	char(25)
medicare_no	char(20)
primary_specialty	char(10)
primary_upin	char(6)
prov_type	char(10)
provider_id	char(14)
provider_key	decimal(0)
provider_name	char(50)
provider_specialty	char(10)
provider_sys_key	decimal(0), number
status_cd	char(10)
title	char(5)

vendor

Used by modules: base, provider

(referenced in SQL; no column details captured)

vendor_addresses

Used by modules: base, billing, clmshare, enroll, funding, mailobj, provider

Column	Declared type
addr_no	number
eff_dt	datetime
exp_dt	datetime
exp_reason	char(10)
rownum	number
vendor_sys_key	number

vendor_contacts

Used by modules: base, provider

Column	Declared type
contact_no	number
rownum	number
vendor_sys_key	number

vendor_holds

Used by modules: base, provider

Column	Declared type
end_dt	datetime
end_dt_type	char(10)
hold_reason	char(10)
start_dt	datetime
vendor_sys_key	number

vendors

Used by modules: base, billfunc, billing, chkrundw, clmentry, clmrpt, clmshare, enroll, enrolrpt, funding, mailobj, provider, sales, stoploss, svclog

Column	Declared type
client_segment	char(10)
create_dt	—
irs_1099_flg	char(1)
lookup_name	char(25)
name	char(50)
pay_to	char(50)
payee_name	char(52)
payment_rule	char(10)
payment_rule_date	char(10)
provider_tax_number	number
status_cd	char(10)
tin	decimal(0), number
tin_type	char(10)
vendor	char(50)
vendor_name	char(50)
vendor_sys_key	decimal(0), number
vendor_tin	number
vendor_tin_type	char(10)