Chapter 1. Database Schema

Table of Contents

Schema acq
Schema action
Schema action_trigger
Schema actor
Schema asset
Schema auditor
Schema authority
Schema biblio
Schema booking
Schema config
Schema container
Schema evergreen
Schema extend_reporter
Schema metabib
Schema money
Schema offline
Schema permission
Schema public
Schema query
Schema reporter
Schema search
Schema serial
Schema staging
Schema stats
Schema unapi
Schema vandelay

This is the schema for the Evergreen database.

Schema acq

Table: acq_lineitem_history

Columns: 

field name : datatype -- parameters, contraints and notes
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 --

Indexes: 

acq_lineitem_hist_id_idx : id

View: acq_lineitem_lifecycle

Columns: 

field name : datatype -- parameters, contraints and notes
?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 --

Table: acq_purchase_order_history

Columns: 

field name : datatype -- parameters, contraints and notes
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,

Indexes: 

acq_po_hist_id_idx : id

View: acq_purchase_order_lifecycle

Columns: 

field name : datatype -- parameters, contraints and notes
?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 --

View: all_fund_allocation_total

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

View: all_fund_combined_balance

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

View: all_fund_encumbrance_total

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

View: all_fund_spent_balance

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

View: all_fund_spent_total

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

Table: cancel_reason

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org_unit : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
label : text -- UNIQUE#1, NOT NULL,
description : text -- NOT NULL,
keep_debits : boolean -- NOT NULL, DEFAULT false,

Tables referencing acq.lineitem via Foreign Key Constraints: 

acq.lineitemacq.lineitem_detail
acq.purchase_orderacq.user_request

Table: claim

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
type : integer -- NOT NULL, REFERENCES acq.claim_type.
lineitem_detail : bigint -- NOT NULL, REFERENCES acq.lineitem_detail.

Indexes: 

claim_lid_idx : lineitem_detail

Tables referencing acq.claim_event via Foreign Key Constraints: 

acq.claim_event 

Table: claim_event

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
type : integer -- NOT NULL, REFERENCES acq.claim_event_type.
claim : serial -- NOT NULL, REFERENCES acq.claim.
event_date : timestamp with time zone -- NOT NULL, DEFAULT now(),
creator : integer -- NOT NULL, REFERENCES actor.usr.
note : text --

Indexes: 

claim_event_claim_date_idx : claim, event_date

Table: claim_event_type

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org_unit : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
code : text -- UNIQUE#1, NOT NULL,
description : text -- NOT NULL,
library_initiated : boolean -- NOT NULL, DEFAULT false,

Tables referencing acq.claim_event via Foreign Key Constraints: 

acq.claim_eventacq.claim_policy_action
acq.serial_claim_event 

Table: claim_policy

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org_unit : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
name : text -- UNIQUE#1, NOT NULL,
description : text -- NOT NULL,

Tables referencing acq.claim_policy_action via Foreign Key Constraints: 

acq.claim_policy_actionacq.lineitem
acq.provider 

Table: claim_policy_action

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
claim_policy : integer -- UNIQUE#1, NOT NULL, REFERENCES acq.claim_policy.
action_interval : interval -- UNIQUE#1, NOT NULL,
action : integer -- NOT NULL, REFERENCES acq.claim_event_type.

Table: claim_type

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org_unit : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
code : text -- UNIQUE#1, NOT NULL,
description : text -- NOT NULL,

Tables referencing acq.claim via Foreign Key Constraints: 

acq.claimacq.serial_claim

Table: currency_type

Columns: 

field name : datatype -- parameters, contraints and notes
code : text -- PRIMARY KEY,
label : text --

Tables referencing acq.exchange_rate via Foreign Key Constraints: 

acq.exchange_rateacq.fund
acq.fund_debitacq.funding_source
acq.provider 

Table: debit_attribution

Columns: 

field name : datatype -- parameters, contraints and notes
id : integer -- PRIMARY KEY,
fund_debit : integer -- NOT NULL, REFERENCES acq.fund_debit.
debit_amount : numeric -- NOT NULL,
funding_source_credit : integer -- REFERENCES acq.funding_source_credit.
credit_amount : numeric --

Indexes: 

acq_attribution_credit_idx : funding_source_credit
acq_attribution_debit_idx : fund_debit

Table: distribution_formula

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
owner : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
name : text -- UNIQUE#1, NOT NULL,
skip_count : integer -- NOT NULL,

Tables referencing acq.distribution_formula_application via Foreign Key Constraints: 

acq.distribution_formula_applicationacq.distribution_formula_entry

Table: distribution_formula_application

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
creator : integer -- NOT NULL, REFERENCES actor.usr.
create_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
formula : integer -- NOT NULL, REFERENCES acq.distribution_formula.
lineitem : integer -- NOT NULL, REFERENCES acq.lineitem.

Indexes: 

acqdfa_creator_idx : creator
acqdfa_df_idx : formula
acqdfa_li_idx : lineitem

Table: distribution_formula_entry

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
formula : integer -- UNIQUE#1, NOT NULL, REFERENCES acq.distribution_formula.
position : integer -- UNIQUE#1, NOT NULL,
item_count : integer -- NOT NULL,
owning_lib : integer -- REFERENCES actor.org_unit.
location : integer -- REFERENCES asset.copy_location.

Constraints: 

acqdfe_must_be_somewhere : CHECK (((owning_lib IS NOT NULL) OR (location IS NOT NULL)))

Table: edi_account

Columns: 

field name : datatype -- parameters, contraints and notes
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 --
provider : integer -- NOT NULL, REFERENCES acq.provider.
in_dir : text --
vendcode : text --
vendacct : text --

Tables referencing acq.edi_message via Foreign Key Constraints: 

acq.edi_messageacq.provider

Table: edi_message

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
account : integer -- REFERENCES acq.edi_account.
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 --
purchase_order : integer -- REFERENCES acq.purchase_order.
message_type : text -- NOT NULL,

Constraints: 

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])))

Table: exchange_rate

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
from_currency : text -- UNIQUE#1, NOT NULL, REFERENCES acq.currency_type.
to_currency : text -- UNIQUE#1, NOT NULL, REFERENCES acq.currency_type.
ratio : numeric -- NOT NULL,

Table: fiscal_calendar

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
name : text -- NOT NULL,

Tables referencing acq.fiscal_year via Foreign Key Constraints: 

acq.fiscal_yearactor.org_unit

Table: fiscal_year

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
calendar : integer -- UNIQUE#1, UNIQUE#2, NOT NULL, REFERENCES acq.fiscal_calendar.
year : integer -- UNIQUE#1, NOT NULL,
year_begin : timestamp with time zone -- UNIQUE#2, NOT NULL,
year_end : timestamp with time zone -- NOT NULL,

Table: fund

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org : integer -- UNIQUE#2, UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
name : text -- UNIQUE#1, NOT NULL,
year : integer -- UNIQUE#2, UNIQUE#1, NOT NULL, DEFAULT date_part('year'::text, now()),
currency_type : text -- NOT NULL, REFERENCES acq.currency_type.
code : text -- UNIQUE#2,
rollover : boolean -- NOT NULL, DEFAULT false,
propagate : boolean -- NOT NULL, DEFAULT true,
active : boolean -- NOT NULL, DEFAULT true,
balance_warning_percent : integer --
balance_stop_percent : integer --

Constraints: 

acq_fund_rollover_implies_propagate : CHECK ((propagate OR (NOT rollover)))

Tables referencing acq.fund_allocation via Foreign Key Constraints: 

acq.fund_allocationacq.fund_debit
acq.fund_tag_mapacq.fund_transfer
acq.invoice_itemacq.lineitem_detail
acq.po_item 

Table: fund_allocation

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
funding_source : integer -- NOT NULL, REFERENCES acq.funding_source.
fund : integer -- NOT NULL, REFERENCES acq.fund.
amount : numeric -- NOT NULL,
allocator : integer -- NOT NULL, REFERENCES actor.usr.
note : text --
create_time : timestamp with time zone -- NOT NULL, DEFAULT now(),

Indexes: 

fund_alloc_allocator_idx : allocator

Table: fund_allocation_percent

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
funding_source : integer -- UNIQUE#1, NOT NULL, REFERENCES acq.funding_source.
org : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
fund_code : text -- UNIQUE#1,
percent : numeric -- NOT NULL,
allocator : integer -- NOT NULL, REFERENCES actor.usr.
note : text --
create_time : timestamp with time zone -- NOT NULL, DEFAULT now(),

Constraints: 

percentage_range : CHECK (((percent >= (0)::numeric) AND (percent <= (100)::numeric)))

View: fund_allocation_total

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric(100,2) --

View: fund_combined_balance

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

Table: fund_debit

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
fund : integer -- NOT NULL, REFERENCES acq.fund.
origin_amount : numeric -- NOT NULL,
origin_currency_type : text -- NOT NULL, REFERENCES acq.currency_type.
amount : numeric -- NOT NULL,
encumbrance : boolean -- NOT NULL, DEFAULT true,
debit_type : text -- NOT NULL,
xfer_destination : integer -- REFERENCES acq.fund.
create_time : timestamp with time zone -- NOT NULL, DEFAULT now(),

Tables referencing acq.debit_attribution via Foreign Key Constraints: 

acq.debit_attributionacq.invoice_item
acq.lineitem_detailacq.po_item

View: fund_debit_total

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

View: fund_encumbrance_total

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

View: fund_spent_balance

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

View: fund_spent_total

Columns: 

field name : datatype -- parameters, contraints and notes
fund : integer --
amount : numeric --

Table: fund_tag

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
owner : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
name : text -- UNIQUE#1, NOT NULL,

Tables referencing acq.fund_tag_map via Foreign Key Constraints: 

acq.fund_tag_map 

Table: fund_tag_map

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
fund : integer -- UNIQUE#1, NOT NULL, REFERENCES acq.fund.
tag : integer -- UNIQUE#1, REFERENCES acq.fund_tag.

Table: 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.

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
src_fund : integer -- NOT NULL, REFERENCES acq.fund.
src_amount : numeric -- NOT NULL,
dest_fund : integer -- REFERENCES acq.fund.
dest_amount : numeric --
transfer_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
transfer_user : integer -- NOT NULL, REFERENCES actor.usr.
note : text --
funding_source_credit : integer -- NOT NULL, REFERENCES acq.funding_source_credit.

Indexes: 

acqftr_usr_idx : transfer_user

Table: funding_source

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
name : text -- UNIQUE#1, NOT NULL,
owner : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
currency_type : text -- NOT NULL, REFERENCES acq.currency_type.
code : text -- UNIQUE,

Tables referencing acq.fund_allocation via Foreign Key Constraints: 

acq.fund_allocationacq.fund_allocation_percent
acq.funding_source_credit 

View: funding_source_allocation_total

Columns: 

field name : datatype -- parameters, contraints and notes
funding_source : integer --
amount : numeric(100,2) --

View: funding_source_balance

Columns: 

field name : datatype -- parameters, contraints and notes
funding_source : integer --
amount : numeric(100,2) --

Table: funding_source_credit

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
funding_source : integer -- NOT NULL, REFERENCES acq.funding_source.
amount : numeric -- NOT NULL,
note : text --
deadline_date : timestamp with time zone --
effective_date : timestamp with time zone -- NOT NULL, DEFAULT now(),

Tables referencing acq.debit_attribution via Foreign Key Constraints: 

acq.debit_attributionacq.fund_transfer

View: funding_source_credit_total

Columns: 

field name : datatype -- parameters, contraints and notes
funding_source : integer --
amount : numeric --

Table: invoice

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
receiver : integer -- NOT NULL, REFERENCES actor.org_unit.
provider : integer -- UNIQUE#1, NOT NULL, REFERENCES acq.provider.
shipper : integer -- NOT NULL, REFERENCES acq.provider.
recv_date : timestamp with time zone -- NOT NULL, DEFAULT now(),
recv_method : text -- NOT NULL, DEFAULT 'EDI'::text, REFERENCES acq.invoice_method.
inv_type : text --
inv_ident : text -- UNIQUE#1, NOT NULL,
payment_auth : text --
payment_method : text -- REFERENCES acq.invoice_payment_method.
note : text --
complete : boolean -- NOT NULL, DEFAULT false,

Tables referencing acq.invoice_entry via Foreign Key Constraints: 

acq.invoice_entryacq.invoice_item

Table: invoice_entry

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
invoice : integer -- NOT NULL, REFERENCES acq.invoice.
purchase_order : integer -- REFERENCES acq.purchase_order.
lineitem : integer -- REFERENCES acq.lineitem.
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) --

Table: invoice_item

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
invoice : integer -- NOT NULL, REFERENCES acq.invoice.
purchase_order : integer -- REFERENCES acq.purchase_order.
fund_debit : integer -- REFERENCES acq.fund_debit.
inv_item_type : text -- NOT NULL, REFERENCES acq.invoice_item_type.
title : text --
author : text --
note : text --
cost_billed : numeric(8,2) --
actual_cost : numeric(8,2) --
fund : integer -- REFERENCES acq.fund.
amount_paid : numeric(8,2) --
po_item : integer -- REFERENCES acq.po_item.
target : bigint --

Table: invoice_item_type

Columns: 

field name : datatype -- parameters, contraints and notes
code : text -- PRIMARY KEY,
name : text -- NOT NULL,
prorate : boolean -- NOT NULL, DEFAULT false,

Tables referencing acq.invoice_item via Foreign Key Constraints: 

acq.invoice_itemacq.po_item

Table: invoice_method

Columns: 

field name : datatype -- parameters, contraints and notes
code : text -- PRIMARY KEY,
name : text -- NOT NULL,

Tables referencing acq.invoice via Foreign Key Constraints: 

acq.invoice 

Table: invoice_payment_method

Columns: 

field name : datatype -- parameters, contraints and notes
code : text -- PRIMARY KEY,
name : text -- NOT NULL,

Tables referencing acq.invoice via Foreign Key Constraints: 

acq.invoice 

Table: lineitem

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
creator : integer -- NOT NULL, REFERENCES actor.usr.
editor : integer -- NOT NULL, REFERENCES actor.usr.
selector : integer -- NOT NULL, REFERENCES actor.usr.
provider : integer -- REFERENCES acq.provider.
purchase_order : integer -- REFERENCES acq.purchase_order.
picklist : integer -- REFERENCES acq.picklist.
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,
eg_bib_id : bigint -- REFERENCES biblio.record_entry.
source_label : text --
state : text -- NOT NULL, DEFAULT 'new'::text,
cancel_reason : integer -- REFERENCES acq.cancel_reason.
estimated_unit_price : numeric --
claim_policy : integer -- REFERENCES acq.claim_policy.

Constraints: 

picklist_or_po : CHECK (((picklist IS NOT NULL) OR (purchase_order IS NOT NULL)))

Indexes: 

li_creator_idx : creator
li_editor_idx : editor
li_pl_idx : picklist
li_po_idx : purchase_order
li_selector_idx : selector

Tables referencing acq.distribution_formula_application via Foreign Key Constraints: 

acq.distribution_formula_applicationacq.invoice_entry
acq.lineitem_attracq.lineitem_detail
acq.lineitem_noteacq.user_request

Table: lineitem_alert_text

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
code : text -- UNIQUE#1, NOT NULL,
description : text --
owning_lib : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.

Tables referencing acq.lineitem_note via Foreign Key Constraints: 

acq.lineitem_note 

Table: lineitem_attr

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
definition : bigint -- NOT NULL,
lineitem : bigint -- NOT NULL, REFERENCES acq.lineitem.
attr_type : text -- NOT NULL,
attr_name : text -- NOT NULL,
attr_value : text -- NOT NULL,

Indexes: 

li_attr_definition_idx : definition
li_attr_li_idx : lineitem
li_attr_value_idx : attr_value

Table: lineitem_attr_definition

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
code : text -- NOT NULL,
description : text -- NOT NULL,
remove : text -- NOT NULL, DEFAULT ''::text,
ident : boolean -- NOT NULL, DEFAULT false,

Table: lineitem_detail

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
lineitem : integer -- NOT NULL, REFERENCES acq.lineitem.
fund : integer -- REFERENCES acq.fund.
fund_debit : integer -- REFERENCES acq.fund_debit.
eg_copy_id : bigint --
barcode : text --
cn_label : text --
note : text --
collection_code : text --
circ_modifier : text -- REFERENCES config.circ_modifier.
owning_lib : integer -- REFERENCES actor.org_unit.
location : integer -- REFERENCES asset.copy_location.
recv_time : timestamp with time zone --
receiver : integer -- REFERENCES actor.usr.
cancel_reason : integer -- REFERENCES acq.cancel_reason.

Indexes: 

li_detail_li_idx : lineitem

Tables referencing acq.claim via Foreign Key Constraints: 

acq.claim 

Table: lineitem_generated_attr_definition

Columns: 

field name : datatype -- parameters, contraints and notes
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: lineitem_local_attr_definition

Columns: 

field name : datatype -- parameters, contraints and notes
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: lineitem_marc_attr_definition

Columns: 

field name : datatype -- parameters, contraints and notes
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: lineitem_note

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
lineitem : integer -- NOT NULL, REFERENCES acq.lineitem.
creator : integer -- NOT NULL, REFERENCES actor.usr.
editor : integer -- NOT NULL, REFERENCES actor.usr.
create_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
edit_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
value : text -- NOT NULL,
alert_text : integer -- REFERENCES acq.lineitem_alert_text.
vendor_public : boolean -- NOT NULL, DEFAULT false,

Indexes: 

li_note_creator_idx : creator
li_note_editor_idx : editor
li_note_li_idx : lineitem

Table: lineitem_provider_attr_definition

Columns: 

field name : datatype -- parameters, contraints and notes
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,
provider : integer -- NOT NULL, REFERENCES acq.provider.

Table: lineitem_usr_attr_definition

Columns: 

field name : datatype -- parameters, contraints and notes
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,
usr : integer -- NOT NULL, REFERENCES actor.usr.

Indexes: 

li_usr_attr_def_usr_idx : usr

View: 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.

Columns: 

field name : datatype -- parameters, contraints and notes
sort_priority : integer --
sort_date : timestamp with time zone --
id : integer --
funding_source : integer --
amount : numeric --
note : text --

Table: picklist

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
owner : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.usr.
creator : integer -- NOT NULL, REFERENCES actor.usr.
editor : integer -- NOT NULL, REFERENCES actor.usr.
org_unit : integer -- NOT NULL, REFERENCES actor.org_unit.
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(),

Indexes: 

acq_picklist_creator_idx : creator
acq_picklist_editor_idx : editor
acq_picklist_owner_idx : owner

Tables referencing acq.lineitem via Foreign Key Constraints: 

acq.lineitem 

Table: po_item

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
purchase_order : integer -- REFERENCES acq.purchase_order.
fund_debit : integer -- REFERENCES acq.fund_debit.
inv_item_type : text -- NOT NULL, REFERENCES acq.invoice_item_type.
title : text --
author : text --
note : text --
estimated_cost : numeric(8,2) --
fund : integer -- REFERENCES acq.fund.
target : bigint --

Tables referencing acq.invoice_item via Foreign Key Constraints: 

acq.invoice_item 

Table: po_note

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
purchase_order : integer -- NOT NULL, REFERENCES acq.purchase_order.
creator : integer -- NOT NULL, REFERENCES actor.usr.
editor : integer -- NOT NULL, REFERENCES actor.usr.
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,

Indexes: 

acq_po_note_creator_idx : creator
acq_po_note_editor_idx : editor
po_note_po_idx : purchase_order

Table: provider

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
name : text -- UNIQUE#1, NOT NULL,
owner : integer -- UNIQUE#2, UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
currency_type : text -- NOT NULL, REFERENCES acq.currency_type.
code : text -- UNIQUE#2, NOT NULL,
holding_tag : text --
san : text --
edi_default : integer -- REFERENCES acq.edi_account.
active : boolean -- NOT NULL, DEFAULT true,
prepayment_required : boolean -- NOT NULL, DEFAULT false,
url : text --
email : text --
phone : text --
fax_phone : text --
default_claim_policy : integer -- REFERENCES acq.claim_policy.

Tables referencing acq.edi_account via Foreign Key Constraints: 

acq.edi_accountacq.invoice
acq.lineitemacq.lineitem_provider_attr_definition
acq.provider_addressacq.provider_contact
acq.provider_holding_subfield_mapacq.provider_note
acq.purchase_order 

Table: provider_address

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
valid : boolean -- NOT NULL, DEFAULT true,
address_type : text --
provider : integer -- NOT NULL, REFERENCES acq.provider.
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 --

Table: provider_contact

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
provider : integer -- NOT NULL, REFERENCES acq.provider.
name : text -- NOT NULL,
role : text --
email : text --
phone : text --

Tables referencing acq.provider_contact_address via Foreign Key Constraints: 

acq.provider_contact_address 

Table: provider_contact_address

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
valid : boolean -- NOT NULL, DEFAULT true,
address_type : text --
contact : integer -- NOT NULL, REFERENCES acq.provider_contact.
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 --

Table: provider_holding_subfield_map

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
provider : integer -- UNIQUE#1, NOT NULL, REFERENCES acq.provider.
name : text -- UNIQUE#1, NOT NULL,
subfield : text -- NOT NULL,

Table: provider_note

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
provider : integer -- NOT NULL, REFERENCES acq.provider.
creator : integer -- NOT NULL, REFERENCES actor.usr.
editor : integer -- NOT NULL, REFERENCES actor.usr.
create_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
edit_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
value : text -- NOT NULL,

Indexes: 

acq_pro_note_creator_idx : creator
acq_pro_note_editor_idx : editor
acq_pro_note_pro_idx : provider

Table: purchase_order

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
owner : integer -- NOT NULL, REFERENCES actor.usr.
creator : integer -- NOT NULL, REFERENCES actor.usr.
editor : integer -- NOT NULL, REFERENCES actor.usr.
ordering_agency : integer -- NOT NULL, REFERENCES actor.org_unit.
create_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
edit_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
provider : integer -- NOT NULL, REFERENCES acq.provider.
state : text -- NOT NULL, DEFAULT 'new'::text,
order_date : timestamp with time zone --
name : text -- NOT NULL,
cancel_reason : integer -- REFERENCES acq.cancel_reason.
prepayment_required : boolean -- NOT NULL, DEFAULT false,

Indexes: 

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

Tables referencing acq.edi_message via Foreign Key Constraints: 

acq.edi_messageacq.invoice_entry
acq.invoice_itemacq.lineitem
acq.po_itemacq.po_note

Table: serial_claim

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
type : integer -- NOT NULL, REFERENCES acq.claim_type.
item : bigint -- NOT NULL, REFERENCES serial.item.

Indexes: 

serial_claim_lid_idx : item

Tables referencing acq.serial_claim_event via Foreign Key Constraints: 

acq.serial_claim_event 

Table: serial_claim_event

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
type : integer -- NOT NULL, REFERENCES acq.claim_event_type.
claim : serial -- NOT NULL, REFERENCES acq.serial_claim.
event_date : timestamp with time zone -- NOT NULL, DEFAULT now(),
creator : integer -- NOT NULL, REFERENCES actor.usr.
note : text --

Indexes: 

serial_claim_event_claim_date_idx : claim, event_date

Table: user_request

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
usr : integer -- NOT NULL, REFERENCES actor.usr.
hold : boolean -- NOT NULL, DEFAULT true,
pickup_lib : integer -- NOT NULL, REFERENCES actor.org_unit.
holdable_formats : text --
phone_notify : text --
email_notify : boolean -- NOT NULL, DEFAULT true,
lineitem : integer -- REFERENCES acq.lineitem.
eg_bib : bigint -- REFERENCES biblio.record_entry.
request_date : timestamp with time zone -- NOT NULL, DEFAULT now(),
need_before : timestamp with time zone --
max_fee : text --
request_type : integer -- NOT NULL, REFERENCES acq.user_request_type.
isxn : text --
title : text --
volume : text --
author : text --
article_title : text --
article_pages : text --
publisher : text --
location : text --
pubdate : text --
mentioned : text --
other_info : text --
cancel_reason : integer -- REFERENCES acq.cancel_reason.

Table: user_request_type

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
label : text -- UNIQUE, NOT NULL,

Tables referencing acq.user_request via Foreign Key Constraints: 

acq.user_request 

attribute_debits()

Function Properties
Language: PLPGSQL
Return Type: void

audit_acq_lineitem_func()

Function Properties
Language: PLPGSQL
Return Type: trigger

audit_acq_purchase_order_func()

Function Properties
Language: PLPGSQL
Return Type: trigger

create_acq_auditor(tbl text, sch text)

Function Properties
Language: PLPGSQL
Return Type: boolean

create_acq_func(tbl text, sch text)

Function Properties
Language: PLPGSQL
Return Type: boolean

create_acq_history(tbl text, sch text)

Function Properties
Language: PLPGSQL
Return Type: boolean

create_acq_lifecycle(tbl text, sch text)

Function Properties
Language: PLPGSQL
Return Type: boolean

create_acq_seq(tbl text, sch text)

Function Properties
Language: PLPGSQL
Return Type: boolean

create_acq_update_trigger(tbl text, sch text)

Function Properties
Language: PLPGSQL
Return Type: boolean

exchange_ratio(text, text, numeric)

Function Properties
Language: SQL
Return Type: numeric

exchange_ratio(to_ex text, from_ex text)

Function Properties
Language: PLPGSQL
Return Type: numeric

extract_holding_attr_table(tag integer, lineitem text)

Function Properties
Language: PLPGSQL
Return Type: SET OF flat_lineitem_holding_subfield

extract_provider_holding_data(lineitem_i integer)

Function Properties
Language: PLPGSQL
Return Type: SET OF flat_lineitem_detail

fap_limit_100()

Function Properties
Language: PLPGSQL
Return Type: trigger

find_bad_fy()

Function Properties
Language: PLPGSQL
Return Type: SET OF record

fund_alloc_percent_val()

Function Properties
Language: PLPGSQL
Return Type: trigger

po_org_name_date_unique()

Function Properties
Language: PLPGSQL
Return Type: trigger

propagate_funds_by_org_tree(org_unit_id integer, user_id integer, old_year integer)

Function Properties
Language: PLPGSQL
Return Type: void

propagate_funds_by_org_unit(org_unit_id integer, user_id integer, old_year integer)

Function Properties
Language: PLPGSQL
Return Type: void

purchase_order_name_default()

Function Properties
Language: PLPGSQL
Return Type: trigger

rollover_funds_by_org_tree(org_unit_id integer, user_id integer, old_year integer)

Function Properties
Language: PLPGSQL
Return Type: void

rollover_funds_by_org_unit(org_unit_id integer, user_id integer, old_year integer)

Function Properties
Language: PLPGSQL
Return Type: void

transfer_fund(xfer_note integer, user_id numeric, new_amount integer, new_fund numeric, old_amount integer, old_fund text)

Function Properties
Language: PLPGSQL
Return Type: void

Report any errors in this documentation using Launchpad.
Documentation for Evergreen version 2.2
Copyright © 2007-2012, Memebers of the Evergreen Project