Evergreen
HomePrev   Next

Chapter 1. Schema acq

Table of Contents

Tables in acq
acq_lineitem_history
acq_lineitem_lifecycle
acq_purchase_order_history
acq_purchase_order_lifecycle
all_fund_allocation_total
all_fund_combined_balance
all_fund_encumbrance_total
all_fund_spent_balance
all_fund_spent_total
cancel_reason
claim
claim_event
claim_event_type
claim_policy
claim_policy_action
claim_type
currency_type
debit_attribution
distribution_formula
distribution_formula_application
distribution_formula_entry
edi_account
edi_attr
edi_attr_set
edi_attr_set_map
edi_message
exchange_rate
fiscal_calendar
fiscal_year
fund
fund_allocation
fund_allocation_percent
fund_allocation_total
fund_combined_balance
fund_debit
fund_debit_total
fund_encumbrance_total
fund_spent_balance
fund_spent_total
fund_tag
fund_tag_map
fund_transfer
funding_source
funding_source_allocation_total
funding_source_balance
funding_source_credit
funding_source_credit_total
invoice
invoice_entry
invoice_item
invoice_item_type
invoice_method
invoice_payment_method
lineitem
lineitem_alert_text
lineitem_attr
lineitem_attr_definition
lineitem_detail
lineitem_generated_attr_definition
lineitem_local_attr_definition
lineitem_marc_attr_definition
lineitem_note
lineitem_provider_attr_definition
lineitem_summary
lineitem_usr_attr_definition
ordered_funding_source_credit
picklist
po_item
po_note
provider
provider_address
provider_contact
provider_contact_address
provider_holding_subfield_map
provider_note
purchase_order
serial_claim
serial_claim_event
user_request
user_request_status_type
user_request_type
Functions in acq

Below are the tables, views and functions for acq

Tables in acq

acq_lineitem_history

  • 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

Indexes on acq_lineitem_history

  • acq_lineitem_hist_id_idx id
  • acq_lineitem_history_queued_record_idx queued_record

acq_lineitem_lifecycle

  • ?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

acq_purchase_order_history

  • 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 on acq_purchase_order_history

  • acq_po_hist_id_idx id

acq_purchase_order_lifecycle

  • ?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

all_fund_allocation_total

  • fund integer
  • amount numeric

all_fund_combined_balance

  • fund integer
  • amount numeric

all_fund_encumbrance_total

  • fund integer
  • amount numeric

all_fund_spent_balance

  • fund integer
  • amount numeric

all_fund_spent_total

  • fund integer
  • amount numeric

cancel_reason

  • id serial PRIMARY KEY
  • org_unit integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • label text UNIQUE #1 NOT NULL
  • description text NOT NULL
  • keep_debits boolean NOT NULL DEFAULT false

Tables referencing via foreign key constraints

  • acq.lineitem
  • acq.lineitem_detail
  • acq.purchase_order
  • acq.user_request

claim

  • id serial PRIMARY KEY
  • type integer NOT NULL REFERENCES acq.table.claim-type
  • lineitem_detail bigint NOT NULL REFERENCES acq.table.lineitem-detail

Indexes on claim

  • claim_lid_idx lineitem_detail

Tables referencing via foreign key constraints

  • acq.claim_event

claim_event

  • id bigserial PRIMARY KEY
  • type integer NOT NULL REFERENCES acq.table.claim-event-type
  • claim serial NOT NULL REFERENCES acq.table.claim
  • event_date timestamp with time zone NOT NULL DEFAULT now()
  • creator integer NOT NULL REFERENCES actor.table.usr
  • note text

Indexes on claim_event

  • claim_event_claim_date_idx claim, event_date

claim_event_type

  • id serial PRIMARY KEY
  • org_unit integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • code text UNIQUE #1 NOT NULL
  • description text NOT NULL
  • library_initiated boolean NOT NULL DEFAULT false

Tables referencing via foreign key constraints

  • acq.claim_event
  • acq.claim_policy_action
  • acq.serial_claim_event

claim_policy

  • id serial PRIMARY KEY
  • org_unit integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • name text UNIQUE #1 NOT NULL
  • description text NOT NULL

Tables referencing via foreign key constraints

  • acq.claim_policy_action
  • acq.lineitem
  • acq.provider

claim_policy_action

  • id serial PRIMARY KEY
  • claim_policy integer UNIQUE #1 NOT NULL REFERENCES acq.table.claim-policy
  • action_interval interval UNIQUE #1 NOT NULL
  • action integer NOT NULL REFERENCES acq.table.claim-event-type

claim_type

  • id serial PRIMARY KEY
  • org_unit integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • code text UNIQUE #1 NOT NULL
  • description text NOT NULL

Tables referencing via foreign key constraints

  • acq.claim
  • acq.serial_claim

currency_type

  • code text PRIMARY KEY
  • label text

Tables referencing via foreign key constraints

  • acq.exchange_rate
  • acq.fund
  • acq.fund_debit
  • acq.funding_source
  • acq.provider

debit_attribution

  • id integer PRIMARY KEY
  • fund_debit integer NOT NULL REFERENCES acq.table.fund-debit
  • debit_amount numeric NOT NULL
  • funding_source_credit integer REFERENCES acq.table.funding-source-credit
  • credit_amount numeric

Indexes on debit_attribution

  • acq_attribution_credit_idx funding_source_credit
  • acq_attribution_debit_idx fund_debit

distribution_formula

  • id serial PRIMARY KEY
  • owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • name text UNIQUE #1 NOT NULL
  • skip_count integer NOT NULL

Tables referencing via foreign key constraints

  • acq.distribution_formula_application
  • acq.distribution_formula_entry

distribution_formula_application

  • id bigserial PRIMARY KEY
  • creator integer NOT NULL REFERENCES actor.table.usr
  • create_time timestamp with time zone NOT NULL DEFAULT now()
  • formula integer NOT NULL REFERENCES acq.table.distribution-formula
  • lineitem integer NOT NULL REFERENCES acq.table.lineitem

Indexes on distribution_formula_application

  • acqdfa_creator_idx creator
  • acqdfa_df_idx formula
  • acqdfa_li_idx lineitem

distribution_formula_entry

  • id serial PRIMARY KEY
  • formula integer UNIQUE #1 NOT NULL REFERENCES acq.table.distribution-formula
  • position integer UNIQUE #1 NOT NULL
  • item_count integer NOT NULL
  • owning_lib integer REFERENCES actor.table.org-unit
  • location integer REFERENCES asset.table.copy-location
  • fund integer REFERENCES acq.table.fund
  • circ_modifier text REFERENCES config.table.circ-modifier
  • collection_code text

Constraints on distribution_formula_entry

  • acqdfe_must_be_somewhere CHECK

edi_account

  • 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.table.provider
  • in_dir text
  • vendcode text
  • vendacct text
  • attr_set integer REFERENCES acq.table.edi-attr-set
  • use_attrs boolean NOT NULL DEFAULT false

Tables referencing via foreign key constraints

  • acq.edi_message
  • acq.provider

edi_attr

  • key text PRIMARY KEY
  • label text UNIQUE NOT NULL

Tables referencing via foreign key constraints

  • acq.edi_attr_set_map

edi_attr_set

  • id serial PRIMARY KEY
  • label text UNIQUE NOT NULL

Tables referencing via foreign key constraints

  • acq.edi_account
  • acq.edi_attr_set_map

edi_attr_set_map

  • id serial PRIMARY KEY
  • attr_set integer UNIQUE #1 NOT NULL REFERENCES acq.table.edi-attr-set
  • attr text UNIQUE #1 NOT NULL REFERENCES acq.table.edi-attr

edi_message

  • id serial PRIMARY KEY
  • account integer REFERENCES acq.table.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.table.purchase-order
  • message_type text NOT NULL

Constraints on edi_message

  • status_value CHECK status = ANY (ARRAY['new'::text
  • valid_message_type CHECK message_type = ANY (ARRAY['ORDERS'::text

Indexes on edi_message

  • edi_message_account_status_idx account, status
  • edi_message_po_idx purchase_order
  • edi_message_remote_file_idx lowercase(remote_file)

exchange_rate

  • id serial PRIMARY KEY
  • from_currency text UNIQUE #1 NOT NULL REFERENCES acq.table.currency-type
  • to_currency text UNIQUE #1 NOT NULL REFERENCES acq.table.currency-type
  • ratio numeric NOT NULL

fiscal_calendar

  • id serial PRIMARY KEY
  • name text NOT NULL

Tables referencing via foreign key constraints

  • acq.fiscal_year
  • actor.org_unit

fiscal_year

  • id serial PRIMARY KEY
  • calendar integer UNIQUE #1 UNIQUE #2 NOT NULL REFERENCES acq.table.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

fund

  • id serial PRIMARY KEY
  • org integer UNIQUE #2 UNIQUE #1 NOT NULL REFERENCES actor.table.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.table.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 on fund

  • acq_fund_rollover_implies_propagate CHECK propagate OR (NOT rollover)

Tables referencing via foreign key constraints

  • acq.distribution_formula_entry
  • acq.fund_allocation
  • acq.fund_debit
  • acq.fund_tag_map
  • acq.fund_transfer
  • acq.invoice_item
  • acq.lineitem_detail
  • acq.po_item

fund_allocation

  • id serial PRIMARY KEY
  • funding_source integer NOT NULL REFERENCES acq.table.funding-source
  • fund integer NOT NULL REFERENCES acq.table.fund
  • amount numeric NOT NULL
  • allocator integer NOT NULL REFERENCES actor.table.usr
  • note text
  • create_time timestamp with time zone NOT NULL DEFAULT now()

Indexes on fund_allocation

  • fund_alloc_allocator_idx allocator

fund_allocation_percent

  • id serial PRIMARY KEY
  • funding_source integer UNIQUE #1 NOT NULL REFERENCES acq.table.funding-source
  • org integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • fund_code text UNIQUE #1
  • percent numeric NOT NULL
  • allocator integer NOT NULL REFERENCES actor.table.usr
  • note text
  • create_time timestamp with time zone NOT NULL DEFAULT now()

Constraints on fund_allocation_percent

  • percentage_range CHECK

fund_allocation_total

  • fund integer
  • amount numeric(100,2)

fund_combined_balance

  • fund integer
  • amount numeric

fund_debit

  • id serial PRIMARY KEY
  • fund integer NOT NULL REFERENCES acq.table.fund
  • origin_amount numeric NOT NULL
  • origin_currency_type text NOT NULL REFERENCES acq.table.currency-type
  • amount numeric NOT NULL
  • encumbrance boolean NOT NULL DEFAULT true
  • debit_type text NOT NULL
  • xfer_destination integer REFERENCES acq.table.fund
  • create_time timestamp with time zone NOT NULL DEFAULT now()
  • invoice_entry integer REFERENCES acq.table.invoice-entry

Indexes on fund_debit

  • fund_debit_invoice_entry_idx invoice_entry

Tables referencing via foreign key constraints

  • acq.debit_attribution
  • acq.invoice_item
  • acq.lineitem_detail
  • acq.po_item

fund_debit_total

  • fund integer
  • amount numeric

fund_encumbrance_total

  • fund integer
  • amount numeric

fund_spent_balance

  • fund integer
  • amount numeric

fund_spent_total

  • fund integer
  • amount numeric

fund_tag

  • id serial PRIMARY KEY
  • owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • name text UNIQUE #1 NOT NULL

Tables referencing via foreign key constraints

  • acq.fund_tag_map

fund_tag_map

  • id serial PRIMARY KEY
  • fund integer UNIQUE #1 NOT NULL REFERENCES acq.table.fund
  • tag integer UNIQUE #1 REFERENCES acq.table.fund-tag

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.

  • id serial PRIMARY KEY
  • src_fund integer NOT NULL REFERENCES acq.table.fund
  • src_amount numeric NOT NULL
  • dest_fund integer REFERENCES acq.table.fund
  • dest_amount numeric
  • transfer_time timestamp with time zone NOT NULL DEFAULT now()
  • transfer_user integer NOT NULL REFERENCES actor.table.usr
  • note text
  • funding_source_credit integer NOT NULL REFERENCES acq.table.funding-source-credit

Indexes on fund_transfer

  • acqftr_usr_idx transfer_user

funding_source

  • id serial PRIMARY KEY
  • name text UNIQUE #2 NOT NULL
  • owner integer UNIQUE #1 UNIQUE #2 NOT NULL REFERENCES actor.table.org-unit
  • currency_type text NOT NULL REFERENCES acq.table.currency-type
  • code text UNIQUE #1 NOT NULL

Tables referencing via foreign key constraints

  • acq.fund_allocation
  • acq.fund_allocation_percent
  • acq.funding_source_credit

funding_source_allocation_total

  • funding_source integer
  • amount numeric(100,2)

funding_source_balance

  • funding_source integer
  • amount numeric(100,2)

funding_source_credit

  • id serial PRIMARY KEY
  • funding_source integer NOT NULL REFERENCES acq.table.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 via foreign key constraints

  • acq.debit_attribution
  • acq.fund_transfer

funding_source_credit_total

  • funding_source integer
  • amount numeric

invoice

  • id serial PRIMARY KEY
  • receiver integer NOT NULL REFERENCES actor.table.org-unit
  • provider integer UNIQUE #1 NOT NULL REFERENCES acq.table.provider
  • shipper integer NOT NULL REFERENCES acq.table.provider
  • recv_date timestamp with time zone NOT NULL DEFAULT now()
  • recv_method text NOT NULL DEFAULT 'EDI'::text REFERENCES acq.table.invoice-method
  • inv_type text
  • inv_ident text UNIQUE #1 NOT NULL
  • payment_auth text
  • payment_method text REFERENCES acq.table.invoice-payment-method
  • note text
  • close_date timestamp with time zone
  • closed_by integer REFERENCES actor.table.usr

Tables referencing via foreign key constraints

  • acq.invoice_entry
  • acq.invoice_item

invoice_entry

  • id serial PRIMARY KEY
  • invoice integer NOT NULL REFERENCES acq.table.invoice
  • purchase_order integer REFERENCES acq.table.purchase-order
  • lineitem integer REFERENCES acq.table.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)

Indexes on invoice_entry

  • ie_inv_idx invoice
  • ie_li_idx lineitem
  • ie_po_idx purchase_order

Tables referencing via foreign key constraints

  • acq.fund_debit

invoice_item

  • id serial PRIMARY KEY
  • invoice integer NOT NULL REFERENCES acq.table.invoice
  • purchase_order integer REFERENCES acq.table.purchase-order
  • fund_debit integer REFERENCES acq.table.fund-debit
  • inv_item_type text NOT NULL REFERENCES acq.table.invoice-item-type
  • title text
  • author text
  • note text
  • cost_billed numeric(8,2)
  • actual_cost numeric(8,2)
  • fund integer REFERENCES acq.table.fund
  • amount_paid numeric(8,2)
  • po_item integer REFERENCES acq.table.po-item
  • target bigint

Indexes on invoice_item

  • ii_inv_idx invoice
  • ii_po_idx purchase_order
  • ii_poi_idx po_item

invoice_item_type

  • code text PRIMARY KEY
  • name text NOT NULL
  • prorate boolean NOT NULL DEFAULT false
  • blanket boolean NOT NULL DEFAULT false

Constraints on invoice_item_type

  • aiit_not_blanket_and_prorate CHECK

Tables referencing via foreign key constraints

  • acq.invoice_item
  • acq.po_item

invoice_method

  • code text PRIMARY KEY
  • name text NOT NULL

Tables referencing via foreign key constraints

  • acq.invoice

invoice_payment_method

  • code text PRIMARY KEY
  • name text NOT NULL

Tables referencing via foreign key constraints

  • acq.invoice

lineitem

  • id bigserial PRIMARY KEY
  • creator integer NOT NULL REFERENCES actor.table.usr
  • editor integer NOT NULL REFERENCES actor.table.usr
  • selector integer NOT NULL REFERENCES actor.table.usr
  • provider integer REFERENCES acq.table.provider
  • purchase_order integer REFERENCES acq.table.purchase-order
  • picklist integer REFERENCES acq.table.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.table.record-entry
  • source_label text
  • state text NOT NULL DEFAULT 'new'::text
  • cancel_reason integer REFERENCES acq.table.cancel-reason
  • estimated_unit_price numeric
  • claim_policy integer REFERENCES acq.table.claim-policy
  • queued_record bigint REFERENCES vandelay.table.queued-bib-record

Constraints on lineitem

  • picklist_or_po CHECK

Indexes on lineitem

  • 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

Tables referencing via foreign key constraints

  • acq.distribution_formula_application
  • acq.invoice_entry
  • acq.lineitem_attr
  • acq.lineitem_detail
  • acq.lineitem_note
  • acq.user_request

lineitem_alert_text

  • id serial PRIMARY KEY
  • code text UNIQUE #1 NOT NULL
  • description text
  • owning_lib integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit

Tables referencing via foreign key constraints

  • acq.lineitem_note

lineitem_attr

  • id bigserial PRIMARY KEY
  • definition bigint NOT NULL
  • lineitem bigint NOT NULL REFERENCES acq.table.lineitem
  • attr_type text NOT NULL
  • attr_name text NOT NULL
  • attr_value text NOT NULL
  • order_ident boolean NOT NULL DEFAULT false

Indexes on lineitem_attr

  • li_attr_definition_idx definition
  • li_attr_li_idx lineitem
  • li_attr_value_idx attr_value

lineitem_attr_definition

  • id bigserial PRIMARY KEY
  • code text NOT NULL
  • description text NOT NULL
  • remove text NOT NULL DEFAULT ''::text
  • ident boolean NOT NULL DEFAULT false

lineitem_detail

  • id bigserial PRIMARY KEY
  • lineitem integer NOT NULL REFERENCES acq.table.lineitem
  • fund integer REFERENCES acq.table.fund
  • fund_debit integer REFERENCES acq.table.fund-debit
  • eg_copy_id bigint
  • barcode text
  • cn_label text
  • note text
  • collection_code text
  • circ_modifier text REFERENCES config.table.circ-modifier
  • owning_lib integer REFERENCES actor.table.org-unit
  • location integer REFERENCES asset.table.copy-location
  • recv_time timestamp with time zone
  • receiver integer REFERENCES actor.table.usr
  • cancel_reason integer REFERENCES acq.table.cancel-reason

Indexes on lineitem_detail

  • li_detail_li_idx lineitem
  • lineitem_detail_fund_debit_idx fund_debit

Tables referencing via foreign key constraints

  • acq.claim

lineitem_generated_attr_definition

  • 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

lineitem_local_attr_definition

  • 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

lineitem_marc_attr_definition

  • 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

lineitem_note

  • id serial PRIMARY KEY
  • lineitem integer NOT NULL REFERENCES acq.table.lineitem
  • creator integer NOT NULL REFERENCES actor.table.usr
  • editor integer NOT NULL REFERENCES actor.table.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.table.lineitem-alert-text
  • vendor_public boolean NOT NULL DEFAULT false

Indexes on lineitem_note

  • li_note_creator_idx creator
  • li_note_editor_idx editor
  • li_note_li_idx lineitem

lineitem_provider_attr_definition

  • 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.table.provider

lineitem_summary

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

lineitem_usr_attr_definition

  • 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.table.usr

Indexes on lineitem_usr_attr_definition

  • li_usr_attr_def_usr_idx usr

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.

  • sort_priority integer
  • sort_date timestamp with time zone
  • id integer
  • funding_source integer
  • amount numeric
  • note text

picklist

  • id serial PRIMARY KEY
  • owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.usr
  • creator integer NOT NULL REFERENCES actor.table.usr
  • editor integer NOT NULL REFERENCES actor.table.usr
  • org_unit integer NOT NULL REFERENCES actor.table.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 on picklist

  • acq_picklist_creator_idx creator
  • acq_picklist_editor_idx editor
  • acq_picklist_owner_idx owner

Tables referencing via foreign key constraints

  • acq.lineitem

po_item

  • id serial PRIMARY KEY
  • purchase_order integer REFERENCES acq.table.purchase-order
  • fund_debit integer REFERENCES acq.table.fund-debit
  • inv_item_type text NOT NULL REFERENCES acq.table.invoice-item-type
  • title text
  • author text
  • note text
  • estimated_cost numeric(8,2)
  • fund integer REFERENCES acq.table.fund
  • target bigint

Indexes on po_item

  • poi_po_idx purchase_order

Tables referencing via foreign key constraints

  • acq.invoice_item

po_note

  • id serial PRIMARY KEY
  • purchase_order integer NOT NULL REFERENCES acq.table.purchase-order
  • creator integer NOT NULL REFERENCES actor.table.usr
  • editor integer NOT NULL REFERENCES actor.table.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 on po_note

  • acq_po_note_creator_idx creator
  • acq_po_note_editor_idx editor
  • po_note_po_idx purchase_order

provider

  • id serial PRIMARY KEY
  • name text UNIQUE #1 NOT NULL
  • owner integer UNIQUE #2 UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • currency_type text NOT NULL REFERENCES acq.table.currency-type
  • code text UNIQUE #2 NOT NULL
  • holding_tag text
  • san text
  • edi_default integer REFERENCES acq.table.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_copy_count integer NOT NULL
  • default_claim_policy integer REFERENCES acq.table.claim-policy

Tables referencing 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

provider_address

  • id serial PRIMARY KEY
  • valid boolean NOT NULL DEFAULT true
  • address_type text
  • provider integer NOT NULL REFERENCES acq.table.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

provider_contact

  • id serial PRIMARY KEY
  • provider integer NOT NULL REFERENCES acq.table.provider
  • name text NOT NULL
  • role text
  • email text
  • phone text

Tables referencing via foreign key constraints

  • acq.provider_contact_address

provider_contact_address

  • id serial PRIMARY KEY
  • valid boolean NOT NULL DEFAULT true
  • address_type text
  • contact integer NOT NULL REFERENCES acq.table.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

provider_holding_subfield_map

  • id serial PRIMARY KEY
  • provider integer UNIQUE #1 NOT NULL REFERENCES acq.table.provider
  • name text UNIQUE #1 NOT NULL
  • subfield text NOT NULL

provider_note

  • id serial PRIMARY KEY
  • provider integer NOT NULL REFERENCES acq.table.provider
  • creator integer NOT NULL REFERENCES actor.table.usr
  • editor integer NOT NULL REFERENCES actor.table.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 on provider_note

  • acq_pro_note_creator_idx creator
  • acq_pro_note_editor_idx editor
  • acq_pro_note_pro_idx provider

purchase_order

  • id serial PRIMARY KEY
  • owner integer NOT NULL REFERENCES actor.table.usr
  • creator integer NOT NULL REFERENCES actor.table.usr
  • editor integer NOT NULL REFERENCES actor.table.usr
  • ordering_agency integer NOT NULL REFERENCES actor.table.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.table.provider
  • state text NOT NULL DEFAULT 'new'::text
  • order_date timestamp with time zone
  • name text NOT NULL
  • cancel_reason integer REFERENCES acq.table.cancel-reason
  • prepayment_required boolean NOT NULL DEFAULT false

Constraints on purchase_order

  • valid_po_state CHECK state = ANY (ARRAY['new'::text

Indexes on purchase_order

  • 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 via foreign key constraints

  • acq.edi_message
  • acq.invoice_entry
  • acq.invoice_item
  • acq.lineitem
  • acq.po_item
  • acq.po_note

serial_claim

  • id serial PRIMARY KEY
  • type integer NOT NULL REFERENCES acq.table.claim-type
  • item bigint NOT NULL REFERENCES serial.table.item

Indexes on serial_claim

  • serial_claim_lid_idx item

Tables referencing via foreign key constraints

  • acq.serial_claim_event

serial_claim_event

  • id bigserial PRIMARY KEY
  • type integer NOT NULL REFERENCES acq.table.claim-event-type
  • claim serial NOT NULL REFERENCES acq.table.serial-claim
  • event_date timestamp with time zone NOT NULL DEFAULT now()
  • creator integer NOT NULL REFERENCES actor.table.usr
  • note text

Indexes on serial_claim_event

  • serial_claim_event_claim_date_idx claim, event_date

user_request

  • id serial PRIMARY KEY
  • usr integer NOT NULL REFERENCES actor.table.usr
  • hold boolean NOT NULL DEFAULT true
  • pickup_lib integer NOT NULL REFERENCES actor.table.org-unit
  • holdable_formats text
  • phone_notify text
  • email_notify boolean NOT NULL DEFAULT true
  • lineitem integer REFERENCES acq.table.lineitem
  • eg_bib bigint REFERENCES biblio.table.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.table.user-request-type
  • 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
  • cancel_reason integer REFERENCES acq.table.cancel-reason
  • cancel_time timestamp with time zone

Tables referencing via foreign key constraints

  • action.hold_request

user_request_status_type

  • id serial PRIMARY KEY
  • label text

user_request_type

  • id serial PRIMARY KEY
  • label text UNIQUE NOT NULL

Tables referencing via foreign key constraints

  • acq.user_request
Prev   Next
 Home Functions in acq