User Tools

Site Tools


newdevs:db:schemas:3.10_schma

Index for evergreen



Evergreen Database Schema - 3.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 acq_lineitem_history_queued_record_idx queued_record

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'::integer AS "?column?"
,
    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 ALL
 
SELECT 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'::integer AS "?column?"
,
    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 ALL
 
SELECT 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.fund.id fund integer
config.circ_modifier.code circ_modifier text
collection_code text

 

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
acq.edi_attr_set.id attr_set integer
use_attrs boolean NOT NULL DEFAULT false

Table acq.edi_account Inherits remote_account,

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.edi_attr

acq.edi_attr Structure
F-Key Name Type Description
key text PRIMARY KEY
label text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.edi_attr_set

acq.edi_attr_set 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


Table: acq.edi_attr_set_map

acq.edi_attr_set_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.edi_attr_set.id attr_set integer UNIQUE#1 NOT NULL
acq.edi_attr.key attr text UNIQUE#1 NOT NULL

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, 'DESADV'::text])))
edi_message_account_status_idx account, status edi_message_po_idx purchase_order edi_message_remote_file_idx lowercase(remote_file)

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 NOT NULL
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()
acq.invoice_entry.id invoice_entry integer

Tables referencing this one via Foreign Key Constraints:

fund_debit_invoice_entry_idx invoice_entry

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#2 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 UNIQUE#2 NOT NULL
acq.currency_type.code currency_type text NOT NULL
code text UNIQUE#1 NOT NULL
active boolean NOT NULL DEFAULT true

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
close_date timestamp with time zone
actor.usr.id closed_by integer

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)

Tables referencing this one via Foreign Key Constraints:

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_fund_debit_idx fund_debit 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
blanket boolean NOT NULL DEFAULT false

 

acq.invoice_item_type Constraints
Name Constraint
aiit_not_blanket_and_prorate CHECK (((blanket IS FALSE) OR (prorate IS 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


View: acq.li_state_label

acq.li_state_label Structure
F-Key Name Type Description
id text
label text
SELECT t.id
,
    t.label
   
FROM ( VALUES 
     ('new'::text
           ,oils_i18n_gettext
           ('new'::text
                 ,'New'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
     , ('selector-ready'::text
           ,oils_i18n_gettext
           ('selector-ready'::text
                 ,'Selector-Ready'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
     , ('order-ready'::text
           ,oils_i18n_gettext
           ('order-ready'::text
                 ,'Order-Ready'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
     , ('approved'::text
           ,oils_i18n_gettext
           ('approved'::text
                 ,'Approved'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
     , ('pending-order'::text
           ,oils_i18n_gettext
           ('pending-order'::text
                 ,'Pending-Order'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
     , ('on-order'::text
           ,oils_i18n_gettext
           ('on-order'::text
                 ,'On-Order'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
     , ('received'::text
           ,oils_i18n_gettext
           ('received'::text
                 ,'Received'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
     , ('cancelled'::text
           ,oils_i18n_gettext
           ('cancelled'::text
                 ,'Cancelled'::text
                 ,'jubstlbl'::text
                 ,'label'::text
           )
     )
) t
(id
     , label
);

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_queued_record_idx queued_record 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
order_ident boolean NOT NULL DEFAULT false
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 lineitem_detail_fund_debit_idx fund_debit

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


View: acq.lineitem_summary

acq.lineitem_summary Structure
F-Key Name Type Description
lineitem bigint
item_count bigint
recv_count bigint
cancel_count bigint
delay_count bigint
invoice_count bigint
claim_count bigint
estimated_amount numeric(8,2)
encumbrance_amount numeric(8,2)
paid_amount numeric(8,2)
SELECT li.id AS lineitem
,
    
(
SELECT count
     (lid.id) AS count
           
  FROM acq.lineitem_detail lid
          
 WHERE (lid.lineitem = li.id)
) AS item_count
,
    
(
SELECT count
     (lid.id) AS count
           
  FROM acq.lineitem_detail lid
          
 WHERE (
           (lid.recv_time IS NOT NULL)
         AND (lid.lineitem = li.id)
     )
) AS recv_count
,
    
(
SELECT count
     (lid.id) AS count
           
  FROM (acq.lineitem_detail lid
             
        JOIN acq.cancel_reason acqcr 
          ON (
                 (acqcr.id = lid.cancel_reason)
           )
     )
          
 WHERE (
           (acqcr.keep_debits IS FALSE)
         AND (lid.lineitem = li.id)
     )
) AS cancel_count
,
    
(
SELECT count
     (lid.id) AS count
           
  FROM (acq.lineitem_detail lid
             
        JOIN acq.cancel_reason acqcr 
          ON (
                 (acqcr.id = lid.cancel_reason)
           )
     )
          
 WHERE (
           (acqcr.keep_debits IS TRUE)
         AND (lid.lineitem = li.id)
     )
) AS delay_count
,
    
(
SELECT count
     (lid.id) AS count
           
  FROM (acq.lineitem_detail lid
             
        JOIN acq.fund_debit debit 
          ON (
                 (lid.fund_debit = debit.id)
           )
     )
          
 WHERE (
           (NOT debit.encumbrance)
         AND (lid.lineitem = li.id)
     )
) AS invoice_count
,
    
(
SELECT count
     (DISTINCT lid.id) AS count
           
  FROM (acq.lineitem_detail lid
             
        JOIN acq.claim claim 
          ON (
                 (claim.lineitem_detail = lid.id)
           )
     )
          
 WHERE (lid.lineitem = li.id)
) AS claim_count
,
    
(
SELECT (
           (
                 (count
                       (lid.id)
                 )::numeric * li.estimated_unit_price
           )
     )::numeric
     (8
           ,2
     ) AS "numeric"
           
  FROM acq.lineitem_detail lid
          
 WHERE (
           (lid.cancel_reason IS NULL)
         AND (lid.lineitem = li.id)
     )
) AS estimated_amount
,
    
(
SELECT (sum
           (debit.amount)
     )::numeric
     (8
           ,2
     ) AS sum
           
  FROM (acq.lineitem_detail lid
             
        JOIN acq.fund_debit debit 
          ON (
                 (lid.fund_debit = debit.id)
           )
     )
          
 WHERE (debit.encumbrance 
         AND (lid.lineitem = li.id)
     )
) AS encumbrance_amount
,
    
(
SELECT (sum
           (debit.amount)
     )::numeric
     (8
           ,2
     ) AS sum
           
  FROM (acq.lineitem_detail lid
             
        JOIN acq.fund_debit debit 
          ON (
                 (lid.fund_debit = debit.id)
           )
     )
          
 WHERE (
           (NOT debit.encumbrance)
         AND (lid.lineitem = li.id)
     )
) AS paid_amount
   
FROM acq.lineitem li;

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_fund_debit_idx fund_debit 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


View: acq.po_state_label

acq.po_state_label Structure
F-Key Name Type Description
id text
label text
SELECT t.id
,
    t.label
   
FROM ( VALUES 
     ('new'::text
           ,oils_i18n_gettext
           ('new'::text
                 ,'New'::text
                 ,'acqpostlbl'::text
                 ,'label'::text
           )
     )
     , ('pending'::text
           ,oils_i18n_gettext
           ('pending'::text
                 ,'Pending'::text
                 ,'acqpostlbl'::text
                 ,'label'::text
           )
     )
     , ('on-order'::text
           ,oils_i18n_gettext
           ('on-order'::text
                 ,'On-Order'::text
                 ,'acqpostlbl'::text
                 ,'label'::text
           )
     )
     , ('received'::text
           ,oils_i18n_gettext
           ('received'::text
                 ,'Received'::text
                 ,'acqpostlbl'::text
                 ,'label'::text
           )
     )
     , ('cancelled'::text
           ,oils_i18n_gettext
           ('cancelled'::text
                 ,'Cancelled'::text
                 ,'acqpostlbl'::text
                 ,'label'::text
           )
     )
) t
(id
     , label
);

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
default_copy_count integer NOT NULL
acq.claim_policy.id default_claim_policy integer
acq.provider_contact.id primary_contact 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.shipment_notification

acq.shipment_notification 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
process_date timestamp with time zone
actor.usr.id processed_by integer
container_code text UNIQUE#1 NOT NULL
lading_number text
note text

Tables referencing this one via Foreign Key Constraints:

acq_asn_container_code_idx container_code

Index - Schema acq


Table: acq.shipment_notification_entry

acq.shipment_notification_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.shipment_notification.id shipment_notification integer NOT NULL
acq.lineitem.id lineitem integer
item_count integer NOT NULL

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
upc 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
cancel_time timestamp with time zone

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.user_request_status_type

acq.user_request_status_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
label text

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.copy_fund_tags(new_fund_id integer, old_fund_id integer)

Returns: void

Language: PLPGSQL

DECLARE
fund_tag_rec	RECORD;
BEGIN
       
	FOR fund_tag_rec IN SELECT * FROM acq.fund_tag_map WHERE fund=old_fund_id LOOP
                BEGIN
		     INSERT INTO acq.fund_tag_map(fund, tag) VALUES(new_fund_id, fund_tag_rec.tag);
                EXCEPTION
			WHEN unique_violation THEN
			--    RAISE NOTICE 'Fund tag already propagated', old_fund.id;
			CONTINUE;
		END;
	END LOOP;
	RETURN;
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(include_desc integer, org_unit_id integer, user_id integer, old_year boolean)

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 ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
                OR (NOT include_desc 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;

		PERFORM acq.copy_fund_tags(old_fund.id,new_id);

		--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: SQL

    SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );

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(include_desc integer, encumb_only integer, org_unit_id integer, user_id boolean, old_year boolean)

Returns: void

Language: PLPGSQL

DECLARE
--
new_fund    INT;
new_year    INT := old_year + 1;
org_found   BOOL;
perm_ous    BOOL;
xfer_amount NUMERIC := 0;
roll_fund   RECORD;
deb         RECORD;
detail      RECORD;
roll_distrib_forms BOOL;
--
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;
		ELSIF encumb_only THEN
			SELECT INTO perm_ous value::BOOL FROM
			actor.org_unit_ancestor_setting(
				'acq.fund.allow_rollover_without_money', org_unit_id
			);
			IF NOT FOUND OR NOT perm_ous THEN
				RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
			END IF;
		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 AND oldf.org = newf.org )
	WHERE
 		    oldf.year = old_year
		AND oldf.propagate
        AND newf.year = new_year
		AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
                OR (NOT include_desc AND oldf.org = 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;

		        PERFORM acq.copy_fund_tags(roll_fund.id,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 NOT encumb_only AND 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 into the void'
				);
			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;

		-- Rollover distribution formulae funds
		SELECT INTO roll_distrib_forms value::BOOL FROM
			actor.org_unit_ancestor_setting(
				'acq.fund.rollover_distrib_forms', org_unit_id
			);

		IF roll_distrib_forms THEN
			UPDATE acq.distribution_formula_entry 
				SET fund = roll_fund.new_fund_id
				WHERE fund = roll_fund.old_fund;
		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(encumb_only integer, org_unit_id integer, user_id integer, old_year boolean)

Returns: void

Language: SQL

    SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );

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;
    old_fund_row       acq.fund%ROWTYPE;
    new_fund_row       acq.fund%ROWTYPE;
    old_org_row        actor.org_unit%ROWTYPE;
    new_org_row        actor.org_unit%ROWTYPE;
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;

    -- Fetch old and new fund's information
    -- in order to construct the allocation notes
    SELECT INTO old_fund_row * FROM acq.fund WHERE id = old_fund;
    SELECT INTO old_org_row * FROM actor.org_unit WHERE id = old_fund_row.org;
    SELECT INTO new_fund_row * FROM acq.fund WHERE id = new_fund;
    SELECT INTO new_org_row * FROM actor.org_unit WHERE id = new_fund_row.org;

	--
	-- 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 := - curr_old_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_row.code || ' ('
                                    || new_fund_row.year || ') ('
                                    || new_org_row.shortname || ')'
			);
		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 := curr_new_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_row.code || ' ('
                                          || old_fund_row.year || ') ('
                                          || old_org_row.shortname || ')'
				);
			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
auto_renewal boolean NOT NULL
auto_renewal_remaining integer
parent_circ bigint
action_aged_circulation_parent_circ_idx parent_circ 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


Table: action.aged_hold_request

action.aged_hold_request 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
staff_placed boolean NOT NULL
id integer PRIMARY KEY
request_time timestamp with time zone NOT NULL
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
cancel_cause integer
cancel_note text
target bigint NOT NULL
current_copy bigint
fulfillment_staff integer
fulfillment_lib integer
request_lib integer NOT NULL
selection_ou integer NOT NULL
selection_depth integer NOT NULL
pickup_lib integer NOT NULL
hold_type text
holdable_formats text
phone_notify boolean NOT NULL
email_notify boolean NOT NULL
sms_notify boolean NOT NULL
frozen boolean NOT NULL
thaw_date timestamp with time zone
shelf_time timestamp with time zone
cut_in_line boolean
mint_condition boolean NOT NULL
shelf_expire_time timestamp with time zone
current_shelf_lib integer
behind_desk boolean NOT NULL
hopeless_date timestamp with time zone
aged_hold_request_current_copy_idx current_copy aged_hold_request_fulfillment_staff_idx fulfillment_staff aged_hold_request_pickup_lib_idx pickup_lib aged_hold_request_target_idx target

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
auto_renewal boolean
auto_renewal_remaining integer
usr integer
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
,
    aged_circulation.auto_renewal
,
    aged_circulation.auto_renewal_remaining
,
    NULL::integer AS usr
   
FROM action.aged_circulation

UNION ALL
 
SELECT 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
,
    circ.auto_renewal
,
    circ.auto_renewal_remaining
,
    circ.usr
   
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


View: action.all_circulation_combined_types

action.all_circulation_combined_types Structure
F-Key Name Type Description
id bigint
xact_start timestamp with time zone
circ_lib integer
circ_staff integer
create_time timestamp with time zone
item_type text
circ_type text
SELECT acirc.id
,
    acirc.xact_start
,
    acirc.circ_lib
,
    acirc.circ_staff
,
    acirc.create_time
,
    ac_acirc.circ_modifier AS item_type
,
    'regular_circ'::text AS circ_type
   
FROM action.circulation acirc
,
    asset.copy ac_acirc
  
WHERE (acirc.target_copy = ac_acirc.id)
UNION ALL
 
SELECT (ancc.id)::bigint AS id
,
    ancc.circ_time AS xact_start
,
    ancc.circ_lib
,
    ancc.staff AS circ_staff
,
    ancc.circ_time AS create_time
,
    cnct_ancc.name AS item_type
,
    'non-cat_circ'::text AS circ_type
   
FROM action.non_cataloged_circulation ancc
,
    config.non_cataloged_type cnct_ancc
  
WHERE (ancc.item_type = cnct_ancc.id)
UNION ALL
 
SELECT (aihu.id)::bigint AS id
,
    aihu.use_time AS xact_start
,
    aihu.org_unit AS circ_lib
,
    aihu.staff AS circ_staff
,
    aihu.use_time AS create_time
,
    ac_aihu.circ_modifier AS item_type
,
    'in-house_use'::text AS circ_type
   
FROM action.in_house_use aihu
,
    asset.copy ac_aihu
  
WHERE (aihu.item = ac_aihu.id)
UNION ALL
 
SELECT (ancihu.id)::bigint AS id
,
    ancihu.use_time AS xact_start
,
    ancihu.org_unit AS circ_lib
,
    ancihu.staff AS circ_staff
,
    ancihu.use_time AS create_time
,
    cnct_ancihu.name AS item_type
,
    'non-cat-in-house_use'::text AS circ_type
   
FROM action.non_cat_in_house_use ancihu
,
    config.non_cataloged_type cnct_ancihu
  
WHERE (ancihu.item_type = cnct_ancihu.id)
UNION ALL
 
SELECT aacirc.id
,
    aacirc.xact_start
,
    aacirc.circ_lib
,
    aacirc.circ_staff
,
    aacirc.create_time
,
    ac_aacirc.circ_modifier AS item_type
,
    'aged_circ'::text AS circ_type
   
FROM action.aged_circulation aacirc
,
    asset.copy ac_aacirc
  
WHERE (aacirc.target_copy = ac_aacirc.id);

Index - Schema action


View: action.all_circulation_slim

action.all_circulation_slim 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
auto_renewal boolean
auto_renewal_remaining integer
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.auto_renewal
,
    circulation.auto_renewal_remaining
,
    circulation.parent_circ
   
FROM action.circulation

UNION ALL
 
SELECT aged_circulation.id
,
    NULL::integer AS usr
,
    aged_circulation.xact_start
,
    aged_circulation.xact_finish
,
    aged_circulation.unrecovered
,
    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.copy_location
,
    aged_circulation.checkin_scan_time
,
    aged_circulation.auto_renewal
,
    aged_circulation.auto_renewal_remaining
,
    aged_circulation.parent_circ
   
FROM action.aged_circulation;

Index - Schema action


View: action.all_hold_request

action.all_hold_request Structure
F-Key Name Type Description
usr_post_code text
usr_home_ou integer
usr_profile integer
usr_birth_year integer
staff_placed boolean
id integer
request_time timestamp with time zone
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
cancel_cause integer
cancel_note text
target bigint
current_copy bigint
fulfillment_staff integer
fulfillment_lib integer
request_lib integer
selection_ou integer
selection_depth integer
pickup_lib integer
hold_type text
holdable_formats text
phone_notify boolean
email_notify boolean
sms_notify boolean
frozen boolean
thaw_date timestamp with time zone
shelf_time timestamp with time zone
cut_in_line boolean
mint_condition boolean
shelf_expire_time timestamp with time zone
current_shelf_lib integer
behind_desk boolean
SELECT DISTINCT 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
,
    
(ahr.requestor <> ahr.usr) AS staff_placed
,
    ahr.id
,
    ahr.request_time
,
    ahr.capture_time
,
    ahr.fulfillment_time
,
    ahr.checkin_time
,
    ahr.return_time
,
    ahr.prev_check_time
,
    ahr.expire_time
,
    ahr.cancel_time
,
    ahr.cancel_cause
,
    ahr.cancel_note
,
    ahr.target
,
    ahr.current_copy
,
    ahr.fulfillment_staff
,
    ahr.fulfillment_lib
,
    ahr.request_lib
,
    ahr.selection_ou
,
    ahr.selection_depth
,
    ahr.pickup_lib
,
    ahr.hold_type
,
    ahr.holdable_formats
,
        CASE
            WHEN 
(ahr.phone_notify IS NULL) THEN false
            WHEN 
(ahr.phone_notify = ''::text) THEN false
            ELSE true
        END AS phone_notify
,
    ahr.email_notify
,
        CASE
            WHEN 
(ahr.sms_notify IS NULL) THEN false
            WHEN 
(ahr.sms_notify = ''::text) THEN false
            ELSE true
        END AS sms_notify
,
    ahr.frozen
,
    ahr.thaw_date
,
    ahr.shelf_time
,
    ahr.cut_in_line
,
    ahr.mint_condition
,
    ahr.shelf_expire_time
,
    ahr.current_shelf_lib
,
    ahr.behind_desk
   
FROM (
     (
           (action.hold_request ahr
     
              JOIN actor.usr p 
                ON (
                       (ahr.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)
     )
)
UNION ALL
 
SELECT aged_hold_request.usr_post_code
,
    aged_hold_request.usr_home_ou
,
    aged_hold_request.usr_profile
,
    aged_hold_request.usr_birth_year
,
    aged_hold_request.staff_placed
,
    aged_hold_request.id
,
    aged_hold_request.request_time
,
    aged_hold_request.capture_time
,
    aged_hold_request.fulfillment_time
,
    aged_hold_request.checkin_time
,
    aged_hold_request.return_time
,
    aged_hold_request.prev_check_time
,
    aged_hold_request.expire_time
,
    aged_hold_request.cancel_time
,
    aged_hold_request.cancel_cause
,
    aged_hold_request.cancel_note
,
    aged_hold_request.target
,
    aged_hold_request.current_copy
,
    aged_hold_request.fulfillment_staff
,
    aged_hold_request.fulfillment_lib
,
    aged_hold_request.request_lib
,
    aged_hold_request.selection_ou
,
    aged_hold_request.selection_depth
,
    aged_hold_request.pickup_lib
,
    aged_hold_request.hold_type
,
    aged_hold_request.holdable_formats
,
    aged_hold_request.phone_notify
,
    aged_hold_request.email_notify
,
    aged_hold_request.sms_notify
,
    aged_hold_request.frozen
,
    aged_hold_request.thaw_date
,
    aged_hold_request.shelf_time
,
    aged_hold_request.cut_in_line
,
    aged_hold_request.mint_condition
,
    aged_hold_request.shelf_expire_time
,
    aged_hold_request.current_shelf_lib
,
    aged_hold_request.behind_desk
   
FROM action.aged_hold_request;

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


Table: action.batch_hold_event

action.batch_hold_event Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id staff integer NOT NULL
container.user_bucket.id bucket integer NOT NULL
target integer NOT NULL
hold_type text NOT NULL DEFAULT 'T'::text
run_date timestamp with time zone NOT NULL DEFAULT now()
cancelled timestamp with time zone

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.batch_hold_event_map

action.batch_hold_event_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.batch_hold_event.id batch_hold_event integer NOT NULL
action.hold_request.id hold integer 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
auto_renewal boolean
auto_renewal_remaining integer
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.auto_renewal
,
    circulation.auto_renewal_remaining
,
    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
auto_renewal boolean NOT NULL DEFAULT false
auto_renewal_remaining integer
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.curbside

action.curbside Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id patron integer NOT NULL
actor.org_unit.id org integer NOT NULL
slot timestamp with time zone
staged timestamp with time zone
actor.usr.id stage_staff integer
arrival timestamp with time zone
delivered timestamp with time zone
actor.usr.id delivery_staff integer
notes text

Index - Schema action


Table: action.emergency_closing

action.emergency_closing Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id creator integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
process_start_time timestamp with time zone
process_end_time timestamp with time zone
last_update_time timestamp with time zone

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.emergency_closing_circulation

action.emergency_closing_circulation Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
action.emergency_closing.id emergency_closing integer NOT NULL
action.circulation.id circulation integer NOT NULL
original_due_date timestamp with time zone
process_time timestamp with time zone
emergency_closing_circulation_circulation_idx circulation emergency_closing_circulation_emergency_closing_idx emergency_closing

Index - Schema action


Table: action.emergency_closing_hold

action.emergency_closing_hold Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
action.emergency_closing.id emergency_closing integer NOT NULL
action.hold_request.id hold integer NOT NULL
original_shelf_expire_time timestamp with time zone
process_time timestamp with time zone
emergency_closing_hold_emergency_closing_idx emergency_closing emergency_closing_hold_hold_idx hold

Index - Schema action


Table: action.emergency_closing_reservation

action.emergency_closing_reservation Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
action.emergency_closing.id emergency_closing integer NOT NULL
booking.reservation.id reservation integer NOT NULL
original_end_time timestamp with time zone
process_time timestamp with time zone
emergency_closing_reservation_emergency_closing_idx emergency_closing emergency_closing_reservation_reservation_idx reservation

Index - Schema action


View: action.emergency_closing_status

action.emergency_closing_status Structure
F-Key Name Type Description
id integer
creator integer
create_time timestamp with time zone
process_start_time timestamp with time zone
process_end_time timestamp with time zone
last_update_time timestamp with time zone
circulations bigint
circulations_complete bigint
reservations bigint
reservations_complete bigint
holds bigint
holds_complete bigint
SELECT e.id
,
    e.creator
,
    e.create_time
,
    e.process_start_time
,
    e.process_end_time
,
    e.last_update_time
,
    COALESCE
(c.count
     , (0)::bigint
) AS circulations
,
    COALESCE
(c.completed
     , (0)::bigint
) AS circulations_complete
,
    COALESCE
(b.count
     , (0)::bigint
) AS reservations
,
    COALESCE
(b.completed
     , (0)::bigint
) AS reservations_complete
,
    COALESCE
(h.count
     , (0)::bigint
) AS holds
,
    COALESCE
(h.completed
     , (0)::bigint
) AS holds_complete
   
FROM (
     (
           (action.emergency_closing e
     
         LEFT JOIN (
                  SELECT emergency_closing_circulation.emergency_closing
                       ,
            count
                       (*) AS count
                       ,
            sum
                       (
                             (
                                   (emergency_closing_circulation.process_time IS NOT NULL)
                             )::integer
                       ) AS completed
           
                    FROM action.emergency_closing_circulation
          
                GROUP BY emergency_closing_circulation.emergency_closing
                 ) c 
                ON (
                       (c.emergency_closing = e.id)
                 )
           )
     
   LEFT JOIN (
            SELECT emergency_closing_reservation.emergency_closing
                 ,
            count
                 (*) AS count
                 ,
            sum
                 (
                       (
                             (emergency_closing_reservation.process_time IS NOT NULL)
                       )::integer
                 ) AS completed
           
              FROM action.emergency_closing_reservation
          
          GROUP BY emergency_closing_reservation.emergency_closing
           ) b 
          ON (
                 (b.emergency_closing = e.id)
           )
     )
     
LEFT JOIN (
      SELECT emergency_closing_hold.emergency_closing
           ,
            count
           (*) AS count
           ,
            sum
           (
                 (
                       (emergency_closing_hold.process_time IS NOT NULL)
                 )::integer
           ) AS completed
           
        FROM action.emergency_closing_hold
          
    GROUP BY emergency_closing_hold.emergency_closing
     ) h 
    ON (
           (h.emergency_closing = e.id)
     )
);

Index - Schema action


Table: action.fieldset

action.fieldset Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.fieldset_group.id fieldset_group integer
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
error_msg text
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.fieldset_group

action.fieldset_group Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
complete_time timestamp with time zone
container integer
container_type text
can_rollback boolean DEFAULT true
action.fieldset_group.id rollback_group integer
rollback_time timestamp with time zone
actor.usr.id creator integer NOT NULL
actor.org_unit.id owning_lib integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

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
proximity numeric
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
config.hold_type.hold_type hold_type text
holdable_formats text
phone_notify text
email_notify boolean NOT NULL DEFAULT false
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
behind_desk boolean NOT NULL DEFAULT false
hopeless_date timestamp with time zone
acq.user_request.id acq_request 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_fulfillment_time_idx fulfillment_time) WHERE (fulfillment_time IS NOT NULL hold_request_copy_capture_time_idx current_copy, capture_time hold_request_current_copy_before_cap_idx current_copy) WHERE ((capture_time IS NULL) AND (cancel_time IS NULL) hold_request_current_copy_idx current_copy hold_request_fulfillment_staff_idx fulfillment_staff hold_request_open_captured_shelf_lib_idx current_shelf_lib) WHERE ((capture_time IS NOT NULL) AND (fulfillment_time IS NULL) AND (pickup_lib <> current_shelf_lib) hold_request_open_idx id) WHERE ((cancel_time IS NULL) AND (fulfillment_time IS NULL) 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_time_idx request_time 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
cancel_time timestamp with time zone
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 hold_transit_copy_hold_idx hold

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.workstation.id workstation integer
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 action_in_house_use_ws_idx workstation

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.workstation.id workstation integer
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 non_cat_in_house_use_ws_idx workstation

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
auto_renewal boolean
auto_renewal_remaining integer
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.auto_renewal
,
    circulation.auto_renewal_remaining
,
    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
cancel_time timestamp with time zone
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
cancel_time timestamp with time zone

Tables referencing this one via Foreign Key Constraints:

active_transit_cp_idx target_copy active_transit_dest_idx dest active_transit_for_copy target_copy) WHERE ((dest_recv_time IS NULL) AND (cancel_time IS NULL) 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


Table: action.usr_circ_history

action.usr_circ_history Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr integer NOT NULL
xact_start timestamp with time zone NOT NULL DEFAULT now()
target_copy bigint NOT NULL
due_date timestamp with time zone NOT NULL
checkin_time timestamp with time zone
action.circulation.id source_circ bigint
action_usr_circ_history_source_circ_idx source_circ action_usr_circ_history_usr_idx usr

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,
        auto_renewal, auto_renewal_remaining)
      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,
        auto_renewal, auto_renewal_remaining
        FROM action.all_circulation WHERE id = OLD.id;

    -- Migrate billings and payments to aged tables

    SELECT 'Y' INTO found FROM config.global_flag 
        WHERE name = 'history.money.age_with_circs' AND enabled;

    IF found = 'Y' THEN
        PERFORM money.age_billings_and_payments_for_xact(OLD.id);
    END IF;

    -- Break the link with the user in action_trigger.event (warning: event_output may essentially have this information)
    UPDATE
        action_trigger.event e
    SET
        context_user = NULL
    FROM
        action.all_circulation c
    WHERE
            c.id = OLD.id
        AND e.context_user = c.usr
        AND e.target = c.id
        AND e.event_def IN (
            SELECT id
            FROM action_trigger.event_definition
            WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')
        )
    ;

    RETURN OLD;
END;

Function: action.age_hold_on_delete()

Returns: trigger

Language: PLPGSQL

DECLARE
BEGIN
    -- Archive a copy of the old row to action.aged_hold_request

    INSERT INTO action.aged_hold_request
           (usr_post_code,
            usr_home_ou,
            usr_profile,
            usr_birth_year,
            staff_placed,
            id,
            request_time,
            capture_time,
            fulfillment_time,
            checkin_time,
            return_time,
            prev_check_time,
            expire_time,
            cancel_time,
            cancel_cause,
            cancel_note,
            target,
            current_copy,
            fulfillment_staff,
            fulfillment_lib,
            request_lib,
            selection_ou,
            selection_depth,
            pickup_lib,
            hold_type,
            holdable_formats,
            phone_notify,
            email_notify,
            sms_notify,
            frozen,
            thaw_date,
            shelf_time,
            cut_in_line,
            mint_condition,
            shelf_expire_time,
            current_shelf_lib,
            behind_desk)
      SELECT 
           usr_post_code,
           usr_home_ou,
           usr_profile,
           usr_birth_year,
           staff_placed,
           id,
           request_time,
           capture_time,
           fulfillment_time,
           checkin_time,
           return_time,
           prev_check_time,
           expire_time,
           cancel_time,
           cancel_cause,
           cancel_note,
           target,
           current_copy,
           fulfillment_staff,
           fulfillment_lib,
           request_lib,
           selection_ou,
           selection_depth,
           pickup_lib,
           hold_type,
           holdable_formats,
           phone_notify,
           email_notify,
           sms_notify,
           frozen,
           thaw_date,
           shelf_time,
           cut_in_line,
           mint_condition,
           shelf_expire_time,
           current_shelf_lib,
           behind_desk
        FROM action.all_hold_request 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.all_circ_chain(ctx_circ_id integer)

Returns: SET OF all_circulation_slim

Language: PLPGSQL

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

    SELECT INTO tmp_circ * FROM action.all_circulation_slim 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.all_circulation_slim 
            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.all_circulation_slim 
            WHERE parent_circ = tmp_circ.id;
    END LOOP;

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;
    where_clause TEXT;
    fs_status TEXT;
    fs_pkey_value TEXT;
    fs_query TEXT;
    sep CHAR;
    status_code TEXT;
    msg TEXT;
    fs_id INT;
    fsg_id INT;
    update_count INT;
    cv RECORD;
    fs_obj action.fieldset%ROWTYPE;
    fs_group action.fieldset_group%ROWTYPE;
    rb_row 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;

    SELECT
        status,
        quote_literal( pkey_value )
    INTO
        fs_status,
        fs_pkey_value
    FROM
        action.fieldset
    WHERE
        id = fieldset_id;

    --
    -- Build 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, ';' );
        where_clause := 'WHERE ' || pkey_name || ' IN ( '
                     || fs_query || ' )';
    ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
        where_clause := 'WHERE ' || pkey_name || ' = ';
        IF pkey_name = 'id' THEN
            where_clause := where_clause || fs_pkey_value;
        ELSIF pkey_name = 'code' THEN
            where_clause := where_clause || quote_literal(fs_pkey_value);
        ELSE
            RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
        END IF;
    ELSE  -- both are not null
        RETURN 'Ambiguous fieldset: both a primary key and a query provided';
    END IF;

    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;

    SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
    SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;

    IF fs_group.can_rollback THEN
        -- This is part of a non-rollback group.  We need to record the current values for future rollback.

        INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
            VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);

        fsg_id := CURRVAL('action.fieldset_group_id_seq');

        FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
            IF pkey_name = 'id' THEN
                fs_pkey_value := rb_row.id;
            ELSIF pkey_name = 'code' THEN
                fs_pkey_value := rb_row.code;
            ELSE
                RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
            END IF;
            INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
                VALUES (fsg_id, fs_obj.owner, fs_obj.owning_lib, 'PENDING', fs_obj.classname, fs_obj.name || ' ROLLBACK FOR ' || fs_pkey_value, fs_pkey_value);

            fs_id := CURRVAL('action.fieldset_id_seq');
            sep := '';
            FOR cv IN
                SELECT  DISTINCT col
                FROM    action.fieldset_col_val
                WHERE   fieldset = fieldset_id
            LOOP
                EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' || 
                    'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
            END LOOP;
        END LOOP;
    END IF;

    statement := 'UPDATE ' || table_name || ' SET';

    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;
    statement := statement || ' ' || where_clause;

    --
    -- Execute the update
    --
    BEGIN
        EXECUTE statement;
        GET DIAGNOSTICS update_count = ROW_COUNT;

        IF update_count = 0 THEN
            RAISE data_exception;
        END IF;

        IF fsg_id IS NOT NULL THEN
            UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
        END IF;

        IF fs_group.id IS NOT NULL THEN
            UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
        END IF;

        UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;

    EXCEPTION WHEN data_exception THEN
        msg := 'No eligible rows found for fieldset ' || fieldset_id;
        UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
        RETURN msg;

    END;

    RETURN msg;

EXCEPTION WHEN OTHERS THEN
    msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
    UPDATE action.fieldset SET status = 'ERROR', 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 bigint)

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_calculated_proximity(vacl_ol integer, vacn_ol integer, vacp_cm integer, vacp_cl text, request integer, pickup integer)

Returns: numeric

Language: PLPGSQL

DECLARE
    baseline_prox   NUMERIC;
    aoupa           actor.org_unit_proximity_adjustment%ROWTYPE;
BEGIN

    -- First, gather the baseline proximity of "here" to pickup lib
    SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = vacp_cl AND to_org = pickup;

    -- Find any absolute adjustments, and set the baseline prox to that
    SELECT  adj.* INTO aoupa
      FROM  actor.org_unit_proximity_adjustment adj
            LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
            LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
            LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acl_ol.id = adj.copy_location)
            LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
            LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
      WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
            (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
            (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
            (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
            (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
            (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
            absolute_adjustment AND
            COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
      ORDER BY
            COALESCE(acp_cl.distance,999)
                + COALESCE(acn_ol.distance,999)
                + COALESCE(acl_ol.distance,999)
                + COALESCE(ahr_pl.distance,999)
                + COALESCE(ahr_rl.distance,999),
            adj.pos
      LIMIT 1;

    IF FOUND THEN
        baseline_prox := aoupa.prox_adjustment;
    END IF;

    -- Now find any relative adjustments, and change the baseline prox based on them
    FOR aoupa IN
        SELECT  adj.*
          FROM  actor.org_unit_proximity_adjustment adj
                LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
                LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
                LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acn_ol.id = adj.copy_location)
                LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
                LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
          WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
                (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
                (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
                (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
                (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
                (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
                NOT absolute_adjustment AND
                COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
    LOOP
        baseline_prox := baseline_prox + aoupa.prox_adjustment;
    END LOOP;

    RETURN baseline_prox;
END;

Function: action.copy_related_hold_stats(copy_id bigint)

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.copy_transit_is_unique()

Returns: trigger

Language: PLPGSQL

BEGIN
    PERFORM * FROM action.transit_copy 
        WHERE target_copy = NEW.target_copy 
              AND dest_recv_time IS NULL 
              AND cancel_time IS NULL;

    IF FOUND THEN
        RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
    END IF;
    RETURN NULL;
END;

Function: action.emergency_closing_stage_1(e_closing integer)

Returns: SET OF emergency_closing_stage_1_count

Language: PLPGSQL

DECLARE
    tmp     INT;
    touched action.emergency_closing_stage_1_count%ROWTYPE;
BEGIN
    -- First, gather circs
    INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation)
        SELECT  e_closing,
                circ.id
          FROM  actor.org_unit_closed closing
                JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
                JOIN action.circulation circ ON (
                    circ.circ_lib = closing.org_unit
                    AND circ.due_date BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
                    AND circ.xact_finish IS NULL
                )
          WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id);

    GET DIAGNOSTICS tmp = ROW_COUNT;
    touched.circulations := tmp;

    INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation)
        SELECT  e_closing,
                res.id
          FROM  actor.org_unit_closed closing
                JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
                JOIN booking.reservation res ON (
                    res.pickup_lib = closing.org_unit
                    AND res.end_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
                )
          WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id);

    GET DIAGNOSTICS tmp = ROW_COUNT;
    touched.reservations := tmp;

    INSERT INTO action.emergency_closing_hold (emergency_closing, hold)
        SELECT  e_closing,
                hold.id
          FROM  actor.org_unit_closed closing
                JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
                JOIN action.hold_request hold ON (
                    pickup_lib = closing.org_unit
                    AND hold.shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
                    AND hold.fulfillment_time IS NULL
                    AND hold.cancel_time IS NULL
                )
          WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id);

    GET DIAGNOSTICS tmp = ROW_COUNT;
    touched.holds := tmp;

    UPDATE  action.emergency_closing
      SET   process_start_time = NOW(),
            last_update_time = NOW()
      WHERE id = e_closing;

    RETURN NEXT touched;
END;

Function: action.emergency_closing_stage_2_circ(circ_closing_entry integer)

Returns: boolean

Language: PLPGSQL

DECLARE
    circ            action.circulation%ROWTYPE;
    e_closing       action.emergency_closing%ROWTYPE;
    e_c_circ        action.emergency_closing_circulation%ROWTYPE;
    closing         actor.org_unit_closed%ROWTYPE;
    adjacent        actor.org_unit_closed%ROWTYPE;
    bill            money.billing%ROWTYPE;
    last_bill       money.billing%ROWTYPE;
    day_number      INT;
    hoo_close       TIME WITHOUT TIME ZONE;
    plus_days       INT;
    avoid_negative  BOOL;
    extend_grace    BOOL;
    new_due_date    TEXT;
BEGIN
    -- Gather objects involved
    SELECT  * INTO e_c_circ
      FROM  action.emergency_closing_circulation
      WHERE id = circ_closing_entry;

    IF e_c_circ.process_time IS NOT NULL THEN
        -- Already processed ... moving on
        RETURN FALSE;
    END IF;

    SELECT  * INTO e_closing
      FROM  action.emergency_closing
      WHERE id = e_c_circ.emergency_closing;

    IF e_closing.process_start_time IS NULL THEN
        -- Huh... that's odd. And wrong.
        RETURN FALSE;
    END IF;

    SELECT  * INTO closing
      FROM  actor.org_unit_closed
      WHERE emergency_closing = e_closing.id;

    SELECT  * INTO circ
      FROM  action.circulation
      WHERE id = e_c_circ.circulation;

    -- Record the processing
    UPDATE  action.emergency_closing_circulation
      SET   original_due_date = circ.due_date,
            process_time = NOW()
      WHERE id = circ_closing_entry;

    UPDATE  action.emergency_closing
      SET   last_update_time = NOW()
      WHERE id = e_closing.id;

    SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', circ.circ_lib);
    SELECT value::BOOL INTO extend_grace FROM actor.org_unit_ancestor_setting('circ.grace.extend', circ.circ_lib);

    new_due_date := evergreen.find_next_open_time( closing.org_unit, circ.due_date, EXTRACT(EPOCH FROM circ.duration)::INT % 86400 > 0 )::TEXT;
    UPDATE action.circulation SET due_date = new_due_date::TIMESTAMPTZ WHERE id = circ.id;

    -- Now, see if we need to get rid of some fines
    SELECT  * INTO last_bill
      FROM  money.billing b
      WHERE b.xact = circ.id
            AND NOT b.voided
            AND b.btype = 1
      ORDER BY billing_ts DESC
      LIMIT 1;

    FOR bill IN
        SELECT  *
          FROM  money.billing b
          WHERE b.xact = circ.id
                AND b.btype = 1
                AND NOT b.voided
                AND (
                    b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
                    OR (extend_grace AND last_bill.billing_ts <= new_due_date::TIMESTAMPTZ + circ.grace_period)
                )
                AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
          ORDER BY billing_ts
    LOOP
        IF avoid_negative THEN
            PERFORM FROM money.materialized_billable_xact_summary WHERE id = circ.id AND balance_owed < bill.amount;
            EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
        END IF;

        UPDATE  money.billing
          SET   voided = TRUE,
                void_time = NOW(),
                note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
          WHERE id = bill.id;
    END LOOP;
    
    RETURN TRUE;
END;

Function: action.emergency_closing_stage_2_hold(hold_closing_entry integer)

Returns: boolean

Language: PLPGSQL

DECLARE
    hold        action.hold_request%ROWTYPE;
    e_closing   action.emergency_closing%ROWTYPE;
    e_c_hold    action.emergency_closing_hold%ROWTYPE;
    closing     actor.org_unit_closed%ROWTYPE;
    day_number  INT;
    hoo_close   TIME WITHOUT TIME ZONE;
    plus_days   INT;
BEGIN
    -- Gather objects involved
    SELECT  * INTO e_c_hold
      FROM  action.emergency_closing_hold
      WHERE id = hold_closing_entry;

    IF e_c_hold.process_time IS NOT NULL THEN
        -- Already processed ... moving on
        RETURN FALSE;
    END IF;

    SELECT  * INTO e_closing
      FROM  action.emergency_closing
      WHERE id = e_c_hold.emergency_closing;

    IF e_closing.process_start_time IS NULL THEN
        -- Huh... that's odd. And wrong.
        RETURN FALSE;
    END IF;

    SELECT  * INTO closing
      FROM  actor.org_unit_closed
      WHERE emergency_closing = e_closing.id;

    SELECT  * INTO hold
      FROM  action.hold_request h
      WHERE id = e_c_hold.hold;

    -- Record the processing
    UPDATE  action.emergency_closing_hold
      SET   original_shelf_expire_time = hold.shelf_expire_time,
            process_time = NOW()
      WHERE id = hold_closing_entry;

    UPDATE  action.emergency_closing
      SET   last_update_time = NOW()
      WHERE id = e_closing.id;

    UPDATE  action.hold_request
      SET   shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE)
      WHERE id = hold.id;

    RETURN TRUE;
END;

Function: action.emergency_closing_stage_2_reservation(res_closing_entry integer)

Returns: boolean

Language: PLPGSQL

DECLARE
    res             booking.reservation%ROWTYPE;
    e_closing       action.emergency_closing%ROWTYPE;
    e_c_res         action.emergency_closing_reservation%ROWTYPE;
    closing         actor.org_unit_closed%ROWTYPE;
    adjacent        actor.org_unit_closed%ROWTYPE;
    bill            money.billing%ROWTYPE;
    day_number      INT;
    hoo_close       TIME WITHOUT TIME ZONE;
    plus_days       INT;
    avoid_negative  BOOL;
    new_due_date    TEXT;
BEGIN
    -- Gather objects involved
    SELECT  * INTO e_c_res
      FROM  action.emergency_closing_reservation
      WHERE id = res_closing_entry;

    IF e_c_res.process_time IS NOT NULL THEN
        -- Already processed ... moving on
        RETURN FALSE;
    END IF;

    SELECT  * INTO e_closing
      FROM  action.emergency_closing
      WHERE id = e_c_res.emergency_closing;

    IF e_closing.process_start_time IS NULL THEN
        -- Huh... that's odd. And wrong.
        RETURN FALSE;
    END IF;

    SELECT  * INTO closing
      FROM  actor.org_unit_closed
      WHERE emergency_closing = e_closing.id;

    SELECT  * INTO res
      FROM  booking.reservation
      WHERE id = e_c_res.reservation;

    IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
        RETURN FALSE;
    END IF;

    -- Record the processing
    UPDATE  action.emergency_closing_reservation
      SET   original_end_time = res.end_time,
            process_time = NOW()
      WHERE id = res_closing_entry;

    UPDATE  action.emergency_closing
      SET   last_update_time = NOW()
      WHERE id = e_closing.id;

    SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);

    new_due_date := evergreen.find_next_open_time( closing.org_unit, res.end_time, EXTRACT(EPOCH FROM res.booking_interval)::INT % 86400 > 0 )::TEXT;
    UPDATE booking.reservation SET end_time = new_due_date::TIMESTAMPTZ WHERE id = res.id;

    -- Now, see if we need to get rid of some fines
    FOR bill IN
        SELECT  *
          FROM  money.billing b
          WHERE b.xact = res.id
                AND b.btype = 1
                AND NOT b.voided
                AND b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
                AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
    LOOP
        IF avoid_negative THEN
            PERFORM FROM money.materialized_billable_xact_summary WHERE id = res.id AND balance_owed < bill.amount;
            EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
        END IF;

        UPDATE  money.billing
          SET   voided = TRUE,
                void_time = NOW(),
                note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
          WHERE id = bill.id;
    END LOOP;
    
    RETURN TRUE;
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.copy_location       := 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.copy_location            IS NULL OR m.copy_location = item_object.location)
                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.copy_location       IS NOT NULL THEN 4^weights.copy_location 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);
    v_pickup_ou         ALIAS FOR pickup_ou;
    v_request_ou         ALIAS FOR request_ou;
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( v_pickup_ou ) puoua ON m.pickup_ou = puoua.id
            LEFT JOIN actor.org_unit_ancestors_distance( v_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_copy_calculated_proximity(copy_context_ou integer, acp_id bigint, ahr_id integer)

Returns: numeric

Language: PLPGSQL

DECLARE
    ahr  action.hold_request%ROWTYPE;
    acp  asset.copy%ROWTYPE;
    acn  asset.call_number%ROWTYPE;
    acl  asset.copy_location%ROWTYPE;

    prox NUMERIC;
BEGIN

    SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
    SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
    SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
    SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;

    IF copy_context_ou IS NULL THEN
        copy_context_ou := acp.circ_lib;
    END IF;

    SELECT action.copy_calculated_proximity(
        ahr.pickup_lib,
        ahr.request_lib,
        copy_context_ou,
        acp.circ_modifier,
        acn.owning_lib,
        acl.owning_lib
    ) INTO prox;

    RETURN prox;
END;

Function: action.hold_copy_calculated_proximity_update()

Returns: trigger

Language: PLPGSQL

BEGIN
    NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
    RETURN NEW;
END;

Function: action.hold_request_clear_map()

Returns: trigger

Language: PLPGSQL

BEGIN
  DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
  RETURN NEW;
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;
    calc_age_prox        actor.org_unit_setting%ROWTYPE;
    result            action.matrix_test_result;
    hold_test        config.hold_matrix_matchpoint%ROWTYPE;
    use_active_date   TEXT;
    prox_ou           INT;
    age_protect_date  TIMESTAMP WITH TIME ZONE;
    hold_count        INT;
    hold_transit_prox    NUMERIC;
    frozen_hold_count    INT;
    context_org_list    INT[];
    done            BOOL := FALSE;
    hold_penalty TEXT;
    v_pickup_ou ALIAS FOR pickup_ou;
    v_request_ou ALIAS FOR request_ou;
    item_prox INT;
    pickup_prox INT;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
    SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( v_pickup_ou );

    result.success := TRUE;

    -- The HOLD penalty block only applies to new holds.
    -- The CAPTURE penalty block applies to existing holds.
    hold_penalty := 'HOLD';
    IF retargetting THEN
        hold_penalty := 'CAPTURE';
    END IF;

    -- 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(v_pickup_ou, v_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 = v_pickup_ou;

        IF NOT FOUND THEN
            result.fail_part := 'transit_range';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;

    -- Proximity of user's home_ou to the pickup_lib to see if penalty should be ignored.
    SELECT INTO pickup_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = v_pickup_ou;
    -- Proximity of user's home_ou to the items' lib to see if penalty should be ignored.
    IF hold_test.distance_is_from_owner THEN
        SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_cn_object.owning_lib;
    ELSE
        SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib;
    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.ignore_proximity IS NULL OR csp.ignore_proximity < item_prox
                     OR csp.ignore_proximity < pickup_prox)
                AND csp.block_list LIKE '%' || hold_penalty || '%' 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
            SELECT INTO calc_age_prox * FROM actor.org_unit_setting WHERE name = 'circ.holds.calculated_age_proximity' AND org_unit = item_object.circ_lib;
            IF hold_test.distance_is_from_owner THEN
                prox_ou := item_cn_object.owning_lib;
            ELSE
                prox_ou := item_object.circ_lib;
            END IF;
            IF calc_age_prox.id IS NOT NULL AND calc_age_prox.value = 'true' THEN
                SELECT INTO hold_transit_prox action.copy_calculated_proximity(
                    v_pickup_ou,
                    v_request_ou,
                    prox_ou,
                    item_object.circ_modifier,
                    item_cn_object.owning_lib,
                    item_location_object.owning_lib
                );
            ELSE
                SELECT INTO hold_transit_prox prox::NUMERIC FROM actor.org_unit_proximity WHERE from_org = prox_ou AND to_org = v_pickup_ou;
            END IF;

            IF hold_transit_prox > age_protect_object.prox::NUMERIC 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_request_regen_copy_maps(copy_ids integer, hold_id integer[])

Returns: void

Language: SQL

    DELETE FROM action.hold_copy_map WHERE hold = $1;
    INSERT INTO action.hold_copy_map (hold, target_copy) SELECT DISTINCT $1, UNNEST($2);

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[];
    permit_renew            TEXT;
    done                    BOOL := FALSE;
    item_prox               INT;
    home_prox               INT;
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 <> 8 AND item_object.status NOT IN (
        (SELECT id FROM config.copy_status WHERE is_available) ) 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 );

    -- Proximity of user's home_ou to circ_ou to see if penalties should be ignored.
    SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = circ_ou;

    -- Proximity of user's home_ou to item circ_lib to see if penalties should be ignored.
    SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib;

    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.ignore_proximity IS NULL
                     OR csp.ignore_proximity < home_prox
                     OR csp.ignore_proximity < item_prox)
                AND csp.block_list LIKE penalty_type LOOP
        -- override PATRON_EXCEEDS_FINES penalty for renewals based on org setting
        IF renewal AND standing_penalty.name = 'PATRON_EXCEEDS_FINES' THEN
            SELECT INTO permit_renew value FROM actor.org_unit_ancestor_setting('circ.permit_renew_when_exceeds_fines', circ_ou);
            IF permit_renew IS NOT NULL AND permit_renew ILIKE 'true' THEN
                CONTINUE;
            END IF;
        END IF;

        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.xact_finish 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 copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_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.maintain_usr_circ_history()

Returns: trigger

Language: PLPGSQL

DECLARE
    cur_circ  BIGINT;
    first_circ BIGINT;
BEGIN                                                                          

    -- Any retention value signifies history is enabled.
    -- This assumes that clearing these values via external 
    -- process deletes the action.usr_circ_history rows.
    -- TODO: replace these settings w/ a single bool setting?
    PERFORM 1 FROM actor.usr_setting 
        WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
            'history.circ.retention_age', 
            'history.circ.retention_start'
        );

    IF NOT FOUND THEN
        RETURN NEW;
    END IF;

    IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
        -- Starting a new circulation.  Insert the history row.
        INSERT INTO action.usr_circ_history 
            (usr, xact_start, target_copy, due_date, source_circ)
        VALUES (
            NEW.usr, 
            NEW.xact_start, 
            NEW.target_copy, 
            NEW.due_date, 
            NEW.id
        );

        RETURN NEW;
    END IF;

    -- find the first and last circs in the circ chain 
    -- for the currently modified circ.
    FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
        IF first_circ IS NULL THEN
            first_circ := cur_circ;
            CONTINUE;
        END IF;
        -- Allow the loop to continue so that at as the loop
        -- completes cur_circ points to the final circulation.
    END LOOP;

    IF NEW.id <> cur_circ THEN
        -- Modifying an intermediate circ.  Ignore it.
        RETURN NEW;
    END IF;

    -- Update the due_date/checkin_time on the history row if the current 
    -- circ is the last circ in the chain and an update is warranted.

    UPDATE action.usr_circ_history 
        SET 
            due_date = NEW.due_date,
            checkin_time = NEW.checkin_time
        WHERE 
            source_circ = first_circ 
            AND (
                due_date <> NEW.due_date OR (
                    (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
                    (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
                    (checkin_time <> NEW.checkin_time)
                )
            );
    RETURN NEW;
END;                                                                           

Function: action.purge_circulations()

Returns: integer

Language: PLPGSQL

DECLARE
    org_keep_age    INTERVAL;
    org_use_last    BOOL = false;
    org_age_is_min  BOOL = false;
    org_keep_count  INT;

    keep_age        INTERVAL;

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

    count_purged    INT;
    num_incomplete  INT;

    last_finished   TIMESTAMP WITH TIME ZONE;
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;

    SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
    SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';

    -- 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
        -- And, for those, select circs that are finished and older than keep_age
        FOR circ_chain_head IN
            -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
            -- The outer query then uses that information to skip the most recent set the library wants to keep
            -- End result is we don't care what order they come out in, as they are all potentials for deletion.
            SELECT ac.* FROM action.circulation ac JOIN (
              SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
                FROM  action.circulation ac
                WHERE ac.target_copy = target_acp.target_copy
                  AND ac.parent_circ IS NULL
                ORDER BY ac.xact_start ) ranked USING (id)
                WHERE ranked.rank > org_keep_count
        LOOP

            SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
            SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id);
            CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;

            IF NOT org_use_last THEN
                last_finished := circ_chain_tail.xact_finish;
            END IF;

            keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );

            IF org_age_is_min THEN
                keep_age := GREATEST( keep_age, org_keep_age );
            END IF;

            CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;

            -- We've passed the purging tests, purge the circ chain starting at the end
            -- A trigger should auto-purge the rest of the chain.
            DELETE FROM action.circulation WHERE id = circ_chain_tail.id;

            count_purged := count_purged + 1;

        END LOOP;
    END LOOP;

    return count_purged;
END;

Function: action.purge_holds()

Returns: integer

Language: PLPGSQL

DECLARE
  current_hold RECORD;
  purged_holds INT;
  cgf_d INTERVAL;
  cgf_f INTERVAL;
  cgf_c INTERVAL;
  prev_usr INT;
  user_start TIMESTAMPTZ;
  user_age INTERVAL;
  user_count INT;
BEGIN
  purged_holds := 0;
  SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
  SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
  SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
  FOR current_hold IN
    SELECT
      rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
      cgf_cs.value::INTERVAL as cgf_cs,
      ahr.*
    FROM
      action.hold_request ahr
      LEFT JOIN config.global_flag cgf_cs ON (ahr.cancel_cause IS NOT NULL AND cgf_cs.name = 'history.hold.retention_age_canceled_' || ahr.cancel_cause AND cgf_cs.enabled)
    WHERE
      (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
  LOOP
    IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
      prev_usr := current_hold.usr;
      SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
      SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
      SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
      IF user_start IS NOT NULL THEN
        user_age := LEAST(user_age, AGE(NOW(), user_start));
      END IF;
      IF user_count IS NULL THEN
        user_count := 1000; -- Assumption based on the user visible holds routine
      END IF;
    END IF;
    -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
    IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
      CONTINUE;
    END IF;
    IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
      CONTINUE;
    END IF;

    -- User keep age needs combining with count. If too old AND within the count, keep!
    IF user_start IS NOT NULL AND COALESCE(current_hold.fulfillment_time, current_hold.cancel_time) > NOW() - user_age AND current_hold.rank <= user_count THEN
      CONTINUE;
    END IF;

    -- All checks should have passed, delete!
    DELETE FROM action.hold_request WHERE id = current_hold.id;
    purged_holds := purged_holds + 1;
  END LOOP;
  RETURN purged_holds;
END;

Function: action.push_circ_due_time()

Returns: trigger

Language: PLPGSQL

DECLARE
    proper_tz TEXT := COALESCE(
        oils_json_to_text((
            SELECT value
              FROM  actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
              LIMIT 1
        )),
        CURRENT_SETTING('timezone')
    );
BEGIN

    IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
        AND SUBSTRING((NEW.due_date AT TIME ZONE proper_tz)::TIME::TEXT FROM 1 FOR 8) <> '23:59:59' THEN -- has not yet been pushed
        NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
    END IF;

    RETURN NEW;
END;

Function: action.summarize_all_circ_chain(ctx_circ_id integer)

Returns: circ_chain_summary

Language: PLPGSQL


DECLARE

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

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

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

BEGIN
    
    chain.num_circs := 0;
    FOR tmp_circ IN SELECT * FROM action.all_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.summarize_circ_chain(ctx_circ_id bigint)

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_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 COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
          ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
          LIMIT view_count
    LOOP
        RETURN NEXT h;
    END LOOP;

    RETURN;
END;

Schema action_trigger


Table: action_trigger.alternate_template

action_trigger.alternate_template Structure
F-Key Name Type Description
id serial NOT NULL
action_trigger.event_definition.id event_def integer UNIQUE#1
template text
active boolean DEFAULT true
config.i18n_locale.code locale text UNIQUE#1
message_title text
message_template text

Index - 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
actor.usr.id context_user integer
actor.org_unit.id context_library integer
biblio.record_entry.id context_bib bigint
context_item 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_async_output async_output atev_context_item context_item atev_context_library context_library atev_context_user context_user atev_def_state event_def, state atev_error_output error_output atev_target_def_idx target, event_def atev_template_output template_output

Index - Schema action_trigger


Table: action_trigger.event_def_group

action_trigger.event_def_group Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer NOT NULL
action_trigger.hook.key hook text NOT NULL
active boolean NOT NULL DEFAULT true
name text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.event_def_group_member

action_trigger.event_def_group_member Structure
F-Key Name Type Description
id serial PRIMARY KEY
action_trigger.event_def_group.id grp integer NOT NULL
action_trigger.event_definition.id event_def integer NOT NULL
sortable boolean NOT NULL DEFAULT true
holdings boolean NOT NULL DEFAULT false
external boolean NOT NULL DEFAULT false
name text NOT NULL

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
repeat_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
context_usr_path text
context_library_path text
context_bib_path text
context_item_path text
message_template text
message_usr_path text
message_library_path text
message_title text
retention_interval interval

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
locale text

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


Function: action_trigger.check_valid_retention_interval()

Returns: trigger

Language: PLPGSQL

BEGIN
    /*
     * 1. Retention intervals are always allowed on active hooks.
     * 2. On passive hooks, retention intervals are only allowed
     *    when the event definition has a max_delay value and the
     *    retention_interval value is greater than the difference 
     *    beteween the delay and max_delay values.
     */ 
    PERFORM TRUE FROM action_trigger.hook 
        WHERE key = NEW.hook AND NOT passive;

    IF FOUND THEN
        RETURN NEW;
    END IF;

    IF NEW.max_delay IS NOT NULL THEN
        IF EXTRACT(EPOCH FROM NEW.retention_interval) > 
            ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN
            RETURN NEW; -- all good
        ELSE
            RAISE EXCEPTION 'retention_interval is too short';
        END IF;
    ELSE
        RAISE EXCEPTION 'retention_interval requires max_delay';
    END IF;
END;

Function: action_trigger.purge_events()

Returns: void

Language: PLPGSQL

/**
  * Deleting expired events without simultaneously deleting their outputs
  * creates orphaned outputs.  Deleting their outputs and all of the events 
  * linking back to them, plus any outputs those events link to is messy and 
  * inefficient.  It's simpler to handle them in 2 sweeping steps.
  *
  * 1. Delete expired events.
  * 2. Delete orphaned event outputs.
  *
  * This has the added benefit of removing outputs that may have been
  * orphaned by some other process.  Such outputs are not usuable by
  * the system.
  *
  * This does not guarantee that all events within an event group are
  * purged at the same time.  In such cases, the remaining events will
  * be purged with the next instance of the purge (or soon thereafter).
  * This is another nod toward efficiency over completeness of old 
  * data that's circling the bit bucket anyway.
  */
BEGIN

    DELETE FROM action_trigger.event WHERE id IN (
        SELECT evt.id
        FROM action_trigger.event evt
        JOIN action_trigger.event_definition def ON (def.id = evt.event_def)
        WHERE def.retention_interval IS NOT NULL 
            AND evt.state <> 'pending'
            AND evt.update_time < (NOW() - def.retention_interval)
    );

    WITH linked_outputs AS (
        SELECT templates.id AS id FROM (
            SELECT DISTINCT(template_output) AS id
                FROM action_trigger.event WHERE template_output IS NOT NULL
            UNION
            SELECT DISTINCT(error_output) AS id
                FROM action_trigger.event WHERE error_output IS NOT NULL
            UNION
            SELECT DISTINCT(async_output) AS id
                FROM action_trigger.event WHERE async_output IS NOT NULL
        ) templates
    ) DELETE FROM action_trigger.event_output
        WHERE id NOT IN (SELECT id FROM linked_outputs);

END;

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.copy_alert_suppress

actor.copy_alert_suppress Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org integer NOT NULL
config.copy_alert_type.id alert_type integer NOT NULL

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_0_note text
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_1_note text
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_2_note text
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_3_note text
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_4_note text
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_5_note text
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?
dow_6_note text

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
country text NOT NULL
post_code text NOT NULL
san text
latitude double precision
longitude double precision

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
global boolean NOT NULL DEFAULT false

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
full_day boolean NOT NULL DEFAULT false
multi_day boolean NOT NULL DEFAULT false
reason text
action.emergency_closing.id emergency_closing integer

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_proximity_adjustment

actor.org_unit_proximity_adjustment Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id item_circ_lib integer
actor.org_unit.id item_owning_lib integer
asset.copy_location.id copy_location integer
actor.org_unit.id hold_pickup_lib integer
actor.org_unit.id hold_request_lib integer
pos integer NOT NULL
absolute_adjustment boolean NOT NULL DEFAULT false
prox_adjustment numeric
config.circ_modifier.code circ_mod text

 

actor.org_unit_proximity_adjustment Constraints
Name Constraint
prox_adj_criterium CHECK ((COALESCE((item_circ_lib)::text, (item_owning_lib)::text, (copy_location)::text, (hold_pickup_lib)::text, (hold_request_lib)::text, circ_mod) IS NOT NULL))
prox_adj_circ_lib_idx item_circ_lib prox_adj_circ_mod_idx circ_mod prox_adj_copy_location_idx copy_location prox_adj_owning_lib_idx item_owning_lib prox_adj_pickup_lib_idx hold_pickup_lib prox_adj_request_lib_idx hold_request_lib

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.passwd

actor.passwd Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer UNIQUE#1 NOT NULL
salt text
passwd text NOT NULL
actor.passwd_type.code passwd_type text UNIQUE#1 NOT NULL
create_date timestamp with time zone NOT NULL DEFAULT now()
edit_date timestamp with time zone NOT NULL DEFAULT now()

Index - Schema actor


Table: actor.passwd_type

actor.passwd_type Structure
F-Key Name Type Description
code text PRIMARY KEY
name text UNIQUE NOT NULL
login boolean NOT NULL DEFAULT false
regex text
crypt_algo text
iter_count integer

 

actor.passwd_type Constraints
Name Constraint
passwd_type_iter_count_check CHECK (((iter_count IS NULL) OR (iter_count > 0)))

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.search_filter_group

actor.search_filter_group Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#2 UNIQUE#1 NOT NULL
code text UNIQUE#2 NOT NULL
label text UNIQUE#1 NOT NULL
create_date timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.search_filter_group_entry

actor.search_filter_group_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.search_filter_group.id grp integer UNIQUE#1 NOT NULL
pos integer NOT NULL
actor.search_query.id query integer UNIQUE#1 NOT NULL

Index - Schema actor


Table: actor.search_query

actor.search_query Structure
F-Key Name Type Description
id serial PRIMARY KEY
label text NOT NULL
query_text text NOT NULL

Tables referencing this one via Foreign Key Constraints:

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
guardian text
pref_prefix text
pref_first_given_name text
pref_second_given_name text
pref_family_name text
pref_suffix text
name_keywords text
name_kw_tsvector tsvector
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 date
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
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
config.i18n_locale.code locale text

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_family_name_unaccent_idx unaccent_and_squash(family_name) actor_usr_first_given_name_idx lowercase(first_given_name) actor_usr_first_given_name_unaccent_idx unaccent_and_squash(first_given_name) actor_usr_guardian_idx lowercase(guardian) actor_usr_guardian_unaccent_idx unaccent_and_squash(guardian) 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_pref_family_name_idx lowercase(pref_family_name) actor_usr_pref_family_name_unaccent_idx unaccent_and_squash(pref_family_name) actor_usr_pref_first_given_name_idx lowercase(pref_first_given_name) actor_usr_pref_first_given_name_unaccent_idx unaccent_and_squash(pref_first_given_name) actor_usr_pref_second_given_name_idx lowercase(pref_second_given_name) actor_usr_pref_second_given_name_unaccent_idx unaccent_and_squash(pref_second_given_name) actor_usr_second_given_name_idx lowercase(second_given_name) actor_usr_second_given_name_unaccent_idx unaccent_and_squash(second_given_name) actor_usr_usrgroup_idx usrgroup actor_usr_usrname_idx lowercase(usrname) actor_usr_usrname_unaccent_idx unaccent_and_squash(usrname)

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()
usr_activity_usr_idx usr

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
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_message

actor.usr_message Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer NOT NULL
title text
message text NOT NULL
create_date timestamp with time zone NOT NULL DEFAULT now()
deleted boolean NOT NULL DEFAULT false
read_date timestamp with time zone
actor.org_unit.id sending_lib integer NOT NULL
pub boolean NOT NULL DEFAULT false
stop_date timestamp with time zone
actor.usr.id editor bigint
edit_date timestamp with time zone

Tables referencing this one via Foreign Key Constraints:

aum_editor editor aum_usr usr

Index - Schema actor


View: actor.usr_message_limited

actor.usr_message_limited Structure
F-Key Name Type Description
id integer
usr integer
title text
message text
create_date timestamp with time zone
deleted boolean
read_date timestamp with time zone
sending_lib integer
pub boolean
stop_date timestamp with time zone
editor bigint
edit_date timestamp with time zone
SELECT usr_message.id
,
    usr_message.usr
,
    usr_message.title
,
    usr_message.message
,
    usr_message.create_date
,
    usr_message.deleted
,
    usr_message.read_date
,
    usr_message.sending_lib
,
    usr_message.pub
,
    usr_message.stop_date
,
    usr_message.editor
,
    usr_message.edit_date
   
FROM actor.usr_message
  
WHERE (usr_message.pub 
   AND (NOT usr_message.deleted)
);

Index - Schema actor


View: actor.usr_message_penalty

actor.usr_message_penalty Structure
F-Key Name Type Description
id integer
ausp_id integer
aum_id integer
org_unit integer
ausp_org_unit integer
aum_sending_lib integer
usr integer
ausp_usr integer
aum_usr integer
standing_penalty integer
staff integer
create_date timestamp with time zone
ausp_set_date timestamp with time zone
aum_create_date timestamp with time zone
stop_date timestamp with time zone
ausp_stop_date timestamp with time zone
aum_stop_date timestamp with time zone
ausp_usr_message bigint
title text
message text
deleted boolean
read_date timestamp with time zone
pub boolean
editor bigint
edit_date timestamp with time zone
SELECT ausp.id
,
    ausp.id AS ausp_id
,
    aum.id AS aum_id
,
    ausp.org_unit
,
    ausp.org_unit AS ausp_org_unit
,
    aum.sending_lib AS aum_sending_lib
,
    ausp.usr
,
    ausp.usr AS ausp_usr
,
    aum.usr AS aum_usr
,
    ausp.standing_penalty
,
    ausp.staff
,
    ausp.set_date AS create_date
,
    ausp.set_date AS ausp_set_date
,
    aum.create_date AS aum_create_date
,
    ausp.stop_date
,
    ausp.stop_date AS ausp_stop_date
,
    aum.stop_date AS aum_stop_date
,
    ausp.usr_message AS ausp_usr_message
,
    aum.title
,
    aum.message
,
    aum.deleted
,
    aum.read_date
,
    aum.pub
,
    aum.editor
,
    aum.edit_date
   
FROM (actor.usr_standing_penalty ausp
     
LEFT JOIN actor.usr_message aum 
    ON (
           (ausp.usr_message = aum.id)
     )
)
UNION ALL
 
SELECT aum.id
,
    NULL::integer AS ausp_id
,
    aum.id AS aum_id
,
    aum.sending_lib AS org_unit
,
    NULL::integer AS ausp_org_unit
,
    aum.sending_lib AS aum_sending_lib
,
    aum.usr
,
    NULL::integer AS ausp_usr
,
    aum.usr AS aum_usr
,
    NULL::integer AS standing_penalty
,
    NULL::integer AS staff
,
    aum.create_date
,
    NULL::timestamp with time zone AS ausp_set_date
,
    aum.create_date AS aum_create_date
,
    aum.stop_date
,
    NULL::timestamp with time zone AS ausp_stop_date
,
    aum.stop_date AS aum_stop_date
,
    NULL::integer AS ausp_usr_message
,
    aum.title
,
    aum.message
,
    aum.deleted
,
    aum.read_date
,
    aum.pub
,
    aum.editor
,
    aum.edit_date
   
FROM (actor.usr_message aum
     
LEFT JOIN actor.usr_standing_penalty ausp 
    ON (
           (ausp.usr_message = aum.id)
     )
)
  
WHERE (
     (NOT aum.deleted)
   AND (ausp.id IS NULL)
);

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_privacy_waiver

actor.usr_privacy_waiver Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr bigint NOT NULL
name text NOT NULL
place_holds boolean DEFAULT false
pickup_holds boolean DEFAULT false
view_history boolean DEFAULT false
checkout_items boolean DEFAULT false
actor_usr_privacy_waiver_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
actor.usr_message.id usr_message bigint
actor_usr_standing_penalty_staff_idx staff actor_usr_standing_penalty_usr_idx usr usr_standing_penalty_usr_message_idx usr_message

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


Table: actor.workstation_setting

actor.workstation_setting Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.workstation.id workstation integer UNIQUE#1 NOT NULL
config.workstation_setting_type.name name text UNIQUE#1 NOT NULL
value json NOT NULL
actor_workstation_setting_workstation_idx workstation

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;
    max_lost            permission.grp_penalty_threshold%ROWTYPE;
    max_longoverdue     permission.grp_penalty_threshold%ROWTYPE;
    tmp_grp             INT;
    items_overdue       INT;
    items_out           INT;
    items_lost          INT;
    items_longoverdue   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_AGG(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 (
                    SELECT 'MAXFINES'::TEXT
                    UNION ALL
                    SELECT 'LONGOVERDUE'::TEXT
                    UNION ALL
                    SELECT 'LOST'::TEXT
                    WHERE 'true' ILIKE
                    (
                        SELECT CASE
                            WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
                            ELSE 'false'
                        END
                    )
                    UNION ALL
                    SELECT 'CLAIMSRETURNED'::TEXT
                    WHERE 'false' ILIKE
                    (
                        SELECT CASE
                            WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
                            ELSE 'false'
                        END
                    )
                    ) OR circ.stop_fines IS NULL)
                AND xact_finish 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 max lost
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

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

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

            IF max_lost.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_lost.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_lost.threshold IS NOT NULL THEN

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

        SELECT  INTO items_lost COUNT(*)
        FROM  action.circulation circ
            JOIN  actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
        WHERE circ.usr = match_user
            AND circ.checkin_time IS NULL
            AND (circ.stop_fines = 'LOST')
            AND xact_finish IS NULL;

        IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_lost.org_unit;
            new_sp_row.standing_penalty := 5;
            RETURN NEXT new_sp_row;
        END IF;
    END IF;

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

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

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

            IF max_longoverdue.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_longoverdue.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_longoverdue.threshold IS NOT NULL THEN

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

        SELECT INTO items_longoverdue COUNT(*)
        FROM action.circulation circ
            JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp 
                ON (circ.circ_lib = fp.id)
        WHERE circ.usr = match_user
            AND circ.checkin_time IS NULL
            AND (circ.stop_fines = 'LONGOVERDUE')
            AND xact_finish IS NULL;

        IF items_longoverdue >= max_longoverdue.threshold::INT 
                AND 0 < max_longoverdue.threshold::INT THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_longoverdue.org_unit;
            new_sp_row.standing_penalty := 35;
            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_AGG(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_AGG(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.change_password(pw_type integer, new_pw text, user_id text)

Returns: void

Language: PLPGSQL

Allows setting a salted password for a user by passing actor.usr id and the text of the password.

DECLARE
    new_salt TEXT;
BEGIN
    SELECT actor.create_salt(pw_type) INTO new_salt;

    IF pw_type = 'main' THEN
        -- Only 'main' passwords are required to have
        -- the extra layer of MD5 hashing.
        PERFORM actor.set_passwd(
            user_id, pw_type, md5(new_salt || md5(new_pw)), new_salt
        );

    ELSE
        PERFORM actor.set_passwd(user_id, pw_type, new_pw, new_salt);
    END IF;
END;

Function: actor.create_salt(pw_type text)

Returns: text

Language: PLPGSQL

DECLARE
    type_row actor.passwd_type%ROWTYPE;
BEGIN
    /* Returns a new salt based on the passwd_type encryption settings.
     * Returns NULL If the password type is not crypt()'ed.
     */

    SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;

    IF NOT FOUND THEN
        RETURN EXCEPTION 'No such password type: %', pw_type;
    END IF;

    IF type_row.iter_count IS NULL THEN
        -- This password type is unsalted.  That's OK.
        RETURN NULL;
    END IF;

    RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
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.get_cascade_setting(workstation_id text, user_id integer, org_id integer, setting_name integer)

Returns: cascade_setting_summary

Language: PLPGSQL

DECLARE
    setting_value JSON;
    summary actor.cascade_setting_summary;
    org_setting_type config.org_unit_setting_type%ROWTYPE;
BEGIN

    summary.name := setting_name;

    -- Collect the org setting type status first in case we exit early.
    -- The existance of an org setting type is not considered
    -- privileged information.
    SELECT INTO org_setting_type * 
        FROM config.org_unit_setting_type WHERE name = setting_name;
    IF FOUND THEN
        summary.has_org_setting := TRUE;
    ELSE
        summary.has_org_setting := FALSE;
    END IF;

    -- User and workstation settings have the same priority.
    -- Start with user settings since that's the simplest code path.
    -- The workstation_id is ignored if no user_id is provided.
    IF user_id IS NOT NULL THEN

        SELECT INTO summary.value value FROM actor.usr_setting
            WHERE usr = user_id AND name = setting_name;

        IF FOUND THEN
            -- if we have a value, we have a setting type
            summary.has_user_setting := TRUE;

            IF workstation_id IS NOT NULL THEN
                -- Only inform the caller about the workstation
                -- setting type disposition when a workstation id is
                -- provided.  Otherwise, it's NULL to indicate UNKNOWN.
                summary.has_workstation_setting := FALSE;
            END IF;

            RETURN summary;
        END IF;

        -- no user setting value, but a setting type may exist
        SELECT INTO summary.has_user_setting EXISTS (
            SELECT TRUE FROM config.usr_setting_type 
            WHERE name = setting_name
        );

        IF workstation_id IS NOT NULL THEN 

            IF NOT summary.has_user_setting THEN
                -- A workstation setting type may only exist when a user
                -- setting type does not.

                SELECT INTO summary.value value 
                    FROM actor.workstation_setting         
                    WHERE workstation = workstation_id AND name = setting_name;

                IF FOUND THEN
                    -- if we have a value, we have a setting type
                    summary.has_workstation_setting := TRUE;
                    RETURN summary;
                END IF;

                -- no value, but a setting type may exist
                SELECT INTO summary.has_workstation_setting EXISTS (
                    SELECT TRUE FROM config.workstation_setting_type 
                    WHERE name = setting_name
                );
            END IF;

            -- Finally make use of the workstation to determine the org
            -- unit if none is provided.
            IF org_id IS NULL AND summary.has_org_setting THEN
                SELECT INTO org_id owning_lib 
                    FROM actor.workstation WHERE id = workstation_id;
            END IF;
        END IF;
    END IF;

    -- Some org unit settings are protected by a view permission.
    -- First see if we have any data that needs protecting, then 
    -- check the permission if needed.

    IF NOT summary.has_org_setting THEN
        RETURN summary;
    END IF;

    -- avoid putting the value into the summary until we confirm
    -- the value should be visible to the caller.
    SELECT INTO setting_value value 
        FROM actor.org_unit_ancestor_setting(setting_name, org_id);

    IF NOT FOUND THEN
        -- No value found -- perm check is irrelevant.
        RETURN summary;
    END IF;

    IF org_setting_type.view_perm IS NOT NULL THEN

        IF user_id IS NULL THEN
            RAISE NOTICE 'Perm check required but no user_id provided';
            RETURN summary;
        END IF;

        IF NOT permission.usr_has_perm(
            user_id, (SELECT code FROM permission.perm_list 
                WHERE id = org_setting_type.view_perm), org_id) 
        THEN
            RAISE NOTICE 'Perm check failed for user % on %',
                user_id, org_setting_type.view_perm;
            RETURN summary;
        END IF;
    END IF;

    -- Perm check succeeded or was not necessary.
    summary.value := setting_value;
    RETURN summary;
END;

Function: actor.get_cascade_setting_batch(workstation_id text[], user_id integer, org_id integer, setting_names integer)

Returns: SET OF cascade_setting_summary

Language: PLPGSQL

-- Returns a row per setting matching the setting name order.  If no 
-- value is applied, NULL is returned to retain name-response ordering.
DECLARE
    setting_name TEXT;
    summary actor.cascade_setting_summary;
BEGIN
    FOREACH setting_name IN ARRAY setting_names LOOP
        SELECT INTO summary * FROM actor.get_cascade_setting(
            setting_Name, org_id, user_id, workstation_id);
        RETURN NEXT summary;
    END LOOP;
END;

Function: actor.get_salt(pw_type integer, pw_usr text)

Returns: text

Language: PLPGSQL

DECLARE
    pw_salt TEXT;
    type_row actor.passwd_type%ROWTYPE;
BEGIN
    /* Returns the salt for the requested user + type.  If the password 
     * type of "main" is requested and no password exists in actor.passwd, 
     * the user's existing password is migrated and the new salt is returned.
     * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
     */

    SELECT INTO pw_salt salt FROM actor.passwd 
        WHERE usr = pw_usr AND passwd_type = pw_type;

    IF FOUND THEN
        RETURN pw_salt;
    END IF;

    IF pw_type = 'main' THEN
        -- Main password has not yet been migrated. 
        -- Do it now and return the newly created salt.
        RETURN actor.migrate_passwd(pw_usr);
    END IF;

    -- We have no salt to return.  actor.create_salt() needed.
    RETURN NULL;
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.migrate_passwd(pw_usr integer)

Returns: text

Language: PLPGSQL

DECLARE
    pw_salt TEXT;
    usr_row actor.usr%ROWTYPE;
BEGIN
    /* Migrates legacy actor.usr.passwd value to actor.passwd with 
     * a password type 'main' and returns the new salt.  For backwards
     * compatibility with existing CHAP-style API's, we perform a 
     * layer of intermediate MD5(MD5()) hashing.  This is intermediate
     * hashing is not required of other passwords.
     */

    -- Avoid calling get_salt() here, because it may result in a 
    -- migrate_passwd() call, creating a loop.
    SELECT INTO pw_salt salt FROM actor.passwd 
        WHERE usr = pw_usr AND passwd_type = 'main';

    -- Only migrate passwords that have not already been migrated.
    IF FOUND THEN
        RETURN pw_salt;
    END IF;

    SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;

    pw_salt := actor.create_salt('main');

    PERFORM actor.set_passwd(
        pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);

    -- clear the existing password
    UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;

    RETURN pw_salt;
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_ancestor_setting_batch(setting_names integer, org_id text[])

Returns: SET OF org_unit_setting

Language: PLPGSQL

For each setting name passed, search "up" the org_unit tree until we find the first occurrence of an org_unit_setting with the given name.

DECLARE
    setting RECORD;
    setting_name TEXT;
    cur_org INT;
BEGIN
    FOREACH setting_name IN ARRAY setting_names
    LOOP
        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;
    END LOOP;
    RETURN;
END;

Function: actor.org_unit_ancestor_setting_batch_by_org(org_ids text, setting_name integer[])

Returns: SET OF org_unit_setting

Language: PLPGSQL

DECLARE
    setting RECORD;
    org_id INTEGER;
BEGIN
    /*  Returns one actor.org_unit_setting row per org unit ID provided.
        When no setting exists for a given org unit, the setting row
        will contain all empty values. */
    FOREACH org_id IN ARRAY org_ids LOOP
        SELECT INTO setting * FROM 
            actor.org_unit_ancestor_setting(setting_name, org_id);
        RETURN NEXT setting;
    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  aou.*
      FROM  actor.org_unit AS aou
            JOIN (
                (SELECT au.id, t.depth FROM actor.org_unit_ancestors($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id))
                    UNION
                (SELECT au.id, t.depth FROM actor.org_unit_descendants($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id))
            ) AS ad ON (aou.id=ad.id)
      ORDER BY ad.depth;

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.permit_remoteauth(userid text, profile_name bigint)

Returns: text

Language: PLPGSQL

DECLARE
    usr               actor.usr%ROWTYPE;
    profile           config.remoteauth_profile%ROWTYPE;
    perm              TEXT;
    context_org_list  INT[];
    home_prox         INT;
    block             TEXT;
    penalty_count     INT;
BEGIN

    SELECT INTO usr * FROM actor.usr WHERE id = userid AND NOT deleted;
    IF usr IS NULL THEN
        RETURN 'not_found';
    END IF;

    IF usr.barred IS TRUE THEN
        RETURN 'blocked';
    END IF;

    SELECT INTO profile * FROM config.remoteauth_profile WHERE name = profile_name;
    SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( profile.context_org );

    -- user's home library must be within the context org
    IF profile.restrict_to_org IS TRUE AND usr.home_ou NOT IN (SELECT * FROM UNNEST(context_org_list)) THEN
        RETURN 'not_found';
    END IF;

    SELECT INTO perm code FROM permission.perm_list WHERE id = profile.perm;
    IF permission.usr_has_perm(usr.id, perm, profile.context_org) IS FALSE THEN
        RETURN 'not_found';
    END IF;
    
    IF usr.expire_date < NOW() AND profile.allow_expired IS FALSE THEN
        RETURN 'expired';
    END IF;

    IF usr.active IS FALSE AND profile.allow_inactive IS FALSE THEN
        RETURN 'blocked';
    END IF;

    -- Proximity of user's home_ou to context_org to see if penalties should be ignored.
    SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = usr.home_ou AND to_org = profile.context_org;

    -- Loop through the block list to see if the user has any matching penalties.
    IF profile.block_list IS NOT NULL THEN
        FOR block IN SELECT UNNEST(STRING_TO_ARRAY(profile.block_list, '|')) LOOP
            SELECT INTO penalty_count COUNT(DISTINCT csp.*)
                FROM  actor.usr_standing_penalty usp
                        JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
                WHERE usp.usr = usr.id
                        AND usp.org_unit IN ( SELECT * FROM UNNEST(context_org_list) )
                        AND ( usp.stop_date IS NULL or usp.stop_date > NOW() )
                        AND ( csp.ignore_proximity IS NULL OR csp.ignore_proximity < home_prox )
                        AND csp.block_list ~ block;
            IF penalty_count > 0 THEN
                -- User has penalties that match this block, so auth is not permitted.
                -- Don't bother testing the rest of the block list.
                RETURN 'blocked';
            END IF;
        END LOOP;
    END IF;

    -- User has passed all tests.
    RETURN 'success';

END;

Function: actor.purge_usr_activity_by_type(act_type integer)

Returns: void

Language: PLPGSQL

DECLARE
    cur_usr INTEGER;
BEGIN
    FOR cur_usr IN SELECT DISTINCT(usr)
        FROM actor.usr_activity WHERE etype = act_type LOOP
        DELETE FROM actor.usr_activity WHERE id IN (
            SELECT id
            FROM actor.usr_activity
            WHERE usr = cur_usr AND etype = act_type
            ORDER BY event_time DESC OFFSET 1
        );

    END LOOP;
END 

Function: actor.restrict_usr_message_limited()

Returns: trigger

Language: PLPGSQL

BEGIN
    IF TG_OP = 'UPDATE' THEN
        UPDATE actor.usr_message
        SET    read_date = NEW.read_date,
               deleted   = NEW.deleted
        WHERE  id = NEW.id;
        RETURN NEW;
    END IF;
    RETURN NULL;
END;

Function: actor.set_passwd(new_salt integer, new_pass text, pw_type text, pw_usr text)

Returns: boolean

Language: PLPGSQL

DECLARE
    pw_salt TEXT;
    pw_text TEXT;
BEGIN
    /* Sets the password value, creating a new actor.passwd row if needed.
     * If the password type supports it, the new_pass value is crypt()'ed.
     * For crypt'ed passwords, the salt comes from one of 3 places in order:
     * new_salt (if present), existing salt (if present), newly created 
     * salt.
     */

    IF new_salt IS NOT NULL THEN
        pw_salt := new_salt;
    ELSE 
        pw_salt := actor.get_salt(pw_usr, pw_type);

        IF pw_salt IS NULL THEN
            /* We have no salt for this user + type.  Assume they want a 
             * new salt.  If this type is unsalted, create_salt() will 
             * return NULL. */
            pw_salt := actor.create_salt(pw_type);
        END IF;
    END IF;

    IF pw_salt IS NULL THEN 
        pw_text := new_pass; -- unsalted, use as-is.
    ELSE
        pw_text := CRYPT(new_pass, pw_salt);
    END IF;

    UPDATE actor.passwd 
        SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
        WHERE usr = pw_usr AND passwd_type = pw_type;

    IF NOT FOUND THEN
        -- no password row exists for this user + type.  Create one.
        INSERT INTO actor.passwd (usr, passwd_type, salt, passwd) 
            VALUES (pw_usr, pw_type, pw_salt, pw_text);
    END IF;

    RETURN TRUE;
END;

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.user_ingest_name_keywords()

Returns: trigger

Language: PLPGSQL

BEGIN
    NEW.name_kw_tsvector := TO_TSVECTOR(
        COALESCE(NEW.prefix, '')                || ' ' || 
        COALESCE(NEW.first_given_name, '')      || ' ' || 
        COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' || 
        COALESCE(NEW.second_given_name, '')     || ' ' || 
        COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' || 
        COALESCE(NEW.family_name, '')           || ' ' || 
        COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' || 
        COALESCE(NEW.suffix, '')                || ' ' || 
        COALESCE(NEW.pref_prefix, '')            || ' ' || 
        COALESCE(NEW.pref_first_given_name, '')  || ' ' || 
        COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' || 
        COALESCE(NEW.pref_second_given_name, '') || ' ' || 
        COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' || 
        COALESCE(NEW.pref_family_name, '')       || ' ' || 
        COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' || 
        COALESCE(NEW.pref_suffix, '')            || ' ' || 
        COALESCE(NEW.name_keywords, '')
    );
    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.u