Chapter 15. Schema money


Home Prev		Next

Below are the tables, views and functions for money

Tables in money

billable_xact

id bigserial PRIMARY KEY
usr integer NOT NULL REFERENCES actor.table.usr
xact_start timestamp with time zone NOT NULL DEFAULT now()
xact_finish timestamp with time zone
unrecovered boolean

Indexes on billable_xact

m_b_x_open_xacts_idx usr

billable_xact_summary

id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name

billable_xact_summary_location_view

id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name
billing_location integer

billable_xact_with_void_summary

id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name

billing

id bigserial PRIMARY KEY
xact bigint NOT NULL
billing_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
voider integer
void_time timestamp with time zone
amount numeric(6,2) NOT NULL
billing_type text NOT NULL
btype integer NOT NULL REFERENCES config.table.billing-type
note text

Indexes on billing

m_b_time_idx billing_ts
m_b_voider_idx voider
m_b_xact_idx xact

bnm_desk_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
cash_drawer integer REFERENCES actor.table.workstation

bnm_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

bnm_payment_view

id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
amount_collected numeric(6,2)
accepting_usr integer
payment_type name

cash_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
cash_drawer integer

Indexes on cash_payment

money_cash_id_idx id
money_cash_payment_accepting_usr_idx accepting_usr
money_cash_payment_cash_drawer_idx cash_drawer
money_cash_payment_ts_idx payment_ts
money_cash_payment_xact_idx xact

cashdrawer_payment_view

org_unit integer
cashdrawer integer
payment_type name
payment_ts timestamp with time zone
amount numeric(6,2)
voided boolean
note text

check_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
cash_drawer integer
check_number text NOT NULL

Indexes on check_payment

money_check_id_idx id
money_check_payment_accepting_usr_idx accepting_usr
money_check_payment_cash_drawer_idx cash_drawer
money_check_payment_ts_idx payment_ts
money_check_payment_xact_idx xact

collections_tracker

id bigserial PRIMARY KEY
usr integer NOT NULL REFERENCES actor.table.usr
collector integer NOT NULL REFERENCES actor.table.usr
location integer NOT NULL REFERENCES actor.table.org-unit
enter_time timestamp with time zone

Indexes on collections_tracker

m_c_t_collector_idx collector

credit_card_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
cash_drawer integer
cc_type text
cc_number text
cc_processor text
cc_first_name text
cc_last_name text
cc_order_number text
expire_month integer
expire_year integer
approval_code text

Indexes on credit_card_payment

money_credit_card_id_idx id
money_credit_card_payment_accepting_usr_idx accepting_usr
money_credit_card_payment_cash_drawer_idx cash_drawer
money_credit_card_payment_ts_idx payment_ts
money_credit_card_payment_xact_idx xact

credit_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Indexes on credit_payment

money_credit_id_idx id
money_credit_payment_accepting_usr_idx accepting_usr
money_credit_payment_payment_ts_idx payment_ts
money_credit_payment_xact_idx xact

desk_payment_view

id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
amount_collected numeric(6,2)
accepting_usr integer
cash_drawer integer
payment_type name

forgive_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Indexes on forgive_payment

money_forgive_id_idx id
money_forgive_payment_accepting_usr_idx accepting_usr
money_forgive_payment_payment_ts_idx payment_ts
money_forgive_payment_xact_idx xact

goods_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Indexes on goods_payment

money_goods_id_idx id
money_goods_payment_accepting_usr_idx accepting_usr
money_goods_payment_payment_ts_idx payment_ts
money_goods_payment_xact_idx xact

grocery

id bigint PRIMARY KEY DEFAULT nextval('money.billable_xact_id_seq'::regclass)
usr integer NOT NULL
xact_start timestamp with time zone NOT NULL DEFAULT now()
xact_finish timestamp with time zone
unrecovered boolean
billing_location integer NOT NULL
note text

Indexes on grocery

circ_open_date_idx xact_start) WHERE (xact_finish IS NULL
m_g_usr_idx usr

materialized_billable_xact_summary

id bigint PRIMARY KEY
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name

Indexes on materialized_billable_xact_summary

money_mat_summary_usr_idx usr
money_mat_summary_xact_start_idx xact_start

non_drawer_payment_view

id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
amount_collected numeric(6,2)
accepting_usr integer
payment_type name

open_billable_xact_summary

id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name
billing_location integer

open_transaction_billing_summary

xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric

open_transaction_billing_type_summary

xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric

open_transaction_payment_summary

xact bigint
last_payment_type name
last_payment_note text
last_payment_ts timestamp with time zone
total_paid numeric

open_usr_circulation_summary

usr integer
total_paid numeric
total_owed numeric
balance_owed numeric

open_usr_summary

usr integer
total_paid numeric
total_owed numeric
balance_owed numeric

payment

id bigserial PRIMARY KEY
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text

Indexes on payment

m_p_time_idx payment_ts
m_p_xact_idx xact

payment_view

id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
payment_type name

transaction_billing_summary

xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric

transaction_billing_type_summary

xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric

transaction_billing_with_void_summary

xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric

transaction_payment_summary

xact bigint
last_payment_type name
last_payment_note text
last_payment_ts timestamp with time zone
total_paid numeric

transaction_payment_with_void_summary

xact bigint
last_payment_type name
last_payment_note text
last_payment_ts timestamp with time zone
total_paid numeric

usr_circulation_summary

usr integer
total_paid numeric
total_owed numeric
balance_owed numeric

usr_summary

usr integer
total_paid numeric
total_owed numeric
balance_owed numeric

work_payment

id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Indexes on work_payment

money_work_id_idx id
money_work_payment_accepting_usr_idx accepting_usr
money_work_payment_payment_ts_idx payment_ts
money_work_payment_xact_idx xact