Chapter 15. Schema money

Table of Contents

Tables in money
billable_xact
billable_xact_summary
billable_xact_summary_location_view
billable_xact_with_void_summary
billing
bnm_desk_payment
bnm_payment
bnm_payment_view
cash_payment
cashdrawer_payment_view
check_payment
collections_tracker
credit_card_payment
credit_payment
desk_payment_view
forgive_payment
goods_payment
grocery
materialized_billable_xact_summary
non_drawer_payment_view
open_billable_xact_summary
open_transaction_billing_summary
open_transaction_billing_type_summary
open_transaction_payment_summary
open_usr_circulation_summary
open_usr_summary
payment
payment_view
transaction_billing_summary
transaction_billing_type_summary
transaction_billing_with_void_summary
transaction_payment_summary
transaction_payment_with_void_summary
usr_circulation_summary
usr_summary
work_payment
Functions in money

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