Dumped on 2012-10-10

Index of database - evergreen


Schema acq


Table: acq.acq_lineitem_history

acq.acq_lineitem_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id bigint NOT NULL
creator integer NOT NULL
editor integer NOT NULL
selector integer NOT NULL
provider integer
purchase_order integer
picklist integer
expected_recv_time timestamp with time zone
create_time timestamp with time zone NOT NULL
edit_time timestamp with time zone NOT NULL
marc text NOT NULL
eg_bib_id bigint
source_label text
state text NOT NULL
cancel_reason integer
estimated_unit_price numeric
claim_policy integer
queued_record bigint
acq_lineitem_hist_id_idx id

Index - Schema acq


View: acq.acq_lineitem_lifecycle

acq.acq_lineitem_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id bigint
creator integer
editor integer
selector integer
provider integer
purchase_order integer
picklist integer
expected_recv_time timestamp with time zone
create_time timestamp with time zone
edit_time timestamp with time zone
marc text
eg_bib_id bigint
source_label text
state text
cancel_reason integer
estimated_unit_price numeric
claim_policy integer
queued_record bigint
SELECT (-1)
, now
() AS audit_time
,'-'::text AS audit_action
, lineitem.id
, lineitem.creator
, lineitem.editor
, lineitem.selector
, lineitem.provider
, lineitem.purchase_order
, lineitem.picklist
, lineitem.expected_recv_time
, lineitem.create_time
, lineitem.edit_time
, lineitem.marc
, lineitem.eg_bib_id
, lineitem.source_label
, lineitem.state
, lineitem.cancel_reason
, lineitem.estimated_unit_price
, lineitem.claim_policy
, lineitem.queued_record 
FROM acq.lineitem 
UNION ALLSELECT acq_lineitem_history.audit_id AS "?column?"
, acq_lineitem_history.audit_time
, acq_lineitem_history.audit_action
, acq_lineitem_history.id
, acq_lineitem_history.creator
, acq_lineitem_history.editor
, acq_lineitem_history.selector
, acq_lineitem_history.provider
, acq_lineitem_history.purchase_order
, acq_lineitem_history.picklist
, acq_lineitem_history.expected_recv_time
, acq_lineitem_history.create_time
, acq_lineitem_history.edit_time
, acq_lineitem_history.marc
, acq_lineitem_history.eg_bib_id
, acq_lineitem_history.source_label
, acq_lineitem_history.state
, acq_lineitem_history.cancel_reason
, acq_lineitem_history.estimated_unit_price
, acq_lineitem_history.claim_policy
, acq_lineitem_history.queued_record 
FROM acq.acq_lineitem_history;

Index - Schema acq


Table: acq.acq_purchase_order_history

acq.acq_purchase_order_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id integer NOT NULL
owner integer NOT NULL
creator integer NOT NULL
editor integer NOT NULL
ordering_agency integer NOT NULL
create_time timestamp with time zone NOT NULL
edit_time timestamp with time zone NOT NULL
provider integer NOT NULL
state text NOT NULL
order_date timestamp with time zone
name text NOT NULL
cancel_reason integer
prepayment_required boolean NOT NULL
acq_po_hist_id_idx id

Index - Schema acq


View: acq.acq_purchase_order_lifecycle

acq.acq_purchase_order_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id integer
owner integer
creator integer
editor integer
ordering_agency integer
create_time timestamp with time zone
edit_time timestamp with time zone
provider integer
state text
order_date timestamp with time zone
name text
cancel_reason integer
prepayment_required boolean
SELECT (-1)
, now
() AS audit_time
,'-'::text AS audit_action
, purchase_order.id
, purchase_order.owner
, purchase_order.creator
, purchase_order.editor
, purchase_order.ordering_agency
, purchase_order.create_time
, purchase_order.edit_time
, purchase_order.provider
, purchase_order.state
, purchase_order.order_date
, purchase_order.name
, purchase_order.cancel_reason
, purchase_order.prepayment_required 
FROM acq.purchase_order 
UNION ALLSELECT acq_purchase_order_history.audit_id AS "?column?"
, acq_purchase_order_history.audit_time
, acq_purchase_order_history.audit_action
, acq_purchase_order_history.id
, acq_purchase_order_history.owner
, acq_purchase_order_history.creator
, acq_purchase_order_history.editor
, acq_purchase_order_history.ordering_agency
, acq_purchase_order_history.create_time
, acq_purchase_order_history.edit_time
, acq_purchase_order_history.provider
, acq_purchase_order_history.state
, acq_purchase_order_history.order_date
, acq_purchase_order_history.name
, acq_purchase_order_history.cancel_reason
, acq_purchase_order_history.prepayment_required 
FROM acq.acq_purchase_order_history;

Index - Schema acq


View: acq.all_fund_allocation_total

acq.all_fund_allocation_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT f.id AS fund
, COALESCE
(
     (sum
           (
                 (a.amount * acq.exchange_ratio
                       (s.currency_type
                             , f.currency_type
                       )
                 )
           )
     )::numeric
     (100
           ,2
     )
     , (0)::numeric
) AS amount 
FROM (
     (acq.fund f 
   LEFT JOIN acq.fund_allocation a 
          ON (
                 (a.fund = f.id)
           )
     )
LEFT JOIN acq.funding_source s 
    ON (
           (a.funding_source = s.id)
     )
)
GROUP BY f.id;

Index - Schema acq


View: acq.all_fund_combined_balance

acq.all_fund_combined_balance Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT a.fund
, (a.amount - COALESCE
     (c.amount
           , (0)::numeric
     )
) AS amount 
FROM (acq.all_fund_allocation_total a 
LEFT JOIN (
      SELECT fund_debit.fund
           , sum
           (fund_debit.amount) AS amount 
        FROM acq.fund_debit 
    GROUP BY fund_debit.fund
     ) c 
 USING (fund)
);

Index - Schema acq


View: acq.all_fund_encumbrance_total

acq.all_fund_encumbrance_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT f.id AS fund
, COALESCE
(encumb.amount
     , (0)::numeric
) AS amount 
FROM (acq.fund f 
LEFT JOIN (
      SELECT fund_debit.fund
           , sum
           (fund_debit.amount) AS amount 
        FROM acq.fund_debit 
       WHERE fund_debit.encumbrance 
    GROUP BY fund_debit.fund
     ) encumb 
    ON (
           (f.id = encumb.fund)
     )
);

Index - Schema acq


View: acq.all_fund_spent_balance

acq.all_fund_spent_balance Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT c.fund
, (c.amount - d.amount) AS amount 
FROM (acq.all_fund_allocation_total c 
LEFT JOIN acq.all_fund_spent_total d 
 USING (fund)
);

Index - Schema acq


View: acq.all_fund_spent_total

acq.all_fund_spent_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT f.id AS fund
, COALESCE
(spent.amount
     , (0)::numeric
) AS amount 
FROM (acq.fund f 
LEFT JOIN (
      SELECT fund_debit.fund
           , sum
           (fund_debit.amount) AS amount 
        FROM acq.fund_debit 
       WHERE (NOT fund_debit.encumbrance)
    GROUP BY fund_debit.fund
     ) spent 
    ON (
           (f.id = spent.fund)
     )
);

Index - Schema acq


Table: acq.cancel_reason

acq.cancel_reason Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
label text UNIQUE#1 NOT NULL
description text NOT NULL
keep_debits boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.claim

acq.claim Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.claim_type.id type integer NOT NULL
acq.lineitem_detail.id lineitem_detail bigint NOT NULL

Tables referencing this one via Foreign Key Constraints:

claim_lid_idx lineitem_detail

Index - Schema acq


Table: acq.claim_event

acq.claim_event Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
acq.claim_event_type.id type integer NOT NULL
acq.claim.id claim serial NOT NULL
event_date timestamp with time zone NOT NULL DEFAULT now()
actor.usr.id creator integer NOT NULL
note text
claim_event_claim_date_idx claim, event_date

Index - Schema acq


Table: acq.claim_event_type

acq.claim_event_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
code text UNIQUE#1 NOT NULL
description text NOT NULL
library_initiated boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.claim_policy

acq.claim_policy Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
description text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.claim_policy_action

acq.claim_policy_action Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.claim_policy.id claim_policy integer UNIQUE#1 NOT NULL
action_interval interval UNIQUE#1 NOT NULL
acq.claim_event_type.id action integer NOT NULL

Index - Schema acq


Table: acq.claim_type

acq.claim_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
code text UNIQUE#1 NOT NULL
description text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.currency_type

acq.currency_type Structure
F-Key Name Type Description
code text PRIMARY KEY
label text

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.debit_attribution

acq.debit_attribution Structure
F-Key Name Type Description
id integer PRIMARY KEY
acq.fund_debit.id fund_debit integer NOT NULL
debit_amount numeric NOT NULL
acq.funding_source_credit.id funding_source_credit integer
credit_amount numeric
acq_attribution_credit_idx funding_source_credit acq_attribution_debit_idx fund_debit

Index - Schema acq


Table: acq.distribution_formula

acq.distribution_formula Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
skip_count integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.distribution_formula_application

acq.distribution_formula_application Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id creator integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
acq.distribution_formula.id formula integer NOT NULL
acq.lineitem.id lineitem integer NOT NULL
acqdfa_creator_idx creator acqdfa_df_idx formula acqdfa_li_idx lineitem

Index - Schema acq


Table: acq.distribution_formula_entry

acq.distribution_formula_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.distribution_formula.id formula integer UNIQUE#1 NOT NULL
position integer UNIQUE#1 NOT NULL
item_count integer NOT NULL
actor.org_unit.id owning_lib integer
asset.copy_location.id location integer

 

acq.distribution_formula_entry Constraints
Name Constraint
acqdfe_must_be_somewhere CHECK (((owning_lib IS NOT NULL) OR (location IS NOT NULL)))

Index - Schema acq


Table: acq.edi_account

acq.edi_account Structure
F-Key Name Type Description
id integer PRIMARY KEY DEFAULT nextval('config.remote_account_id_seq'::regclass)
label text NOT NULL
host text NOT NULL
username text
password text
account text
path text
owner integer NOT NULL
last_activity timestamp with time zone
acq.provider.id provider integer NOT NULL
in_dir text
vendcode text
vendacct text

Table acq.edi_account Inherits remote_account,

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.edi_message

acq.edi_message Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.edi_account.id account integer
remote_file text
create_time timestamp with time zone NOT NULL DEFAULT now()
translate_time timestamp with time zone
process_time timestamp with time zone
error_time timestamp with time zone
status text NOT NULL DEFAULT 'new'::text
edi text
jedi text
error text
acq.purchase_order.id purchase_order integer
message_type text NOT NULL

 

acq.edi_message Constraints
Name Constraint
status_value CHECK ((status = ANY (ARRAY['new'::text, 'translated'::text, 'trans_error'::text, 'processed'::text, 'proc_error'::text, 'delete_error'::text, 'retry'::text, 'complete'::text])))
valid_message_type CHECK ((message_type = ANY (ARRAY['ORDERS'::text, 'ORDRSP'::text, 'INVOIC'::text, 'OSTENQ'::text, 'OSTRPT'::text])))

Index - Schema acq


Table: acq.exchange_rate

acq.exchange_rate Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.currency_type.code from_currency text UNIQUE#1 NOT NULL
acq.currency_type.code to_currency text UNIQUE#1 NOT NULL
ratio numeric NOT NULL

Index - Schema acq


Table: acq.fiscal_calendar

acq.fiscal_calendar Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.fiscal_year

acq.fiscal_year Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.fiscal_calendar.id calendar integer UNIQUE#1 UNIQUE#2 NOT NULL
year integer UNIQUE#1 NOT NULL
year_begin timestamp with time zone UNIQUE#2 NOT NULL
year_end timestamp with time zone NOT NULL

Index - Schema acq


Table: acq.fund

acq.fund Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org integer UNIQUE#2 UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
year integer UNIQUE#2 UNIQUE#1 NOT NULL DEFAULT date_part('year'::text, now())
acq.currency_type.code currency_type text NOT NULL
code text UNIQUE#2
rollover boolean NOT NULL DEFAULT false
propagate boolean NOT NULL DEFAULT true
active boolean NOT NULL DEFAULT true
balance_warning_percent integer
balance_stop_percent integer

 

acq.fund Constraints
Name Constraint
acq_fund_rollover_implies_propagate CHECK ((propagate OR (NOT rollover)))

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.fund_allocation

acq.fund_allocation Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.funding_source.id funding_source integer NOT NULL
acq.fund.id fund integer NOT NULL
amount numeric NOT NULL
actor.usr.id allocator integer NOT NULL
note text
create_time timestamp with time zone NOT NULL DEFAULT now()
fund_alloc_allocator_idx allocator

Index - Schema acq


Table: acq.fund_allocation_percent

acq.fund_allocation_percent Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.funding_source.id funding_source integer UNIQUE#1 NOT NULL
actor.org_unit.id org integer UNIQUE#1 NOT NULL
fund_code text UNIQUE#1
percent numeric NOT NULL
actor.usr.id allocator integer NOT NULL
note text
create_time timestamp with time zone NOT NULL DEFAULT now()

 

acq.fund_allocation_percent Constraints
Name Constraint
percentage_range CHECK (((percent >= (0)::numeric) AND (percent <= (100)::numeric)))

Index - Schema acq


View: acq.fund_allocation_total

acq.fund_allocation_total Structure
F-Key Name Type Description
fund integer
amount numeric(100,2)
SELECT a.fund
, (sum
     (
           (a.amount * acq.exchange_ratio
                 (s.currency_type
                       , f.currency_type
                 )
           )
     )
)::numeric
(100
     ,2
) AS amount 
FROM (
     (acq.fund_allocation a 
        JOIN acq.fund f 
          ON (
                 (a.fund = f.id)
           )
     )
  JOIN acq.funding_source s 
    ON (
           (a.funding_source = s.id)
     )
)
GROUP BY a.fund;

Index - Schema acq


View: acq.fund_combined_balance

acq.fund_combined_balance Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT c.fund
, (c.amount - COALESCE
     (d.amount
           , 0.0
     )
) AS amount 
FROM (acq.fund_allocation_total c 
LEFT JOIN acq.fund_debit_total d 
 USING (fund)
);

Index - Schema acq


Table: acq.fund_debit

acq.fund_debit Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.fund.id fund integer NOT NULL
origin_amount numeric NOT NULL
acq.currency_type.code origin_currency_type text NOT NULL
amount numeric NOT NULL
encumbrance boolean NOT NULL DEFAULT true
debit_type text NOT NULL
acq.fund.id xfer_destination integer
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


View: acq.fund_debit_total

acq.fund_debit_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT fund.id AS fund
, sum
(COALESCE
     (fund_debit.amount
           , (0)::numeric
     )
) AS amount 
FROM (acq.fund fund 
LEFT JOIN acq.fund_debit fund_debit 
    ON (
           (fund.id = fund_debit.fund)
     )
)
GROUP BY fund.id;

Index - Schema acq


View: acq.fund_encumbrance_total

acq.fund_encumbrance_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT fund.id AS fund
, sum
(COALESCE
     (fund_debit.amount
           , (0)::numeric
     )
) AS amount 
FROM (acq.fund fund 
LEFT JOIN acq.fund_debit fund_debit 
    ON (
           (fund.id = fund_debit.fund)
     )
)
WHERE fund_debit.encumbrance 
GROUP BY fund.id;

Index - Schema acq


View: acq.fund_spent_balance

acq.fund_spent_balance Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT c.fund
, (c.amount - COALESCE
     (d.amount
           , 0.0
     )
) AS amount 
FROM (acq.fund_allocation_total c 
LEFT JOIN acq.fund_spent_total d 
 USING (fund)
);

Index - Schema acq


View: acq.fund_spent_total

acq.fund_spent_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT fund.id AS fund
, sum
(COALESCE
     (fund_debit.amount
           , (0)::numeric
     )
) AS amount 
FROM (acq.fund fund 
LEFT JOIN acq.fund_debit fund_debit 
    ON (
           (fund.id = fund_debit.fund)
     )
)
WHERE (NOT fund_debit.encumbrance)
GROUP BY fund.id;

Index - Schema acq


Table: acq.fund_tag

acq.fund_tag Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.fund_tag_map

acq.fund_tag_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.fund.id fund integer UNIQUE#1 NOT NULL
acq.fund_tag.id tag integer UNIQUE#1

Index - Schema acq


Table: acq.fund_transfer

Fund Transfer Each row represents the transfer of money from a source fund to a destination fund. There should be corresponding entries in acq.fund_allocation. The purpose of acq.fund_transfer is to record how much money moved from which fund to which other fund. The presence of two amount fields, rather than one, reflects the possibility that the two funds are denominated in different currencies. If they use the same currency type, the two amounts should be the same.

acq.fund_transfer Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.fund.id src_fund integer NOT NULL
src_amount numeric NOT NULL
acq.fund.id dest_fund integer
dest_amount numeric
transfer_time timestamp with time zone NOT NULL DEFAULT now()
actor.usr.id transfer_user integer NOT NULL
note text
acq.funding_source_credit.id funding_source_credit integer NOT NULL
acqftr_usr_idx transfer_user

Index - Schema acq


Table: acq.funding_source

acq.funding_source Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
acq.currency_type.code currency_type text NOT NULL
code text UNIQUE

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


View: acq.funding_source_allocation_total

acq.funding_source_allocation_total Structure
F-Key Name Type Description
funding_source integer
amount numeric(100,2)
SELECT a.funding_source
, (sum
     (a.amount)
)::numeric
(100
     ,2
) AS amount 
FROM acq.fund_allocation a 
GROUP BY a.funding_source;

Index - Schema acq


View: acq.funding_source_balance

acq.funding_source_balance Structure
F-Key Name Type Description
funding_source integer
amount numeric(100,2)
SELECT COALESCE
(c.funding_source
     , a.funding_source
) AS funding_source
, (sum
     (
           (COALESCE
                 (c.amount
                       , 0.0
                 ) - COALESCE
                 (a.amount
                       , 0.0
                 )
           )
     )
)::numeric
(100
     ,2
) AS amount 
FROM (acq.funding_source_credit_total c FULL 
  JOIN acq.funding_source_allocation_total a 
 USING (funding_source)
)
GROUP BY COALESCE
(c.funding_source
     , a.funding_source
);

Index - Schema acq


Table: acq.funding_source_credit

acq.funding_source_credit Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.funding_source.id funding_source integer NOT NULL
amount numeric NOT NULL
note text
deadline_date timestamp with time zone
effective_date timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


View: acq.funding_source_credit_total

acq.funding_source_credit_total Structure
F-Key Name Type Description
funding_source integer
amount numeric
SELECT funding_source_credit.funding_source
, sum
(funding_source_credit.amount) AS amount 
FROM acq.funding_source_credit 
GROUP BY funding_source_credit.funding_source;

Index - Schema acq


Table: acq.invoice

acq.invoice Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id receiver integer NOT NULL
acq.provider.id provider integer UNIQUE#1 NOT NULL
acq.provider.id shipper integer NOT NULL
recv_date timestamp with time zone NOT NULL DEFAULT now()
acq.invoice_method.code recv_method text NOT NULL DEFAULT 'EDI'::text
inv_type text
inv_ident text UNIQUE#1 NOT NULL
payment_auth text
acq.invoice_payment_method.code payment_method text
note text
complete boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.invoice_entry

acq.invoice_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.invoice.id invoice integer NOT NULL
acq.purchase_order.id purchase_order integer
acq.lineitem.id lineitem integer
inv_item_count integer NOT NULL
phys_item_count integer
note text
billed_per_item boolean
cost_billed numeric(8,2)
actual_cost numeric(8,2)
amount_paid numeric(8,2)
ie_inv_idx invoice ie_li_idx lineitem ie_po_idx purchase_order

Index - Schema acq


Table: acq.invoice_item

acq.invoice_item Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.invoice.id invoice integer NOT NULL
acq.purchase_order.id purchase_order integer
acq.fund_debit.id fund_debit integer
acq.invoice_item_type.code inv_item_type text NOT NULL
title text
author text
note text
cost_billed numeric(8,2)
actual_cost numeric(8,2)
acq.fund.id fund integer
amount_paid numeric(8,2)
acq.po_item.id po_item integer
target bigint
ii_inv_idx invoice ii_po_idx purchase_order ii_poi_idx po_item

Index - Schema acq


Table: acq.invoice_item_type

acq.invoice_item_type Structure
F-Key Name Type Description
code text PRIMARY KEY
name text NOT NULL
prorate boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.invoice_method

acq.invoice_method Structure
F-Key Name Type Description
code text PRIMARY KEY
name text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.invoice_payment_method

acq.invoice_payment_method Structure
F-Key Name Type Description
code text PRIMARY KEY
name text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.lineitem

acq.lineitem Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
actor.usr.id selector integer NOT NULL
acq.provider.id provider integer
acq.purchase_order.id purchase_order integer
acq.picklist.id picklist integer
expected_recv_time timestamp with time zone
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
marc text NOT NULL
biblio.record_entry.id eg_bib_id bigint
source_label text
state text NOT NULL DEFAULT 'new'::text
acq.cancel_reason.id cancel_reason integer
estimated_unit_price numeric
acq.claim_policy.id claim_policy integer
vandelay.queued_bib_record.id queued_record bigint

 

acq.lineitem Constraints
Name Constraint
picklist_or_po CHECK (((picklist IS NOT NULL) OR (purchase_order IS NOT NULL)))

Tables referencing this one via Foreign Key Constraints:

li_creator_idx creator li_editor_idx editor li_pl_idx picklist li_po_idx purchase_order li_selector_idx selector

Index - Schema acq


Table: acq.lineitem_alert_text

acq.lineitem_alert_text Structure
F-Key Name Type Description
id serial PRIMARY KEY
code text UNIQUE#1 NOT NULL
description text
actor.org_unit.id owning_lib integer UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.lineitem_attr

acq.lineitem_attr Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
definition bigint NOT NULL
acq.lineitem.id lineitem bigint NOT NULL
attr_type text NOT NULL
attr_name text NOT NULL
attr_value text NOT NULL
li_attr_definition_idx definition li_attr_li_idx lineitem li_attr_value_idx attr_value

Index - Schema acq


Table: acq.lineitem_attr_definition

acq.lineitem_attr_definition Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false

Index - Schema acq


Table: acq.lineitem_detail

acq.lineitem_detail Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
acq.lineitem.id lineitem integer NOT NULL
acq.fund.id fund integer
acq.fund_debit.id fund_debit integer
eg_copy_id bigint
barcode text
cn_label text
note text
collection_code text
config.circ_modifier.code circ_modifier text
actor.org_unit.id owning_lib integer
asset.copy_location.id location integer
recv_time timestamp with time zone
actor.usr.id receiver integer
acq.cancel_reason.id cancel_reason integer

Tables referencing this one via Foreign Key Constraints:

li_detail_li_idx lineitem

Index - Schema acq


Table: acq.lineitem_generated_attr_definition

acq.lineitem_generated_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
xpath text NOT NULL

Table acq.lineitem_generated_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_local_attr_definition

acq.lineitem_local_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false

Table acq.lineitem_local_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_marc_attr_definition

acq.lineitem_marc_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
xpath text NOT NULL

Table acq.lineitem_marc_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_note

acq.lineitem_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.lineitem.id lineitem integer NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
value text NOT NULL
acq.lineitem_alert_text.id alert_text integer
vendor_public boolean NOT NULL DEFAULT false
li_note_creator_idx creator li_note_editor_idx editor li_note_li_idx lineitem

Index - Schema acq


Table: acq.lineitem_provider_attr_definition

acq.lineitem_provider_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
xpath text NOT NULL
acq.provider.id provider integer NOT NULL

Table acq.lineitem_provider_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_usr_attr_definition

acq.lineitem_usr_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
actor.usr.id usr integer NOT NULL

Table acq.lineitem_usr_attr_definition Inherits lineitem_attr_definition,

li_usr_attr_def_usr_idx usr

Index - Schema acq


View: acq.ordered_funding_source_credit

The acq.ordered_funding_source_credit view is a prioritized ordering of funding source credits. When ordered by the first three columns, this view defines the order in which the various credits are to be tapped for spending, subject to the allocations in the acq.fund_allocation table. The first column reflects the principle that we should spend money with deadlines before spending money without deadlines. The second column reflects the principle that we should spend the oldest money first. For money with deadlines, that means that we spend first from the credit with the earliest deadline. For money without deadlines, we spend first from the credit with the earliest effective date. The third column is a tie breaker to ensure a consistent ordering.

acq.ordered_funding_source_credit Structure
F-Key Name Type Description
sort_priority integer
sort_date timestamp with time zone
id integer
funding_source integer
amount numeric
note text
SELECT CASE WHEN 
(funding_source_credit.deadline_date IS NULL) THEN 2 ELSE 1 END AS sort_priority
, CASE WHEN 
(funding_source_credit.deadline_date IS NULL) THEN funding_source_credit.effective_date ELSE funding_source_credit.deadline_date END AS sort_date
, funding_source_credit.id
, funding_source_credit.funding_source
, funding_source_credit.amount
, funding_source_credit.note 
FROM acq.funding_source_credit;

Index - Schema acq


Table: acq.picklist

acq.picklist Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
name text UNIQUE#1 NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

acq_picklist_creator_idx creator acq_picklist_editor_idx editor acq_picklist_owner_idx owner

Index - Schema acq


Table: acq.po_item

acq.po_item Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.purchase_order.id purchase_order integer
acq.fund_debit.id fund_debit integer
acq.invoice_item_type.code inv_item_type text NOT NULL
title text
author text
note text
estimated_cost numeric(8,2)
acq.fund.id fund integer
target bigint

Tables referencing this one via Foreign Key Constraints:

poi_po_idx purchase_order

Index - Schema acq


Table: acq.po_note

acq.po_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.purchase_order.id purchase_order integer NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
value text NOT NULL
vendor_public boolean NOT NULL DEFAULT false
acq_po_note_creator_idx creator acq_po_note_editor_idx editor po_note_po_idx purchase_order

Index - Schema acq


Table: acq.provider

acq.provider Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#2 UNIQUE#1 NOT NULL
acq.currency_type.code currency_type text NOT NULL
code text UNIQUE#2 NOT NULL
holding_tag text
san text
acq.edi_account.id edi_default integer
active boolean NOT NULL DEFAULT true
prepayment_required boolean NOT NULL DEFAULT false
url text
email text
phone text
fax_phone text
acq.claim_policy.id default_claim_policy integer

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.provider_address

acq.provider_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
address_type text
acq.provider.id provider integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL
fax_phone text

Index - Schema acq


Table: acq.provider_contact

acq.provider_contact Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.provider.id provider integer NOT NULL
name text NOT NULL
role text
email text
phone text

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.provider_contact_address

acq.provider_contact_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
address_type text
acq.provider_contact.id contact integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL
fax_phone text

Index - Schema acq


Table: acq.provider_holding_subfield_map

acq.provider_holding_subfield_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.provider.id provider integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
subfield text NOT NULL

Index - Schema acq


Table: acq.provider_note

acq.provider_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.provider.id provider integer NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
value text NOT NULL
acq_pro_note_creator_idx creator acq_pro_note_editor_idx editor acq_pro_note_pro_idx provider

Index - Schema acq


Table: acq.purchase_order

acq.purchase_order Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
actor.org_unit.id ordering_agency integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
acq.provider.id provider integer NOT NULL
state text NOT NULL DEFAULT 'new'::text
order_date timestamp with time zone
name text NOT NULL
acq.cancel_reason.id cancel_reason integer
prepayment_required boolean NOT NULL DEFAULT false

 

acq.purchase_order Constraints
Name Constraint
valid_po_state CHECK ((state = ANY (ARRAY['new'::text, 'pending'::text, 'on-order'::text, 'received'::text, 'cancelled'::text])))

Tables referencing this one via Foreign Key Constraints:

acq_po_org_name_order_date_idx ordering_agency, name, order_date po_creator_idx creator po_editor_idx editor po_owner_idx owner po_provider_idx provider po_state_idx state

Index - Schema acq


Table: acq.serial_claim

acq.serial_claim Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.claim_type.id type integer NOT NULL
serial.item.id item bigint NOT NULL

Tables referencing this one via Foreign Key Constraints:

serial_claim_lid_idx item

Index - Schema acq


Table: acq.serial_claim_event

acq.serial_claim_event Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
acq.claim_event_type.id type integer NOT NULL
acq.serial_claim.id claim serial NOT NULL
event_date timestamp with time zone NOT NULL DEFAULT now()
actor.usr.id creator integer NOT NULL
note text
serial_claim_event_claim_date_idx claim, event_date

Index - Schema acq


Table: acq.user_request

acq.user_request Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer NOT NULL
hold boolean NOT NULL DEFAULT true
actor.org_unit.id pickup_lib integer NOT NULL
holdable_formats text
phone_notify text
email_notify boolean NOT NULL DEFAULT true
acq.lineitem.id lineitem integer
biblio.record_entry.id eg_bib bigint
request_date timestamp with time zone NOT NULL DEFAULT now()
need_before timestamp with time zone
max_fee text
acq.user_request_type.id request_type integer NOT NULL
isxn text
title text
volume text
author text
article_title text
article_pages text
publisher text
location text
pubdate text
mentioned text
other_info text
acq.cancel_reason.id cancel_reason integer

Index - Schema acq


Table: acq.user_request_type

acq.user_request_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
label text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Function: acq.attribute_debits()

Returns: void

Language: PLPGSQL

/*
Function to attribute expenditures and encumbrances to funding source credits,
and thereby to funding sources.

Read the debits in chonological order, attributing each one to one or
more funding source credits.  Constraints:

1. Don't attribute more to a credit than the amount of the credit.

2. For a given fund, don't attribute more to a funding source than the
source has allocated to that fund.

3. Attribute debits to credits with deadlines before attributing them to
credits without deadlines.  Otherwise attribute to the earliest credits
first, based on the deadline date when present, or on the effective date
when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
This ordering is defined by an ORDER BY clause on the view
acq.ordered_funding_source_credit.

Start by truncating the table acq.debit_attribution.  Then insert a row
into that table for each attribution.  If a debit cannot be fully
attributed, insert a row for the unattributable balance, with the 
funding_source_credit and credit_amount columns NULL.
*/
DECLARE
	curr_fund_source_bal RECORD;
	seqno                INT;     -- sequence num for credits applicable to a fund
	fund_credit          RECORD;  -- current row in temp t_fund_credit table
	fc                   RECORD;  -- used for loading t_fund_credit table
	sc                   RECORD;  -- used for loading t_fund_credit table
	--
	-- Used exclusively in the main loop:
	--
	deb                 RECORD;   -- current row from acq.fund_debit table
	curr_credit_bal     RECORD;   -- current row from temp t_credit table
	debit_balance       NUMERIC;  -- amount left to attribute for current debit
	conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
	attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
	conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
	conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
	conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
	attrib_count        INT;      -- populates id of acq.debit_attribution
BEGIN
	--
	-- Load a temporary table.  For each combination of fund and funding source,
	-- load an entry with the total amount allocated to that fund by that source.
	-- This sum may reflect transfers as well as original allocations.  We will
	-- reduce this balance whenever we attribute debits to it.
	--
	CREATE TEMP TABLE t_fund_source_bal
	ON COMMIT DROP AS
		SELECT
			fund AS fund,
			funding_source AS source,
			sum( amount ) AS balance
		FROM
			acq.fund_allocation
		GROUP BY
			fund,
			funding_source
		HAVING
			sum( amount ) > 0;
	--
	CREATE INDEX t_fund_source_bal_idx
		ON t_fund_source_bal( fund, source );
	-------------------------------------------------------------------------------
	--
	-- Load another temporary table.  For each fund, load zero or more
	-- funding source credits from which that fund can get money.
	--
	CREATE TEMP TABLE t_fund_credit (
		fund        INT,
		seq         INT,
		credit      INT
	) ON COMMIT DROP;
	--
	FOR fc IN
		SELECT DISTINCT fund
		FROM acq.fund_allocation
		ORDER BY fund
	LOOP                  -- Loop over the funds
		seqno := 1;
		FOR sc IN
			SELECT
				ofsc.id
			FROM
				acq.ordered_funding_source_credit AS ofsc
			WHERE
				ofsc.funding_source IN
				(
					SELECT funding_source
					FROM acq.fund_allocation
					WHERE fund = fc.fund
				)
    		ORDER BY
    		    ofsc.sort_priority,
    		    ofsc.sort_date,
    		    ofsc.id
		LOOP                        -- Add each credit to the list
			INSERT INTO t_fund_credit (
				fund,
				seq,
				credit
			) VALUES (
				fc.fund,
				seqno,
				sc.id
			);
			--RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
			seqno := seqno + 1;
		END LOOP;     -- Loop over credits for a given fund
	END LOOP;         -- Loop over funds
	--
	CREATE INDEX t_fund_credit_idx
		ON t_fund_credit( fund, seq );
	-------------------------------------------------------------------------------
	--
	-- Load yet another temporary table.  This one is a list of funding source
	-- credits, with their balances.  We shall reduce those balances as we
	-- attribute debits to them.
	--
	CREATE TEMP TABLE t_credit
	ON COMMIT DROP AS
        SELECT
            fsc.id AS credit,
            fsc.funding_source AS source,
            fsc.amount AS balance,
            fs.currency_type AS currency_type
        FROM
            acq.funding_source_credit AS fsc,
            acq.funding_source fs
        WHERE
            fsc.funding_source = fs.id
			AND fsc.amount > 0;
	--
	CREATE INDEX t_credit_idx
		ON t_credit( credit );
	--
	-------------------------------------------------------------------------------
	--
	-- Now that we have loaded the lookup tables: loop through the debits,
	-- attributing each one to one or more funding source credits.
	-- 
	truncate table acq.debit_attribution;
	--
	attrib_count := 0;
	FOR deb in
		SELECT
			fd.id,
			fd.fund,
			fd.amount,
			f.currency_type,
			fd.encumbrance
		FROM
			acq.fund_debit fd,
			acq.fund f
		WHERE
			fd.fund = f.id
		ORDER BY
			fd.id
	LOOP
		--RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
		--
		debit_balance := deb.amount;
		--
		-- Loop over the funding source credits that are eligible
		-- to pay for this debit
		--
		FOR fund_credit IN
			SELECT
				credit
			FROM
				t_fund_credit
			WHERE
				fund = deb.fund
			ORDER BY
				seq
		LOOP
			--RAISE NOTICE '   Examining credit %', fund_credit.credit;
			--
			-- Look up the balance for this credit.  If it's zero, then
			-- it's not useful, so treat it as if you didn't find it.
			-- (Actually there shouldn't be any zero balances in the table,
			-- but we check just to make sure.)
			--
			SELECT *
			INTO curr_credit_bal
			FROM t_credit
			WHERE
				credit = fund_credit.credit
				AND balance > 0;
			--
			IF curr_credit_bal IS NULL THEN
				--
				-- This credit is exhausted; try the next one.
				--
				CONTINUE;
			END IF;
			--
			--
			-- At this point we have an applicable credit with some money left.
			-- Now see if the relevant funding_source has any money left.
			--
			-- Look up the balance of the allocation for this combination of
			-- fund and source.  If you find such an entry, but it has a zero
			-- balance, then it's not useful, so treat it as unfound.
			-- (Actually there shouldn't be any zero balances in the table,
			-- but we check just to make sure.)
			--
			SELECT *
			INTO curr_fund_source_bal
			FROM t_fund_source_bal
			WHERE
				fund = deb.fund
				AND source = curr_credit_bal.source
				AND balance > 0;
			--
			IF curr_fund_source_bal IS NULL THEN
				--
				-- This fund/source doesn't exist or is already exhausted,
				-- so we can't use this credit.  Go on to the next one.
				--
				CONTINUE;
			END IF;
			--
			-- Convert the available balances to the currency of the fund
			--
			conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
				curr_credit_bal.currency_type, deb.currency_type );
			conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
				curr_credit_bal.currency_type, deb.currency_type );
			--
			-- Determine how much we can attribute to this credit: the minimum
			-- of the debit amount, the fund/source balance, and the
			-- credit balance
			--
			--RAISE NOTICE '   deb bal %', debit_balance;
			--RAISE NOTICE '      source % balance %', curr_credit_bal.source, conv_alloc_balance;
			--RAISE NOTICE '      credit % balance %', curr_credit_bal.credit, conv_cred_balance;
			--
			conv_attr_amount := NULL;
			attr_amount := debit_balance;
			--
			IF attr_amount > conv_alloc_balance THEN
				attr_amount := conv_alloc_balance;
				conv_attr_amount := curr_fund_source_bal.balance;
			END IF;
			IF attr_amount > conv_cred_balance THEN
				attr_amount := conv_cred_balance;
				conv_attr_amount := curr_credit_bal.balance;
			END IF;
			--
			-- If we're attributing all of one of the balances, then that's how
			-- much we will deduct from the balances, and we already captured
			-- that amount above.  Otherwise we must convert the amount of the
			-- attribution from the currency of the fund back to the currency of
			-- the funding source.
			--
			IF conv_attr_amount IS NULL THEN
				conv_attr_amount := attr_amount * acq.exchange_ratio(
					deb.currency_type, curr_credit_bal.currency_type );
			END IF;
			--
			-- Insert a row to record the attribution
			--
			attrib_count := attrib_count + 1;
			INSERT INTO acq.debit_attribution (
				id,
				fund_debit,
				debit_amount,
				funding_source_credit,
				credit_amount
			) VALUES (
				attrib_count,
				deb.id,
				attr_amount,
				curr_credit_bal.credit,
				conv_attr_amount
			);
			--
			-- Subtract the attributed amount from the various balances
			--
			debit_balance := debit_balance - attr_amount;
			curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
			--
			IF curr_fund_source_bal.balance <= 0 THEN
				--
				-- This allocation is exhausted.  Delete it so
				-- that we don't waste time looking at it again.
				--
				DELETE FROM t_fund_source_bal
				WHERE
					fund = curr_fund_source_bal.fund
					AND source = curr_fund_source_bal.source;
			ELSE
				UPDATE t_fund_source_bal
				SET balance = balance - conv_attr_amount
				WHERE
					fund = curr_fund_source_bal.fund
					AND source = curr_fund_source_bal.source;
			END IF;
			--
			IF curr_credit_bal.balance <= 0 THEN
				--
				-- This funding source credit is exhausted.  Delete it
				-- so that we don't waste time looking at it again.
				--
				--DELETE FROM t_credit
				--WHERE
				--	credit = curr_credit_bal.credit;
				--
				DELETE FROM t_fund_credit
				WHERE
					credit = curr_credit_bal.credit;
			ELSE
				UPDATE t_credit
				SET balance = curr_credit_bal.balance
				WHERE
					credit = curr_credit_bal.credit;
			END IF;
			--
			-- Are we done with this debit yet?
			--
			IF debit_balance <= 0 THEN
				EXIT;       -- We've fully attributed this debit; stop looking at credits.
			END IF;
		END LOOP;       -- End loop over credits
		--
		IF debit_balance <> 0 THEN
			--
			-- We weren't able to attribute this debit, or at least not
			-- all of it.  Insert a row for the unattributed balance.
			--
			attrib_count := attrib_count + 1;
			INSERT INTO acq.debit_attribution (
				id,
				fund_debit,
				debit_amount,
				funding_source_credit,
				credit_amount
			) VALUES (
				attrib_count,
				deb.id,
				debit_balance,
				NULL,
				NULL
			);
		END IF;
	END LOOP;   -- End of loop over debits
END;

Function: acq.audit_acq_lineitem_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO acq.acq_lineitem_history
                SELECT	nextval('acq.acq_lineitem_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    OLD.*;
            RETURN NULL;
        END;
        

Function: acq.audit_acq_purchase_order_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO acq.acq_purchase_order_history
                SELECT	nextval('acq.acq_purchase_order_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    OLD.*;
            RETURN NULL;
        END;
        

Function: acq.create_acq_auditor(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    PERFORM acq.create_acq_seq(sch, tbl);
    PERFORM acq.create_acq_history(sch, tbl);
    PERFORM acq.create_acq_func(sch, tbl);
    PERFORM acq.create_acq_update_trigger(sch, tbl);
    PERFORM acq.create_acq_lifecycle(sch, tbl);
    RETURN TRUE;
END;

Function: acq.create_acq_func(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE OR REPLACE FUNCTION acq.audit_$$ || sch || $$_$$ || tbl || $$_func ()
        RETURNS TRIGGER AS $func$
        BEGIN
            INSERT INTO acq.$$ || sch || $$_$$ || tbl || $$_history
                SELECT	nextval('acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    OLD.*;
            RETURN NULL;
        END;
        $func$ LANGUAGE 'plpgsql';
    $$;
	RETURN TRUE;
END;

Function: acq.create_acq_history(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE TABLE acq.$$ || sch || $$_$$ || tbl || $$_history (
            audit_id	BIGINT				PRIMARY KEY,
            audit_time	TIMESTAMP WITH TIME ZONE	NOT NULL,
            audit_action	TEXT				NOT NULL,
            LIKE $$ || sch || $$.$$ || tbl || $$
        );
    $$;
	RETURN TRUE;
END;

Function: acq.create_acq_lifecycle(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE OR REPLACE VIEW acq.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
            SELECT	-1, now() as audit_time, '-' as audit_action, *
              FROM	$$ || sch || $$.$$ || tbl || $$
                UNION ALL
            SELECT	*
              FROM	acq.$$ || sch || $$_$$ || tbl || $$_history;
    $$;
	RETURN TRUE;
END;

Function: acq.create_acq_seq(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE SEQUENCE acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
    $$;
	RETURN TRUE;
END;

Function: acq.create_acq_update_trigger(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
            AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
            EXECUTE PROCEDURE acq.audit_$$ || sch || $$_$$ || tbl || $$_func ();
    $$;
	RETURN TRUE;
END;

Function: acq.exchange_ratio(text, text, numeric)

Returns: numeric

Language: SQL

    SELECT $3 * acq.exchange_ratio($1, $2);

Function: acq.exchange_ratio(to_ex text, from_ex text)

Returns: numeric

Language: PLPGSQL

DECLARE
    rat NUMERIC;
BEGIN
    IF from_ex = to_ex THEN
        RETURN 1.0;
    END IF;

    SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;

    IF FOUND THEN
        RETURN rat;
    ELSE
        SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
        IF FOUND THEN
            RETURN 1.0/rat;
        END IF;
    END IF;

    RETURN NULL;

END;

Function: acq.extract_holding_attr_table(tag integer, lineitem text)

Returns: SET OF flat_lineitem_holding_subfield

Language: PLPGSQL

DECLARE
    counter INT;
    lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
BEGIN

    SELECT  COUNT(*) INTO counter
      FROM  oils_xpath_table(
                'id',
                'marc',
                'acq.lineitem',
                '//*[@tag="' || tag || '"]',
                'id=' || lineitem
            ) as t(i int,c text);

    FOR i IN 1 .. counter LOOP
        FOR lida IN
            SELECT  * 
              FROM  (   SELECT  id,i,t,v
                          FROM  oils_xpath_table(
                                    'id',
                                    'marc',
                                    'acq.lineitem',
                                    '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
                                        '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
                                    'id=' || lineitem
                                ) as t(id int,t text,v text)
                    )x
        LOOP
            RETURN NEXT lida;
        END LOOP;
    END LOOP;

    RETURN;
END;

Function: acq.extract_provider_holding_data(lineitem_i integer)

Returns: SET OF flat_lineitem_detail

Language: PLPGSQL

DECLARE
    prov_i  INT;
    tag_t   TEXT;
    lida    acq.flat_lineitem_detail%ROWTYPE;
BEGIN
    SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
    IF NOT FOUND THEN RETURN; END IF;

    SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
    IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;

    FOR lida IN
        SELECT  lineitem_i,
                h.holding,
                a.name,
                h.data
          FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
                JOIN acq.provider_holding_subfield_map a USING (subfield)
          WHERE a.provider = prov_i
    LOOP
        RETURN NEXT lida;
    END LOOP;

    RETURN;
END;

Function: acq.fap_limit_100()

Returns: trigger

Language: PLPGSQL

DECLARE
--
total_percent numeric;
--
BEGIN
    SELECT
        sum( percent )
    INTO
        total_percent
    FROM
        acq.fund_allocation_percent AS fap
    WHERE
        fap.funding_source = NEW.funding_source;
    --
    IF total_percent > 100 THEN
        RAISE EXCEPTION 'Total percentages exceed 100 for funding_source %',
            NEW.funding_source;
    ELSE
        RETURN NEW;
    END IF;
END;

Function: acq.find_bad_fy()

Returns: SET OF record

Language: PLPGSQL

DECLARE
	first_row  BOOLEAN;
	curr_year  RECORD;
	prev_year  RECORD;
	return_rec RECORD;
BEGIN
	first_row := true;
	FOR curr_year in
		SELECT
			id,
			calendar,
			year,
			year_begin,
			year_end
		FROM
			acq.fiscal_year
		ORDER BY
			calendar,
			year_begin
	LOOP
		--
		IF first_row THEN
			first_row := FALSE;
		ELSIF curr_year.calendar    = prev_year.calendar THEN
			IF curr_year.year_begin > prev_year.year_end THEN
				-- This ugly kludge works around the fact that older
				-- versions of PostgreSQL don't support RETURN QUERY SELECT
				FOR return_rec IN SELECT
					prev_year.id,
					prev_year.year,
					'Gap between fiscal years'::TEXT
				LOOP
					RETURN NEXT return_rec;
				END LOOP;
			ELSIF curr_year.year_begin < prev_year.year_end THEN
				FOR return_rec IN SELECT
					prev_year.id,
					prev_year.year,
					'Overlapping fiscal years'::TEXT
				LOOP
					RETURN NEXT return_rec;
				END LOOP;
			ELSIF curr_year.year < prev_year.year THEN
				FOR return_rec IN SELECT
					prev_year.id,
					prev_year.year,
					'Fiscal years out of order'::TEXT
				LOOP
					RETURN NEXT return_rec;
				END LOOP;
			END IF;
		END IF;
		--
		prev_year := curr_year;
	END LOOP;
	--
	RETURN;
END;

Function: acq.fund_alloc_percent_val()

Returns: trigger

Language: PLPGSQL

--
DECLARE
--
dummy int := 0;
--
BEGIN
    SELECT
        1
    INTO
        dummy
    FROM
        acq.fund
    WHERE
        org = NEW.org
        AND code = NEW.fund_code
        LIMIT 1;
    --
    IF dummy = 1 then
        RETURN NEW;
    ELSE
        RAISE EXCEPTION 'No fund exists for org % and code %', NEW.org, NEW.fund_code;
    END IF;
END;

Function: acq.po_org_name_date_unique()

Returns: trigger

Language: PLPGSQL

DECLARE
	collision INT;
BEGIN
	--
	-- If order_date is not null, then make sure we don't have a collision
	-- on order_date (truncated to day), org, and name
	--
	IF NEW.order_date IS NULL THEN
		RETURN NEW;
	END IF;
	--
	-- In the WHERE clause, we compare the order_dates without regard to time of day.
	-- We use a pair of inequalities instead of comparing truncated dates so that the
	-- query can do an indexed range scan.
	--
	SELECT 1 INTO collision
	FROM acq.purchase_order
	WHERE
		ordering_agency = NEW.ordering_agency
		AND name = NEW.name
		AND order_date >= date_trunc( 'day', NEW.order_date )
		AND order_date <  date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
		AND id <> NEW.id;
	--
	IF collision IS NULL THEN
		-- okay, no collision
		RETURN NEW;
	ELSE
		-- collision; nip it in the bud
		RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
			NEW.ordering_agency, NEW.order_date, NEW.name;
	END IF;
END;

Function: acq.propagate_funds_by_org_tree(org_unit_id integer, user_id integer, old_year integer)

Returns: void

Language: PLPGSQL

DECLARE
--
new_id      INT;
old_fund    RECORD;
org_found   BOOLEAN;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
	ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
		RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		SELECT TRUE INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id is invalid';
		END IF;
	END IF;
	--
	-- Loop over the applicable funds
	--
	FOR old_fund in SELECT * FROM acq.fund
	WHERE
		year = old_year
		AND propagate
		AND org in (
			SELECT id FROM actor.org_unit_descendants( org_unit_id )
		)
	LOOP
		BEGIN
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				old_fund.org,
				old_fund.name,
				old_year + 1,
				old_fund.currency_type,
				old_fund.code,
				old_fund.rollover,
				true,
				old_fund.balance_warning_percent,
				old_fund.balance_stop_percent
			)
			RETURNING id INTO new_id;
		EXCEPTION
			WHEN unique_violation THEN
				--RAISE NOTICE 'Fund % already propagated', old_fund.id;
				CONTINUE;
		END;
		--RAISE NOTICE 'Propagating fund % to fund %',
		--	old_fund.code, new_id;
	END LOOP;
END;

Function: acq.propagate_funds_by_org_unit(org_unit_id integer, user_id integer, old_year integer)

Returns: void

Language: PLPGSQL

DECLARE
--
new_id      INT;
old_fund    RECORD;
org_found   BOOLEAN;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
	ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
		RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		SELECT TRUE INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id is invalid';
		END IF;
	END IF;
	--
	-- Loop over the applicable funds
	--
	FOR old_fund in SELECT * FROM acq.fund
	WHERE
		year = old_year
		AND propagate
		AND org = org_unit_id
	LOOP
		BEGIN
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				old_fund.org,
				old_fund.name,
				old_year + 1,
				old_fund.currency_type,
				old_fund.code,
				old_fund.rollover,
				true,
				old_fund.balance_warning_percent,
				old_fund.balance_stop_percent
			)
			RETURNING id INTO new_id;
		EXCEPTION
			WHEN unique_violation THEN
				--RAISE NOTICE 'Fund % already propagated', old_fund.id;
				CONTINUE;
		END;
		--RAISE NOTICE 'Propagating fund % to fund %',
		--	old_fund.code, new_id;
	END LOOP;
END;

Function: acq.purchase_order_name_default()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF NEW.name IS NULL THEN
		NEW.name := NEW.id::TEXT;
	END IF;

	RETURN NEW;
END;

Function: acq.rollover_funds_by_org_tree(org_unit_id integer, user_id integer, old_year integer)

Returns: void

Language: PLPGSQL

DECLARE
--
new_fund    INT;
new_year    INT := old_year + 1;
org_found   BOOL;
xfer_amount NUMERIC;
roll_fund   RECORD;
deb         RECORD;
detail      RECORD;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
    ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
        RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		--
		-- Validate the org unit
		--
		SELECT TRUE
		INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
		END IF;
	END IF;
	--
	-- Loop over the propagable funds to identify the details
	-- from the old fund plus the id of the new one, if it exists.
	--
	FOR roll_fund in
	SELECT
	    oldf.id AS old_fund,
	    oldf.org,
	    oldf.name,
	    oldf.currency_type,
	    oldf.code,
		oldf.rollover,
	    newf.id AS new_fund_id
	FROM
    	acq.fund AS oldf
    	LEFT JOIN acq.fund AS newf
        	ON ( oldf.code = newf.code )
	WHERE
 		    oldf.year = old_year
		AND oldf.propagate
        AND newf.year = new_year
		AND oldf.org in (
			SELECT id FROM actor.org_unit_descendants( org_unit_id )
		)
	LOOP
		--RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
		--
		IF roll_fund.new_fund_id IS NULL THEN
			--
			-- The old fund hasn't been propagated yet.  Propagate it now.
			--
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				roll_fund.org,
				roll_fund.name,
				new_year,
				roll_fund.currency_type,
				roll_fund.code,
				true,
				true,
				roll_fund.balance_warning_percent,
				roll_fund.balance_stop_percent
			)
			RETURNING id INTO new_fund;
		ELSE
			new_fund = roll_fund.new_fund_id;
		END IF;
		--
		-- Determine the amount to transfer
		--
		SELECT amount
		INTO xfer_amount
		FROM acq.fund_spent_balance
		WHERE fund = roll_fund.old_fund;
		--
		IF xfer_amount <> 0 THEN
			IF roll_fund.rollover THEN
				--
				-- Transfer balance from old fund to new
				--
				--RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					new_fund,
					xfer_amount,
					user_id,
					'Rollover'
				);
			ELSE
				--
				-- Transfer balance from old fund to the void
				--
				-- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					NULL,
					NULL,
					user_id,
					'Rollover'
				);
			END IF;
		END IF;
		--
		IF roll_fund.rollover THEN
			--
			-- Move any lineitems from the old fund to the new one
			-- where the associated debit is an encumbrance.
			--
			-- Any other tables tying expenditure details to funds should
			-- receive similar treatment.  At this writing there are none.
			--
			UPDATE acq.lineitem_detail
			SET fund = new_fund
			WHERE
    			fund = roll_fund.old_fund -- this condition may be redundant
    			AND fund_debit in
    			(
        			SELECT id
        			FROM acq.fund_debit
        			WHERE
            			fund = roll_fund.old_fund
            			AND encumbrance
    			);
			--
			-- Move encumbrance debits from the old fund to the new fund
			--
			UPDATE acq.fund_debit
			SET fund = new_fund
			wHERE
				fund = roll_fund.old_fund
				AND encumbrance;
		END IF;
		--
		-- Mark old fund as inactive, now that we've closed it
		--
		UPDATE acq.fund
		SET active = FALSE
		WHERE id = roll_fund.old_fund;
	END LOOP;
END;

Function: acq.rollover_funds_by_org_unit(org_unit_id integer, user_id integer, old_year integer)

Returns: void

Language: PLPGSQL

DECLARE
--
new_fund    INT;
new_year    INT := old_year + 1;
org_found   BOOL;
xfer_amount NUMERIC;
roll_fund   RECORD;
deb         RECORD;
detail      RECORD;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
    ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
        RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		--
		-- Validate the org unit
		--
		SELECT TRUE
		INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
		END IF;
	END IF;
	--
	-- Loop over the propagable funds to identify the details
	-- from the old fund plus the id of the new one, if it exists.
	--
	FOR roll_fund in
	SELECT
	    oldf.id AS old_fund,
	    oldf.org,
	    oldf.name,
	    oldf.currency_type,
	    oldf.code,
		oldf.rollover,
	    newf.id AS new_fund_id
	FROM
    	acq.fund AS oldf
    	LEFT JOIN acq.fund AS newf
        	ON ( oldf.code = newf.code )
	WHERE
		    oldf.org = org_unit_id
 		and oldf.year = old_year
		and oldf.propagate
        and newf.year = new_year
	LOOP
		--RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
		--
		IF roll_fund.new_fund_id IS NULL THEN
			--
			-- The old fund hasn't been propagated yet.  Propagate it now.
			--
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				roll_fund.org,
				roll_fund.name,
				new_year,
				roll_fund.currency_type,
				roll_fund.code,
				true,
				true,
				roll_fund.balance_warning_percent,
				roll_fund.balance_stop_percent
			)
			RETURNING id INTO new_fund;
		ELSE
			new_fund = roll_fund.new_fund_id;
		END IF;
		--
		-- Determine the amount to transfer
		--
		SELECT amount
		INTO xfer_amount
		FROM acq.fund_spent_balance
		WHERE fund = roll_fund.old_fund;
		--
		IF xfer_amount <> 0 THEN
			IF roll_fund.rollover THEN
				--
				-- Transfer balance from old fund to new
				--
				--RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					new_fund,
					xfer_amount,
					user_id,
					'Rollover'
				);
			ELSE
				--
				-- Transfer balance from old fund to the void
				--
				-- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					NULL,
					NULL,
					user_id,
					'Rollover'
				);
			END IF;
		END IF;
		--
		IF roll_fund.rollover THEN
			--
			-- Move any lineitems from the old fund to the new one
			-- where the associated debit is an encumbrance.
			--
			-- Any other tables tying expenditure details to funds should
			-- receive similar treatment.  At this writing there are none.
			--
			UPDATE acq.lineitem_detail
			SET fund = new_fund
			WHERE
    			fund = roll_fund.old_fund -- this condition may be redundant
    			AND fund_debit in
    			(
        			SELECT id
        			FROM acq.fund_debit
        			WHERE
            			fund = roll_fund.old_fund
            			AND encumbrance
    			);
			--
			-- Move encumbrance debits from the old fund to the new fund
			--
			UPDATE acq.fund_debit
			SET fund = new_fund
			wHERE
				fund = roll_fund.old_fund
				AND encumbrance;
		END IF;
		--
		-- Mark old fund as inactive, now that we've closed it
		--
		UPDATE acq.fund
		SET active = FALSE
		WHERE id = roll_fund.old_fund;
	END LOOP;
END;

Function: acq.transfer_fund(xfer_note integer, user_id numeric, new_amount integer, new_fund numeric, old_amount integer, old_fund text)

Returns: void

Language: PLPGSQL

/* -------------------------------------------------------------------------------

Function to transfer money from one fund to another.

A transfer is represented as a pair of entries in acq.fund_allocation, with a
negative amount for the old (losing) fund and a positive amount for the new
(gaining) fund.  In some cases there may be more than one such pair of entries
in order to pull the money from different funding sources, or more specifically
from different funding source credits.  For each such pair there is also an
entry in acq.fund_transfer.

Since funding_source is a non-nullable column in acq.fund_allocation, we must
choose a funding source for the transferred money to come from.  This choice
must meet two constraints, so far as possible:

1. The amount transferred from a given funding source must not exceed the
amount allocated to the old fund by the funding source.  To that end we
compare the amount being transferred to the amount allocated.

2. We shouldn't transfer money that has already been spent or encumbered, as
defined by the funding attribution process.  We attribute expenses to the
oldest funding source credits first.  In order to avoid transferring that
attributed money, we reverse the priority, transferring from the newest funding
source credits first.  There can be no guarantee that this approach will
avoid overcommitting a fund, but no other approach can do any better.

In this context the age of a funding source credit is defined by the
deadline_date for credits with deadline_dates, and by the effective_date for
credits without deadline_dates, with the proviso that credits with deadline_dates
are all considered "older" than those without.

----------

In the signature for this function, there is one last parameter commented out,
named "funding_source_in".  Correspondingly, the WHERE clause for the query
driving the main loop has an OR clause commented out, which references the
funding_source_in parameter.

If these lines are uncommented, this function will allow the user optionally to
restrict a fund transfer to a specified funding source.  If the source
parameter is left NULL, then there will be no such restriction.

------------------------------------------------------------------------------- */ 
DECLARE
	same_currency      BOOLEAN;
	currency_ratio     NUMERIC;
	old_fund_currency  TEXT;
	old_remaining      NUMERIC;  -- in currency of old fund
	new_fund_currency  TEXT;
	new_fund_active    BOOLEAN;
	new_remaining      NUMERIC;  -- in currency of new fund
	curr_old_amt       NUMERIC;  -- in currency of old fund
	curr_new_amt       NUMERIC;  -- in currency of new fund
	source_addition    NUMERIC;  -- in currency of funding source
	source_deduction   NUMERIC;  -- in currency of funding source
	orig_allocated_amt NUMERIC;  -- in currency of funding source
	allocated_amt      NUMERIC;  -- in currency of fund
	source             RECORD;
BEGIN
	--
	-- Sanity checks
	--
	IF old_fund IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
	END IF;
	--
	IF old_amount IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
	END IF;
	--
	-- The new fund and its amount must be both NULL or both not NULL.
	--
	IF new_fund IS NOT NULL AND new_amount IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
	END IF;
	--
	IF new_fund IS NULL AND new_amount IS NOT NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
	END IF;
	--
	-- Initialize the amounts to be transferred, each denominated
	-- in the currency of its respective fund.  They will be
	-- reduced on each iteration of the loop.
	--
	old_remaining := old_amount;
	new_remaining := new_amount;
	--
	-- RAISE NOTICE 'Transferring % in fund % to % in fund %',
	--	old_amount, old_fund, new_amount, new_fund;
	--
	-- Get the currency types of the old and new funds.
	--
	SELECT
		currency_type
	INTO
		old_fund_currency
	FROM
		acq.fund
	WHERE
		id = old_fund;
	--
	IF old_fund_currency IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
	END IF;
	--
	IF new_fund IS NOT NULL THEN
		SELECT
			currency_type,
			active
		INTO
			new_fund_currency,
			new_fund_active
		FROM
			acq.fund
		WHERE
			id = new_fund;
		--
		IF new_fund_currency IS NULL THEN
			RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
		ELSIF NOT new_fund_active THEN
			--
			-- No point in putting money into a fund from whence you can't spend it
			--
			RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
		END IF;
		--
		IF new_amount = old_amount THEN
			same_currency := true;
			currency_ratio := 1;
		ELSE
			--
			-- We'll have to translate currency between funds.  We presume that
			-- the calling code has already applied an appropriate exchange rate,
			-- so we'll apply the same conversion to each sub-transfer.
			--
			same_currency := false;
			currency_ratio := new_amount / old_amount;
		END IF;
	END IF;
	--
	-- Identify the funding source(s) from which we want to transfer the money.
	-- The principle is that we want to transfer the newest money first, because
	-- we spend the oldest money first.  The priority for spending is defined
	-- by a sort of the view acq.ordered_funding_source_credit.
	--
	FOR source in
		SELECT
			ofsc.id,
			ofsc.funding_source,
			ofsc.amount,
			ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
				AS converted_amt,
			fs.currency_type
		FROM
			acq.ordered_funding_source_credit AS ofsc,
			acq.funding_source fs
		WHERE
			ofsc.funding_source = fs.id
			and ofsc.funding_source IN
			(
				SELECT funding_source
				FROM acq.fund_allocation
				WHERE fund = old_fund
			)
			-- and
			-- (
			-- 	ofsc.funding_source = funding_source_in
			-- 	OR funding_source_in IS NULL
			-- )
		ORDER BY
			ofsc.sort_priority desc,
			ofsc.sort_date desc,
			ofsc.id desc
	LOOP
		--
		-- Determine how much money the old fund got from this funding source,
		-- denominated in the currency types of the source and of the fund.
		-- This result may reflect transfers from previous iterations.
		--
		SELECT
			COALESCE( sum( amount ), 0 ),
			COALESCE( sum( amount )
				* acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
		INTO
			orig_allocated_amt,     -- in currency of the source
			allocated_amt           -- in currency of the old fund
		FROM
			acq.fund_allocation
		WHERE
			fund = old_fund
			and funding_source = source.funding_source;
		--	
		-- Determine how much to transfer from this credit, in the currency
		-- of the fund.   Begin with the amount remaining to be attributed:
		--
		curr_old_amt := old_remaining;
		--
		-- Can't attribute more than was allocated from the fund:
		--
		IF curr_old_amt > allocated_amt THEN
			curr_old_amt := allocated_amt;
		END IF;
		--
		-- Can't attribute more than the amount of the current credit:
		--
		IF curr_old_amt > source.converted_amt THEN
			curr_old_amt := source.converted_amt;
		END IF;
		--
		curr_old_amt := trunc( curr_old_amt, 2 );
		--
		old_remaining := old_remaining - curr_old_amt;
		--
		-- Determine the amount to be deducted, if any,
		-- from the old allocation.
		--
		IF old_remaining > 0 THEN
			--
			-- In this case we're using the whole allocation, so use that
			-- amount directly instead of applying a currency translation
			-- and thereby inviting round-off errors.
			--
			source_deduction := - orig_allocated_amt;
		ELSE 
			source_deduction := trunc(
				( - curr_old_amt ) *
					acq.exchange_ratio( old_fund_currency, source.currency_type ),
				2 );
		END IF;
		--
		IF source_deduction <> 0 THEN
			--
			-- Insert negative allocation for old fund in fund_allocation,
			-- converted into the currency of the funding source
			--
			INSERT INTO acq.fund_allocation (
				funding_source,
				fund,
				amount,
				allocator,
				note
			) VALUES (
				source.funding_source,
				old_fund,
				source_deduction,
				user_id,
				'Transfer to fund ' || new_fund
			);
		END IF;
		--
		IF new_fund IS NOT NULL THEN
			--
			-- Determine how much to add to the new fund, in
			-- its currency, and how much remains to be added:
			--
			IF same_currency THEN
				curr_new_amt := curr_old_amt;
			ELSE
				IF old_remaining = 0 THEN
					--
					-- This is the last iteration, so nothing should be left
					--
					curr_new_amt := new_remaining;
					new_remaining := 0;
				ELSE
					curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
					new_remaining := new_remaining - curr_new_amt;
				END IF;
			END IF;
			--
			-- Determine how much to add, if any,
			-- to the new fund's allocation.
			--
			IF old_remaining > 0 THEN
				--
				-- In this case we're using the whole allocation, so use that amount
				-- amount directly instead of applying a currency translation and
				-- thereby inviting round-off errors.
				--
				source_addition := orig_allocated_amt;
			ELSIF source.currency_type = old_fund_currency THEN
				--
				-- In this case we don't need a round trip currency translation,
				-- thereby inviting round-off errors:
				--
				source_addition := curr_old_amt;
			ELSE 
				source_addition := trunc(
					curr_new_amt *
						acq.exchange_ratio( new_fund_currency, source.currency_type ),
					2 );
			END IF;
			--
			IF source_addition <> 0 THEN
				--
				-- Insert positive allocation for new fund in fund_allocation,
				-- converted to the currency of the founding source
				--
				INSERT INTO acq.fund_allocation (
					funding_source,
					fund,
					amount,
					allocator,
					note
				) VALUES (
					source.funding_source,
					new_fund,
					source_addition,
					user_id,
					'Transfer from fund ' || old_fund
				);
			END IF;
		END IF;
		--
		IF trunc( curr_old_amt, 2 ) <> 0
		OR trunc( curr_new_amt, 2 ) <> 0 THEN
			--
			-- Insert row in fund_transfer, using amounts in the currency of the funds
			--
			INSERT INTO acq.fund_transfer (
				src_fund,
				src_amount,
				dest_fund,
				dest_amount,
				transfer_user,
				note,
				funding_source_credit
			) VALUES (
				old_fund,
				trunc( curr_old_amt, 2 ),
				new_fund,
				trunc( curr_new_amt, 2 ),
				user_id,
				xfer_note,
				source.id
			);
		END IF;
		--
		if old_remaining <= 0 THEN
			EXIT;                   -- Nothing more to be transferred
		END IF;
	END LOOP;
END;

Schema action


Table: action.aged_circulation

action.aged_circulation Structure
F-Key Name Type Description
usr_post_code text
usr_home_ou integer NOT NULL
usr_profile integer NOT NULL
usr_birth_year integer
copy_call_number integer NOT NULL
copy_owning_lib integer NOT NULL
copy_circ_lib integer NOT NULL
copy_bib_record bigint NOT NULL
id bigint PRIMARY KEY
xact_start timestamp with time zone NOT NULL
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint NOT NULL
circ_lib integer NOT NULL
circ_staff integer NOT NULL
checkin_staff integer
checkin_lib integer
renewal_remaining integer NOT NULL
grace_period interval NOT NULL
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone NOT NULL
duration interval
fine_interval interval NOT NULL
recurring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean NOT NULL
desk_renewal boolean NOT NULL
opac_renewal boolean NOT NULL
duration_rule text NOT NULL
recurring_fine_rule text NOT NULL
max_fine_rule text NOT NULL
stop_fines text
workstation integer
checkin_workstation integer
copy_location integer NOT NULL
checkin_scan_time timestamp with time zone
parent_circ bigint
action_aged_circulation_target_copy_idx target_copy aged_circ_circ_lib_idx circ_lib aged_circ_copy_circ_lib_idx copy_circ_lib aged_circ_copy_location_idx copy_location aged_circ_copy_owning_lib_idx copy_owning_lib aged_circ_start_idx xact_start

Index - Schema action


View: action.all_circulation

action.all_circulation Structure
F-Key Name Type Description
id bigint
usr_post_code text
usr_home_ou integer
usr_profile integer
usr_birth_year integer
copy_call_number bigint
copy_location integer
copy_owning_lib integer
copy_circ_lib integer
copy_bib_record bigint
xact_start timestamp with time zone
xact_finish timestamp with time zone
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
grace_period interval
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recurring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recurring_fine_rule text
max_fine_rule text
stop_fines text
workstation integer
checkin_workstation integer
checkin_scan_time timestamp with time zone
parent_circ bigint
SELECT aged_circulation.id
, aged_circulation.usr_post_code
, aged_circulation.usr_home_ou
, aged_circulation.usr_profile
, aged_circulation.usr_birth_year
, aged_circulation.copy_call_number
, aged_circulation.copy_location
, aged_circulation.copy_owning_lib
, aged_circulation.copy_circ_lib
, aged_circulation.copy_bib_record
, aged_circulation.xact_start
, aged_circulation.xact_finish
, aged_circulation.target_copy
, aged_circulation.circ_lib
, aged_circulation.circ_staff
, aged_circulation.checkin_staff
, aged_circulation.checkin_lib
, aged_circulation.renewal_remaining
, aged_circulation.grace_period
, aged_circulation.due_date
, aged_circulation.stop_fines_time
, aged_circulation.checkin_time
, aged_circulation.create_time
, aged_circulation.duration
, aged_circulation.fine_interval
, aged_circulation.recurring_fine
, aged_circulation.max_fine
, aged_circulation.phone_renewal
, aged_circulation.desk_renewal
, aged_circulation.opac_renewal
, aged_circulation.duration_rule
, aged_circulation.recurring_fine_rule
, aged_circulation.max_fine_rule
, aged_circulation.stop_fines
, aged_circulation.workstation
, aged_circulation.checkin_workstation
, aged_circulation.checkin_scan_time
, aged_circulation.parent_circ 
FROM action.aged_circulation 
UNION ALLSELECT DISTINCT circ.id
, COALESCE
(a.post_code
     , b.post_code
) AS usr_post_code
, p.home_ou AS usr_home_ou
, p.profile AS usr_profile
, (date_part
     ('year'::text
           , p.dob
     )
)::integer AS usr_birth_year
, cp.call_number AS copy_call_number
, circ.copy_location
, cn.owning_lib AS copy_owning_lib
, cp.circ_lib AS copy_circ_lib
, cn.record AS copy_bib_record
, circ.xact_start
, circ.xact_finish
, circ.target_copy
, circ.circ_lib
, circ.circ_staff
, circ.checkin_staff
, circ.checkin_lib
, circ.renewal_remaining
, circ.grace_period
, circ.due_date
, circ.stop_fines_time
, circ.checkin_time
, circ.create_time
, circ.duration
, circ.fine_interval
, circ.recurring_fine
, circ.max_fine
, circ.phone_renewal
, circ.desk_renewal
, circ.opac_renewal
, circ.duration_rule
, circ.recurring_fine_rule
, circ.max_fine_rule
, circ.stop_fines
, circ.workstation
, circ.checkin_workstation
, circ.checkin_scan_time
, circ.parent_circ 
FROM (
     (
           (
                 (
                       (action.circulation circ 
                          JOIN asset.copy cp 
                            ON (
                                   (circ.target_copy = cp.id)
                             )
                       )
                    JOIN asset.call_number cn 
                      ON (
                             (cp.call_number = cn.id)
                       )
                 )
              JOIN actor.usr p 
                ON (
                       (circ.usr = p.id)
                 )
           )
   LEFT JOIN actor.usr_address a 
          ON (
                 (p.mailing_address = a.id)
           )
     )
LEFT JOIN actor.usr_address b 
    ON (
           (p.billing_address = b.id)
     )
);

Index - Schema action


Table: action.archive_actor_stat_cat

action.archive_actor_stat_cat Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
xact bigint NOT NULL
stat_cat integer NOT NULL
value text NOT NULL

Index - Schema action


Table: action.archive_asset_stat_cat

action.archive_asset_stat_cat Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
xact bigint NOT NULL
stat_cat integer NOT NULL
value text NOT NULL

Index - Schema action


View: action.billable_circulations

action.billable_circulations Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
grace_period interval
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recurring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recurring_fine_rule text
max_fine_rule text
stop_fines text
workstation integer
checkin_workstation integer
copy_location integer
checkin_scan_time timestamp with time zone
parent_circ bigint
SELECT circulation.id
, circulation.usr
, circulation.xact_start
, circulation.xact_finish
, circulation.unrecovered
, circulation.target_copy
, circulation.circ_lib
, circulation.circ_staff
, circulation.checkin_staff
, circulation.checkin_lib
, circulation.renewal_remaining
, circulation.grace_period
, circulation.due_date
, circulation.stop_fines_time
, circulation.checkin_time
, circulation.create_time
, circulation.duration
, circulation.fine_interval
, circulation.recurring_fine
, circulation.max_fine
, circulation.phone_renewal
, circulation.desk_renewal
, circulation.opac_renewal
, circulation.duration_rule
, circulation.recurring_fine_rule
, circulation.max_fine_rule
, circulation.stop_fines
, circulation.workstation
, circulation.checkin_workstation
, circulation.copy_location
, circulation.checkin_scan_time
, circulation.parent_circ 
FROM action.circulation 
WHERE (circulation.xact_finish IS NULL);

Index - Schema action


Table: action.circulation

action.circulation Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.billable_xact_id_seq'::regclass)
actor.usr.id usr integer NOT NULL
xact_start timestamp with time zone NOT NULL DEFAULT now()
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint NOT NULL
actor.org_unit.id circ_lib integer NOT NULL
circ_staff integer NOT NULL
checkin_staff integer
checkin_lib integer
renewal_remaining integer NOT NULL
grace_period interval NOT NULL
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone NOT NULL DEFAULT now()
duration interval
fine_interval interval NOT NULL DEFAULT '1 day'::interval
recurring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean NOT NULL DEFAULT false
desk_renewal boolean NOT NULL DEFAULT false
opac_renewal boolean NOT NULL DEFAULT false
duration_rule text NOT NULL
recurring_fine_rule text NOT NULL
max_fine_rule text NOT NULL
stop_fines text
actor.workstation.id workstation integer
actor.workstation.id checkin_workstation integer
asset.copy_location.id copy_location integer NOT NULL DEFAULT 1
checkin_scan_time timestamp with time zone
action.circulation.id parent_circ bigint

Table action.circulation Inherits billable_xact,

 

action.circulation Constraints
Name Constraint
circulation_stop_fines_check CHECK ((stop_fines = ANY (ARRAY['CHECKIN'::text, 'CLAIMSRETURNED'::text, 'LOST'::text, 'MAXFINES'::text, 'RENEW'::text, 'LONGOVERDUE'::text, 'CLAIMSNEVERCHECKEDOUT'::text])))

Tables referencing this one via Foreign Key Constraints:

action_circulation_target_copy_idx target_copy circ_all_usr_idx usr circ_checkin_staff_idx checkin_staff circ_checkin_time checkin_time) WHERE (checkin_time IS NOT NULL circ_circ_lib_idx circ_lib circ_circ_staff_idx circ_staff circ_open_date_idx xact_start) WHERE (xact_finish IS NULL circ_open_xacts_idx usr) WHERE (xact_finish IS NULL circ_outstanding_idx usr) WHERE (checkin_time IS NULL

Index - Schema action


Table: action.circulation_limit_group_map

action.circulation_limit_group_map Structure
F-Key Name Type Description
action.circulation.id circ bigint PRIMARY KEY
config.circ_limit_group.id limit_group integer PRIMARY KEY

Index - Schema action


Table: action.fieldset

action.fieldset Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer NOT NULL
actor.org_unit.id owning_lib integer UNIQUE#1 NOT NULL
status text NOT NULL
creation_time timestamp with time zone NOT NULL DEFAULT now()
scheduled_time timestamp with time zone
applied_time timestamp with time zone
classname text NOT NULL
name text UNIQUE#1 NOT NULL
query.stored_query.id stored_query integer
pkey_value text

 

action.fieldset Constraints
Name Constraint
fieldset_one_or_the_other CHECK ((((stored_query IS NOT NULL) AND (pkey_value IS NULL)) OR ((pkey_value IS NOT NULL) AND (stored_query IS NULL))))
valid_status CHECK ((status = ANY (ARRAY['PENDING'::text, 'APPLIED'::text, 'ERROR'::text])))

Tables referencing this one via Foreign Key Constraints:

action_fieldset_sched_time_idx scheduled_time action_owner_idx owner

Index - Schema action


Table: action.fieldset_col_val

action.fieldset_col_val Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.fieldset.id fieldset integer UNIQUE#1 NOT NULL
col text UNIQUE#1 NOT NULL
val text

Index - Schema action


Table: action.hold_copy_map

action.hold_copy_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
action.hold_request.id hold integer UNIQUE#1 NOT NULL
target_copy bigint UNIQUE#1 NOT NULL
acm_copy_idx target_copy

Index - Schema action


Table: action.hold_notification

action.hold_notification Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.hold_request.id hold integer NOT NULL
actor.usr.id notify_staff integer
notify_time timestamp with time zone NOT NULL DEFAULT now()
method text NOT NULL
note text
ahn_hold_idx hold ahn_notify_staff_idx notify_staff

Index - Schema action


Table: action.hold_request

action.hold_request Structure
F-Key Name Type Description
id serial PRIMARY KEY
request_time timestamp with time zone NOT NULL DEFAULT now()
capture_time timestamp with time zone
fulfillment_time timestamp with time zone
checkin_time timestamp with time zone
return_time timestamp with time zone
prev_check_time timestamp with time zone
expire_time timestamp with time zone
cancel_time timestamp with time zone
action.hold_request_cancel_cause.id cancel_cause integer
cancel_note text
target bigint NOT NULL
current_copy bigint
actor.usr.id fulfillment_staff integer
actor.org_unit.id fulfillment_lib integer
actor.org_unit.id request_lib integer NOT NULL
actor.usr.id requestor integer NOT NULL
actor.usr.id usr integer NOT NULL
selection_ou integer NOT NULL
selection_depth integer NOT NULL
actor.org_unit.id pickup_lib integer NOT NULL
hold_type text NOT NULL
holdable_formats text
phone_notify text
email_notify boolean NOT NULL DEFAULT true
sms_notify text
config.sms_carrier.id sms_carrier integer
frozen boolean NOT NULL DEFAULT false
thaw_date timestamp with time zone
shelf_time timestamp with time zone
cut_in_line boolean
mint_condition boolean NOT NULL DEFAULT true
shelf_expire_time timestamp with time zone
actor.org_unit.id current_shelf_lib integer

 

action.hold_request Constraints
Name Constraint
sms_check CHECK (((sms_notify IS NULL) OR (sms_carrier IS NOT NULL)))

Tables referencing this one via Foreign Key Constraints:

hold_request_current_copy_idx current_copy hold_request_fulfillment_staff_idx fulfillment_staff hold_request_pickup_lib_idx pickup_lib hold_request_prev_check_time_idx prev_check_time hold_request_requestor_idx requestor hold_request_target_idx target hold_request_usr_idx usr

Index - Schema action


Table: action.hold_request_cancel_cause

action.hold_request_cancel_cause Structure
F-Key Name Type Description
id serial PRIMARY KEY
label text UNIQUE

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.hold_request_note

action.hold_request_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
action.hold_request.id hold bigint NOT NULL
title text NOT NULL
body text NOT NULL
slip boolean NOT NULL DEFAULT false
pub boolean NOT NULL DEFAULT false
staff boolean NOT NULL DEFAULT false
ahrn_hold_idx hold

Index - Schema action


Table: action.hold_transit_copy

action.hold_transit_copy Structure
F-Key Name Type Description
id integer PRIMARY KEY DEFAULT nextval('action.transit_copy_id_seq'::regclass)
source_send_time timestamp with time zone
dest_recv_time timestamp with time zone
target_copy bigint NOT NULL
source integer NOT NULL
dest integer NOT NULL
prev_hop integer
copy_status integer NOT NULL
persistant_transfer boolean NOT NULL DEFAULT false
prev_dest integer
action.hold_request.id hold integer

Table action.hold_transit_copy Inherits transit_copy,

active_hold_transit_cp_idx target_copy active_hold_transit_dest_idx dest active_hold_transit_source_idx source

Index - Schema action


Table: action.in_house_use

action.in_house_use Structure
F-Key Name Type Description
id serial PRIMARY KEY
item bigint NOT NULL
actor.usr.id staff integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
use_time timestamp with time zone NOT NULL DEFAULT now()
action_in_house_use_staff_idx staff

Index - Schema action


Table: action.non_cat_in_house_use

action.non_cat_in_house_use Structure
F-Key Name Type Description
id serial PRIMARY KEY
config.non_cataloged_type.id item_type bigint NOT NULL
actor.usr.id staff integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
use_time timestamp with time zone NOT NULL DEFAULT now()
non_cat_in_house_use_staff_idx staff

Index - Schema action


Table: action.non_cataloged_circulation

action.non_cataloged_circulation Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id patron integer NOT NULL
actor.usr.id staff integer NOT NULL
actor.org_unit.id circ_lib integer NOT NULL
config.non_cataloged_type.id item_type integer NOT NULL
circ_time timestamp with time zone NOT NULL DEFAULT now()
action_non_cat_circ_patron_idx patron action_non_cat_circ_staff_idx staff

Index - Schema action


View: action.open_circulation

action.open_circulation Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
grace_period interval
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recurring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recurring_fine_rule text
max_fine_rule text
stop_fines text
workstation integer
checkin_workstation integer
copy_location integer
checkin_scan_time timestamp with time zone
parent_circ bigint
SELECT circulation.id
, circulation.usr
, circulation.xact_start
, circulation.xact_finish
, circulation.unrecovered
, circulation.target_copy
, circulation.circ_lib
, circulation.circ_staff
, circulation.checkin_staff
, circulation.checkin_lib
, circulation.renewal_remaining
, circulation.grace_period
, circulation.due_date
, circulation.stop_fines_time
, circulation.checkin_time
, circulation.create_time
, circulation.duration
, circulation.fine_interval
, circulation.recurring_fine
, circulation.max_fine
, circulation.phone_renewal
, circulation.desk_renewal
, circulation.opac_renewal
, circulation.duration_rule
, circulation.recurring_fine_rule
, circulation.max_fine_rule
, circulation.stop_fines
, circulation.workstation
, circulation.checkin_workstation
, circulation.copy_location
, circulation.checkin_scan_time
, circulation.parent_circ 
FROM action.circulation 
WHERE (circulation.checkin_time IS NULL)
ORDER BY circulation.due_date;

Index - Schema action


Table: action.reservation_transit_copy

action.reservation_transit_copy Structure
F-Key Name Type Description
id integer PRIMARY KEY DEFAULT nextval('action.transit_copy_id_seq'::regclass)
source_send_time timestamp with time zone
dest_recv_time timestamp with time zone
booking.resource.id target_copy bigint NOT NULL
source integer NOT NULL
dest integer NOT NULL
prev_hop integer
copy_status integer NOT NULL
persistant_transfer boolean NOT NULL DEFAULT false
prev_dest integer
booking.reservation.id reservation integer

Table action.reservation_transit_copy Inherits transit_copy,

active_reservation_transit_cp_idx target_copy active_reservation_transit_dest_idx dest active_reservation_transit_source_idx source

Index - Schema action


Table: action.survey

action.survey Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer NOT NULL
start_date timestamp with time zone NOT NULL DEFAULT now()
end_date timestamp with time zone NOT NULL DEFAULT (now() + '10 years'::interval)
usr_summary boolean NOT NULL DEFAULT false
opac boolean NOT NULL DEFAULT false
poll boolean NOT NULL DEFAULT false
required boolean NOT NULL DEFAULT false
name text NOT NULL
description text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.survey_answer

action.survey_answer Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.survey_question.id question integer NOT NULL
answer text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.survey_question

action.survey_question Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.survey.id survey integer NOT NULL
question text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.survey_response

action.survey_response Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
response_group_id integer
usr integer
action.survey.id survey integer NOT NULL
action.survey_question.id question integer NOT NULL
action.survey_answer.id answer integer NOT NULL
answer_date timestamp with time zone
effective_date timestamp with time zone NOT NULL DEFAULT now()
action_survey_response_usr_idx usr

Index - Schema action


Table: action.transit_copy

action.transit_copy Structure
F-Key Name Type Description
id serial PRIMARY KEY
source_send_time timestamp with time zone
dest_recv_time timestamp with time zone
target_copy bigint NOT NULL
actor.org_unit.id source integer NOT NULL
actor.org_unit.id dest integer NOT NULL
action.transit_copy.id prev_hop integer
config.copy_status.id copy_status integer NOT NULL
persistant_transfer boolean NOT NULL DEFAULT false
actor.org_unit.id prev_dest integer

Tables referencing this one via Foreign Key Constraints:

active_transit_cp_idx target_copy active_transit_dest_idx dest active_transit_source_idx source

Index - Schema action


View: action.unfulfilled_hold_innermost_loop

action.unfulfilled_hold_innermost_loop Structure
F-Key Name Type Description
hold integer
circ_lib integer
count bigint
SELECT DISTINCT l.hold
, l.circ_lib
, l.count 
FROM (action.unfulfilled_hold_loops l 
  JOIN action.unfulfilled_hold_min_loop m 
 USING (hold)
)
WHERE (l.count = m.min);

Index - Schema action


Table: action.unfulfilled_hold_list

action.unfulfilled_hold_list Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
current_copy bigint NOT NULL
hold integer NOT NULL
circ_lib integer NOT NULL
fail_time timestamp with time zone NOT NULL DEFAULT now()
uhr_hold_idx hold

Index - Schema action


View: action.unfulfilled_hold_loops

action.unfulfilled_hold_loops Structure
F-Key Name Type Description
hold integer
circ_lib integer
count bigint
SELECT u.hold
, c.circ_lib
, count
(*) AS count 
FROM (action.unfulfilled_hold_list u 
  JOIN asset.copy c 
    ON (
           (c.id = u.current_copy)
     )
)
GROUP BY u.hold
, c.circ_lib;

Index - Schema action


View: action.unfulfilled_hold_max_loop

action.unfulfilled_hold_max_loop Structure
F-Key Name Type Description
hold integer
max bigint
SELECT unfulfilled_hold_loops.hold
, max
(unfulfilled_hold_loops.count) AS max 
FROM action.unfulfilled_hold_loops 
GROUP BY unfulfilled_hold_loops.hold;

Index - Schema action


View: action.unfulfilled_hold_min_loop

action.unfulfilled_hold_min_loop Structure
F-Key Name Type Description
hold integer
min bigint
SELECT unfulfilled_hold_loops.hold
, min
(unfulfilled_hold_loops.count) AS min 
FROM action.unfulfilled_hold_loops 
GROUP BY unfulfilled_hold_loops.hold;

Index - Schema action


Function: action.age_circ_on_delete()

Returns: trigger

Language: PLPGSQL

DECLARE
found char := 'N';
BEGIN

    -- If there are any renewals for this circulation, don't archive or delete
    -- it yet.   We'll do so later, when we archive and delete the renewals.

    SELECT 'Y' INTO found
    FROM action.circulation
    WHERE parent_circ = OLD.id
    LIMIT 1;

    IF found = 'Y' THEN
        RETURN NULL;  -- don't delete
	END IF;

    -- Archive a copy of the old row to action.aged_circulation

    INSERT INTO action.aged_circulation
        (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
        copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
        circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
        stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
        max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
        max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
      SELECT
        id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
        copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
        circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
        stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
        max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
        max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
        FROM action.all_circulation WHERE id = OLD.id;

    RETURN OLD;
END;

Function: action.age_parent_circ_on_delete()

Returns: trigger

Language: PLPGSQL

BEGIN

    -- Having deleted a renewal, we can delete the original circulation (or a previous
    -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
    -- deletion of any prior parents, etc. recursively.

    IF OLD.parent_circ IS NOT NULL THEN
        DELETE FROM action.circulation
        WHERE id = OLD.parent_circ;
    END IF;

    RETURN OLD;
END;

Function: action.apply_fieldset(query integer, pkey_name text, table_name text, fieldset_id text)

Returns: text

Language: PLPGSQL

Applies a specified fieldset, using a supplied table name and primary key name. The query parameter should be non-null only for query-based fieldsets. Returns NULL if successful, or an error message if not.

DECLARE
	statement TEXT;
	fs_status TEXT;
	fs_pkey_value TEXT;
	fs_query TEXT;
	sep CHAR;
	status_code TEXT;
	msg TEXT;
	update_count INT;
	cv RECORD;
BEGIN
	-- Sanity checks
	IF fieldset_id IS NULL THEN
		RETURN 'Fieldset ID parameter is NULL';
	END IF;
	IF table_name IS NULL THEN
		RETURN 'Table name parameter is NULL';
	END IF;
	IF pkey_name IS NULL THEN
		RETURN 'Primary key name parameter is NULL';
	END IF;
	--
	statement := 'UPDATE ' || table_name || ' SET';
	--
	SELECT
		status,
		quote_literal( pkey_value )
	INTO
		fs_status,
		fs_pkey_value
	FROM
		action.fieldset
	WHERE
		id = fieldset_id;
	--
	IF fs_status IS NULL THEN
		RETURN 'No fieldset found for id = ' || fieldset_id;
	ELSIF fs_status = 'APPLIED' THEN
		RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
	END IF;
	--
	sep := '';
	FOR cv IN
		SELECT  col,
				val
		FROM    action.fieldset_col_val
		WHERE   fieldset = fieldset_id
	LOOP
		statement := statement || sep || ' ' || cv.col
					 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
		sep := ',';
	END LOOP;
	--
	IF sep = '' THEN
		RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
	END IF;
	--
	-- Add the WHERE clause.  This differs according to whether it's a
	-- single-row fieldset or a query-based fieldset.
	--
	IF query IS NULL        AND fs_pkey_value IS NULL THEN
		RETURN 'Incomplete fieldset: neither a primary key nor a query available';
	ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
	    fs_query := rtrim( query, ';' );
	    statement := statement || ' WHERE ' || pkey_name || ' IN ( '
	                 || fs_query || ' );';
	ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
		statement := statement || ' WHERE ' || pkey_name || ' = '
				     || fs_pkey_value || ';';
	ELSE  -- both are not null
		RETURN 'Ambiguous fieldset: both a primary key and a query provided';
	END IF;
	--
	-- Execute the update
	--
	BEGIN
		EXECUTE statement;
		GET DIAGNOSTICS update_count = ROW_COUNT;
		--
		IF UPDATE_COUNT > 0 THEN
			status_code := 'APPLIED';
			msg := NULL;
		ELSE
			status_code := 'ERROR';
			msg := 'No eligible rows found for fieldset ' || fieldset_id;
    	END IF;
	EXCEPTION WHEN OTHERS THEN
		status_code := 'ERROR';
		msg := 'Unable to apply fieldset ' || fieldset_id
			   || ': ' || sqlerrm;
	END;
	--
	-- Update fieldset status
	--
	UPDATE action.fieldset
	SET status       = status_code,
	    applied_time = now()
	WHERE id = fieldset_id;
	--
	RETURN msg;
END;

Function: action.archive_stat_cats()

Returns: trigger

Language: PLPGSQL

BEGIN
    INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
        SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
        FROM actor.stat_cat_entry_usr_map asceum
             JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
        WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
    INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
        SELECT NEW.id, ascecm.stat_cat, asce.value
        FROM asset.stat_cat_entry_copy_map ascecm
             JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
             JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
        WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
    RETURN NULL;
END;

Function: action.circ_chain(ctx_circ_id integer)

Returns: SET OF circulation

Language: PLPGSQL

DECLARE
    tmp_circ action.circulation%ROWTYPE;
    circ_0 action.circulation%ROWTYPE;
BEGIN

    SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;

    IF tmp_circ IS NULL THEN
        RETURN NEXT tmp_circ;
    END IF;
    circ_0 := tmp_circ;

    -- find the front of the chain
    WHILE TRUE LOOP
        SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
        IF tmp_circ IS NULL THEN
            EXIT;
        END IF;
        circ_0 := tmp_circ;
    END LOOP;

    -- now send the circs to the caller, oldest to newest
    tmp_circ := circ_0;
    WHILE TRUE LOOP
        IF tmp_circ IS NULL THEN
            EXIT;
        END IF;
        RETURN NEXT tmp_circ;
        SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
    END LOOP;

END;

Function: action.circulation_claims_returned()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
		IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
			UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
		END IF;
		IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
			UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
		END IF;
		IF NEW.stop_fines = 'LOST' THEN
			UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
		END IF;
	END IF;
	RETURN NEW;
END;

Function: action.copy_related_hold_stats(copy_id integer)

Returns: hold_stats

Language: PLPGSQL

DECLARE
    output          action.hold_stats%ROWTYPE;
    hold_count      INT := 0;
    copy_count      INT := 0;
    available_count INT := 0;
    hold_map_data   RECORD;
BEGIN

    output.hold_count := 0;
    output.copy_count := 0;
    output.available_count := 0;

    SELECT  COUNT( DISTINCT m.hold ) INTO hold_count
      FROM  action.hold_copy_map m
            JOIN action.hold_request h ON (m.hold = h.id)
      WHERE m.target_copy = copy_id
            AND NOT h.frozen;

    output.hold_count := hold_count;

    IF output.hold_count > 0 THEN
        FOR hold_map_data IN
            SELECT  DISTINCT m.target_copy,
                    acp.status
              FROM  action.hold_copy_map m
                    JOIN asset.copy acp ON (m.target_copy = acp.id)
                    JOIN action.hold_request h ON (m.hold = h.id)
              WHERE m.hold IN ( SELECT DISTINCT hold FROM action.hold_copy_map WHERE target_copy = copy_id ) AND NOT h.frozen
        LOOP
            output.copy_count := output.copy_count + 1;
            IF hold_map_data.status IN (0,7,12) THEN
                output.available_count := output.available_count + 1;
            END IF;
        END LOOP;
        output.total_copy_ratio = output.copy_count::FLOAT / output.hold_count::FLOAT;
        output.available_copy_ratio = output.available_count::FLOAT / output.hold_count::FLOAT;

    END IF;

    RETURN output;

END;

Function: action.fill_circ_copy_location()

Returns: trigger

Language: PLPGSQL

BEGIN
    SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
    RETURN NEW;
END;

Function: action.find_circ_matrix_matchpoint(renewal integer, match_user bigint, match_item integer, context_ou boolean)

Returns: SET OF found_circ_matrix_matchpoint

Language: PLPGSQL

DECLARE
    item_object asset.copy%ROWTYPE;
    user_object actor.usr%ROWTYPE;
BEGIN
    SELECT INTO item_object * FROM asset.copy 	WHERE id = match_item;
    SELECT INTO user_object * FROM actor.usr	WHERE id = match_user;

    RETURN QUERY SELECT * FROM action.find_circ_matrix_matchpoint( context_ou, item_object, user_object, renewal );
END;

Function: action.find_circ_matrix_matchpoint(renewal integer, user_object asset.copy, item_object actor.usr, context_ou boolean)

Returns: found_circ_matrix_matchpoint

Language: PLPGSQL

DECLARE
    cn_object       asset.call_number%ROWTYPE;
    rec_descriptor  metabib.rec_descriptor%ROWTYPE;
    cur_matchpoint  config.circ_matrix_matchpoint%ROWTYPE;
    matchpoint      config.circ_matrix_matchpoint%ROWTYPE;
    weights         config.circ_matrix_weights%ROWTYPE;
    user_age        INTERVAL;
    my_item_age     INTERVAL;
    denominator     NUMERIC(6,2);
    row_list        INT[];
    result          action.found_circ_matrix_matchpoint;
BEGIN
    -- Assume failure
    result.success = false;

    -- Fetch useful data
    SELECT INTO cn_object       * FROM asset.call_number        WHERE id = item_object.call_number;
    SELECT INTO rec_descriptor  * FROM metabib.rec_descriptor   WHERE record = cn_object.record;

    -- Pre-generate this so we only calc it once
    IF user_object.dob IS NOT NULL THEN
        SELECT INTO user_age age(user_object.dob);
    END IF;

    -- Ditto
    SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));

    -- Grab the closest set circ weight setting.
    SELECT INTO weights cw.*
      FROM config.weight_assoc wa
           JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
           JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
      WHERE active
      ORDER BY d.distance
      LIMIT 1;

    -- No weights? Bad admin! Defaults to handle that anyway.
    IF weights.id IS NULL THEN
        weights.grp                 := 11.0;
        weights.org_unit            := 10.0;
        weights.circ_modifier       := 5.0;
        weights.marc_type           := 4.0;
        weights.marc_form           := 3.0;
        weights.marc_bib_level      := 2.0;
        weights.marc_vr_format      := 2.0;
        weights.copy_circ_lib       := 8.0;
        weights.copy_owning_lib     := 8.0;
        weights.user_home_ou        := 8.0;
        weights.ref_flag            := 1.0;
        weights.juvenile_flag       := 6.0;
        weights.is_renewal          := 7.0;
        weights.usr_age_lower_bound := 0.0;
        weights.usr_age_upper_bound := 0.0;
        weights.item_age            := 0.0;
    END IF;

    -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
    -- If you break your org tree with funky parenting this may be wrong
    -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function
    -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
    WITH all_distance(distance) AS (
            SELECT depth AS distance FROM actor.org_unit_type
        UNION
       	    SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
	)
    SELECT INTO denominator MAX(distance) + 1 FROM all_distance;

    -- Loop over all the potential matchpoints
    FOR cur_matchpoint IN
        SELECT m.*
          FROM  config.circ_matrix_matchpoint m
                /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
                /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
                LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
                LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
                LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou  ) uhoua ON m.user_home_ou = uhoua.id
          WHERE m.active
                -- Permission Groups
             -- AND (m.grp                      IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
                -- Org Units
             -- AND (m.org_unit                 IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
                AND (m.copy_owning_lib          IS NULL OR cnoua.id IS NOT NULL)
                AND (m.copy_circ_lib            IS NULL OR iooua.id IS NOT NULL)
                AND (m.user_home_ou             IS NULL OR uhoua.id IS NOT NULL)
                -- Circ Type
                AND (m.is_renewal               IS NULL OR m.is_renewal = renewal)
                -- Static User Checks
                AND (m.juvenile_flag            IS NULL OR m.juvenile_flag = user_object.juvenile)
                AND (m.usr_age_lower_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
                AND (m.usr_age_upper_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
                -- Static Item Checks
                AND (m.circ_modifier            IS NULL OR m.circ_modifier = item_object.circ_modifier)
                AND (m.marc_type                IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
                AND (m.marc_form                IS NULL OR m.marc_form = rec_descriptor.item_form)
                AND (m.marc_bib_level           IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
                AND (m.marc_vr_format           IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
                AND (m.ref_flag                 IS NULL OR m.ref_flag = item_object.ref)
                AND (m.item_age                 IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
          ORDER BY
                -- Permission Groups
                CASE WHEN upgad.distance        IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
                -- Org Units
                CASE WHEN ctoua.distance        IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
                CASE WHEN cnoua.distance        IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
                CASE WHEN iooua.distance        IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
                CASE WHEN uhoua.distance        IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
                -- Circ Type                    -- Note: 4^x is equiv to 2^(2*x)
                CASE WHEN m.is_renewal          IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
                -- Static User Checks
                CASE WHEN m.juvenile_flag       IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
                CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
                CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
                -- Static Item Checks
                CASE WHEN m.circ_modifier       IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
                CASE WHEN m.marc_type           IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
                CASE WHEN m.marc_form           IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
                CASE WHEN m.marc_vr_format      IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
                CASE WHEN m.ref_flag            IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
                -- Item age has a slight adjustment to weight based on value.
                -- This should ensure that a shorter age limit comes first when all else is equal.
                -- NOTE: This assumes that intervals will normally be in days.
                CASE WHEN m.item_age            IS NOT NULL THEN 4^weights.item_age - 1 + 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC,
                -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
                -- This prevents "we changed the table order by updating a rule, and we started getting different results"
                m.id LOOP

        -- Record the full matching row list
        row_list := row_list || cur_matchpoint.id;

        -- No matchpoint yet?
        IF matchpoint.id IS NULL THEN
            -- Take the entire matchpoint as a starting point
            matchpoint := cur_matchpoint;
            CONTINUE; -- No need to look at this row any more.
        END IF;

        -- Incomplete matchpoint?
        IF matchpoint.circulate IS NULL THEN
            matchpoint.circulate := cur_matchpoint.circulate;
        END IF;
        IF matchpoint.duration_rule IS NULL THEN
            matchpoint.duration_rule := cur_matchpoint.duration_rule;
        END IF;
        IF matchpoint.recurring_fine_rule IS NULL THEN
            matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
        END IF;
        IF matchpoint.max_fine_rule IS NULL THEN
            matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
        END IF;
        IF matchpoint.hard_due_date IS NULL THEN
            matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
        END IF;
        IF matchpoint.total_copy_hold_ratio IS NULL THEN
            matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
        END IF;
        IF matchpoint.available_copy_hold_ratio IS NULL THEN
            matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
        END IF;
        IF matchpoint.renewals IS NULL THEN
            matchpoint.renewals := cur_matchpoint.renewals;
        END IF;
        IF matchpoint.grace_period IS NULL THEN
            matchpoint.grace_period := cur_matchpoint.grace_period;
        END IF;
    END LOOP;

    -- Check required fields
    IF matchpoint.circulate             IS NOT NULL AND
       matchpoint.duration_rule         IS NOT NULL AND
       matchpoint.recurring_fine_rule   IS NOT NULL AND
       matchpoint.max_fine_rule         IS NOT NULL THEN
        -- All there? We have a completed match.
        result.success := true;
    END IF;

    -- Include the assembled matchpoint, even if it isn't complete
    result.matchpoint := matchpoint;

    -- Include (for debugging) the full list of matching rows
    result.buildrows := row_list;

    -- Hand the result back to caller
    RETURN result;
END;

Function: action.find_hold_matrix_matchpoint(match_requestor integer, match_user integer, match_item bigint, request_ou integer, pickup_ou integer)

Returns: integer

Language: PLPGSQL

DECLARE
    requestor_object    actor.usr%ROWTYPE;
    user_object         actor.usr%ROWTYPE;
    item_object         asset.copy%ROWTYPE;
    item_cn_object      asset.call_number%ROWTYPE;
    my_item_age         INTERVAL;
    rec_descriptor      metabib.rec_descriptor%ROWTYPE;
    matchpoint          config.hold_matrix_matchpoint%ROWTYPE;
    weights             config.hold_matrix_weights%ROWTYPE;
    denominator         NUMERIC(6,2);
BEGIN
    SELECT INTO user_object         * FROM actor.usr                WHERE id = match_user;
    SELECT INTO requestor_object    * FROM actor.usr                WHERE id = match_requestor;
    SELECT INTO item_object         * FROM asset.copy               WHERE id = match_item;
    SELECT INTO item_cn_object      * FROM asset.call_number        WHERE id = item_object.call_number;
    SELECT INTO rec_descriptor      * FROM metabib.rec_descriptor   WHERE record = item_cn_object.record;

    SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));

    -- The item's owner should probably be the one determining if the item is holdable
    -- How to decide that is debatable. Decided to default to the circ library (where the item lives)
    -- This flag will allow for setting it to the owning library (where the call number "lives")
    PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.weight_owner_not_circ' AND enabled;

    -- Grab the closest set circ weight setting.
    IF NOT FOUND THEN
        -- Default to circ library
        SELECT INTO weights hw.*
          FROM config.weight_assoc wa
               JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
               JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) d ON (wa.org_unit = d.id)
          WHERE active
          ORDER BY d.distance
          LIMIT 1;
    ELSE
        -- Flag is set, use owning library
        SELECT INTO weights hw.*
          FROM config.weight_assoc wa
               JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
               JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) d ON (wa.org_unit = d.id)
          WHERE active
          ORDER BY d.distance
          LIMIT 1;
    END IF;

    -- No weights? Bad admin! Defaults to handle that anyway.
    IF weights.id IS NULL THEN
        weights.user_home_ou    := 5.0;
        weights.request_ou      := 5.0;
        weights.pickup_ou       := 5.0;
        weights.item_owning_ou  := 5.0;
        weights.item_circ_ou    := 5.0;
        weights.usr_grp         := 7.0;
        weights.requestor_grp   := 8.0;
        weights.circ_modifier   := 4.0;
        weights.marc_type       := 3.0;
        weights.marc_form       := 2.0;
        weights.marc_bib_level  := 1.0;
        weights.marc_vr_format  := 1.0;
        weights.juvenile_flag   := 4.0;
        weights.ref_flag        := 0.0;
        weights.item_age        := 0.0;
    END IF;

    -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
    -- If you break your org tree with funky parenting this may be wrong
    -- Note: This CTE is duplicated in the find_circ_matrix_matchpoint function, and it may be a good idea to split it off to a function
    -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
    WITH all_distance(distance) AS (
            SELECT depth AS distance FROM actor.org_unit_type
        UNION
            SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
	)
    SELECT INTO denominator MAX(distance) + 1 FROM all_distance;

    -- To ATTEMPT to make this work like it used to, make it reverse the user/requestor profile ids.
    -- This may be better implemented as part of the upgrade script?
    -- Set usr_grp = requestor_grp, requestor_grp = 1 or something when this flag is already set
    -- Then remove this flag, of course.
    PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled;

    IF FOUND THEN
        -- Note: This, to me, is REALLY hacky. I put it in anyway.
        -- If you can't tell, this is a single call swap on two variables.
        SELECT INTO user_object.profile, requestor_object.profile
                    requestor_object.profile, user_object.profile;
    END IF;

    -- Select the winning matchpoint into the matchpoint variable for returning
    SELECT INTO matchpoint m.*
      FROM  config.hold_matrix_matchpoint m
            /*LEFT*/ JOIN permission.grp_ancestors_distance( requestor_object.profile ) rpgad ON m.requestor_grp = rpgad.id
            LEFT JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.usr_grp = upgad.id
            LEFT JOIN actor.org_unit_ancestors_distance( pickup_ou ) puoua ON m.pickup_ou = puoua.id
            LEFT JOIN actor.org_unit_ancestors_distance( request_ou ) rqoua ON m.request_ou = rqoua.id
            LEFT JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) cnoua ON m.item_owning_ou = cnoua.id
            LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.item_circ_ou = iooua.id
            LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou  ) uhoua ON m.user_home_ou = uhoua.id
      WHERE m.active
            -- Permission Groups
         -- AND (m.requestor_grp        IS NULL OR upgad.id IS NOT NULL) -- Optional Requestor Group?
            AND (m.usr_grp              IS NULL OR upgad.id IS NOT NULL)
            -- Org Units
            AND (m.pickup_ou            IS NULL OR (puoua.id IS NOT NULL AND (puoua.distance = 0 OR NOT m.strict_ou_match)))
            AND (m.request_ou           IS NULL OR (rqoua.id IS NOT NULL AND (rqoua.distance = 0 OR NOT m.strict_ou_match)))
            AND (m.item_owning_ou       IS NULL OR (cnoua.id IS NOT NULL AND (cnoua.distance = 0 OR NOT m.strict_ou_match)))
            AND (m.item_circ_ou         IS NULL OR (iooua.id IS NOT NULL AND (iooua.distance = 0 OR NOT m.strict_ou_match)))
            AND (m.user_home_ou         IS NULL OR (uhoua.id IS NOT NULL AND (uhoua.distance = 0 OR NOT m.strict_ou_match)))
            -- Static User Checks
            AND (m.juvenile_flag        IS NULL OR m.juvenile_flag = user_object.juvenile)
            -- Static Item Checks
            AND (m.circ_modifier        IS NULL OR m.circ_modifier = item_object.circ_modifier)
            AND (m.marc_type            IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
            AND (m.marc_form            IS NULL OR m.marc_form = rec_descriptor.item_form)
            AND (m.marc_bib_level       IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
            AND (m.marc_vr_format       IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
            AND (m.ref_flag             IS NULL OR m.ref_flag = item_object.ref)
            AND (m.item_age             IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
      ORDER BY
            -- Permission Groups
            CASE WHEN rpgad.distance    IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END +
            CASE WHEN upgad.distance    IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END +
            -- Org Units
            CASE WHEN puoua.distance    IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END +
            CASE WHEN rqoua.distance    IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END +
            CASE WHEN cnoua.distance    IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END +
            CASE WHEN iooua.distance    IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END +
            CASE WHEN uhoua.distance    IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
            -- Static User Checks       -- Note: 4^x is equiv to 2^(2*x)
            CASE WHEN m.juvenile_flag   IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
            -- Static Item Checks
            CASE WHEN m.circ_modifier   IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
            CASE WHEN m.marc_type       IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
            CASE WHEN m.marc_form       IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
            CASE WHEN m.marc_vr_format  IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
            CASE WHEN m.ref_flag        IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
            -- Item age has a slight adjustment to weight based on value.
            -- This should ensure that a shorter age limit comes first when all else is equal.
            -- NOTE: This assumes that intervals will normally be in days.
            CASE WHEN m.item_age            IS NOT NULL THEN 4^weights.item_age - 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC,
            -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
            -- This prevents "we changed the table order by updating a rule, and we started getting different results"
            m.id;

    -- Return just the ID for now
    RETURN matchpoint.id;
END;

Function: action.hold_request_permit_test(match_requestor integer, match_user integer, match_item bigint, request_ou integer, pickup_ou integer)

Returns: SET OF matrix_test_result

Language: SQL

    SELECT * FROM action.hold_request_permit_test( $1, $2, $3, $4, $5, FALSE );

Function: action.hold_request_permit_test(retargetting integer, match_requestor integer, match_user bigint, match_item integer, request_ou integer, pickup_ou boolean)

Returns: SET OF matrix_test_result

Language: PLPGSQL

DECLARE
    matchpoint_id        INT;
    user_object        actor.usr%ROWTYPE;
    age_protect_object    config.rule_age_hold_protect%ROWTYPE;
    standing_penalty    config.standing_penalty%ROWTYPE;
    transit_range_ou_type    actor.org_unit_type%ROWTYPE;
    transit_source        actor.org_unit%ROWTYPE;
    item_object        asset.copy%ROWTYPE;
    item_cn_object     asset.call_number%ROWTYPE;
    item_status_object  config.copy_status%ROWTYPE;
    item_location_object    asset.copy_location%ROWTYPE;
    ou_skip              actor.org_unit_setting%ROWTYPE;
    result            action.matrix_test_result;
    hold_test        config.hold_matrix_matchpoint%ROWTYPE;
    use_active_date   TEXT;
    age_protect_date  TIMESTAMP WITH TIME ZONE;
    hold_count        INT;
    hold_transit_prox    INT;
    frozen_hold_count    INT;
    context_org_list    INT[];
    done            BOOL := FALSE;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
    SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );

    result.success := TRUE;

    -- Fail if we couldn't find a user
    IF user_object.id IS NULL THEN
        result.fail_part := 'no_user';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO item_object * FROM asset.copy WHERE id = match_item;

    -- Fail if we couldn't find a copy
    IF item_object.id IS NULL THEN
        result.fail_part := 'no_item';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
    result.matchpoint := matchpoint_id;

    SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;

    -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
    IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
        result.fail_part := 'circ.holds.target_skip_me';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    -- Fail if user is barred
    IF user_object.barred IS TRUE THEN
        result.fail_part := 'actor.usr.barred';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
    SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
    SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;

    -- Fail if we couldn't find any matchpoint (requires a default)
    IF matchpoint_id IS NULL THEN
        result.fail_part := 'no_matchpoint';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;

    IF hold_test.holdable IS FALSE THEN
        result.fail_part := 'config.hold_matrix_test.holdable';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    IF item_object.holdable IS FALSE THEN
        result.fail_part := 'item.holdable';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    IF item_status_object.holdable IS FALSE THEN
        result.fail_part := 'status.holdable';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    IF item_location_object.holdable IS FALSE THEN
        result.fail_part := 'location.holdable';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    IF hold_test.transit_range IS NOT NULL THEN
        SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
        IF hold_test.distance_is_from_owner THEN
            SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
        ELSE
            SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
        END IF;

        PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;

        IF NOT FOUND THEN
            result.fail_part := 'transit_range';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;
 
    FOR standing_penalty IN
        SELECT  DISTINCT csp.*
          FROM  actor.usr_standing_penalty usp
                JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
          WHERE usr = match_user
                AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
                AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                AND csp.block_list LIKE '%HOLD%' LOOP

        result.fail_part := standing_penalty.name;
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END LOOP;

    IF hold_test.stop_blocked_user IS TRUE THEN
        FOR standing_penalty IN
            SELECT  DISTINCT csp.*
              FROM  actor.usr_standing_penalty usp
                    JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
              WHERE usr = match_user
                    AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
                    AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                    AND csp.block_list LIKE '%CIRC%' LOOP
    
            result.fail_part := standing_penalty.name;
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END LOOP;
    END IF;

    IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
        SELECT    INTO hold_count COUNT(*)
          FROM    action.hold_request
          WHERE    usr = match_user
            AND fulfillment_time IS NULL
            AND cancel_time IS NULL
            AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;

        IF hold_count >= hold_test.max_holds THEN
            result.fail_part := 'config.hold_matrix_test.max_holds';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;

    IF item_object.age_protect IS NOT NULL THEN
        SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
        IF hold_test.distance_is_from_owner THEN
            SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
        ELSE
            SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
        END IF;
        IF use_active_date = 'true' THEN
            age_protect_date := COALESCE(item_object.active_date, NOW());
        ELSE
            age_protect_date := item_object.create_date;
        END IF;
        IF age_protect_date + age_protect_object.age > NOW() THEN
            IF hold_test.distance_is_from_owner THEN
                SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
                SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
            ELSE
                SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
            END IF;

            IF hold_transit_prox > age_protect_object.prox THEN
                result.fail_part := 'config.rule_age_hold_protect.prox';
                result.success := FALSE;
                done := TRUE;
                RETURN NEXT result;
            END IF;
        END IF;
    END IF;

    IF NOT done THEN
        RETURN NEXT result;
    END IF;

    RETURN;
END;

Function: action.hold_retarget_permit_test(match_requestor integer, match_user integer, match_item bigint, request_ou integer, pickup_ou integer)

Returns: SET OF matrix_test_result

Language: SQL

    SELECT * FROM action.hold_request_permit_test( $1, $2, $3, $4, $5, TRUE );

Function: action.item_user_circ_test(integer, bigint, integer)

Returns: SET OF circ_matrix_test_result

Language: SQL

    SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );

Function: action.item_user_circ_test(renewal integer, match_user bigint, match_item integer, circ_ou boolean)

Returns: SET OF circ_matrix_test_result

Language: PLPGSQL

DECLARE
    user_object             actor.usr%ROWTYPE;
    standing_penalty        config.standing_penalty%ROWTYPE;
    item_object             asset.copy%ROWTYPE;
    item_status_object      config.copy_status%ROWTYPE;
    item_location_object    asset.copy_location%ROWTYPE;
    result                  action.circ_matrix_test_result;
    circ_test               action.found_circ_matrix_matchpoint;
    circ_matchpoint         config.circ_matrix_matchpoint%ROWTYPE;
    circ_limit_set          config.circ_limit_set%ROWTYPE;
    hold_ratio              action.hold_stats%ROWTYPE;
    penalty_type            TEXT;
    items_out               INT;
    context_org_list        INT[];
    done                    BOOL := FALSE;
BEGIN
    -- Assume success unless we hit a failure condition
    result.success := TRUE;

    -- Need user info to look up matchpoints
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;

    -- (Insta)Fail if we couldn't find the user
    IF user_object.id IS NULL THEN
        result.fail_part := 'no_user';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    -- Need item info to look up matchpoints
    SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;

    -- (Insta)Fail if we couldn't find the item 
    IF item_object.id IS NULL THEN
        result.fail_part := 'no_item';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);

    circ_matchpoint             := circ_test.matchpoint;
    result.matchpoint           := circ_matchpoint.id;
    result.circulate            := circ_matchpoint.circulate;
    result.duration_rule        := circ_matchpoint.duration_rule;
    result.recurring_fine_rule  := circ_matchpoint.recurring_fine_rule;
    result.max_fine_rule        := circ_matchpoint.max_fine_rule;
    result.hard_due_date        := circ_matchpoint.hard_due_date;
    result.renewals             := circ_matchpoint.renewals;
    result.grace_period         := circ_matchpoint.grace_period;
    result.buildrows            := circ_test.buildrows;

    -- (Insta)Fail if we couldn't find a matchpoint
    IF circ_test.success = false THEN
        result.fail_part := 'no_matchpoint';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    -- All failures before this point are non-recoverable
    -- Below this point are possibly overridable failures

    -- Fail if the user is barred
    IF user_object.barred IS TRUE THEN
        result.fail_part := 'actor.usr.barred';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the item can't circulate
    IF item_object.circulate IS FALSE THEN
        result.fail_part := 'asset.copy.circulate';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the item isn't in a circulateable status on a non-renewal
    IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN 
        result.fail_part := 'asset.copy.status';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    -- Alternately, fail if the item isn't checked out on a renewal
    ELSIF renewal AND item_object.status <> 1 THEN
        result.fail_part := 'asset.copy.status';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the item can't circulate because of the shelving location
    SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
    IF item_location_object.circulate IS FALSE THEN
        result.fail_part := 'asset.copy_location.circulate';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Use Circ OU for penalties and such
    SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );

    IF renewal THEN
        penalty_type = '%RENEW%';
    ELSE
        penalty_type = '%CIRC%';
    END IF;

    FOR standing_penalty IN
        SELECT  DISTINCT csp.*
          FROM  actor.usr_standing_penalty usp
                JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
          WHERE usr = match_user
                AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
                AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                AND csp.block_list LIKE penalty_type LOOP

        result.fail_part := standing_penalty.name;
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END LOOP;

    -- Fail if the test is set to hard non-circulating
    IF circ_matchpoint.circulate IS FALSE THEN
        result.fail_part := 'config.circ_matrix_test.circulate';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the total copy-hold ratio is too low
    IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
        SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
        IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
            result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;

    -- Fail if the available copy-hold ratio is too low
    IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
        IF hold_ratio.hold_count IS NULL THEN
            SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
        END IF;
        IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
            result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;

    -- Fail if the user has too many items out by defined limit sets
    FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
      JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
      WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
        ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
        ) LOOP
            IF circ_limit_set.items_out > 0 AND NOT renewal THEN
                SELECT INTO context_org_list ARRAY_AGG(aou.id)
                  FROM actor.org_unit_full_path( circ_ou ) aou
                    JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
                  WHERE aout.depth >= circ_limit_set.depth;
                IF circ_limit_set.global THEN
                    WITH RECURSIVE descendant_depth AS (
                        SELECT  ou.id,
                            ou.parent_ou
                        FROM  actor.org_unit ou
                        WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
                            UNION
                        SELECT  ou.id,
                            ou.parent_ou
                        FROM  actor.org_unit ou
                            JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
                    ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
                END IF;
                SELECT INTO items_out COUNT(DISTINCT circ.id)
                  FROM action.circulation circ
                    JOIN asset.copy copy ON (copy.id = circ.target_copy)
                    LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
                  WHERE circ.usr = match_user
                    AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
                    AND circ.checkin_time IS NULL
                    AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
                    AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
                        OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
                    );
                IF items_out >= circ_limit_set.items_out THEN
                    result.fail_part := 'config.circ_matrix_circ_mod_test';
                    result.success := FALSE;
                    done := TRUE;
                    RETURN NEXT result;
                END IF;
            END IF;
            SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only;
    END LOOP;

    -- If we passed everything, return the successful matchpoint
    IF NOT done THEN
        RETURN NEXT result;
    END IF;

    RETURN;
END;

Function: action.item_user_renew_test(integer, bigint, integer)

Returns: SET OF circ_matrix_test_result

Language: SQL

    SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );

Function: action.link_circ_limit_groups(bigint, integer[])

Returns: void

Language: SQL

    INSERT INTO action.circulation_limit_group_map(circ, limit_group) SELECT $1, id FROM config.circ_limit_group WHERE id IN (SELECT * FROM UNNEST($2));

Function: action.purge_circulations()

Returns: integer

Language: PLPGSQL

DECLARE
    usr_keep_age    actor.usr_setting%ROWTYPE;
    usr_keep_start  actor.usr_setting%ROWTYPE;
    org_keep_age    INTERVAL;
    org_keep_count  INT;

    keep_age        INTERVAL;

    target_acp      RECORD;
    circ_chain_head action.circulation%ROWTYPE;
    circ_chain_tail action.circulation%ROWTYPE;

    purge_position  INT;
    count_purged    INT;
BEGIN

    count_purged := 0;

    SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;

    SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
    IF org_keep_count IS NULL THEN
        RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
    END IF;

    -- First, find copies with more than keep_count non-renewal circs
    FOR target_acp IN
        SELECT  target_copy,
                COUNT(*) AS total_real_circs
          FROM  action.circulation
          WHERE parent_circ IS NULL
                AND xact_finish IS NOT NULL
          GROUP BY target_copy
          HAVING COUNT(*) > org_keep_count
    LOOP
        purge_position := 0;
        -- And, for those, select circs that are finished and older than keep_age
        FOR circ_chain_head IN
            SELECT  *
              FROM  action.circulation
              WHERE target_copy = target_acp.target_copy
                    AND parent_circ IS NULL
              ORDER BY xact_start
        LOOP

            -- Stop once we've purged enough circs to hit org_keep_count
            EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;

            SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
            EXIT WHEN circ_chain_tail.xact_finish IS NULL;

            -- Now get the user settings, if any, to block purging if the user wants to keep more circs
            usr_keep_age.value := NULL;
            SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';

            usr_keep_start.value := NULL;
            SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';

            IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
                IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
                    keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
                ELSE
                    keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
                END IF;
            ELSIF usr_keep_start.value IS NOT NULL THEN
                keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
            ELSE
                keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
            END IF;

            EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;

            -- We've passed the purging tests, purge the circ chain starting at the end
            DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
            WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
                SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
                DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
            END LOOP;

            count_purged := count_purged + 1;
            purge_position := purge_position + 1;

        END LOOP;
    END LOOP;
END;

Function: action.push_circ_due_time()

Returns: trigger

Language: PLPGSQL

BEGIN
    IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN
        NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ;
    END IF;

    RETURN NEW;
END;

Function: action.summarize_circ_chain(ctx_circ_id integer)

Returns: circ_chain_summary

Language: PLPGSQL


DECLARE

    -- first circ in the chain
    circ_0 action.circulation%ROWTYPE;

    -- last circ in the chain
    circ_n action.circulation%ROWTYPE;

    -- circ chain under construction
    chain action.circ_chain_summary;
    tmp_circ action.circulation%ROWTYPE;

BEGIN
    
    chain.num_circs := 0;
    FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP

        IF chain.num_circs = 0 THEN
            circ_0 := tmp_circ;
        END IF;

        chain.num_circs := chain.num_circs + 1;
        circ_n := tmp_circ;
    END LOOP;

    chain.start_time := circ_0.xact_start;
    chain.last_stop_fines := circ_n.stop_fines;
    chain.last_stop_fines_time := circ_n.stop_fines_time;
    chain.last_checkin_time := circ_n.checkin_time;
    chain.last_checkin_scan_time := circ_n.checkin_scan_time;
    SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
    SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;

    IF chain.num_circs > 1 THEN
        chain.last_renewal_time := circ_n.xact_start;
        SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
    END IF;

    RETURN chain;

END;

Function: action.survey_response_answer_date_fixup()

Returns: trigger

Language: PLPGSQL

BEGIN
	NEW.answer_date := NOW();
	RETURN NEW;
END;

Function: action.usr_visible_circ_copies(integer)

Returns: SET OF bigint

Language: SQL

    SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)

Function: action.usr_visible_circs(usr_id integer)

Returns: SET OF circulation

Language: PLPGSQL

DECLARE
    c               action.circulation%ROWTYPE;
    view_age        INTERVAL;
    usr_view_age    actor.usr_setting%ROWTYPE;
    usr_view_start  actor.usr_setting%ROWTYPE;
BEGIN
    SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
    SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';

    IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
        -- User opted in and supplied a retention age
        IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
            view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
        ELSE
            view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
        END IF;
    ELSIF usr_view_start.value IS NOT NULL THEN
        -- User opted in
        view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
    ELSE
        -- User did not opt in
        RETURN;
    END IF;

    FOR c IN
        SELECT  *
          FROM  action.circulation
          WHERE usr = usr_id
                AND parent_circ IS NULL
                AND xact_start > NOW() - view_age
          ORDER BY xact_start DESC
    LOOP
        RETURN NEXT c;
    END LOOP;

    RETURN;
END;

Function: action.usr_visible_holds(usr_id integer)

Returns: SET OF hold_request

Language: PLPGSQL

DECLARE
    h               action.hold_request%ROWTYPE;
    view_age        INTERVAL;
    view_count      INT;
    usr_view_count  actor.usr_setting%ROWTYPE;
    usr_view_age    actor.usr_setting%ROWTYPE;
    usr_view_start  actor.usr_setting%ROWTYPE;
BEGIN
    SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
    SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
    SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';

    FOR h IN
        SELECT  *
          FROM  action.hold_request
          WHERE usr = usr_id
                AND fulfillment_time IS NULL
                AND cancel_time IS NULL
          ORDER BY request_time DESC
    LOOP
        RETURN NEXT h;
    END LOOP;

    IF usr_view_start.value IS NULL THEN
        RETURN;
    END IF;

    IF usr_view_age.value IS NOT NULL THEN
        -- User opted in and supplied a retention age
        IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
            view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
        ELSE
            view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
        END IF;
    ELSE
        -- User opted in
        view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
    END IF;

    IF usr_view_count.value IS NOT NULL THEN
        view_count := oils_json_to_text(usr_view_count.value)::INT;
    ELSE
        view_count := 1000;
    END IF;

    -- show some fulfilled/canceled holds
    FOR h IN
        SELECT  *
          FROM  action.hold_request
          WHERE usr = usr_id
                AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
                AND request_time > NOW() - view_age
          ORDER BY request_time DESC
          LIMIT view_count
    LOOP
        RETURN NEXT h;
    END LOOP;

    RETURN;
END;

Schema action_trigger


Table: action_trigger.cleanup

action_trigger.cleanup Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.collector

action_trigger.collector Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.environment

action_trigger.environment Structure
F-Key Name Type Description
id serial PRIMARY KEY
action_trigger.event_definition.id event_def integer UNIQUE#1 NOT NULL
path text
action_trigger.collector.module collector text
label text UNIQUE#1

 

action_trigger.environment Constraints
Name Constraint
environment_label_check CHECK ((label <> ALL (ARRAY['result'::text, 'target'::text, 'event'::text])))

Index - Schema action_trigger


Table: action_trigger.event

action_trigger.event Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
target bigint NOT NULL
action_trigger.event_definition.id event_def integer
add_time timestamp with time zone NOT NULL DEFAULT now()
run_time timestamp with time zone NOT NULL
start_time timestamp with time zone
update_time timestamp with time zone
complete_time timestamp with time zone
update_process integer
state text NOT NULL DEFAULT 'pending'::text
user_data text
action_trigger.event_output.id template_output bigint
action_trigger.event_output.id error_output bigint
action_trigger.event_output.id async_output bigint

 

action_trigger.event Constraints
Name Constraint
event_state_check CHECK ((state = ANY (ARRAY['pending'::text, 'invalid'::text, 'found'::text, 'collecting'::text, 'collected'::text, 'validating'::text, 'valid'::text, 'reacting'::text, 'reacted'::text, 'cleaning'::text, 'complete'::text, 'error'::text])))
event_user_data_check CHECK (((user_data IS NULL) OR is_json(user_data)))
atev_target_def_idx target, event_def

Index - Schema action_trigger


Table: action_trigger.event_definition

action_trigger.event_definition Structure
F-Key Name Type Description
id serial PRIMARY KEY
active boolean NOT NULL DEFAULT true
actor.org_unit.id owner integer UNIQUE#2 UNIQUE#1 NOT NULL
name text UNIQUE#2 NOT NULL
action_trigger.hook.key hook text UNIQUE#1 NOT NULL
action_trigger.validator.module validator text UNIQUE#1 NOT NULL
action_trigger.reactor.module reactor text UNIQUE#1 NOT NULL
action_trigger.cleanup.module cleanup_success text
action_trigger.cleanup.module cleanup_failure text
delay interval UNIQUE#1 NOT NULL DEFAULT '00:05:00'::interval
max_delay interval
usr_field text
config.usr_setting_type.name opt_in_setting text
delay_field text UNIQUE#1
group_field text
template text
granularity text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.event_output

action_trigger.event_output Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
create_time timestamp with time zone NOT NULL DEFAULT now()
is_error boolean NOT NULL DEFAULT false
data text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.event_params

action_trigger.event_params Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
action_trigger.event_definition.id event_def integer UNIQUE#1 NOT NULL
param text UNIQUE#1 NOT NULL
value text NOT NULL

Index - Schema action_trigger


Table: action_trigger.hook

action_trigger.hook Structure
F-Key Name Type Description
key text PRIMARY KEY
core_type text NOT NULL
description text
passive boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.reactor

action_trigger.reactor Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.validator

action_trigger.validator Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Schema actor

Holds all tables pertaining to users and libraries (org units).


Table: actor.address_alert

actor.address_alert Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer NOT NULL
active boolean NOT NULL DEFAULT true
match_all boolean NOT NULL DEFAULT true
alert_message text NOT NULL
street1 text
street2 text
city text
county text
state text
country text
post_code text
mailing_address boolean NOT NULL DEFAULT false
billing_address boolean NOT NULL DEFAULT false

Index - Schema actor


Table: actor.card

Library Cards Each User has one or more library cards. The current "main" card is linked to here from the actor.usr table, and it is up to the consortium policy whether more than one card can be active for any one user at a given time.

actor.card Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer NOT NULL
barcode text UNIQUE NOT NULL
active boolean NOT NULL DEFAULT true
actor_card_barcode_evergreen_lowercase_idx lowercase(barcode) actor_card_usr_idx usr

Index - Schema actor


Table: actor.hours_of_operation

When does this org_unit usually open and close? (Variations are expressed in the actor.org_unit_closed table.)

actor.hours_of_operation Structure
F-Key Name Type Description
actor.org_unit.id id integer PRIMARY KEY
dow_0_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone

When does this org_unit open on Monday?
dow_0_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

When does this org_unit close on Monday?
dow_1_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone

When does this org_unit open on Tuesday?
dow_1_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

When does this org_unit close on Tuesday?
dow_2_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone

When does this org_unit open on Wednesday?
dow_2_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

When does this org_unit close on Wednesday?
dow_3_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone

When does this org_unit open on Thursday?
dow_3_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

When does this org_unit close on Thursday?
dow_4_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone

When does this org_unit open on Friday?
dow_4_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

When does this org_unit close on Friday?
dow_5_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone

When does this org_unit open on Saturday?
dow_5_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

When does this org_unit close on Saturday?
dow_6_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone

When does this org_unit open on Sunday?
dow_6_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

When does this org_unit close on Sunday?

Index - Schema actor


Table: actor.org_address

actor.org_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
address_type text NOT NULL DEFAULT 'MAILING'::text
actor.org_unit.id org_unit integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL
san text

Tables referencing this one via Foreign Key Constraints:

actor_org_address_org_unit_idx org_unit

Index - Schema actor


Table: actor.org_lasso

actor.org_lasso Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.org_lasso_map

actor.org_lasso_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_lasso.id lasso integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
ou_lasso_org_unit_idx org_unit

Index - Schema actor


Table: actor.org_unit

actor.org_unit Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id parent_ou integer
actor.org_unit_type.id ou_type integer NOT NULL
actor.org_address.id ill_address integer
actor.org_address.id holds_address integer
actor.org_address.id mailing_address integer
actor.org_address.id billing_address integer
shortname text UNIQUE NOT NULL
name text UNIQUE NOT NULL
email text
phone text
opac_visible boolean NOT NULL DEFAULT true
acq.fiscal_calendar.id fiscal_calendar integer NOT NULL DEFAULT 1

Tables referencing this one via Foreign Key Constraints:

actor_org_unit_billing_address_idx billing_address actor_org_unit_holds_address_idx holds_address actor_org_unit_ill_address_idx ill_address actor_org_unit_mailing_address_idx mailing_address actor_org_unit_ou_type_idx ou_type actor_org_unit_parent_ou_idx parent_ou

Index - Schema actor


Table: actor.org_unit_closed

actor.org_unit_closed Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer NOT NULL
close_start timestamp with time zone NOT NULL
close_end timestamp with time zone NOT NULL
reason text

Index - Schema actor


Table: actor.org_unit_custom_tree

actor.org_unit_custom_tree Structure
F-Key Name Type Description
id serial PRIMARY KEY
active boolean DEFAULT false
purpose actor.org_unit_custom_tree_purpose UNIQUE NOT NULL DEFAULT 'opac'::actor.org_unit_custom_tree_purpose

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.org_unit_custom_tree_node

actor.org_unit_custom_tree_node Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit_custom_tree.id tree integer UNIQUE#1
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
actor.org_unit_custom_tree_node.id parent_node integer
sibling_order integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.org_unit_proximity

actor.org_unit_proximity Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
from_org integer
to_org integer
prox integer
from_prox_idx from_org

Index - Schema actor


Table: actor.org_unit_setting

Org Unit settings This table contains any arbitrary settings that a client program would like to save for an org unit.

actor.org_unit_setting Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
config.org_unit_setting_type.name name text UNIQUE#1 NOT NULL
value text NOT NULL

 

actor.org_unit_setting Constraints
Name Constraint
aous_must_be_json CHECK (is_json(value))
actor_org_unit_setting_usr_idx org_unit

Index - Schema actor


Table: actor.org_unit_type

actor.org_unit_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text NOT NULL
opac_label text NOT NULL
depth integer NOT NULL
actor.org_unit_type.id parent integer
can_have_vols boolean NOT NULL DEFAULT true
can_have_users boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

actor_org_unit_type_parent_idx parent

Index - Schema actor


Table: actor.stat_cat

User Statistical Catagories Local data collected about Users is placed into a Statistical Catagory. Here's where those catagories are defined.

actor.stat_cat Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
opac_visible boolean NOT NULL DEFAULT false
usr_summary boolean NOT NULL DEFAULT false
actor.stat_cat_sip_fields.field sip_field character(2)
sip_format text
checkout_archive boolean NOT NULL DEFAULT false
required boolean NOT NULL DEFAULT false
allow_freetext boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.stat_cat_entry

User Statistical Catagory Entries Local data collected about Users is placed into a Statistical Catagory. Each library can create entries into any of its own stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.

actor.stat_cat_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
value text UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.stat_cat_entry_default

User Statistical Category Default Entry A library may choose one of the stat_cat entries to be the default entry.

actor.stat_cat_entry_default Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.stat_cat_entry.id stat_cat_entry integer NOT NULL
actor.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL

Index - Schema actor


Table: actor.stat_cat_entry_usr_map

Statistical Catagory Entry to User map Records the stat_cat entries for each user.

actor.stat_cat_entry_usr_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
stat_cat_entry text NOT NULL
actor.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
actor.usr.id target_usr integer UNIQUE#1 NOT NULL
actor_stat_cat_entry_usr_idx target_usr

Index - Schema actor


Table: actor.stat_cat_sip_fields

Actor Statistical Category SIP Fields Contains the list of valid SIP Field identifiers for Statistical Categories.

actor.stat_cat_sip_fields Structure
F-Key Name Type Description
field character(2) PRIMARY KEY
name text NOT NULL
one_only boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.toolbar

actor.toolbar Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.workstation.id ws integer
actor.org_unit.id org integer
actor.usr.id usr integer
label text NOT NULL
layout text NOT NULL

 

actor.toolbar Constraints
Name Constraint
layout_must_be_json CHECK (is_json(layout))
only_one_type CHECK (((((ws IS NOT NULL) AND (COALESCE(org, usr) IS NULL)) OR ((org IS NOT NULL) AND (COALESCE(ws, usr) IS NULL))) OR ((usr IS NOT NULL) AND (COALESCE(org, ws) IS NULL))))

Index - Schema actor


Table: actor.usr

User objects This table contains the core User objects that describe both staff members and patrons. The difference between the two types of users is based on the user's permissions.

actor.usr Structure
F-Key Name Type Description
id serial PRIMARY KEY
card integer UNIQUE
permission.grp_tree.id profile integer NOT NULL
usrname text UNIQUE NOT NULL
email text
passwd text NOT NULL
config.standing.id standing integer NOT NULL DEFAULT 1
config.identification_type.id ident_type integer NOT NULL
ident_value text
config.identification_type.id ident_type2 integer
ident_value2 text
config.net_access_level.id net_access_level integer NOT NULL DEFAULT 1
photo_url text
prefix text
first_given_name text NOT NULL
second_given_name text
family_name text NOT NULL
suffix text
alias text
day_phone text
evening_phone text
other_phone text
actor.usr_address.id mailing_address integer
actor.usr_address.id billing_address integer
actor.org_unit.id home_ou integer NOT NULL
dob timestamp with time zone
active boolean NOT NULL DEFAULT true
master_account boolean NOT NULL DEFAULT false
super_user boolean NOT NULL DEFAULT false
barred boolean NOT NULL DEFAULT false
deleted boolean NOT NULL DEFAULT false
juvenile boolean NOT NULL DEFAULT false
usrgroup serial NOT NULL
claims_returned_count integer NOT NULL
credit_forward_balance numeric(6,2) NOT NULL DEFAULT 0.00
last_xact_id text NOT NULL DEFAULT 'none'::text
alert_message text
create_date timestamp with time zone NOT NULL DEFAULT now()
expire_date timestamp with time zone NOT NULL DEFAULT (now() + '3 years'::interval)
claims_never_checked_out_count integer NOT NULL
last_update_time timestamp with time zone

Tables referencing this one via Foreign Key Constraints:

actor_usr_billing_address_idx billing_address actor_usr_day_phone_idx lowercase(day_phone) actor_usr_day_phone_idx_numeric lowercase(regexp_replace(day_phone, '[^0-9]'::text, ''::text, 'g'::text)) actor_usr_email_idx lowercase(email) actor_usr_evening_phone_idx lowercase(evening_phone) actor_usr_evening_phone_idx_numeric lowercase(regexp_replace(evening_phone, '[^0-9]'::text, ''::text, 'g'::text)) actor_usr_family_name_idx lowercase(family_name) actor_usr_first_given_name_idx lowercase(first_given_name) actor_usr_home_ou_idx home_ou actor_usr_ident_value2_idx lowercase(ident_value2) actor_usr_ident_value_idx lowercase(ident_value) actor_usr_mailing_address_idx mailing_address actor_usr_other_phone_idx lowercase(other_phone) actor_usr_other_phone_idx_numeric lowercase(regexp_replace(other_phone, '[^0-9]'::text, ''::text, 'g'::text)) actor_usr_second_given_name_idx lowercase(second_given_name) actor_usr_usrgroup_idx usrgroup

Index - Schema actor


Table: actor.usr_activity

actor.usr_activity Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr integer
config.usr_activity_type.id etype integer NOT NULL
event_time timestamp with time zone NOT NULL DEFAULT now()

Index - Schema actor


Table: actor.usr_address

actor.usr_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
within_city_limits boolean NOT NULL DEFAULT true
address_type text NOT NULL DEFAULT 'MAILING'::text
actor.usr.id usr integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL
pending boolean NOT NULL DEFAULT false
actor.usr_address.id replaces integer

Tables referencing this one via Foreign Key Constraints:

actor_usr_addr_city_idx lowercase(city) actor_usr_addr_post_code_idx lowercase(post_code) actor_usr_addr_state_idx lowercase(state) actor_usr_addr_street1_idx lowercase(street1) actor_usr_addr_street2_idx lowercase(street2) actor_usr_addr_usr_idx usr

Index - Schema actor


Table: actor.usr_note

actor.usr_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr bigint NOT NULL
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
pub boolean NOT NULL DEFAULT false
title text NOT NULL
value text NOT NULL
actor_usr_note_creator_idx creator actor_usr_note_usr_idx usr

Index - Schema actor


Table: actor.usr_org_unit_opt_in

actor.usr_org_unit_opt_in Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
actor.usr.id usr integer UNIQUE#1 NOT NULL
actor.usr.id staff integer NOT NULL
opt_in_ts timestamp with time zone NOT NULL DEFAULT now()
actor.workstation.id opt_in_ws integer NOT NULL
usr_org_unit_opt_in_staff_idx staff

Index - Schema actor


Table: actor.usr_password_reset

Self-serve password reset requests

actor.usr_password_reset Structure
F-Key Name Type Description
id serial PRIMARY KEY
uuid text NOT NULL
actor.usr.id usr bigint NOT NULL
request_time timestamp with time zone NOT NULL DEFAULT now()
has_been_reset boolean NOT NULL DEFAULT false
actor_usr_password_reset_has_been_reset_idx has_been_reset actor_usr_password_reset_request_time_idx request_time actor_usr_password_reset_usr_idx usr

Index - Schema actor


Table: actor.usr_saved_search

actor.usr_saved_search Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
create_date timestamp with time zone NOT NULL DEFAULT now()
query_text text NOT NULL
query_type text NOT NULL DEFAULT 'URL'::text
target text NOT NULL

 

actor.usr_saved_search Constraints
Name Constraint
valid_query_text CHECK ((query_type = 'URL'::text))
valid_target CHECK ((target = ANY (ARRAY['record'::text, 'metarecord'::text, 'callnumber'::text])))

Index - Schema actor


Table: actor.usr_setting

User settings This table contains any arbitrary settings that a client program would like to save for a user.

actor.usr_setting Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr integer UNIQUE#1 NOT NULL
config.usr_setting_type.name name text UNIQUE#1 NOT NULL
value text NOT NULL
actor_usr_setting_usr_idx usr

Index - Schema actor


Table: actor.usr_standing_penalty

User standing penalties

actor.usr_standing_penalty Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer NOT NULL
actor.usr.id usr integer NOT NULL
config.standing_penalty.id standing_penalty integer NOT NULL
actor.usr.id staff integer
set_date timestamp with time zone DEFAULT now()
stop_date timestamp with time zone
note text
actor_usr_standing_penalty_staff_idx staff actor_usr_standing_penalty_usr_idx usr

Index - Schema actor


Table: actor.workstation

actor.workstation Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
actor.org_unit.id owning_lib integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Function: actor.address_alert_matches(billing_address integer, mailing_address text, post_code text, country text, state text, county text, city text, street2 text, street1 boolean, org_unit boolean)

Returns: SET OF address_alert

Language: SQL


SELECT *
FROM actor.address_alert
WHERE
    active
    AND owner IN (SELECT id FROM actor.org_unit_ancestors($1)) 
    AND (
        (NOT mailing_address AND NOT billing_address)
        OR (mailing_address AND $9)
        OR (billing_address AND $10)
    )
    AND (
            (
                match_all
                AND COALESCE($2, '') ~* COALESCE(street1,   '.*')
                AND COALESCE($3, '') ~* COALESCE(street2,   '.*')
                AND COALESCE($4, '') ~* COALESCE(city,      '.*')
                AND COALESCE($5, '') ~* COALESCE(county,    '.*')
                AND COALESCE($6, '') ~* COALESCE(state,     '.*')
                AND COALESCE($7, '') ~* COALESCE(country,   '.*')
                AND COALESCE($8, '') ~* COALESCE(post_code, '.*')
            ) OR (
                NOT match_all 
                AND (  
                       $2 ~* street1
                    OR $3 ~* street2
                    OR $4 ~* city
                    OR $5 ~* county
                    OR $6 ~* state
                    OR $7 ~* country
                    OR $8 ~* post_code
                )
            )
        )
    ORDER BY actor.org_unit_proximity(owner, $1)

Function: actor.approve_pending_address(pending_id integer)

Returns: bigint

Language: PLPGSQL

Replaces an address with a pending address. This is done by giving the pending address the ID of the old address. The replaced address is retained with -id.

DECLARE
    old_id INT;
BEGIN
    SELECT INTO old_id replaces FROM actor.usr_address where id = pending_id;
    IF old_id IS NULL THEN
        UPDATE actor.usr_address SET pending = 'f' WHERE id = pending_id;
        RETURN pending_id;
    END IF;
    -- address replaces an existing address
    DELETE FROM actor.usr_address WHERE id = -old_id;
    UPDATE actor.usr_address SET id = -id WHERE id = old_id;
    UPDATE actor.usr_address SET replaces = NULL, id = old_id, pending = 'f' WHERE id = pending_id;
    RETURN old_id;
END

Function: actor.au_updated()

Returns: trigger

Language: PLPGSQL

BEGIN
    NEW.last_update_time := now();
	RETURN NEW;
END;

Function: actor.calculate_system_penalties(context_org integer, match_user integer)

Returns: SET OF usr_standing_penalty

Language: PLPGSQL

DECLARE
    user_object         actor.usr%ROWTYPE;
    new_sp_row          actor.usr_standing_penalty%ROWTYPE;
    existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
    collections_fines   permission.grp_penalty_threshold%ROWTYPE;
    max_fines           permission.grp_penalty_threshold%ROWTYPE;
    max_overdue         permission.grp_penalty_threshold%ROWTYPE;
    max_items_out       permission.grp_penalty_threshold%ROWTYPE;
    tmp_grp             INT;
    items_overdue       INT;
    items_out           INT;
    context_org_list    INT[];
    current_fines        NUMERIC(8,2) := 0.0;
    tmp_fines            NUMERIC(8,2);
    tmp_groc            RECORD;
    tmp_circ            RECORD;
    tmp_org             actor.org_unit%ROWTYPE;
    tmp_penalty         config.standing_penalty%ROWTYPE;
    tmp_depth           INTEGER;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;

    -- Max fines
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has a high fine balance
    LOOP
        tmp_grp := user_object.profile;
        LOOP
            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;

            IF max_fines.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;

    IF max_fines.threshold IS NOT NULL THEN

        RETURN QUERY
            SELECT  *
              FROM  actor.usr_standing_penalty
              WHERE usr = match_user
                    AND org_unit = max_fines.org_unit
                    AND (stop_date IS NULL or stop_date > NOW())
                    AND standing_penalty = 1;

        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );

        SELECT  SUM(f.balance_owed) INTO current_fines
          FROM  money.materialized_billable_xact_summary f
                JOIN (
                    SELECT  r.id
                      FROM  booking.reservation r
                      WHERE r.usr = match_user
                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
                            AND xact_finish IS NULL
                                UNION ALL
                    SELECT  g.id
                      FROM  money.grocery g
                      WHERE g.usr = match_user
                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
                            AND xact_finish IS NULL
                                UNION ALL
                    SELECT  circ.id
                      FROM  action.circulation circ
                      WHERE circ.usr = match_user
                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
                            AND xact_finish IS NULL ) l USING (id);

        IF current_fines >= max_fines.threshold THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_fines.org_unit;
            new_sp_row.standing_penalty := 1;
            RETURN NEXT new_sp_row;
        END IF;
    END IF;

    -- Start over for max overdue
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has too many overdue items
    LOOP
        tmp_grp := user_object.profile;
        LOOP

            SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;

            IF max_overdue.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;

    IF max_overdue.threshold IS NOT NULL THEN

        RETURN QUERY
            SELECT  *
              FROM  actor.usr_standing_penalty
              WHERE usr = match_user
                    AND org_unit = max_overdue.org_unit
                    AND (stop_date IS NULL or stop_date > NOW())
                    AND standing_penalty = 2;

        SELECT  INTO items_overdue COUNT(*)
          FROM  action.circulation circ
                JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
          WHERE circ.usr = match_user
            AND circ.checkin_time IS NULL
            AND circ.due_date < NOW()
            AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);

        IF items_overdue >= max_overdue.threshold::INT THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_overdue.org_unit;
            new_sp_row.standing_penalty := 2;
            RETURN NEXT new_sp_row;
        END IF;
    END IF;

    -- Start over for max out
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has too many checked out items
    LOOP
        tmp_grp := user_object.profile;
        LOOP
            SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;

            IF max_items_out.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;


    -- Fail if the user has too many items checked out
    IF max_items_out.threshold IS NOT NULL THEN

        RETURN QUERY
            SELECT  *
              FROM  actor.usr_standing_penalty
              WHERE usr = match_user
                    AND org_unit = max_items_out.org_unit
                    AND (stop_date IS NULL or stop_date > NOW())
                    AND standing_penalty = 3;

        SELECT  INTO items_out COUNT(*)
          FROM  action.circulation circ
                JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
          WHERE circ.usr = match_user
                AND circ.checkin_time IS NULL
                AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);

           IF items_out >= max_items_out.threshold::INT THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_items_out.org_unit;
            new_sp_row.standing_penalty := 3;
            RETURN NEXT new_sp_row;
           END IF;
    END IF;

    -- Start over for collections warning
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has a collections-level fine balance
    LOOP
        tmp_grp := user_object.profile;
        LOOP
            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;

            IF max_fines.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;

    IF max_fines.threshold IS NOT NULL THEN

        RETURN QUERY
            SELECT  *
              FROM  actor.usr_standing_penalty
              WHERE usr = match_user
                    AND org_unit = max_fines.org_unit
                    AND (stop_date IS NULL or stop_date > NOW())
                    AND standing_penalty = 4;

        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );

        SELECT  SUM(f.balance_owed) INTO current_fines
          FROM  money.materialized_billable_xact_summary f
                JOIN (
                    SELECT  r.id
                      FROM  booking.reservation r
                      WHERE r.usr = match_user
                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
                            AND r.xact_finish IS NULL
                                UNION ALL
                    SELECT  g.id
                      FROM  money.grocery g
                      WHERE g.usr = match_user
                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
                            AND g.xact_finish IS NULL
                                UNION ALL
                    SELECT  circ.id
                      FROM  action.circulation circ
                      WHERE circ.usr = match_user
                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
                            AND circ.xact_finish IS NULL ) l USING (id);

        IF current_fines >= max_fines.threshold THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_fines.org_unit;
            new_sp_row.standing_penalty := 4;
            RETURN NEXT new_sp_row;
        END IF;
    END IF;

    -- Start over for in collections
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Remove the in-collections penalty if the user has paid down enough
    -- This penalty is different, because this code is not responsible for creating 
    -- new in-collections penalties, only for removing them
    LOOP
        tmp_grp := user_object.profile;
        LOOP
            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;

            IF max_fines.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;

    IF max_fines.threshold IS NOT NULL THEN

        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );

        -- first, see if the user had paid down to the threshold
        SELECT  SUM(f.balance_owed) INTO current_fines
          FROM  money.materialized_billable_xact_summary f
                JOIN (
                    SELECT  r.id
                      FROM  booking.reservation r
                      WHERE r.usr = match_user
                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
                            AND r.xact_finish IS NULL
                                UNION ALL
                    SELECT  g.id
                      FROM  money.grocery g
                      WHERE g.usr = match_user
                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
                            AND g.xact_finish IS NULL
                                UNION ALL
                    SELECT  circ.id
                      FROM  action.circulation circ
                      WHERE circ.usr = match_user
                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
                            AND circ.xact_finish IS NULL ) l USING (id);

        IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
            -- patron has paid down enough

            SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;

            IF tmp_penalty.org_depth IS NOT NULL THEN

                -- since this code is not responsible for applying the penalty, it can't 
                -- guarantee the current context org will match the org at which the penalty 
                --- was applied.  search up the org tree until we hit the configured penalty depth
                SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
                SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;

                WHILE tmp_depth >= tmp_penalty.org_depth LOOP

                    RETURN QUERY
                        SELECT  *
                          FROM  actor.usr_standing_penalty
                          WHERE usr = match_user
                                AND org_unit = tmp_org.id
                                AND (stop_date IS NULL or stop_date > NOW())
                                AND standing_penalty = 30;

                    IF tmp_org.parent_ou IS NULL THEN
                        EXIT;
                    END IF;

                    SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
                    SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
                END LOOP;

            ELSE

                -- no penalty depth is defined, look for exact matches

                RETURN QUERY
                    SELECT  *
                      FROM  actor.usr_standing_penalty
                      WHERE usr = match_user
                            AND org_unit = max_fines.org_unit
                            AND (stop_date IS NULL or stop_date > NOW())
                            AND standing_penalty = 30;
            END IF;
    
        END IF;

    END IF;

    RETURN;
END;

Function: actor.crypt_pw_insert()

Returns: trigger

Language: PLPGSQL

	BEGIN
		NEW.passwd = MD5( NEW.passwd );
		RETURN NEW;
	END;

Function: actor.crypt_pw_update()

Returns: trigger

Language: PLPGSQL

	BEGIN
		IF NEW.passwd <> OLD.passwd THEN
			NEW.passwd = MD5( NEW.passwd );
		END IF;
		RETURN NEW;
	END;

Function: actor.insert_usr_activity(ehow integer, ewhat text, ewho text, usr text)

Returns: SET OF usr_activity

Language: PLPGSQL

DECLARE
    new_row actor.usr_activity%ROWTYPE;
BEGIN
    SELECT id INTO new_row.etype FROM actor.usr_activity_get_type(ewho, ewhat, ehow);
    IF FOUND THEN
        new_row.usr := usr;
        INSERT INTO actor.usr_activity (usr, etype) 
            VALUES (usr, new_row.etype)
            RETURNING * INTO new_row;
        RETURN NEXT new_row;
    END IF;
END;

Function: actor.org_unit_ancestor_at_depth(integer, integer)

Returns: org_unit

Language: SQL

	SELECT	a.*
	  FROM	actor.org_unit a
	  WHERE	id = ( SELECT FIRST(x.id)
	  		 FROM	actor.org_unit_ancestors($1) x
			   	JOIN actor.org_unit_type y
					ON x.ou_type = y.id AND y.depth = $2);

Function: actor.org_unit_ancestor_setting(org_id text, setting_name integer)

Returns: SET OF org_unit_setting

Language: PLPGSQL

Search "up" the org_unit tree until we find the first occurrence of an org_unit_setting with the given name.

DECLARE
    setting RECORD;
    cur_org INT;
BEGIN
    cur_org := org_id;
    LOOP
        SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
        IF FOUND THEN
            RETURN NEXT setting;
            EXIT;
        END IF;
        SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
        EXIT WHEN cur_org IS NULL;
    END LOOP;
    RETURN;
END;

Function: actor.org_unit_ancestors(integer)

Returns: SET OF org_unit

Language: SQL

    WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
            SELECT $1, 0
        UNION
            SELECT ou.parent_ou, ouad.distance+1
            FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
            WHERE ou.parent_ou IS NOT NULL
    )
    SELECT ou.* FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;

Function: actor.org_unit_ancestors_distance(distance integer)

Returns: SET OF record

Language: SQL

    WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
            SELECT $1, 0
        UNION
            SELECT ou.parent_ou, ouad.distance+1
            FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
            WHERE ou.parent_ou IS NOT NULL
    )
    SELECT * FROM org_unit_ancestors_distance;

Function: actor.org_unit_combined_ancestors(integer, integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	*
	  FROM	actor.org_unit_ancestors($1)
			UNION
	SELECT	*
	  FROM	actor.org_unit_ancestors($2);

Function: actor.org_unit_common_ancestors(integer, integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	*
	  FROM	actor.org_unit_ancestors($1)
			INTERSECT
	SELECT	*
	  FROM	actor.org_unit_ancestors($2);

Function: actor.org_unit_descendants(integer)

Returns: SET OF org_unit

Language: SQL

    WITH RECURSIVE descendant_depth AS (
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
          WHERE ou.id = $1
            UNION ALL
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
    ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);

Function: actor.org_unit_descendants(integer, integer)

Returns: SET OF org_unit

Language: SQL

    WITH RECURSIVE descendant_depth AS (
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN anscestor_depth ad ON (ad.id = ou.id)
          WHERE ad.depth = $2
            UNION ALL
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
    ), anscestor_depth AS (
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
          WHERE ou.id = $1
            UNION ALL
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
    ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);

Function: actor.org_unit_descendants_distance(distance integer)

Returns: SET OF record

Language: SQL

    WITH RECURSIVE org_unit_descendants_distance(id, distance) AS (
            SELECT $1, 0
        UNION
            SELECT ou.id, oudd.distance+1
            FROM actor.org_unit ou JOIN org_unit_descendants_distance oudd ON (ou.parent_ou = oudd.id)
    )
    SELECT * FROM org_unit_descendants_distance;

Function: actor.org_unit_full_path(integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	*
	  FROM	actor.org_unit_ancestors($1)
			UNION
	SELECT	*
	  FROM	actor.org_unit_descendants($1);

Function: actor.org_unit_full_path(integer, integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	* FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)

Function: actor.org_unit_parent_protect()

Returns: trigger

Language: PLPGSQL

	DECLARE
		current_aou actor.org_unit%ROWTYPE;
		seen_ous    INT[];
		depth_count INT;
	BEGIN
		current_aou := NEW;
		depth_count := 0;
		seen_ous := ARRAY[NEW.id];

		IF (TG_OP = 'UPDATE') THEN
			IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
				RETURN NEW; -- Doing an UPDATE with no change, just return it
			END IF;
		END IF;

		LOOP
			IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
				RETURN NEW; -- No loop. Carry on.
			END IF;
			IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
				RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
			END IF;
			-- Get the next one!
			SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
			seen_ous := seen_ous || current_aou.id;
			depth_count := depth_count + 1;
			IF depth_count = 100 THEN
				RAISE 'OU CHECK TOO DEEP';
			END IF;
		END LOOP;

		RETURN NEW;
	END;

Function: actor.org_unit_prox_update()

Returns: trigger

Language: PLPGSQL

BEGIN


IF TG_OP = 'DELETE' THEN

    DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);

END IF;

IF TG_OP = 'UPDATE' THEN

    IF NEW.parent_ou <> OLD.parent_ou THEN

        DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
            INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
            SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
                FROM  actor.org_unit l, actor.org_unit r
                WHERE (l.id = NEW.id or r.id = NEW.id);

    END IF;

END IF;

IF TG_OP = 'INSERT' THEN

     INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
     SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
         FROM  actor.org_unit l, actor.org_unit r
         WHERE (l.id = NEW.id or r.id = NEW.id);

END IF;

RETURN null;

END;

Function: actor.org_unit_proximity(integer, integer)

Returns: integer

Language: SQL

	SELECT COUNT(id)::INT FROM (
		SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
			EXCEPT
		SELECT id FROM actor.org_unit_common_ancestors($1, $2)
	) z;

Function: actor.org_unit_simple_path(integer, integer)

Returns: integer[]

Language: SQL

    WITH RECURSIVE descendant_depth(id, path) AS (
        SELECT  aou.id,
                ARRAY[aou.id]
          FROM  actor.org_unit aou
                JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
          WHERE aou.id = $2
            UNION ALL
        SELECT  aou.id,
                dd.path || ARRAY[aou.id]
          FROM  actor.org_unit aou
                JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
                JOIN descendant_depth dd ON (dd.id = aou.parent_ou)
    ) SELECT dd.path
        FROM actor.org_unit aou
        JOIN descendant_depth dd USING (id)
        WHERE aou.id = $1 ORDER BY dd.path;

Function: actor.stat_cat_check()

Returns: trigger

Language: PLPGSQL

DECLARE
    sipfield actor.stat_cat_sip_fields%ROWTYPE;
    use_count INT;
BEGIN
    IF NEW.sip_field IS NOT NULL THEN
        SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
        IF sipfield.one_only THEN
            SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
            IF use_count > 0 THEN
                RAISE EXCEPTION 'Sip field cannot be used twice';
            END IF;
        END IF;
    END IF;
    RETURN NEW;
END;

Function: actor.usr_activity_get_type(ehow text, ewhat text, ewho text)

Returns: SET OF usr_activity_type

Language: SQL

SELECT * FROM config.usr_activity_type 
    WHERE 
        enabled AND 
        (ewho  IS NULL OR ewho  = $1) AND
        (ewhat IS NULL OR ewhat = $2) AND
        (ehow  IS NULL OR ehow  = $3) 
    ORDER BY 
        -- BOOL comparisons sort false to true
        COALESCE(ewho, '')  != COALESCE($1, ''),
        COALESCE(ewhat,'')  != COALESCE($2, ''),
        COALESCE(ehow, '')  != COALESCE($3, '') 
    LIMIT 1;

Function: actor.usr_activity_transient_trg()

Returns: trigger

Language: PLPGSQL

BEGIN
    DELETE FROM actor.usr_activity act USING config.usr_activity_type atype
        WHERE atype.transient AND 
            NEW.etype = atype.id AND
            act.etype = atype.id AND
            act.usr = NEW.usr;
    RETURN NEW;
END;

Function: actor.usr_delete(dest_usr integer, src_usr integer)

Returns: void

Language: PLPGSQL

Logically deletes a user. Removes personally identifiable information, and purges associated data in other tables.

DECLARE
	old_profile actor.usr.profile%type;
	old_home_ou actor.usr.home_ou%type;
	new_profile actor.usr.profile%type;
	new_home_ou actor.usr.home_ou%type;
	new_name    text;
	new_dob     actor.usr.dob%type;
BEGIN
	SELECT
		id || '-PURGED-' || now(),
		profile,
		home_ou,
		dob
	INTO
		new_name,
		old_profile,
		old_home_ou,
		new_dob
	FROM
		actor.usr
	WHERE
		id = src_usr;
	--
	-- Quit if no such user
	--
	IF old_profile IS NULL THEN
		RETURN;
	END IF;
	--
	perform actor.usr_purge_data( src_usr, dest_usr );
	--
	-- Find the root grp_tree and the root org_unit.  This would be simpler if we 
	-- could assume that there is only one root.  Theoretically, someday, maybe,
	-- there could be multiple roots, so we take extra trouble to get the right ones.
	--
	SELECT
		id
	INTO
		new_profile
	FROM
		permission.grp_ancestors( old_profile )
	WHERE
		parent is null;
	--
	SELECT
		id
	INTO
		new_home_ou
	FROM
		actor.org_unit_ancestors( old_home_ou )
	WHERE
		parent_ou is null;
	--
	-- Truncate date of birth
	--
	IF new_dob IS NOT NULL THEN
		new_dob := date_trunc( 'year', new_dob );
	END IF;
	--
	UPDATE
		actor.usr
		SET
			card = NULL,
			profile = new_profile,
			usrname = new_name,
			email = NULL,
			passwd = random()::text,
			standing = DEFAULT,
			ident_type = 
			(
				SELECT MIN( id )
				FROM config.identification_type
			),
			ident_value = NULL,
			ident_type2 = NULL,
			ident_value2 = NULL,
			net_access_level = DEFAULT,
			photo_url = NULL,
			prefix = NULL,
			first_given_name = new_name,
			second_given_name = NULL,
			family_name = new_name,
			suffix = NULL,
			alias = NULL,
			day_phone = NULL,
			evening_phone = NULL,
			other_phone = NULL,
			mailing_address = NULL,
			billing_address = NULL,
			home_ou = new_home_ou,
			dob = new_dob,
			active = FALSE,
			master_account = DEFAULT, 
			super_user = DEFAULT,
			barred = FALSE,
			deleted = TRUE,
			juvenile = DEFAULT,
			usrgroup = 0,
			claims_returned_count = DEFAULT,
			credit_forward_balance = DEFAULT,
			last_xact_id = DEFAULT,
			alert_message = NULL,
			create_date = now(),
			expire_date = now()
	WHERE
		id = src_usr;
END;

Function: actor.usr_merge(deactivate_cards integer, del_cards integer, del_addrs boolean, dest_usr boolean, src_usr boolean)

Returns: void

Language: PLPGSQL

Merges all user date from src_usr to dest_usr. When collisions occur, keep dest_usr's data and delete src_usr's data.

DECLARE
	suffix TEXT;
	bucket_row RECORD;
	picklist_row RECORD;
	queue_row RECORD;
	folder_row RECORD;
BEGIN

    -- do some initial cleanup 
    UPDATE actor.usr SET card = NULL WHERE id = src_usr;
    UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
    UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;

    -- actor.*
    IF del_cards THEN
        DELETE FROM actor.card where usr = src_usr;
    ELSE
        IF deactivate_cards THEN
            UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
        END IF;
        UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
    END IF;


    IF del_addrs THEN
        DELETE FROM actor.usr_address WHERE usr = src_usr;
    ELSE
        UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
    END IF;

    UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
    -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
    UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
    PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);

    -- permission.*
    PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);


    -- container.*
	
	-- For each *_bucket table: transfer every bucket belonging to src_usr
	-- into the custody of dest_usr.
	--
	-- In order to avoid colliding with an existing bucket owned by
	-- the destination user, append the source user's id (in parenthesese)
	-- to the name.  If you still get a collision, add successive
	-- spaces to the name and keep trying until you succeed.
	--
	FOR bucket_row in
		SELECT id, name
		FROM   container.biblio_record_entry_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.biblio_record_entry_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR bucket_row in
		SELECT id, name
		FROM   container.call_number_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.call_number_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR bucket_row in
		SELECT id, name
		FROM   container.copy_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.copy_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR bucket_row in
		SELECT id, name
		FROM   container.user_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.user_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;

    -- vandelay.*
	-- transfer queues the same way we transfer buckets (see above)
	FOR queue_row in
		SELECT id, name
		FROM   vandelay.queue
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  vandelay.queue
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = queue_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

    -- money.*
    PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
    UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
    UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
    UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;

    -- action.*
    UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
    UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
    UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;

    UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
    UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
    UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
    UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;

    UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
    UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;

    -- acq.*
    UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
	UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;

	-- transfer picklists the same way we transfer buckets (see above)
	FOR picklist_row in
		SELECT id, name
		FROM   acq.picklist
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  acq.picklist
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = picklist_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

    UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
    UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;

    -- asset.*
    UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
    UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
    UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;

    -- serial.*
    UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
    UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;

    -- reporter.*
    -- It's not uncommon to define the reporter schema in a replica 
    -- DB only, so don't assume these tables exist in the write DB.
    BEGIN
    	UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
    	UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
    	UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
		-- transfer folders the same way we transfer buckets (see above)
		FOR folder_row in
			SELECT id, name
			FROM   reporter.template_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.template_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = folder_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
		-- transfer folders the same way we transfer buckets (see above)
		FOR folder_row in
			SELECT id, name
			FROM   reporter.report_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.report_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = folder_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
		-- transfer folders the same way we transfer buckets (see above)
		FOR folder_row in
			SELECT id, name
			FROM   reporter.output_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.output_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = folder_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;

    -- Finally, delete the source user
    DELETE FROM actor.usr WHERE id = src_usr;

END;

Function: actor.usr_merge_rows(dest_usr text, src_usr text, col_name integer, table_name integer)

Returns: void

Language: PLPGSQL

Attempts to move each row of the specified table from src_user to dest_user. Where conflicts exist, the conflicting "source" row is deleted.

DECLARE
    sel TEXT;
    upd TEXT;
    del TEXT;
    cur_row RECORD;
BEGIN
    sel := 'SELECT id::BIGINT FROM ' || table_name || ' WHERE ' || quote_ident(col_name) || ' = ' || quote_literal(src_usr);
    upd := 'UPDATE ' || table_name || ' SET ' || quote_ident(col_name) || ' = ' || quote_literal(dest_usr) || ' WHERE id = ';
    del := 'DELETE FROM ' || table_name || ' WHERE id = ';
    FOR cur_row IN EXECUTE sel LOOP
        BEGIN
            --RAISE NOTICE 'Attempting to merge % %', table_name, cur_row.id;
            EXECUTE upd || cur_row.id;
        EXCEPTION WHEN unique_violation THEN
            --RAISE NOTICE 'Deleting conflicting % %', table_name, cur_row.id;
            EXECUTE del || cur_row.id;
        END;
    END LOOP;
END;

Function: actor.usr_purge_data(specified_dest_usr integer, src_usr integer)

Returns: void

Language: PLPGSQL

Finds rows dependent on a given row in actor.usr and either deletes them or reassigns them to a different user.

DECLARE
	suffix TEXT;
	renamable_row RECORD;
	dest_usr INTEGER;
BEGIN

	IF specified_dest_usr IS NULL THEN
		dest_usr := 1; -- Admin user on stock installs
	ELSE
		dest_usr := specified_dest_usr;
	END IF;

	UPDATE actor.usr SET
		active = FALSE,
		card = NULL,
		mailing_address = NULL,
		billing_address = NULL
	WHERE id = src_usr;

	-- acq.*
	UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
	UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
	UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
	UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
	UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
	UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
	DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;

	-- Update with a rename to avoid collisions
	FOR renamable_row in
		SELECT id, name
		FROM   acq.picklist
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  acq.picklist
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = renamable_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
	UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
	UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
	UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
	UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
	UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
	UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
	UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;

	-- action.*
	DELETE FROM action.circulation WHERE usr = src_usr;
	UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
	UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
	UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
	UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
	UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
	DELETE FROM action.hold_request WHERE usr = src_usr;
	UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
	UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
	DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
	UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
	DELETE FROM action.survey_response WHERE usr = src_usr;
	UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;

	-- actor.*
	DELETE FROM actor.card WHERE usr = src_usr;
	DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;

	-- The following update is intended to avoid transient violations of a foreign
	-- key constraint, whereby actor.usr_address references itself.  It may not be
	-- necessary, but it does no harm.
	UPDATE actor.usr_address SET replaces = NULL
		WHERE usr = src_usr AND replaces IS NOT NULL;
	DELETE FROM actor.usr_address WHERE usr = src_usr;
	DELETE FROM actor.usr_note WHERE usr = src_usr;
	UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
	DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
	UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
	DELETE FROM actor.usr_setting WHERE usr = src_usr;
	DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
	UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;

	-- asset.*
	UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
	UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
	UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
	UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
	UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
	UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;

	-- auditor.*
	DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
	DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
	UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
	UPDATE auditor.asset_call_number_history SET editor  = dest_usr WHERE editor  = src_usr;
	UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
	UPDATE auditor.asset_copy_history SET editor  = dest_usr WHERE editor  = src_usr;
	UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
	UPDATE auditor.biblio_record_entry_history SET editor  = dest_usr WHERE editor  = src_usr;

	-- biblio.*
	UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
	UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
	UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
	UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;

	-- container.*
	-- Update buckets with a rename to avoid collisions
	FOR renamable_row in
		SELECT id, name
		FROM   container.biblio_record_entry_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.biblio_record_entry_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = renamable_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR renamable_row in
		SELECT id, name
		FROM   container.call_number_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.call_number_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = renamable_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR renamable_row in
		SELECT id, name
		FROM   container.copy_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.copy_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = renamable_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR renamable_row in
		SELECT id, name
		FROM   container.user_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.user_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = renamable_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	DELETE FROM container.user_bucket_item WHERE target_user = src_usr;

	-- money.*
	DELETE FROM money.billable_xact WHERE usr = src_usr;
	DELETE FROM money.collections_tracker WHERE usr = src_usr;
	UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;

	-- permission.*
	DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
	DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
	DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
	DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;

	-- reporter.*
	-- Update with a rename to avoid collisions
	BEGIN
		FOR renamable_row in
			SELECT id, name
			FROM   reporter.output_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.output_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = renamable_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
	EXCEPTION WHEN undefined_table THEN
		-- do nothing
	END;

	BEGIN
		UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
	EXCEPTION WHEN undefined_table THEN
		-- do nothing
	END;

	-- Update with a rename to avoid collisions
	BEGIN
		FOR renamable_row in
			SELECT id, name
			FROM   reporter.report_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.report_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = renamable_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
	EXCEPTION WHEN undefined_table THEN
		-- do nothing
	END;

	BEGIN
		UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
	EXCEPTION WHEN undefined_table THEN
		-- do nothing
	END;

	BEGIN
		UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
	EXCEPTION WHEN undefined_table THEN
		-- do nothing
	END;

	-- Update with a rename to avoid collisions
	BEGIN
		FOR renamable_row in
			SELECT id, name
			FROM   reporter.template_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.template_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = renamable_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
	EXCEPTION WHEN undefined_table THEN
	-- do nothing
	END;

	-- vandelay.*
	-- Update with a rename to avoid collisions
	FOR renamable_row in
		SELECT id, name
		FROM   vandelay.queue
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  vandelay.queue
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = renamable_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

END;

Schema asset


Table: asset.call_number

asset.call_number Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
actor.usr.id editor bigint NOT NULL
edit_date timestamp with time zone DEFAULT now()
biblio.record_entry.id record bigint NOT NULL
actor.org_unit.id owning_lib integer NOT NULL
label text NOT NULL
deleted boolean NOT NULL DEFAULT false
asset.call_number_prefix.id prefix integer NOT NULL DEFAULT (-1)
asset.call_number_suffix.id suffix integer NOT NULL DEFAULT (-1)
asset.call_number_class.id label_class bigint NOT NULL
label_sortkey text

Tables referencing this one via Foreign Key Constraints:

asset_call_number_creator_idx creator asset_call_number_dewey_idx call_number_dewey(label) asset_call_number_editor_idx editor asset_call_number_label_sortkey oils_text_as_bytea(label_sortkey) asset_call_number_label_sortkey_browse oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE ((deleted IS FALSE) OR (deleted = false) asset_call_number_record_idx record asset_call_number_upper_label_id_owning_lib_idx oils_text_as_bytea(label), id, owning_lib

Index - Schema asset


Table: asset.call_number_class

Defines the call number normalization database functions in the "normalizer" column and the tag/subfield combinations to use to lookup the call number in the "field" column for a given classification scheme. Tag/subfield combinations are delimited by commas.

asset.call_number_class Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
name text NOT NULL
normalizer text NOT NULL DEFAULT 'asset.normalize_generic'::text
field text NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'::text

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.call_number_note

asset.call_number_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.call_number.id call_number bigint NOT NULL
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
pub boolean NOT NULL DEFAULT false
title text NOT NULL
value text NOT NULL
asset_call_number_note_creator_idx creator

Index - Schema asset


Table: asset.call_number_prefix

asset.call_number_prefix Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owning_lib integer NOT NULL
label text NOT NULL
label_sortkey text

Tables referencing this one via Foreign Key Constraints:

asset_call_number_prefix_sortkey_idx label_sortkey

Index - Schema asset


Table: asset.call_number_suffix

asset.call_number_suffix Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owning_lib integer NOT NULL
label text NOT NULL
label_sortkey text

Tables referencing this one via Foreign Key Constraints:

asset_call_number_suffix_sortkey_idx label_sortkey

Index - Schema asset


Table: asset.copy

asset.copy Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.org_unit.id circ_lib integer NOT NULL
actor.usr.id creator bigint NOT NULL
asset.call_number.id call_number bigint NOT NULL
actor.usr.id editor bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
edit_date timestamp with time zone DEFAULT now()
copy_number integer
config.copy_status.id status integer NOT NULL
asset.copy_location.id location integer NOT NULL DEFAULT 1
loan_duration integer NOT NULL
fine_level integer NOT NULL
age_protect integer
circulate boolean NOT NULL DEFAULT true
deposit boolean NOT NULL DEFAULT false
ref boolean NOT NULL DEFAULT false
holdable boolean NOT NULL DEFAULT true
deposit_amount numeric(6,2) NOT NULL DEFAULT 0.00
price numeric(8,2)
barcode text NOT NULL
config.circ_modifier.code circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean NOT NULL DEFAULT true
deleted boolean NOT NULL DEFAULT false
floating boolean NOT NULL DEFAULT false
dummy_isbn text
status_changed_time timestamp with time zone
active_date timestamp with time zone
mint_condition boolean NOT NULL DEFAULT true
cost numeric(8,2)

 

asset.copy Constraints
Name Constraint
copy_fine_level_check CHECK ((fine_level = ANY (ARRAY[1, 2, 3])))
copy_loan_duration_check CHECK ((loan_duration = ANY (ARRAY[1, 2, 3])))

Tables referencing this one via Foreign Key Constraints:

cp_avail_cn_idx call_number cp_cn_idx call_number cp_create_date create_date cp_creator_idx creator cp_editor_idx editor

Index - Schema asset


Table: asset.copy_location

asset.copy_location Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owning_lib integer UNIQUE#1 NOT NULL
holdable boolean NOT NULL DEFAULT true
hold_verify boolean NOT NULL DEFAULT false
opac_visible boolean NOT NULL DEFAULT true
circulate boolean NOT NULL DEFAULT true
label_prefix text
label_suffix text
checkin_alert boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.copy_location_group

asset.copy_location_group Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
pos integer NOT NULL
top boolean NOT NULL DEFAULT false
opac_visible boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.copy_location_group_map

asset.copy_location_group_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
asset.copy_location.id location integer UNIQUE#1 NOT NULL
asset.copy_location_group.id lgroup integer UNIQUE#1 NOT NULL

Index - Schema asset


Table: asset.copy_location_order

asset.copy_location_order Structure
F-Key Name Type Description
id serial PRIMARY KEY
asset.copy_location.id location integer UNIQUE#1 NOT NULL
actor.org_unit.id org integer UNIQUE#1 NOT NULL
position integer NOT NULL

Index - Schema asset


Table: asset.copy_note

asset.copy_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.copy.id owning_copy bigint NOT NULL
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
pub boolean NOT NULL DEFAULT false
title text NOT NULL
value text NOT NULL
asset_copy_note_creator_idx creator asset_copy_note_owning_copy_idx owning_copy

Index - Schema asset


Table: asset.copy_part_map

asset.copy_part_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
target_copy bigint NOT NULL
biblio.monograph_part.id part integer NOT NULL

Index - Schema asset


Table: asset.copy_template

asset.copy_template Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owning_lib integer NOT NULL
actor.usr.id creator bigint NOT NULL
actor.usr.id editor bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
edit_date timestamp with time zone DEFAULT now()
name text NOT NULL
actor.org_unit.id circ_lib integer
config.copy_status.id status integer
asset.copy_location.id location integer
loan_duration integer
fine_level integer
age_protect integer
circulate boolean
deposit boolean
ref boolean
holdable boolean
deposit_amount numeric(6,2)
price numeric(8,2)
circ_modifier text
circ_as_type text
alert_message text
opac_visible boolean
floating boolean
mint_condition boolean

 

asset.copy_template Constraints
Name Constraint
valid_fine_level CHECK (((fine_level IS NULL) OR (loan_duration = ANY (ARRAY[1, 2, 3]))))
valid_loan_duration CHECK (((loan_duration IS NULL) OR (loan_duration = ANY (ARRAY[1, 2, 3]))))

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.opac_visible_copies

Materialized view of copies that are visible in the OPAC, used by search.query_parser_fts() to speed up OPAC visibility checks on large databases. Contents are maintained by a set of triggers.

asset.opac_visible_copies Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
copy_id bigint
record bigint
circ_lib integer
opac_visible_copies_copy_id_idx copy_id opac_visible_copies_idx1 record, circ_lib

Index - Schema asset


Table: asset.stat_cat

asset.stat_cat Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
opac_visible boolean NOT NULL DEFAULT false
name text UNIQUE#1 NOT NULL
required boolean NOT NULL DEFAULT false
asset.stat_cat_sip_fields.field sip_field character(2)
sip_format text
checkout_archive boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.stat_cat_entry

asset.stat_cat_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
asset.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
value text UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.stat_cat_entry_copy_map

asset.stat_cat_entry_copy_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
asset.stat_cat_entry.id stat_cat_entry integer NOT NULL
owning_copy bigint UNIQUE#1 NOT NULL
scecm_owning_copy_idx owning_copy

Index - Schema asset


Table: asset.stat_cat_entry_transparency_map

asset.stat_cat_entry_transparency_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
stat_cat integer UNIQUE#1 NOT NULL
stat_cat_entry integer NOT NULL
owning_transparency integer UNIQUE#1 NOT NULL

Index - Schema asset


Table: asset.stat_cat_sip_fields

Asset Statistical Category SIP Fields Contains the list of valid SIP Field identifiers for Statistical Categories.

asset.stat_cat_sip_fields Structure
F-Key Name Type Description
field character(2) PRIMARY KEY
name text NOT NULL
one_only boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.uri

asset.uri Structure
F-Key Name Type Description
id serial PRIMARY KEY
href text NOT NULL
label text
use_restriction text
active boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.uri_call_number_map

asset.uri_call_number_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.uri.id uri integer UNIQUE#1 NOT NULL
asset.call_number.id call_number integer UNIQUE#1 NOT NULL
asset_uri_call_number_map_cn_idx call_number

Index - Schema asset


Function: asset.acp_created()

Returns: trigger

Language: PLPGSQL

BEGIN
    IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
        NEW.active_date := now();
    END IF;
    IF NEW.status_changed_time IS NULL THEN
        NEW.status_changed_time := now();
    END IF;
    RETURN NEW;
END;

Function: asset.acp_status_changed()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
        NEW.status_changed_time := now();
        IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
            NEW.active_date := now();
        END IF;
    END IF;
    RETURN NEW;
END;

Function: asset.autogenerate_placeholder_barcode()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF NEW.barcode LIKE '@@%' THEN
		NEW.barcode := '@@' || NEW.id;
	END IF;
	RETURN NEW;
END;

Function: asset.cache_copy_visibility()

Returns: trigger

Language: PLPGSQL

Trigger function to update the copy OPAC visiblity cache.

DECLARE
    add_front       TEXT;
    add_back        TEXT;
    add_base_query  TEXT;
    add_peer_query  TEXT;
    remove_query    TEXT;
    do_add          BOOLEAN := false;
    do_remove       BOOLEAN := false;
BEGIN
    add_base_query := $$
        SELECT  cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status
          FROM  asset.copy cp
                JOIN asset.call_number cn ON (cn.id = cp.call_number)
                JOIN actor.org_unit a ON (cp.circ_lib = a.id)
                JOIN asset.copy_location cl ON (cp.location = cl.id)
                JOIN config.copy_status cs ON (cp.status = cs.id)
                JOIN biblio.record_entry b ON (cn.record = b.id)
          WHERE NOT cp.deleted
                AND NOT cn.deleted
                AND NOT b.deleted
                AND cs.opac_visible
                AND cl.opac_visible
                AND cp.opac_visible
                AND a.opac_visible
    $$;
    add_peer_query := $$
        SELECT  cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status
          FROM  asset.copy cp
                JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
                JOIN actor.org_unit a ON (cp.circ_lib = a.id)
                JOIN asset.copy_location cl ON (cp.location = cl.id)
                JOIN config.copy_status cs ON (cp.status = cs.id)
          WHERE NOT cp.deleted
                AND cs.opac_visible
                AND cl.opac_visible
                AND cp.opac_visible
                AND a.opac_visible
    $$;
    add_front := $$
        INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
          SELECT DISTINCT ON (id, record) id, circ_lib, record FROM (
    $$;
    add_back := $$
        ) AS x
    $$;
 
    remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;

    IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
        IF TG_OP = 'INSERT' THEN
            add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record;
            EXECUTE add_front || add_peer_query || add_back;
            RETURN NEW;
        ELSE
            remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
            EXECUTE remove_query;
            RETURN OLD;
        END IF;
    END IF;

    IF TG_OP = 'INSERT' THEN

        IF TG_TABLE_NAME IN ('copy', 'unit') THEN
            add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
            EXECUTE add_front || add_base_query || add_back;
        END IF;

        RETURN NEW;

    END IF;

    -- handle items first, since with circulation activity
    -- their statuses change frequently
    IF TG_TABLE_NAME IN ('copy', 'unit') THEN

        IF OLD.location    <> NEW.location OR
           OLD.call_number <> NEW.call_number OR
           OLD.status      <> NEW.status OR
           OLD.circ_lib    <> NEW.circ_lib THEN
            -- any of these could change visibility, but
            -- we'll save some queries and not try to calculate
            -- the change directly
            do_remove := true;
            do_add := true;
        ELSE

            IF OLD.deleted <> NEW.deleted THEN
                IF NEW.deleted THEN
                    do_remove := true;
                ELSE
                    do_add := true;
                END IF;
            END IF;

            IF OLD.opac_visible <> NEW.opac_visible THEN
                IF OLD.opac_visible THEN
                    do_remove := true;
                ELSIF NOT do_remove THEN -- handle edge case where deleted item
                                        -- is also marked opac_visible
                    do_add := true;
                END IF;
            END IF;

        END IF;

        IF do_remove THEN
            DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
        END IF;
        IF do_add THEN
            add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
            add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id;
            EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
        END IF;

        RETURN NEW;

    END IF;

    IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
 
        IF OLD.deleted AND NEW.deleted THEN -- do nothing

            RETURN NEW;
 
        ELSIF NEW.deleted THEN -- remove rows
 
            IF TG_TABLE_NAME = 'call_number' THEN
                DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
            ELSIF TG_TABLE_NAME = 'record_entry' THEN
                DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
            END IF;
 
            RETURN NEW;
 
        ELSIF OLD.deleted THEN -- add rows
 
            IF TG_TABLE_NAME = 'call_number' THEN
                add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
                EXECUTE add_front || add_base_query || add_back;
            ELSIF TG_TABLE_NAME = 'record_entry' THEN
                add_base_query := add_base_query || ' AND cn.record = ' || NEW.id;
                add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id;
                EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
            END IF;
 
            RETURN NEW;
 
        END IF;
 
    END IF;

    IF TG_TABLE_NAME = 'call_number' THEN

        IF OLD.record <> NEW.record THEN
            -- call number is linked to different bib
            remove_query := remove_query || 'call_number = ' || NEW.id || ');';
            EXECUTE remove_query;
            add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
            EXECUTE add_front || add_base_query || add_back;
        END IF;

        RETURN NEW;

    END IF;

    IF TG_TABLE_NAME IN ('record_entry') THEN
        RETURN NEW; -- don't have 'opac_visible'
    END IF;

    -- actor.org_unit, asset.copy_location, asset.copy_status
    IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing

        RETURN NEW;

    ELSIF NEW.opac_visible THEN -- add rows

        IF TG_TABLE_NAME = 'org_unit' THEN
            add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id;
            add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id;
        ELSIF TG_TABLE_NAME = 'copy_location' THEN
            add_base_query := add_base_query || ' AND cp.location = ' || NEW.id;
            add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id;
        ELSIF TG_TABLE_NAME = 'copy_status' THEN
            add_base_query := add_base_query || ' AND cp.status = ' || NEW.id;
            add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id;
        END IF;
 
        EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
 
    ELSE -- delete rows

        IF TG_TABLE_NAME = 'org_unit' THEN
            remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
        ELSIF TG_TABLE_NAME = 'copy_location' THEN
            remove_query := remove_query || 'location = ' || NEW.id || ');';
        ELSIF TG_TABLE_NAME = 'copy_status' THEN
            remove_query := remove_query || 'status = ' || NEW.id || ');';
        END IF;
 
        EXECUTE remove_query;
 
    END IF;
 
    RETURN NEW;
END;

Function: asset.label_normalizer()

Returns: trigger

Language: PLPGSQL

DECLARE
    sortkey        TEXT := '';
BEGIN
    sortkey := NEW.label_sortkey;

    IF NEW.label_class IS NULL THEN
            NEW.label_class := COALESCE(
            (
                SELECT substring(value from E'\\d+')::integer
                FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
            ), 1
        );
    END IF;

    EXECUTE 'SELECT ' || acnc.normalizer || '(' || 
       quote_literal( NEW.label ) || ')'
       FROM asset.call_number_class acnc
       WHERE acnc.id = NEW.label_class
       INTO sortkey;
    NEW.label_sortkey = sortkey;
    RETURN NEW;
END;

Function: asset.label_normalizer_dewey(text)

Returns: text

Language: PLPERLU

    # Derived from the Koha C4::ClassSortRoutine::Dewey module
    # Copyright (C) 2007 LibLime
    # Licensed under the GPL v2 or later

    use strict;
    use warnings;

    my $init = uc(shift);
    $init =~ s/^\s+//;
    $init =~ s/\s+$//;
    $init =~ s!/!!g;
    $init =~ s/^([\p{IsAlpha}]+)/$1 /;
    my @tokens = split /\.|\s+/, $init;
    my $digit_group_count = 0;
    for (my $i = 0; $i <= $#tokens; $i++) {
        if ($tokens[$i] =~ /^\d+$/) {
            $digit_group_count++;
            if (2 == $digit_group_count) {
                $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
                $tokens[$i] =~ tr/ /0/;
            }
        }
    }
    # Pad the first digit_group if there was only one
    if (1 == $digit_group_count) {
        $tokens[0] .= '_000000000000000'
    }
    my $key = join("_", @tokens);
    $key =~ s/[^\p{IsAlnum}_]//g;

    return $key;


Function: asset.label_normalizer_generic(text)

Returns: text

Language: PLPERLU

    # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
    # thus could probably be considered a derived work, although nothing was
    # directly copied - but to err on the safe side of providing attribution:
    # Copyright (C) 2007 LibLime
    # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
    # Licensed under the GPL v2 or later

    use strict;
    use warnings;

    # Converts the callnumber to uppercase
    # Strips spaces from start and end of the call number
    # Converts anything other than letters, digits, and periods into spaces
    # Collapses multiple spaces into a single underscore
    my $callnum = uc(shift);
    $callnum =~ s/^\s//g;
    $callnum =~ s/\s$//g;
    # NOTE: this previously used underscores, but this caused sorting issues
    # for the "before" half of page 0 on CN browse, sorting CNs containing a
    # decimal before "whole number" CNs
    $callnum =~ s/[^A-Z0-9_.]/ /g;
    $callnum =~ s/ {2,}/ /g;

    return $callnum;

Function: asset.label_normalizer_lc(text)

Returns: text

Language: PLPERLU

    use strict;
    use warnings;

    # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
    # The author hopes to upload it to CPAN some day, which would make our lives easier
    use Library::CallNumber::LC;

    my $callnum = Library::CallNumber::LC->new(shift);
    return $callnum->normalize();


Function: asset.merge_record_assets(source_record bigint, target_record bigint)

Returns: integer

Language: PLPGSQL

DECLARE
    moved_objects INT := 0;
    source_cn     asset.call_number%ROWTYPE;
    target_cn     asset.call_number%ROWTYPE;
    metarec       metabib.metarecord%ROWTYPE;
    hold          action.hold_request%ROWTYPE;
    ser_rec       serial.record_entry%ROWTYPE;
    ser_sub       serial.subscription%ROWTYPE;
    acq_lineitem  acq.lineitem%ROWTYPE;
    acq_request   acq.user_request%ROWTYPE;
    booking       booking.resource_type%ROWTYPE;
    source_part   biblio.monograph_part%ROWTYPE;
    target_part   biblio.monograph_part%ROWTYPE;
    multi_home    biblio.peer_bib_copy_map%ROWTYPE;
    uri_count     INT := 0;
    counter       INT := 0;
    uri_datafield TEXT;
    uri_text      TEXT := '';
BEGIN

    -- move any 856 entries on records that have at least one MARC-mapped URI entry
    SELECT  INTO uri_count COUNT(*)
      FROM  asset.uri_call_number_map m
            JOIN asset.call_number cn ON (m.call_number = cn.id)
      WHERE cn.record = source_record;

    IF uri_count > 0 THEN
        
        -- This returns more nodes than you might expect:
        -- 7 instead of 1 for an 856 with $u $y $9
        SELECT  COUNT(*) INTO counter
          FROM  oils_xpath_table(
                    'id',
                    'marc',
                    'biblio.record_entry',
                    '//*[@tag="856"]',
                    'id=' || source_record
                ) as t(i int,c text);
    
        FOR i IN 1 .. counter LOOP
            SELECT  '<datafield xmlns="http://www.loc.gov/MARC21/slim"' || 
			' tag="856"' ||
			' ind1="' || FIRST(ind1) || '"'  ||
			' ind2="' || FIRST(ind2) || '">' ||
                        array_to_string(
                            array_accum(
                                '<subfield code="' || subfield || '">' ||
                                regexp_replace(
                                    regexp_replace(
                                        regexp_replace(data,'&','&amp;','g'),
                                        '>', '&gt;', 'g'
                                    ),
                                    '<', '&lt;', 'g'
                                ) || '</subfield>'
                            ), ''
                        ) || '</datafield>' INTO uri_datafield
              FROM  oils_xpath_table(
                        'id',
                        'marc',
                        'biblio.record_entry',
                        '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
                        '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
                        '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
                        '//*[@tag="856"][position()=' || i || ']/*[@code]',
                        'id=' || source_record
                    ) as t(id int,ind1 text, ind2 text,subfield text,data text);

            -- As most of the results will be NULL, protect against NULLifying
            -- the valid content that we do generate
            uri_text := uri_text || COALESCE(uri_datafield, '');
        END LOOP;

        IF uri_text <> '' THEN
            UPDATE  biblio.record_entry
              SET   marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
              WHERE id = target_record;
        END IF;

    END IF;

	-- Find and move metarecords to the target record
	SELECT	INTO metarec *
	  FROM	metabib.metarecord
	  WHERE	master_record = source_record;

	IF FOUND THEN
		UPDATE	metabib.metarecord
		  SET	master_record = target_record,
			mods = NULL
		  WHERE	id = metarec.id;

		moved_objects := moved_objects + 1;
	END IF;

	-- Find call numbers attached to the source ...
	FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP

		SELECT	INTO target_cn *
		  FROM	asset.call_number
		  WHERE	label = source_cn.label
			AND owning_lib = source_cn.owning_lib
			AND record = target_record;

		-- ... and if there's a conflicting one on the target ...
		IF FOUND THEN

			-- ... move the copies to that, and ...
			UPDATE	asset.copy
			  SET	call_number = target_cn.id
			  WHERE	call_number = source_cn.id;

			-- ... move V holds to the move-target call number
			FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
		
				UPDATE	action.hold_request
				  SET	target = target_cn.id
				  WHERE	id = hold.id;
		
				moved_objects := moved_objects + 1;
			END LOOP;

		-- ... if not ...
		ELSE
			-- ... just move the call number to the target record
			UPDATE	asset.call_number
			  SET	record = target_record
			  WHERE	id = source_cn.id;
		END IF;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find T holds targeting the source record ...
	FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP

		-- ... and move them to the target record
		UPDATE	action.hold_request
		  SET	target = target_record
		  WHERE	id = hold.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find serial records targeting the source record ...
	FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
		-- ... and move them to the target record
		UPDATE	serial.record_entry
		  SET	record = target_record
		  WHERE	id = ser_rec.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find serial subscriptions targeting the source record ...
	FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
		-- ... and move them to the target record
		UPDATE	serial.subscription
		  SET	record_entry = target_record
		  WHERE	id = ser_sub.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find booking resource types targeting the source record ...
	FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
		-- ... and move them to the target record
		UPDATE	booking.resource_type
		  SET	record = target_record
		  WHERE	id = booking.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find acq lineitems targeting the source record ...
	FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
		-- ... and move them to the target record
		UPDATE	acq.lineitem
		  SET	eg_bib_id = target_record
		  WHERE	id = acq_lineitem.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find acq user purchase requests targeting the source record ...
	FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
		-- ... and move them to the target record
		UPDATE	acq.user_request
		  SET	eg_bib = target_record
		  WHERE	id = acq_request.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find parts attached to the source ...
	FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP

		SELECT	INTO target_part *
		  FROM	biblio.monograph_part
		  WHERE	label = source_part.label
			AND record = target_record;

		-- ... and if there's a conflicting one on the target ...
		IF FOUND THEN

			-- ... move the copy-part maps to that, and ...
			UPDATE	asset.copy_part_map
			  SET	part = target_part.id
			  WHERE	part = source_part.id;

			-- ... move P holds to the move-target part
			FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
		
				UPDATE	action.hold_request
				  SET	target = target_part.id
				  WHERE	id = hold.id;
		
				moved_objects := moved_objects + 1;
			END LOOP;

		-- ... if not ...
		ELSE
			-- ... just move the part to the target record
			UPDATE	biblio.monograph_part
			  SET	record = target_record
			  WHERE	id = source_part.id;
		END IF;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find multi_home items attached to the source ...
	FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
		-- ... and move them to the target record
		UPDATE	biblio.peer_bib_copy_map
		  SET	peer_record = target_record
		  WHERE	id = multi_home.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- And delete mappings where the item's home bib was merged with the peer bib
	DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
		SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
		FROM asset.copy WHERE id = target_copy
	);

    -- Finally, "delete" the source record
    DELETE FROM biblio.record_entry WHERE id = source_record;

	-- That's all, folks!
	RETURN moved_objects;
END;

Function: asset.metarecord_copy_count(transcendant integer, unshadow bigint, available boolean)

Returns: SET OF record

Language: PLPGSQL

BEGIN
    IF staff IS TRUE THEN
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
        ELSE
            RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
        END IF;
    ELSE
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
        ELSE
            RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
        END IF;
    END IF;

    RETURN;
END;

Function: asset.normalize_affix_sortkey()

Returns: trigger

Language: PLPGSQL

BEGIN
    NEW.label_sortkey := REGEXP_REPLACE(
        evergreen.lpad_number_substrings(
            naco_normalize(NEW.label),
            '0',
            10
        ),
        E'\\s+',
        '',
        'g'
    );
    RETURN NEW;
END;

Function: asset.opac_lasso_metarecord_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.copy_id)
                JOIN metabib.metarecord_source_map m ON (m.source = av.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;   
                
    RETURN;     
END;            

Function: asset.opac_lasso_record_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.copy_id)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;   
                
    RETURN;     
END;            

Function: asset.opac_ou_metarecord_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM  
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.copy_id)
                JOIN metabib.metarecord_source_map m ON (m.source = av.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;

Function: asset.opac_ou_record_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM  
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.copy_id)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;

Function: asset.record_copy_count(transcendant integer, unshadow bigint, available boolean)

Returns: SET OF record

Language: PLPGSQL

BEGIN
    IF staff IS TRUE THEN
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
        ELSE
            RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
        END IF;
    ELSE
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
        ELSE
            RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
        END IF;
    END IF;

    RETURN;
END;

Function: asset.refresh_opac_visible_copies_mat_view()

Returns: void

Language: SQL

Rebuild the copy OPAC visibility cache. Useful during migrations.


    TRUNCATE TABLE asset.opac_visible_copies;

    INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
    SELECT  cp.id, cp.circ_lib, cn.record
    FROM  asset.copy cp
        JOIN asset.call_number cn ON (cn.id = cp.call_number)
        JOIN actor.org_unit a ON (cp.circ_lib = a.id)
        JOIN asset.copy_location cl ON (cp.location = cl.id)
        JOIN config.copy_status cs ON (cp.status = cs.id)
        JOIN biblio.record_entry b ON (cn.record = b.id)
    WHERE NOT cp.deleted
        AND NOT cn.deleted
        AND NOT b.deleted
        AND cs.opac_visible
        AND cl.opac_visible
        AND cp.opac_visible
        AND a.opac_visible
            UNION
    SELECT  cp.id, cp.circ_lib, pbcm.peer_record AS record
    FROM  asset.copy cp
        JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
        JOIN actor.org_unit a ON (cp.circ_lib = a.id)
        JOIN asset.copy_location cl ON (cp.location = cl.id)
        JOIN config.copy_status cs ON (cp.status = cs.id)
    WHERE NOT cp.deleted
        AND cs.opac_visible
        AND cl.opac_visible
        AND cp.opac_visible
        AND a.opac_visible;


Function: asset.staff_lasso_metarecord_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( cp.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
                JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
                JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;

Function: asset.staff_lasso_record_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
                JOIN asset.copy_location cl ON (cp.location = cl.id)
                JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;

Function: asset.staff_ou_metarecord_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE         
    ans RECORD; 
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( cp.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
                JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
                JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;

Function: asset.staff_ou_record_copy_count(transcendant integer, unshadow bigint)

Returns: SET OF record

Language: PLPGSQL

DECLARE         
    ans RECORD; 
    trans INT;
BEGIN           
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
                JOIN asset.copy_location cl ON (cp.location = cl.id)
                JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;

Function: asset.stat_cat_check()

Returns: trigger

Language: PLPGSQL

DECLARE
    sipfield asset.stat_cat_sip_fields%ROWTYPE;
    use_count INT;
BEGIN
    IF NEW.sip_field IS NOT NULL THEN
        SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
        IF sipfield.one_only THEN
            SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
            IF use_count > 0 THEN
                RAISE EXCEPTION 'Sip field cannot be used twice';
            END IF;
        END IF;
    END IF;
    RETURN NEW;
END;

Schema auditor


Table: auditor.acq_invoice_entry_history

auditor.acq_invoice_entry_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id integer NOT NULL
invoice integer NOT NULL
purchase_order integer
lineitem integer
inv_item_count integer NOT NULL
phys_item_count integer
note text
billed_per_item boolean
cost_billed numeric(8,2)
actual_cost numeric(8,2)
amount_paid numeric(8,2)

Index - Schema auditor


View: auditor.acq_invoice_entry_lifecycle

auditor.acq_invoice_entry_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id integer
invoice integer
purchase_order integer
lineitem integer
inv_item_count integer
phys_item_count integer
note text
billed_per_item boolean
cost_billed numeric(8,2)
actual_cost numeric(8,2)
amount_paid numeric(8,2)
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, invoice_entry.id
, invoice_entry.invoice
, invoice_entry.purchase_order
, invoice_entry.lineitem
, invoice_entry.inv_item_count
, invoice_entry.phys_item_count
, invoice_entry.note
, invoice_entry.billed_per_item
, invoice_entry.cost_billed
, invoice_entry.actual_cost
, invoice_entry.amount_paid 
FROM acq.invoice_entry 
UNION ALLSELECT acq_invoice_entry_history.audit_id
, acq_invoice_entry_history.audit_time
, acq_invoice_entry_history.audit_action
, acq_invoice_entry_history.audit_user
, acq_invoice_entry_history.audit_ws
, acq_invoice_entry_history.id
, acq_invoice_entry_history.invoice
, acq_invoice_entry_history.purchase_order
, acq_invoice_entry_history.lineitem
, acq_invoice_entry_history.inv_item_count
, acq_invoice_entry_history.phys_item_count
, acq_invoice_entry_history.note
, acq_invoice_entry_history.billed_per_item
, acq_invoice_entry_history.cost_billed
, acq_invoice_entry_history.actual_cost
, acq_invoice_entry_history.amount_paid 
FROM auditor.acq_invoice_entry_history;

Index - Schema auditor


Table: auditor.acq_invoice_history

auditor.acq_invoice_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id integer NOT NULL
receiver integer NOT NULL
provider integer NOT NULL
shipper integer NOT NULL
recv_date timestamp with time zone NOT NULL
recv_method text NOT NULL
inv_type text
inv_ident text NOT NULL
payment_auth text
payment_method text
note text
complete boolean NOT NULL

Index - Schema auditor


Table: auditor.acq_invoice_item_history

auditor.acq_invoice_item_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id integer NOT NULL
invoice integer NOT NULL
purchase_order integer
fund_debit integer
inv_item_type text NOT NULL
title text
author text
note text
cost_billed numeric(8,2)
actual_cost numeric(8,2)
fund integer
amount_paid numeric(8,2)
po_item integer
target bigint

Index - Schema auditor


View: auditor.acq_invoice_item_lifecycle

auditor.acq_invoice_item_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id integer
invoice integer
purchase_order integer
fund_debit integer
inv_item_type text
title text
author text
note text
cost_billed numeric(8,2)
actual_cost numeric(8,2)
fund integer
amount_paid numeric(8,2)
po_item integer
target bigint
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, invoice_item.id
, invoice_item.invoice
, invoice_item.purchase_order
, invoice_item.fund_debit
, invoice_item.inv_item_type
, invoice_item.title
, invoice_item.author
, invoice_item.note
, invoice_item.cost_billed
, invoice_item.actual_cost
, invoice_item.fund
, invoice_item.amount_paid
, invoice_item.po_item
, invoice_item.target 
FROM acq.invoice_item 
UNION ALLSELECT acq_invoice_item_history.audit_id
, acq_invoice_item_history.audit_time
, acq_invoice_item_history.audit_action
, acq_invoice_item_history.audit_user
, acq_invoice_item_history.audit_ws
, acq_invoice_item_history.id
, acq_invoice_item_history.invoice
, acq_invoice_item_history.purchase_order
, acq_invoice_item_history.fund_debit
, acq_invoice_item_history.inv_item_type
, acq_invoice_item_history.title
, acq_invoice_item_history.author
, acq_invoice_item_history.note
, acq_invoice_item_history.cost_billed
, acq_invoice_item_history.actual_cost
, acq_invoice_item_history.fund
, acq_invoice_item_history.amount_paid
, acq_invoice_item_history.po_item
, acq_invoice_item_history.target 
FROM auditor.acq_invoice_item_history;

Index - Schema auditor


View: auditor.acq_invoice_lifecycle

auditor.acq_invoice_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id integer
receiver integer
provider integer
shipper integer
recv_date timestamp with time zone
recv_method text
inv_type text
inv_ident text
payment_auth text
payment_method text
note text
complete boolean
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, invoice.id
, invoice.receiver
, invoice.provider
, invoice.shipper
, invoice.recv_date
, invoice.recv_method
, invoice.inv_type
, invoice.inv_ident
, invoice.payment_auth
, invoice.payment_method
, invoice.note
, invoice.complete 
FROM acq.invoice 
UNION ALLSELECT acq_invoice_history.audit_id
, acq_invoice_history.audit_time
, acq_invoice_history.audit_action
, acq_invoice_history.audit_user
, acq_invoice_history.audit_ws
, acq_invoice_history.id
, acq_invoice_history.receiver
, acq_invoice_history.provider
, acq_invoice_history.shipper
, acq_invoice_history.recv_date
, acq_invoice_history.recv_method
, acq_invoice_history.inv_type
, acq_invoice_history.inv_ident
, acq_invoice_history.payment_auth
, acq_invoice_history.payment_method
, acq_invoice_history.note
, acq_invoice_history.complete 
FROM auditor.acq_invoice_history;

Index - Schema auditor


Table: auditor.actor_org_unit_history

auditor.actor_org_unit_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id integer NOT NULL
parent_ou integer
ou_type integer NOT NULL
ill_address integer
holds_address integer
mailing_address integer
billing_address integer
shortname text NOT NULL
name text NOT NULL
email text
phone text
opac_visible boolean NOT NULL
fiscal_calendar integer NOT NULL

Index - Schema auditor


View: auditor.actor_org_unit_lifecycle

auditor.actor_org_unit_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id integer
parent_ou integer
ou_type integer
ill_address integer
holds_address integer
mailing_address integer
billing_address integer
shortname text
name text
email text
phone text
opac_visible boolean
fiscal_calendar integer
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, org_unit.id
, org_unit.parent_ou
, org_unit.ou_type
, org_unit.ill_address
, org_unit.holds_address
, org_unit.mailing_address
, org_unit.billing_address
, org_unit.shortname
, org_unit.name
, org_unit.email
, org_unit.phone
, org_unit.opac_visible
, org_unit.fiscal_calendar 
FROM actor.org_unit 
UNION ALLSELECT actor_org_unit_history.audit_id
, actor_org_unit_history.audit_time
, actor_org_unit_history.audit_action
, actor_org_unit_history.audit_user
, actor_org_unit_history.audit_ws
, actor_org_unit_history.id
, actor_org_unit_history.parent_ou
, actor_org_unit_history.ou_type
, actor_org_unit_history.ill_address
, actor_org_unit_history.holds_address
, actor_org_unit_history.mailing_address
, actor_org_unit_history.billing_address
, actor_org_unit_history.shortname
, actor_org_unit_history.name
, actor_org_unit_history.email
, actor_org_unit_history.phone
, actor_org_unit_history.opac_visible
, actor_org_unit_history.fiscal_calendar 
FROM auditor.actor_org_unit_history;

Index - Schema auditor


Table: auditor.actor_usr_address_history

auditor.actor_usr_address_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id integer NOT NULL
valid boolean NOT NULL
within_city_limits boolean NOT NULL
address_type text NOT NULL
usr integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL
pending boolean NOT NULL
replaces integer
aud_actor_usr_address_hist_id_idx id

Index - Schema auditor


View: auditor.actor_usr_address_lifecycle

auditor.actor_usr_address_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id integer
valid boolean
within_city_limits boolean
address_type text
usr integer
street1 text
street2 text
city text
county text
state text
country text
post_code text
pending boolean
replaces integer
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, usr_address.id
, usr_address.valid
, usr_address.within_city_limits
, usr_address.address_type
, usr_address.usr
, usr_address.street1
, usr_address.street2
, usr_address.city
, usr_address.county
, usr_address.state
, usr_address.country
, usr_address.post_code
, usr_address.pending
, usr_address.replaces 
FROM actor.usr_address 
UNION ALLSELECT actor_usr_address_history.audit_id
, actor_usr_address_history.audit_time
, actor_usr_address_history.audit_action
, actor_usr_address_history.audit_user
, actor_usr_address_history.audit_ws
, actor_usr_address_history.id
, actor_usr_address_history.valid
, actor_usr_address_history.within_city_limits
, actor_usr_address_history.address_type
, actor_usr_address_history.usr
, actor_usr_address_history.street1
, actor_usr_address_history.street2
, actor_usr_address_history.city
, actor_usr_address_history.county
, actor_usr_address_history.state
, actor_usr_address_history.country
, actor_usr_address_history.post_code
, actor_usr_address_history.pending
, actor_usr_address_history.replaces 
FROM auditor.actor_usr_address_history;

Index - Schema auditor


Table: auditor.actor_usr_history

auditor.actor_usr_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id integer NOT NULL
card integer
profile integer NOT NULL
usrname text NOT NULL
email text
passwd text NOT NULL
standing integer NOT NULL
ident_type integer NOT NULL
ident_value text
ident_type2 integer
ident_value2 text
net_access_level integer NOT NULL
photo_url text
prefix text
first_given_name text NOT NULL
second_given_name text
family_name text NOT NULL
suffix text
alias text
day_phone text
evening_phone text
other_phone text
mailing_address integer
billing_address integer
home_ou integer NOT NULL
dob timestamp with time zone
active boolean NOT NULL
master_account boolean NOT NULL
super_user boolean NOT NULL
barred boolean NOT NULL
deleted boolean NOT NULL
juvenile boolean NOT NULL
usrgroup integer NOT NULL
claims_returned_count integer NOT NULL
credit_forward_balance numeric(6,2) NOT NULL
last_xact_id text NOT NULL
alert_message text
create_date timestamp with time zone NOT NULL
expire_date timestamp with time zone NOT NULL
claims_never_checked_out_count integer NOT NULL
last_update_time timestamp with time zone
aud_actor_usr_hist_id_idx id

Index - Schema auditor


View: auditor.actor_usr_lifecycle

auditor.actor_usr_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id integer
card integer
profile integer
usrname text
email text
passwd text
standing integer
ident_type integer
ident_value text
ident_type2 integer
ident_value2 text
net_access_level integer
photo_url text
prefix text
first_given_name text
second_given_name text
family_name text
suffix text
alias text
day_phone text
evening_phone text
other_phone text
mailing_address integer
billing_address integer
home_ou integer
dob timestamp with time zone
active boolean
master_account boolean
super_user boolean
barred boolean
deleted boolean
juvenile boolean
usrgroup integer
claims_returned_count integer
credit_forward_balance numeric(6,2)
last_xact_id text
alert_message text
create_date timestamp with time zone
expire_date timestamp with time zone
claims_never_checked_out_count integer
last_update_time timestamp with time zone
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, usr.id
, usr.card
, usr.profile
, usr.usrname
, usr.email
, usr.passwd
, usr.standing
, usr.ident_type
, usr.ident_value
, usr.ident_type2
, usr.ident_value2
, usr.net_access_level
, usr.photo_url
, usr.prefix
, usr.first_given_name
, usr.second_given_name
, usr.family_name
, usr.suffix
, usr.alias
, usr.day_phone
, usr.evening_phone
, usr.other_phone
, usr.mailing_address
, usr.billing_address
, usr.home_ou
, usr.dob
, usr.active
, usr.master_account
, usr.super_user
, usr.barred
, usr.deleted
, usr.juvenile
, usr.usrgroup
, usr.claims_returned_count
, usr.credit_forward_balance
, usr.last_xact_id
, usr.alert_message
, usr.create_date
, usr.expire_date
, usr.claims_never_checked_out_count
, usr.last_update_time 
FROM actor.usr 
UNION ALLSELECT actor_usr_history.audit_id
, actor_usr_history.audit_time
, actor_usr_history.audit_action
, actor_usr_history.audit_user
, actor_usr_history.audit_ws
, actor_usr_history.id
, actor_usr_history.card
, actor_usr_history.profile
, actor_usr_history.usrname
, actor_usr_history.email
, actor_usr_history.passwd
, actor_usr_history.standing
, actor_usr_history.ident_type
, actor_usr_history.ident_value
, actor_usr_history.ident_type2
, actor_usr_history.ident_value2
, actor_usr_history.net_access_level
, actor_usr_history.photo_url
, actor_usr_history.prefix
, actor_usr_history.first_given_name
, actor_usr_history.second_given_name
, actor_usr_history.family_name
, actor_usr_history.suffix
, actor_usr_history.alias
, actor_usr_history.day_phone
, actor_usr_history.evening_phone
, actor_usr_history.other_phone
, actor_usr_history.mailing_address
, actor_usr_history.billing_address
, actor_usr_history.home_ou
, actor_usr_history.dob
, actor_usr_history.active
, actor_usr_history.master_account
, actor_usr_history.super_user
, actor_usr_history.barred
, actor_usr_history.deleted
, actor_usr_history.juvenile
, actor_usr_history.usrgroup
, actor_usr_history.claims_returned_count
, actor_usr_history.credit_forward_balance
, actor_usr_history.last_xact_id
, actor_usr_history.alert_message
, actor_usr_history.create_date
, actor_usr_history.expire_date
, actor_usr_history.claims_never_checked_out_count
, actor_usr_history.last_update_time 
FROM auditor.actor_usr_history;

Index - Schema auditor


Table: auditor.asset_call_number_history

auditor.asset_call_number_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id bigint NOT NULL
creator bigint NOT NULL
create_date timestamp with time zone
editor bigint NOT NULL
edit_date timestamp with time zone
record bigint NOT NULL
owning_lib integer NOT NULL
label text NOT NULL
deleted boolean NOT NULL
prefix integer NOT NULL
suffix integer NOT NULL
label_class bigint NOT NULL
label_sortkey text
aud_asset_cn_hist_creator_idx creator aud_asset_cn_hist_editor_idx editor

Index - Schema auditor


View: auditor.asset_call_number_lifecycle

auditor.asset_call_number_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id bigint
creator bigint
create_date timestamp with time zone
editor bigint
edit_date timestamp with time zone
record bigint
owning_lib integer
label text
deleted boolean
prefix integer
suffix integer
label_class bigint
label_sortkey text
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, call_number.id
, call_number.creator
, call_number.create_date
, call_number.editor
, call_number.edit_date
, call_number.record
, call_number.owning_lib
, call_number.label
, call_number.deleted
, call_number.prefix
, call_number.suffix
, call_number.label_class
, call_number.label_sortkey 
FROM asset.call_number 
UNION ALLSELECT asset_call_number_history.audit_id
, asset_call_number_history.audit_time
, asset_call_number_history.audit_action
, asset_call_number_history.audit_user
, asset_call_number_history.audit_ws
, asset_call_number_history.id
, asset_call_number_history.creator
, asset_call_number_history.create_date
, asset_call_number_history.editor
, asset_call_number_history.edit_date
, asset_call_number_history.record
, asset_call_number_history.owning_lib
, asset_call_number_history.label
, asset_call_number_history.deleted
, asset_call_number_history.prefix
, asset_call_number_history.suffix
, asset_call_number_history.label_class
, asset_call_number_history.label_sortkey 
FROM auditor.asset_call_number_history;

Index - Schema auditor


Table: auditor.asset_copy_history

auditor.asset_copy_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id bigint NOT NULL
circ_lib integer NOT NULL
creator bigint NOT NULL
call_number bigint NOT NULL
editor bigint NOT NULL
create_date timestamp with time zone
edit_date timestamp with time zone
copy_number integer
status integer NOT NULL
location integer NOT NULL
loan_duration integer NOT NULL
fine_level integer NOT NULL
age_protect integer
circulate boolean NOT NULL
deposit boolean NOT NULL
ref boolean NOT NULL
holdable boolean NOT NULL
deposit_amount numeric(6,2) NOT NULL
price numeric(8,2)
barcode text NOT NULL
circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean NOT NULL
deleted boolean NOT NULL
floating boolean NOT NULL
dummy_isbn text
status_changed_time timestamp with time zone
active_date timestamp with time zone
mint_condition boolean NOT NULL
cost numeric(8,2)
aud_asset_cp_hist_creator_idx creator aud_asset_cp_hist_editor_idx editor

Index - Schema auditor


View: auditor.asset_copy_lifecycle

auditor.asset_copy_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id bigint
circ_lib integer
creator bigint
call_number bigint
editor bigint
create_date timestamp with time zone
edit_date timestamp with time zone
copy_number integer
status integer
location integer
loan_duration integer
fine_level integer
age_protect integer
circulate boolean
deposit boolean
ref boolean
holdable boolean
deposit_amount numeric(6,2)
price numeric(8,2)
barcode text
circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean
deleted boolean
floating boolean
dummy_isbn text
status_changed_time timestamp with time zone
active_date timestamp with time zone
mint_condition boolean
cost numeric(8,2)
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, copy.id
, copy.circ_lib
, copy.creator
, copy.call_number
, copy.editor
, copy.create_date
, copy.edit_date
, copy.copy_number
, copy.status
, copy.location
, copy.loan_duration
, copy.fine_level
, copy.age_protect
, copy.circulate
, copy.deposit
, copy.ref
, copy.holdable
, copy.deposit_amount
, copy.price
, copy.barcode
, copy.circ_modifier
, copy.circ_as_type
, copy.dummy_title
, copy.dummy_author
, copy.alert_message
, copy.opac_visible
, copy.deleted
, copy.floating
, copy.dummy_isbn
, copy.status_changed_time
, copy.active_date
, copy.mint_condition
, copy.cost 
FROM asset.copy 
UNION ALLSELECT asset_copy_history.audit_id
, asset_copy_history.audit_time
, asset_copy_history.audit_action
, asset_copy_history.audit_user
, asset_copy_history.audit_ws
, asset_copy_history.id
, asset_copy_history.circ_lib
, asset_copy_history.creator
, asset_copy_history.call_number
, asset_copy_history.editor
, asset_copy_history.create_date
, asset_copy_history.edit_date
, asset_copy_history.copy_number
, asset_copy_history.status
, asset_copy_history.location
, asset_copy_history.loan_duration
, asset_copy_history.fine_level
, asset_copy_history.age_protect
, asset_copy_history.circulate
, asset_copy_history.deposit
, asset_copy_history.ref
, asset_copy_history.holdable
, asset_copy_history.deposit_amount
, asset_copy_history.price
, asset_copy_history.barcode
, asset_copy_history.circ_modifier
, asset_copy_history.circ_as_type
, asset_copy_history.dummy_title
, asset_copy_history.dummy_author
, asset_copy_history.alert_message
, asset_copy_history.opac_visible
, asset_copy_history.deleted
, asset_copy_history.floating
, asset_copy_history.dummy_isbn
, asset_copy_history.status_changed_time
, asset_copy_history.active_date
, asset_copy_history.mint_condition
, asset_copy_history.cost 
FROM auditor.asset_copy_history;

Index - Schema auditor


Table: auditor.biblio_record_entry_history

auditor.biblio_record_entry_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id bigint NOT NULL
creator integer NOT NULL
editor integer NOT NULL
source integer
quality integer
create_date timestamp with time zone NOT NULL
edit_date timestamp with time zone NOT NULL
active boolean NOT NULL
deleted boolean NOT NULL
fingerprint text
tcn_source text NOT NULL
tcn_value text NOT NULL
marc text NOT NULL
last_xact_id text NOT NULL
owner integer
share_depth integer
aud_bib_rec_entry_hist_creator_idx creator aud_bib_rec_entry_hist_editor_idx editor

Index - Schema auditor


View: auditor.biblio_record_entry_lifecycle

auditor.biblio_record_entry_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id bigint
creator integer
editor integer
source integer
quality integer
create_date timestamp with time zone
edit_date timestamp with time zone
active boolean
deleted boolean
fingerprint text
tcn_source text
tcn_value text
marc text
last_xact_id text
owner integer
share_depth integer
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, record_entry.id
, record_entry.creator
, record_entry.editor
, record_entry.source
, record_entry.quality
, record_entry.create_date
, record_entry.edit_date
, record_entry.active
, record_entry.deleted
, record_entry.fingerprint
, record_entry.tcn_source
, record_entry.tcn_value
, record_entry.marc
, record_entry.last_xact_id
, record_entry.owner
, record_entry.share_depth 
FROM biblio.record_entry 
UNION ALLSELECT biblio_record_entry_history.audit_id
, biblio_record_entry_history.audit_time
, biblio_record_entry_history.audit_action
, biblio_record_entry_history.audit_user
, biblio_record_entry_history.audit_ws
, biblio_record_entry_history.id
, biblio_record_entry_history.creator
, biblio_record_entry_history.editor
, biblio_record_entry_history.source
, biblio_record_entry_history.quality
, biblio_record_entry_history.create_date
, biblio_record_entry_history.edit_date
, biblio_record_entry_history.active
, biblio_record_entry_history.deleted
, biblio_record_entry_history.fingerprint
, biblio_record_entry_history.tcn_source
, biblio_record_entry_history.tcn_value
, biblio_record_entry_history.marc
, biblio_record_entry_history.last_xact_id
, biblio_record_entry_history.owner
, biblio_record_entry_history.share_depth 
FROM auditor.biblio_record_entry_history;

Index - Schema auditor


Table: auditor.serial_unit_history

auditor.serial_unit_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
audit_user integer
audit_ws integer
id bigint NOT NULL
circ_lib integer NOT NULL
creator bigint NOT NULL
call_number bigint NOT NULL
editor bigint NOT NULL
create_date timestamp with time zone
edit_date timestamp with time zone
copy_number integer
status integer NOT NULL
location integer NOT NULL
loan_duration integer NOT NULL
fine_level integer NOT NULL
age_protect integer
circulate boolean NOT NULL
deposit boolean NOT NULL
ref boolean NOT NULL
holdable boolean NOT NULL
deposit_amount numeric(6,2) NOT NULL
price numeric(8,2)
barcode text NOT NULL
circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean NOT NULL
deleted boolean NOT NULL
floating boolean NOT NULL
dummy_isbn text
status_changed_time timestamp with time zone
active_date timestamp with time zone
mint_condition boolean NOT NULL
cost numeric(8,2)
sort_key text
detailed_contents text NOT NULL
summary_contents text NOT NULL
aud_serial_unit_hist_creator_idx creator aud_serial_unit_hist_editor_idx editor

Index - Schema auditor


View: auditor.serial_unit_lifecycle

auditor.serial_unit_lifecycle Structure
F-Key Name Type Description
audit_id bigint
audit_time timestamp with time zone
audit_action text
audit_user integer
audit_ws integer
id bigint
circ_lib integer
creator bigint
call_number bigint
editor bigint
create_date timestamp with time zone
edit_date timestamp with time zone
copy_number integer
status integer
location integer
loan_duration integer
fine_level integer
age_protect integer
circulate boolean
deposit boolean
ref boolean
holdable boolean
deposit_amount numeric(6,2)
price numeric(8,2)
barcode text
circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean
deleted boolean
floating boolean
dummy_isbn text
status_changed_time timestamp with time zone
active_date timestamp with time zone
mint_condition boolean
cost numeric(8,2)
sort_key text
detailed_contents text
summary_contents text
SELECT (-1) AS audit_id
, now
() AS audit_time
,'-'::text AS audit_action
, (-1) AS audit_user
, (-1) AS audit_ws
, unit.id
, unit.circ_lib
, unit.creator
, unit.call_number
, unit.editor
, unit.create_date
, unit.edit_date
, unit.copy_number
, unit.status
, unit.location
, unit.loan_duration
, unit.fine_level
, unit.age_protect
, unit.circulate
, unit.deposit
, unit.ref
, unit.holdable
, unit.deposit_amount
, unit.price
, unit.barcode
, unit.circ_modifier
, unit.circ_as_type
, unit.dummy_title
, unit.dummy_author
, unit.alert_message
, unit.opac_visible
, unit.deleted
, unit.floating
, unit.dummy_isbn
, unit.status_changed_time
, unit.active_date
, unit.mint_condition
, unit.cost
, unit.sort_key
, unit.detailed_contents
, unit.summary_contents 
FROM serial.unit 
UNION ALLSELECT serial_unit_history.audit_id
, serial_unit_history.audit_time
, serial_unit_history.audit_action
, serial_unit_history.audit_user
, serial_unit_history.audit_ws
, serial_unit_history.id
, serial_unit_history.circ_lib
, serial_unit_history.creator
, serial_unit_history.call_number
, serial_unit_history.editor
, serial_unit_history.create_date
, serial_unit_history.edit_date
, serial_unit_history.copy_number
, serial_unit_history.status
, serial_unit_history.location
, serial_unit_history.loan_duration
, serial_unit_history.fine_level
, serial_unit_history.age_protect
, serial_unit_history.circulate
, serial_unit_history.deposit
, serial_unit_history.ref
, serial_unit_history.holdable
, serial_unit_history.deposit_amount
, serial_unit_history.price
, serial_unit_history.barcode
, serial_unit_history.circ_modifier
, serial_unit_history.circ_as_type
, serial_unit_history.dummy_title
, serial_unit_history.dummy_author
, serial_unit_history.alert_message
, serial_unit_history.opac_visible
, serial_unit_history.deleted
, serial_unit_history.floating
, serial_unit_history.dummy_isbn
, serial_unit_history.status_changed_time
, serial_unit_history.active_date
, serial_unit_history.mint_condition
, serial_unit_history.cost
, serial_unit_history.sort_key
, serial_unit_history.detailed_contents
, serial_unit_history.summary_contents 
FROM auditor.serial_unit_history;

Index - Schema auditor


Function: auditor.audit_acq_invoice_entry_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.acq_invoice_entry_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, invoice, purchase_order, lineitem, inv_item_count, phys_item_count, note, billed_per_item, cost_billed, actual_cost, amount_paid )
                SELECT  nextval('auditor.acq_invoice_entry_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.invoice, OLD.purchase_order, OLD.lineitem, OLD.inv_item_count, OLD.phys_item_count, OLD.note, OLD.billed_per_item, OLD.cost_billed, OLD.actual_cost, OLD.amount_paid
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_acq_invoice_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.acq_invoice_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, receiver, provider, shipper, recv_date, recv_method, inv_type, inv_ident, payment_auth, payment_method, note, complete )
                SELECT  nextval('auditor.acq_invoice_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.receiver, OLD.provider, OLD.shipper, OLD.recv_date, OLD.recv_method, OLD.inv_type, OLD.inv_ident, OLD.payment_auth, OLD.payment_method, OLD.note, OLD.complete
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_acq_invoice_item_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.acq_invoice_item_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, invoice, purchase_order, fund_debit, inv_item_type, title, author, note, cost_billed, actual_cost, fund, amount_paid, po_item, target )
                SELECT  nextval('auditor.acq_invoice_item_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.invoice, OLD.purchase_order, OLD.fund_debit, OLD.inv_item_type, OLD.title, OLD.author, OLD.note, OLD.cost_billed, OLD.actual_cost, OLD.fund, OLD.amount_paid, OLD.po_item, OLD.target
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_actor_org_unit_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.actor_org_unit_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, parent_ou, ou_type, ill_address, holds_address, mailing_address, billing_address, shortname, name, email, phone, opac_visible, fiscal_calendar )
                SELECT  nextval('auditor.actor_org_unit_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.parent_ou, OLD.ou_type, OLD.ill_address, OLD.holds_address, OLD.mailing_address, OLD.billing_address, OLD.shortname, OLD.name, OLD.email, OLD.phone, OLD.opac_visible, OLD.fiscal_calendar
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_actor_usr_address_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.actor_usr_address_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, valid, within_city_limits, address_type, usr, street1, street2, city, county, state, country, post_code, pending, replaces )
                SELECT  nextval('auditor.actor_usr_address_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.valid, OLD.within_city_limits, OLD.address_type, OLD.usr, OLD.street1, OLD.street2, OLD.city, OLD.county, OLD.state, OLD.country, OLD.post_code, OLD.pending, OLD.replaces
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_actor_usr_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.actor_usr_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, card, profile, usrname, email, passwd, standing, ident_type, ident_value, ident_type2, ident_value2, net_access_level, photo_url, prefix, first_given_name, second_given_name, family_name, suffix, alias, day_phone, evening_phone, other_phone, mailing_address, billing_address, home_ou, dob, active, master_account, super_user, barred, deleted, juvenile, usrgroup, claims_returned_count, credit_forward_balance, last_xact_id, alert_message, create_date, expire_date, claims_never_checked_out_count, last_update_time )
                SELECT  nextval('auditor.actor_usr_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.card, OLD.profile, OLD.usrname, OLD.email, OLD.passwd, OLD.standing, OLD.ident_type, OLD.ident_value, OLD.ident_type2, OLD.ident_value2, OLD.net_access_level, OLD.photo_url, OLD.prefix, OLD.first_given_name, OLD.second_given_name, OLD.family_name, OLD.suffix, OLD.alias, OLD.day_phone, OLD.evening_phone, OLD.other_phone, OLD.mailing_address, OLD.billing_address, OLD.home_ou, OLD.dob, OLD.active, OLD.master_account, OLD.super_user, OLD.barred, OLD.deleted, OLD.juvenile, OLD.usrgroup, OLD.claims_returned_count, OLD.credit_forward_balance, OLD.last_xact_id, OLD.alert_message, OLD.create_date, OLD.expire_date, OLD.claims_never_checked_out_count, OLD.last_update_time
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_asset_call_number_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.asset_call_number_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, creator, create_date, editor, edit_date, record, owning_lib, label, deleted, prefix, suffix, label_class, label_sortkey )
                SELECT  nextval('auditor.asset_call_number_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.creator, OLD.create_date, OLD.editor, OLD.edit_date, OLD.record, OLD.owning_lib, OLD.label, OLD.deleted, OLD.prefix, OLD.suffix, OLD.label_class, OLD.label_sortkey
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_asset_copy_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.asset_copy_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, circ_lib, creator, call_number, editor, create_date, edit_date, copy_number, status, location, loan_duration, fine_level, age_protect, circulate, deposit, ref, holdable, deposit_amount, price, barcode, circ_modifier, circ_as_type, dummy_title, dummy_author, alert_message, opac_visible, deleted, floating, dummy_isbn, status_changed_time, active_date, mint_condition, cost )
                SELECT  nextval('auditor.asset_copy_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.circ_lib, OLD.creator, OLD.call_number, OLD.editor, OLD.create_date, OLD.edit_date, OLD.copy_number, OLD.status, OLD.location, OLD.loan_duration, OLD.fine_level, OLD.age_protect, OLD.circulate, OLD.deposit, OLD.ref, OLD.holdable, OLD.deposit_amount, OLD.price, OLD.barcode, OLD.circ_modifier, OLD.circ_as_type, OLD.dummy_title, OLD.dummy_author, OLD.alert_message, OLD.opac_visible, OLD.deleted, OLD.floating, OLD.dummy_isbn, OLD.status_changed_time, OLD.active_date, OLD.mint_condition, OLD.cost
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_biblio_record_entry_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.biblio_record_entry_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, creator, editor, source, quality, create_date, edit_date, active, deleted, fingerprint, tcn_source, tcn_value, marc, last_xact_id, owner, share_depth )
                SELECT  nextval('auditor.biblio_record_entry_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.creator, OLD.editor, OLD.source, OLD.quality, OLD.create_date, OLD.edit_date, OLD.active, OLD.deleted, OLD.fingerprint, OLD.tcn_source, OLD.tcn_value, OLD.marc, OLD.last_xact_id, OLD.owner, OLD.share_depth
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.audit_serial_unit_func()

Returns: trigger

Language: PLPGSQL

        BEGIN
            INSERT INTO auditor.serial_unit_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, circ_lib, creator, call_number, editor, create_date, edit_date, copy_number, status, location, loan_duration, fine_level, age_protect, circulate, deposit, ref, holdable, deposit_amount, price, barcode, circ_modifier, circ_as_type, dummy_title, dummy_author, alert_message, opac_visible, deleted, floating, dummy_isbn, status_changed_time, active_date, mint_condition, cost, sort_key, detailed_contents, summary_contents )
                SELECT  nextval('auditor.serial_unit_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.circ_lib, OLD.creator, OLD.call_number, OLD.editor, OLD.create_date, OLD.edit_date, OLD.copy_number, OLD.status, OLD.location, OLD.loan_duration, OLD.fine_level, OLD.age_protect, OLD.circulate, OLD.deposit, OLD.ref, OLD.holdable, OLD.deposit_amount, OLD.price, OLD.barcode, OLD.circ_modifier, OLD.circ_as_type, OLD.dummy_title, OLD.dummy_author, OLD.alert_message, OLD.opac_visible, OLD.deleted, OLD.floating, OLD.dummy_isbn, OLD.status_changed_time, OLD.active_date, OLD.mint_condition, OLD.cost, OLD.sort_key, OLD.detailed_contents, OLD.summary_contents
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        

Function: auditor.clear_audit_info()

Returns: void

Language: PLPERLU

    delete($_SHARED{"eg_audit_user"});
    delete($_SHARED{"eg_audit_ws"});

Function: auditor.create_auditor(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    PERFORM auditor.create_auditor_seq(sch, tbl);
    PERFORM auditor.create_auditor_history(sch, tbl);
    PERFORM auditor.create_auditor_func(sch, tbl);
    PERFORM auditor.create_auditor_update_trigger(sch, tbl);
    PERFORM auditor.create_auditor_lifecycle(sch, tbl);
    RETURN TRUE;
END;

Function: auditor.create_auditor_func(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

DECLARE
    column_list TEXT[];
BEGIN
    SELECT INTO column_list array_agg(a.attname)
        FROM pg_catalog.pg_attribute a
            JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;

    EXECUTE $$
        CREATE OR REPLACE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
        RETURNS TRIGGER AS $func$
        BEGIN
            INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, $$
            || array_to_string(column_list, ', ') || $$ )
                SELECT  nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.$$ || array_to_string(column_list, ', OLD.') || $$
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;
        $func$ LANGUAGE 'plpgsql';
    $$;
    RETURN TRUE;
END;

Function: auditor.create_auditor_history(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
            audit_id	BIGINT				PRIMARY KEY,
            audit_time	TIMESTAMP WITH TIME ZONE	NOT NULL,
            audit_action	TEXT				NOT NULL,
            audit_user  INT,
            audit_ws    INT,
            LIKE $$ || sch || $$.$$ || tbl || $$
        );
    $$;
	RETURN TRUE;
END;

Function: auditor.create_auditor_lifecycle(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

DECLARE
    column_list TEXT[];
BEGIN
    SELECT INTO column_list array_agg(a.attname)
        FROM pg_catalog.pg_attribute a
            JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;

    EXECUTE $$
        CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
            SELECT -1 AS audit_id,
                   now() AS audit_time,
                   '-' AS audit_action,
                   -1 AS audit_user,
                   -1 AS audit_ws,
                   $$ || array_to_string(column_list, ', ') || $$
              FROM $$ || sch || $$.$$ || tbl || $$
                UNION ALL
            SELECT audit_id, audit_time, audit_action, audit_user, audit_ws,
            $$ || array_to_string(column_list, ', ') || $$
              FROM auditor.$$ || sch || $$_$$ || tbl || $$_history;
    $$;
    RETURN TRUE;
END;

Function: auditor.create_auditor_seq(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
    $$;
	RETURN TRUE;
END;

Function: auditor.create_auditor_update_trigger(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
    EXECUTE $$
        CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
            AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
            EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();
    $$;
	RETURN TRUE;
END;

Function: auditor.fix_columns()

Returns: void

Language: PLPGSQL

DECLARE
    current_table TEXT = ''; -- Storage for post-loop main table name
    current_audit_table TEXT = ''; -- Storage for post-loop audit table name
    query TEXT = ''; -- Storage for built query
    cr RECORD; -- column record object
    alter_t BOOL = false; -- Has the alter table command been appended yet
    auditor_cores TEXT[] = ARRAY[]::TEXT[]; -- Core auditor function list (filled inside of loop)
    core_column TEXT; -- The current core column we are adding
BEGIN
    FOR cr IN
        WITH audit_tables AS ( -- Basic grab of auditor tables. Anything in the auditor namespace, basically. With oids.
            SELECT c.oid AS audit_oid, c.relname AS audit_table
            FROM pg_catalog.pg_class c
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE relkind='r' AND nspname = 'auditor'
        ),
        table_set AS ( -- Union of auditor tables with their "main" tables. With oids.
            SELECT a.audit_oid, a.audit_table, c.oid AS main_oid, n.nspname as main_namespace, c.relname as main_table
            FROM pg_catalog.pg_class c
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            JOIN audit_tables a ON a.audit_table = n.nspname || '_' || c.relname || '_history'
            WHERE relkind = 'r'
        ),
        column_lists AS ( -- All columns associated with the auditor or main table, grouped by the main table's oid.
            SELECT DISTINCT ON (main_oid, attname) t.main_oid, a.attname
            FROM table_set t
            JOIN pg_catalog.pg_attribute a ON a.attrelid IN (t.main_oid, t.audit_oid)
            WHERE attnum > 0 AND NOT attisdropped
        ),
        column_defs AS ( -- The motherload, every audit table and main table plus column names and defs.
            SELECT audit_table,
                   main_namespace,
                   main_table,
                   a.attname AS main_column, -- These two will be null for columns that have since been deleted, or for auditor core columns
                   pg_catalog.format_type(a.atttypid, a.atttypmod) AS main_column_def,
                   b.attname AS audit_column, -- These two will be null for columns that have since been added
                   pg_catalog.format_type(b.atttypid, b.atttypmod) AS audit_column_def
            FROM table_set t
            JOIN column_lists c USING (main_oid)
            LEFT JOIN pg_catalog.pg_attribute a ON a.attname = c.attname AND a.attrelid = t.main_oid AND a.attnum > 0 AND NOT a.attisdropped
            LEFT JOIN pg_catalog.pg_attribute b ON b.attname = c.attname AND b.attrelid = t.audit_oid AND b.attnum > 0 AND NOT b.attisdropped
        )
        -- Nice sorted output from the above
        SELECT * FROM column_defs WHERE main_column_def IS DISTINCT FROM audit_column_def ORDER BY main_namespace, main_table, main_column, audit_column
    LOOP
        IF current_table <> (cr.main_namespace || '.' || cr.main_table) THEN -- New table?
            FOR core_column IN SELECT DISTINCT unnest(auditor_cores) LOOP -- Update missing core auditor columns
                IF NOT alter_t THEN -- Add ALTER TABLE if we haven't already
                    query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
                    alter_t:=TRUE;
                ELSE
                    query:=query || $$,$$;
                END IF;
                -- Bit of a sneaky bit here. Create audit_id as a bigserial so it gets automatic values and doesn't complain about nulls when becoming a PRIMARY KEY.
                query:=query || $$ ADD COLUMN $$ || CASE WHEN core_column = 'audit_id bigint' THEN $$audit_id bigserial PRIMARY KEY$$ ELSE core_column END;
            END LOOP;
            IF alter_t THEN -- Open alter table = needs a semicolon
                query:=query || $$; $$;
                alter_t:=FALSE;
                IF 'audit_id bigint' = ANY(auditor_cores) THEN -- We added a primary key...
                    -- Fun! Drop the default on audit_id, drop the auto-created sequence, create a new one, and set the current value
                    -- For added fun, we have to execute in chunks due to the parser checking setval/currval arguments at parse time.
                    EXECUTE query;
                    EXECUTE $$ALTER TABLE auditor.$$ || current_audit_table || $$ ALTER COLUMN audit_id DROP DEFAULT; $$ ||
                        $$CREATE SEQUENCE auditor.$$ || current_audit_table || $$_pkey_seq;$$;
                    EXECUTE $$SELECT setval('auditor.$$ || current_audit_table || $$_pkey_seq', currval('auditor.$$ || current_audit_table || $$_audit_id_seq')); $$ ||
                        $$DROP SEQUENCE auditor.$$ || current_audit_table || $$_audit_id_seq;$$;
                    query:=''