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_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_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_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.table.lineitem
  • acq.table.lineitem-detail
  • acq.table.purchase-order
  • acq.table.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.table.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.table.claim-event
  • acq.table.claim-policy-action
  • acq.table.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.table.claim-policy-action
  • acq.table.lineitem
  • acq.table.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.table.claim
  • acq.table.serial-claim

currency_type

  • code text PRIMARY KEY
  • label text

Tables referencing via foreign key constraints

  • acq.table.exchange-rate
  • acq.table.fund
  • acq.table.fund-debit
  • acq.table.funding-source
  • acq.table.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.table.distribution-formula-application
  • acq.table.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

Tables referencing via foreign key constraints

  • acq.table.edi-message
  • acq.table.provider

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

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.table.fiscal-year
  • actor.table.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.table.distribution-formula-entry
  • acq.table.fund-allocation
  • acq.table.fund-debit
  • acq.table.fund-tag-map
  • acq.table.fund-transfer
  • acq.table.invoice-item
  • acq.table.lineitem-detail
  • acq.table.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()

Tables referencing via foreign key constraints

  • acq.table.debit-attribution
  • acq.table.invoice-item
  • acq.table.lineitem-detail
  • acq.table.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.table.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

  • 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 #1 NOT NULL
  • owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • currency_type text NOT NULL REFERENCES acq.table.currency-type
  • code text UNIQUE

Tables referencing via foreign key constraints

  • acq.table.fund-allocation
  • acq.table.fund-allocation-percent
  • acq.table.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.table.debit-attribution
  • acq.table.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
  • complete boolean NOT NULL DEFAULT false

Tables referencing via foreign key constraints

  • acq.table.invoice-entry
  • acq.table.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

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

Tables referencing via foreign key constraints

  • acq.table.invoice-item
  • acq.table.po-item

invoice_method

  • code text PRIMARY KEY
  • name text NOT NULL

Tables referencing via foreign key constraints

  • acq.table.invoice

invoice_payment_method

  • code text PRIMARY KEY
  • name text NOT NULL

Tables referencing via foreign key constraints

  • acq.table.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_selector_idx selector

Tables referencing via foreign key constraints

  • acq.table.distribution-formula-application
  • acq.table.invoice-entry
  • acq.table.lineitem-attr
  • acq.table.lineitem-detail
  • acq.table.lineitem-note
  • acq.table.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.table.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

Tables referencing via foreign key constraints

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

  • 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.table.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.table.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.table.edi-account
  • acq.table.invoice
  • acq.table.lineitem
  • acq.table.lineitem-provider-attr-definition
  • acq.table.provider-address
  • acq.table.provider-contact
  • acq.table.provider-holding-subfield-map
  • acq.table.provider-note
  • acq.table.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.table.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.table.edi-message
  • acq.table.invoice-entry
  • acq.table.invoice-item
  • acq.table.lineitem
  • acq.table.po-item
  • acq.table.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.table.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
  • 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

user_request_type

  • id serial PRIMARY KEY
  • label text UNIQUE NOT NULL

Tables referencing via foreign key constraints

  • acq.table.user-request