Billing, Funding & Payments

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

bank_acct_info

Used by modules: chkrundw, clmrpt, funding, reprint

Column	Declared type
account_number	char(20)
bank_acct_info_sys_key	decimal(0), number
bank_acct_no	char(20)
bank_form_sys_key	number
create_dt	datetime
create_user_id	char(8)
inactive_dt	datetime
last_check_no	number
last_manual_check_no	number
last_worksheet_no	number
multigroup_ind	char(1)
print_addr_no	number
print_name	char(40)
updt_dt	datetime
updt_user_id	char(8)

bank_acct_info_grps

Used by modules: clmrpt, funding

Column	Declared type
bank_acct_info_sys_key	decimal(0), number
group_id	char(15)

bank_forms

Used by modules: chkrundw, funding, reprint

Column	Declared type
addr_no	—
bank_form_code	char(40)
bank_form_sys_key	number
bank_name	char(40)
routing_fraction	char(20)
routing_trans_cd	char(9)
signature_1_text	char(40)
signature_2_text	char(40)

bill_charges

Used by modules: billfunc, billing

Column	Declared type
add_amt	number
add_count	number
add_volume	number
adj_count	number
adj_prior_election_benefit_amt	decimal(2)
adj_prior_election_level	char(13)
adj_prior_rate_item	char(2000)
alloc_entity_sys_key	number
appearance_run_sys_key	number
bill_amt	number
bill_category	char(15)
bill_charge_sys_key	number
bill_count	number
bill_def_sys_key	number
bill_level	char(15)
bill_period_dt	datetime
bill_rate_amt_sys_key	number
bill_receipt_sys_key	number
bill_rule_sys_key	number
bill_run_sys_key	number
bill_units	number
bill_volume	number
change_amt	number
change_count	number
change_volume	number
charge_amt	number
charge_type	char(15)
create_dt	datetime
create_user_id	char(8)
display_rate	char(13)
display_seq	number
drop_amt	number
drop_count	number
drop_volume	number
election_benefit_amt	decimal(2)
election_level	char(13)
group_id	char(15)
manual_adj_run_sys_key	number
member_sys_key	number
page_break	number
rate_amt	number
rate_count	number
rate_item	char(2000)
rate_volume	number
updt_dt	datetime
updt_user_id	char(8)
vendor_category	—
volume	decimal(2)

bill_charges_temp

Used by modules: billfunc

Column	Declared type
adj_prior_election_benefit_amt	decimal(2)
adj_prior_election_level	char(13)
alloc_entity_sys_key	number
bill_category	char(15)
bill_charge_sys_key	number
bill_def_sys_key	number
bill_level	char(15)
bill_period_dt	datetime
bill_rate_amt_sys_key	number
bill_rule_sys_key	number
bill_run_sys_key	number
bill_units	number
charge_amt	number
charge_type	char(15)
create_dt	datetime
create_user_id	char(8)
election_benefit_amt	decimal(2)
election_level	char(13)
group_id	char(15)
manual_adj_run_sys_key	number
member_sys_key	number
rate_amt	number
rate_item	char(2000)
updt_dt	datetime
updt_user_id	char(8)

bill_def_groups

Used by modules: billfunc, billing

Column	Declared type
bill_def_sys_key	number
group_id	char(15)

bill_def_option_vals

Used by modules: billfunc, billing

Column	Declared type
bill_def_sys_key	number
bill_option_cd	char(10)
eff_dt	datetime
exp_dt	datetime
option_val_cd	char(10)
option_val_char	char(50)
option_val_dt	datetime
option_val_no	number

bill_def_options

Used by modules: billfunc, billing

Column	Declared type
bill_option_cd	—
code_key	char(10)
option_cd	char(10)
val_saved_as_cd	char(10)

bill_def_rules

Used by modules: billfunc, billing

Column	Declared type
beg_month	number
bill_category	char(15)
bill_def_sys_key	number
bill_rule_sys_key	number
frequency	char(10), char(15)
from_dt	datetime
rate_period_cd	char(10), char(15)
thru_dt	datetime

bill_defs

Used by modules: billfunc, billing

Column	Declared type
adj_beg_dt	datetime
adj_beg_month	number
adj_logic_cd	char(10)
adj_periods	number
alloc_rpt_fmt_sys_key	number
beg_month	number
bill_beg_dt	datetime
bill_def_cd	char(15)
bill_def_sys_key	number
bill_rpt_fmt_sys_key	number
create_dt	datetime
create_user_id	char(8)
description	char(50)
frequency	char(10), char(15)
inactive_dt	datetime
updt_dt	datetime
updt_user_id	char(8)

bill_range_items

Used by modules: billfunc, billing

Column	Declared type
bill_range_sys_key	number
char_from	char(15)
char_thru	char(15)
heading	char(15)
number_from	number
number_thru	number

bill_ranges

Used by modules: billfunc, billing

Column	Declared type
bill_range_cd	char(15)
bill_range_sys_key	number
create_dt	datetime
create_user_id	char(8)
description	char(50)
updt_dt	datetime
updt_user_id	char(8)
value_type_cd	char(10), char(15)

bill_rate_allocs

Used by modules: billfunc, billing

Column	Declared type
alloc_entity_sys_key	number
allocate_amt	number
bill_rate_amt_sys_key	number

bill_rate_amts

Used by modules: billfunc, billing

Column	Declared type
bill_rate_amt_sys_key	number
bill_rate_table_sys_key	number
rate_amt	number
rate_item	char(2000)

bill_rate_tables

Used by modules: billfunc, billing

Column	Declared type
alloc_method	char(15)
bill_rate_table_sys_key	number
bill_rule_sys_key	number
from_dt	datetime
rate_rounding_cd	char(10), char(15)
rate_unit	number
thru_dt	datetime
unit_rounding_cd	char(10), char(15)

bill_rate_types

Used by modules: billfunc, billing

Column	Declared type
bill_level	char(15)
bill_rate_type_cd	char(15)
bill_rate_type_sys_key	number
description	char(50)

bill_rate_vars

Used by modules: billfunc, billing

Column	Declared type
bill_range_sys_key	number
bill_rate_type_sys_key	number
code_key	char(15)
rate_source	char(50)
rate_var_sql	char(2000)
rate_variable	char(50)
seq	number
short_desc	char(15)
value_type_cd	char(10), char(15)

bill_recalc_charges

Used by modules: billing

Column	Declared type
bill_period_dt	—
bill_rule_sys_key	—

bill_recalc_charges_temp

Used by modules: billfunc

Column	Declared type
alloc_entity_sys_key	decimal(0)
bill_category	char(15)
bill_charge_sys_key	decimal(0)
bill_def_sys_key	decimal(0)
bill_level	char(15)
bill_period_dt	datetime
bill_rate_amt_sys_key	number
bill_rule_sys_key	decimal(0)
bill_run_sys_key	decimal(0)
bill_units	number
charge_amt	number
charge_type	char(15)
election_benefit_amt	decimal(2)
election_level	char(13)
group_id	char(15)
member_sys_key	decimal(0)
rate_amt	number
rate_item	char(2000)
vendor_category	char(10)

bill_recalc_charges_tempmp

Used by modules: billfunc

Column	Declared type
bill_def_sys_key	decimal(0)

bill_receipts

Used by modules: billfunc, billing

Column	Declared type
appearance_run_sys_key	number
bill_def_sys_key	number
bill_receipt_sys_key	number
bill_run_sys_key	number
check_number	char(20)
create_dt	—
description	char(200)
group_id	char(15)
member_sys_key	number
receipt_amt	decimal(2)
receipt_dt	datetime

bill_reports

Used by modules: billing

Column	Declared type
bill_def_sys_key	decimal(0)
bill_run_sys_key	decimal(0)
create_dt	—
report_data_sys_key	decimal(0)
status_cd	char(10)
updt_dt	—

bill_rule_parm_details

Used by modules: billfunc, billing

Column	Declared type
bill_rule_parm_sys_key	number
group_parm	char(2000)
parm_operator	char(15)
parm_value	char(2000)
parm_variable	char(50)
value_type_cd	char(10), char(15)

bill_rule_parms

Used by modules: billfunc, billing

Column	Declared type
bill_rule_parm_sys_key	number
bill_rule_sys_key	number
parm_connector	char(15)
parm_source	char(50)
seq	number

bill_rules

Used by modules: billfunc, billing

Column	Declared type
bill_level	char(15)
bill_rate_type_sys_key	number
bill_rule_cd	char(15), char(40)
bill_rule_sys_key	number
create_dt	datetime
create_user_id	char(8)
default_bill_category	char(15)
default_frequency	char(10), char(15)
description	char(50)
updt_dt	datetime
updt_user_id	char(8)

bill_run_defs

Used by modules: billfunc, billing

Column	Declared type
bill_def_sys_key	number
bill_run_sys_key	number
status_cd	char(10)
total_billed_amt	number
updt_dt	—

bill_runs

Used by modules: billfunc, billing

Column	Declared type
bill_dt	datetime
bill_run_sys_key	number
due_dt	datetime
status_cd	char(10)

check

Used by modules: billing, funding

(referenced in SQL; no column details captured)

check_log

Used by modules: funding

Column	Declared type
amount	decimal(2)
check_date	datetime
check_log_sys_key	decimal(0)
check_number	decimal(0), number
create_dt	datetime
create_user_id	char(8)
forward_cd	char(10)
forward_desc	char(40)
group_id	char(15)
payee	char(50)
payer	char(50)
purpose_cd	char(10)
purpose_desc	char(40)
received_dt	datetime
status	char(10)
updt_dt	datetime
updt_user_id	char(8)

checks

Used by modules: billing, funding, pfcmain

(referenced in SQL; no column details captured)

fund_page_covrs

Used by modules: funding

Column	Declared type
benefit_coverage_type_cd	char(10)
fund_page_sys_key	number
matching_fica_ind	char(1)
payment_method_cd	char(10)

fund_page_grps

Used by modules: funding

Column	Declared type
code_desc	char(50)
fund_page_sys_key	number
funding_method_cd	char(10)
group_id	char(15)
group_level	number
rownum	number
sort_level	number

fund_pages

Used by modules: clmprint, funding

Column	Declared type
applied_desc	char(40)
bank_acct_info_sys_key	number
create_dt	datetime
create_user_id	char(8)
eval_activity_req_flg	char(1)
fund_balance_amt	decimal(2), number
fund_page_cd	char(10)
fund_page_description	char(40)
fund_page_sys_key	decimal(0), number
funding_method_cd	char(10)
inactive_dt	datetime
last_register_balance	decimal(2), number
last_register_dt	datetime
minimum_balance	decimal(2), number
page_applied	char(40)
page_source	char(40)
register_sort_order	char(15)
tran_desc	char(40)
updt_dt	datetime
updt_user_id	char(8)

fund_register

Used by modules: base, chkrundw, claims, clmentry, clmprint, clmrpt, clmsproc, enrollutils, enrolrpt, funding, homersys, provider, sales, stoploss, um_main, um_maint

Column	Declared type
adj_dt	datetime
applied_amount	decimal(2)
applied_amt	decimal(2), number
applied_date	datetime
applied_dt	datetime
backout_dt	datetime
backout_ind	char(1)
bank_acct_info_sys_key	number
check_amount	decimal(2)
check_number	number
claim_id	char(17)
cobra_ind	char(1)
create_dt	datetime
create_user_id	char(8)
expected	decimal(2)
explanation	char(10)
fund_page_sys_key	decimal(0), number
group_id	char(15)
hold_desc	char(40)
hold_ind	char(1)
independent_ind	char(1)
new_row	number
original_paid_date	datetime
original_paid_dt	datetime
paid_amount	decimal(2)
paid_amt	decimal(2), number
paid_dt	datetime
payable_amount	decimal(2)
payable_amt	decimal(2), number
payee_cd	char(10)
payee_sys_key	decimal(0), number
payment_type_cd	char(10)
payment_type_code	char(10)
register_dt	datetime
register_status_cd	char(10)
register_sys_key	decimal(0), number
related_fund_register_sys_key	decimal(0), number
related_register_sys_key	number
split_amt	number
type_of_claim	char(10)
unpaid_flg	char(1)
updt_dt	datetime
updt_user_id	char(8)
waiver_dt	datetime
waiver_ind	char(1)
wdi_ind	char(1)
worksheet_ind	char(1)
worksheet_number	number

funding_group_excludes

Used by modules: funding

Column	Declared type
group_id	char(15)

fundtrans

Used by modules: chkrundw, clmentry, clmprint, clmrpt, enrolrpt, funding, sales, stoploss, um_main

Column	Declared type
amount	decimal(2)
amount_applied	decimal(2), number
applied_to	decimal(0), number
check_cleared_date	datetime
check_no	number
check_number	decimal(0), number
cleared_date	datetime
create_dt	—
date_applied	datetime
description	char(40)
fundtrans_sys_key	decimal(0), number
group_id	char(15)
issued_notify	char(1)
issued_notify_filename	char(25)
new_balance	decimal(2), number
original_paid_dt	—
single_amount	decimal(2)
tran_date	datetime
tran_source	decimal(0), number
tran_type	char(10)
void_date	datetime
void_notify	char(1)
void_notify_filename	char(25)
worksheet_number	char(15)

print_1099s

Used by modules: provider

Column	Declared type
group_addr1	char(40)
group_city	char(40)
group_id	char(15)
group_name	char(40)
group_phone_no	char(13)
group_state	char(2)
group_tin	number
group_zip	char(10)
last_print_dt	datetime
orig_print_dt	datetime
printed_ind	char(1)
reason_for_reprint	char(2000)
tax_amt_box_6	number
tax_amt_box_7	number
tax_year	char(4)
vendor_addr1	char(40)
vendor_addr2	char(40)
vendor_city	char(40)
vendor_name	char(40)
vendor_state	char(2)
vendor_tin	number
vendor_tin_type	char(10)
vendor_zip	char(10)