Chapter 1. Database Schema

Chapter 1. Database Schema
Home Prev		Next

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.lineitem	acq.lineitem_detail
acq.purchase_order	acq.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_event	acq.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_action	acq.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.claim

acq.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_rate	acq.fund
acq.fund_debit	acq.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_application

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

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

actor.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_allocation	acq.fund_debit
acq.fund_tag_map	acq.fund_transfer
acq.invoice_item	acq.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_attribution	acq.invoice_item
acq.lineitem_detail	acq.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_allocation	acq.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_attribution

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

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

acq.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_application	acq.invoice_entry
acq.lineitem_attr	acq.lineitem_detail
acq.lineitem_note	acq.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_account	acq.invoice
acq.lineitem	acq.lineitem_provider_attr_definition
acq.provider_address	acq.provider_contact
acq.provider_holding_subfield_map	acq.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_message	acq.invoice_entry
acq.invoice_item	acq.lineitem
acq.po_item	acq.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