1 DROP SCHEMA acq CASCADE;
11 CREATE TABLE acq.currency_type (
12 code TEXT PRIMARY KEY,
16 -- Use the ISO 4217 abbreviations for currency codes
17 INSERT INTO acq.currency_type (code, label) VALUES ('USD','US Dollars');
18 INSERT INTO acq.currency_type (code, label) VALUES ('CAN','Canadian Dollars');
19 INSERT INTO acq.currency_type (code, label) VALUES ('EUR','Euros');
21 CREATE TABLE acq.exchange_rate (
22 id SERIAL PRIMARY KEY,
23 from_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
24 to_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
25 ratio NUMERIC NOT NULL,
26 CONSTRAINT exchange_rate_from_to_once UNIQUE (from_currency,to_currency)
29 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','CAN',1.2);
30 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','EUR',0.5);
32 CREATE TABLE acq.provider (
33 id SERIAL PRIMARY KEY,
35 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
36 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
40 edi_default INT, -- REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED
41 CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner),
42 CONSTRAINT code_once_per_owner UNIQUE (code, owner)
45 CREATE TABLE acq.provider_holding_subfield_map (
46 id SERIAL PRIMARY KEY,
47 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
48 name TEXT NOT NULL, -- barcode, price, etc
49 subfield TEXT NOT NULL,
50 CONSTRAINT name_once_per_provider UNIQUE (provider,name)
53 CREATE TABLE acq.provider_address (
54 id SERIAL PRIMARY KEY,
55 valid BOOL NOT NULL DEFAULT TRUE,
57 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
58 street1 TEXT NOT NULL,
63 country TEXT NOT NULL,
64 post_code TEXT NOT NULL
67 CREATE TABLE acq.provider_contact (
68 id SERIAL PRIMARY KEY,
69 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
71 role TEXT, -- free-form.. e.g. "our sales guy"
76 CREATE TABLE acq.provider_contact_address (
77 id SERIAL PRIMARY KEY,
78 valid BOOL NOT NULL DEFAULT TRUE,
80 contact INT NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
81 street1 TEXT NOT NULL,
86 country TEXT NOT NULL,
87 post_code TEXT NOT NULL
91 CREATE TABLE acq.funding_source (
92 id SERIAL PRIMARY KEY,
94 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
95 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
97 CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
100 CREATE TABLE acq.funding_source_credit (
101 id SERIAL PRIMARY KEY,
102 funding_source INT NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
103 amount NUMERIC NOT NULL,
105 deadline_date TIMESTAMPTZ,
106 effective_date TIMESTAMPTZ NOT NULL default now()
109 CREATE VIEW acq.ordered_funding_source_credit AS
111 CASE WHEN deadline_date IS NULL THEN
115 END AS sort_priority,
116 CASE WHEN deadline_date IS NULL THEN
126 acq.funding_source_credit;
128 COMMENT ON VIEW acq.ordered_funding_source_credit IS $$
130 * Copyright (C) 2009 Georgia Public Library Service
131 * Scott McKellar <scott@gmail.com>
133 * The acq.ordered_funding_source_credit view is a prioritized
134 * ordering of funding source credits. When ordered by the first
135 * three columns, this view defines the order in which the various
136 * credits are to be tapped for spending, subject to the allocations
137 * in the acq.fund_allocation table.
139 * The first column reflects the principle that we should spend
140 * money with deadlines before spending money without deadlines.
142 * The second column reflects the principle that we should spend the
143 * oldest money first. For money with deadlines, that means that we
144 * spend first from the credit with the earliest deadline. For
145 * money without deadlines, we spend first from the credit with the
146 * earliest effective date.
148 * The third column is a tie breaker to ensure a consistent
153 * This program is free software; you can redistribute it and/or
154 * modify it under the terms of the GNU General Public License
155 * as published by the Free Software Foundation; either version 2
156 * of the License, or (at your option) any later version.
158 * This program is distributed in the hope that it will be useful,
159 * but WITHOUT ANY WARRANTY; without even the implied warranty of
160 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
161 * GNU General Public License for more details.
165 CREATE TABLE acq.fund (
166 id SERIAL PRIMARY KEY,
167 org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
169 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
170 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
172 rollover BOOL NOT NULL DEFAULT FALSE,
173 propagate BOOL NOT NULL DEFAULT TRUE,
174 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
175 CONSTRAINT code_once_per_org_year UNIQUE (org, code, year),
176 CONSTRAINT acq_fund_rollover_implies_propagate CHECK ( propagate OR NOT rollover )
179 CREATE TABLE acq.fund_debit (
180 id SERIAL PRIMARY KEY,
181 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
182 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
183 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
184 amount NUMERIC NOT NULL,
185 encumbrance BOOL NOT NULL DEFAULT TRUE,
186 debit_type TEXT NOT NULL,
187 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
188 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
191 CREATE TABLE acq.fund_allocation (
192 id SERIAL PRIMARY KEY,
193 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
194 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
195 amount NUMERIC NOT NULL,
196 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
198 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
200 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
202 CREATE TABLE acq.picklist (
203 id SERIAL PRIMARY KEY,
204 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
205 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
206 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
207 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
209 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
210 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
211 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
213 CREATE INDEX acq_picklist_owner_idx ON acq.picklist ( owner );
214 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
215 CREATE INDEX acq_picklist_editor_idx ON acq.picklist ( editor );
217 CREATE TABLE acq.purchase_order (
218 id SERIAL PRIMARY KEY,
219 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
220 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
221 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
222 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
223 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
224 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
225 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
226 state TEXT NOT NULL DEFAULT 'new',
227 order_date TIMESTAMP WITH TIME ZONE,
230 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
231 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
232 CREATE INDEX po_state_idx ON acq.purchase_order (state);
233 CREATE INDEX po_creator_idx ON acq.purchase_order ( creator );
234 CREATE INDEX po_editor_idx ON acq.purchase_order ( editor );
235 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
237 -- The name should default to the id, as text. We can't reference a column
238 -- in a DEFAULT clause, so we use a trigger:
240 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
243 IF NEW.name IS NULL THEN
244 NEW.name := NEW.id::TEXT;
251 CREATE TRIGGER po_name_default_trg
252 BEFORE INSERT OR UPDATE ON acq.purchase_order
253 FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
255 -- The order name should be unique for a given ordering agency on a given order date
256 -- (truncated to midnight), but only where the order_date is not NULL. Conceptually
257 -- this rule requires a check constraint with a subquery. However you can't have a
258 -- subquery in a CHECK constraint, so we fake it with a trigger.
260 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
266 -- If order_date is not null, then make sure we don't have a collision
267 -- on order_date (truncated to day), org, and name
269 IF NEW.order_date IS NULL THEN
273 -- In the WHERE clause, we compare the order_dates without regard to time of day.
274 -- We use a pair of inequalities instead of comparing truncated dates so that the
275 -- query can do an indexed range scan.
277 SELECT 1 INTO collision
278 FROM acq.purchase_order
280 ordering_agency = NEW.ordering_agency
282 AND order_date >= date_trunc( 'day', NEW.order_date )
283 AND order_date < date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
286 IF collision IS NULL THEN
287 -- okay, no collision
290 -- collision; nip it in the bud
291 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
292 NEW.ordering_agency, NEW.order_date, NEW.name;
297 CREATE TRIGGER po_org_name_date_unique_trg
298 BEFORE INSERT OR UPDATE ON acq.purchase_order
299 FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
301 CREATE TABLE acq.po_note (
302 id SERIAL PRIMARY KEY,
303 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
304 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
305 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
306 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
307 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
310 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
311 CREATE INDEX acq_po_note_creator_idx ON acq.po_note ( creator );
312 CREATE INDEX acq_po_note_editor_idx ON acq.po_note ( editor );
314 CREATE TABLE acq.lineitem (
315 id BIGSERIAL PRIMARY KEY,
316 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
317 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
318 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
319 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
320 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
321 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
322 expected_recv_time TIMESTAMP WITH TIME ZONE,
323 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
324 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
326 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
328 item_count INT NOT NULL DEFAULT 0,
329 state TEXT NOT NULL DEFAULT 'new',
330 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
332 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
333 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
334 CREATE INDEX li_creator_idx ON acq.lineitem ( creator );
335 CREATE INDEX li_editor_idx ON acq.lineitem ( editor );
336 CREATE INDEX li_selector_idx ON acq.lineitem ( selector );
338 CREATE TABLE acq.lineitem_note (
339 id SERIAL PRIMARY KEY,
340 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
341 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
342 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
343 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
344 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
347 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
348 CREATE INDEX li_note_creator_idx ON acq.lineitem_note ( creator );
349 CREATE INDEX li_note_editor_idx ON acq.lineitem_note ( editor );
351 CREATE TABLE acq.lineitem_detail (
352 id BIGSERIAL PRIMARY KEY,
353 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
354 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
355 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
356 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
360 collection_code TEXT,
361 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
362 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
363 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
364 recv_time TIMESTAMP WITH TIME ZONE
367 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
369 CREATE TABLE acq.lineitem_attr_definition (
370 id BIGSERIAL PRIMARY KEY,
372 description TEXT NOT NULL,
373 remove TEXT NOT NULL DEFAULT '',
374 ident BOOL NOT NULL DEFAULT FALSE
377 CREATE TABLE acq.lineitem_marc_attr_definition (
378 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
380 ) INHERITS (acq.lineitem_attr_definition);
382 CREATE TABLE acq.lineitem_provider_attr_definition (
383 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
385 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
386 ) INHERITS (acq.lineitem_attr_definition);
388 CREATE TABLE acq.lineitem_generated_attr_definition (
389 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
391 ) INHERITS (acq.lineitem_attr_definition);
393 CREATE TABLE acq.lineitem_usr_attr_definition (
394 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
395 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
396 ) INHERITS (acq.lineitem_attr_definition);
397 CREATE INDEX li_usr_attr_def_usr_idx ON acq.lineitem_usr_attr_definition ( usr );
399 CREATE TABLE acq.lineitem_local_attr_definition (
400 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
401 ) INHERITS (acq.lineitem_attr_definition);
403 CREATE TABLE acq.lineitem_attr (
404 id BIGSERIAL PRIMARY KEY,
405 definition BIGINT NOT NULL,
406 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
407 attr_type TEXT NOT NULL,
408 attr_name TEXT NOT NULL,
409 attr_value TEXT NOT NULL
412 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
413 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
414 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
420 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
421 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('author','Author of work','//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
422 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
423 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
424 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
425 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
426 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
427 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
428 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
429 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
430 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
432 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
435 CREATE TABLE acq.distribution_formula (
436 id SERIAL PRIMARY KEY,
438 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
440 skip_count INT NOT NULL DEFAULT 0,
441 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
444 CREATE TABLE acq.distribution_formula_entry (
445 id SERIAL PRIMARY KEY,
446 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
448 DEFERRABLE INITIALLY DEFERRED,
449 position INTEGER NOT NULL,
450 item_count INTEGER NOT NULL,
451 owning_lib INTEGER REFERENCES actor.org_unit(id)
452 DEFERRABLE INITIALLY DEFERRED,
453 location INTEGER REFERENCES asset.copy_location(id),
454 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
455 CONSTRAINT acqdfe_must_be_somewhere
456 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
459 CREATE TABLE acq.fund_tag (
460 id SERIAL PRIMARY KEY,
462 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
464 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
467 CREATE TABLE acq.fund_tag_map (
468 id SERIAL PRIMARY KEY,
469 fund INTEGER NOT NULL REFERENCES acq.fund(id)
470 DEFERRABLE INITIALLY DEFERRED,
471 tag INTEGER REFERENCES acq.fund_tag(id)
473 DEFERRABLE INITIALLY DEFERRED,
474 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
477 CREATE TABLE acq.fund_transfer (
478 id SERIAL PRIMARY KEY,
479 src_fund INT NOT NULL REFERENCES acq.fund( id )
480 DEFERRABLE INITIALLY DEFERRED,
481 src_amount NUMERIC NOT NULL,
482 dest_fund INT NOT NULL REFERENCES acq.fund( id )
483 DEFERRABLE INITIALLY DEFERRED,
484 dest_amount NUMERIC NOT NULL,
485 transfer_time TIMESTAMPTZ NOT NULL DEFAULT now(),
486 transfer_user INT NOT NULL REFERENCES actor.usr( id )
487 DEFERRABLE INITIALLY DEFERRED,
489 funding_source_credit INT NOT NULL REFERENCES acq.funding_source_credit( id )
490 DEFERRABLE INITIALLY DEFERRED
493 CREATE INDEX acqftr_usr_idx
494 ON acq.fund_transfer( transfer_user );
496 COMMENT ON TABLE acq.fund_transfer IS $$
498 * Copyright (C) 2009 Georgia Public Library Service
499 * Scott McKellar <scott@esilibrary.com>
503 * Each row represents the transfer of money from a source fund
504 * to a destination fund. There should be corresponding entries
505 * in acq.fund_allocation. The purpose of acq.fund_transfer is
506 * to record how much money moved from which fund to which other
509 * The presence of two amount fields, rather than one, reflects
510 * the possibility that the two funds are denominated in different
511 * currencies. If they use the same currency type, the two
512 * amounts should be the same.
516 * This program is free software; you can redistribute it and/or
517 * modify it under the terms of the GNU General Public License
518 * as published by the Free Software Foundation; either version 2
519 * of the License, or (at your option) any later version.
521 * This program is distributed in the hope that it will be useful,
522 * but WITHOUT ANY WARRANTY; without even the implied warranty of
523 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
524 * GNU General Public License for more details.
528 CREATE TABLE acq.fiscal_calendar (
529 id SERIAL PRIMARY KEY,
533 -- Create a default calendar (though we don't specify its contents).
534 -- Create a foreign key in actor.org_unit, initially pointing to
535 -- the default calendar.
537 INSERT INTO acq.fiscal_calendar (
544 ALTER TABLE actor.org_unit
545 ADD COLUMN fiscal_calendar INT NOT NULL
546 REFERENCES acq.fiscal_calendar( id )
547 DEFERRABLE INITIALLY DEFERRED
550 CREATE TABLE acq.fiscal_year (
551 id SERIAL PRIMARY KEY,
552 calendar INT NOT NULL
553 REFERENCES acq.fiscal_calendar
555 DEFERRABLE INITIALLY DEFERRED,
557 year_begin TIMESTAMPTZ NOT NULL,
558 year_end TIMESTAMPTZ NOT NULL,
559 CONSTRAINT acq_fy_logical_key UNIQUE ( calendar, year ),
560 CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
563 CREATE TABLE acq.edi_account ( -- similar tables can extend remote_account for other parts of EG
564 provider INT NOT NULL REFERENCES acq.provider (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
565 in_dir TEXT -- incoming messages dir (probably different than config.remote_account.path, the outgoing dir)
566 ) INHERITS (config.remote_account);
568 -- We need a UNIQUE constraint here also, to support the FK from acq.provider.edi_default
569 ALTER TABLE acq.edi_account ADD CONSTRAINT acq_edi_account_id_unique UNIQUE (id);
573 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
574 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
577 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
580 SELECT COUNT(*) INTO counter
585 '//*[@tag="' || tag || '"]',
587 ) as t(i int,c text);
589 FOR i IN 1 .. counter LOOP
592 FROM ( SELECT id,i,t,v
597 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
598 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
600 ) as t(id int,t text,v text)
611 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
612 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
616 lida acq.flat_lineitem_detail%ROWTYPE;
618 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
619 IF NOT FOUND THEN RETURN; END IF;
621 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
622 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
629 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
630 JOIN acq.provider_holding_subfield_map a USING (subfield)
631 WHERE a.provider = prov_i
640 -- select * from acq.extract_provider_holding_data(699);
642 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
643 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
647 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
648 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
651 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
652 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
656 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
659 Suggested vendor fields:
667 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
675 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
677 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
679 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
680 IF (atype = 'lineitem_provider_attr_definition') THEN
681 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
682 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
685 IF (atype = 'lineitem_provider_attr_definition') THEN
686 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
687 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
688 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
689 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
690 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
693 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
695 IF (value IS NOT NULL AND value <> '') THEN
696 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
697 VALUES (NEW.id, adef.id, atype, adef.code, value);
708 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
710 IF TG_OP = 'UPDATE' THEN
711 DELETE FROM acq.lineitem_attr
712 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
715 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
721 CREATE TRIGGER cleanup_lineitem_trigger
722 BEFORE UPDATE OR DELETE ON acq.lineitem
723 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
725 CREATE TRIGGER ingest_lineitem_trigger
726 AFTER INSERT OR UPDATE ON acq.lineitem
727 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
729 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
733 IF from_ex = to_ex THEN
737 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
742 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
753 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
754 SELECT $3 * acq.exchange_ratio($1, $2);
757 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
759 Examine the acq.fiscal_year table, comparing successive years.
760 Report any inconsistencies, i.e. years that overlap, have gaps
761 between them, or are out of sequence.
763 RETURNS SETOF RECORD AS $$
787 ELSIF curr_year.calendar = prev_year.calendar THEN
788 IF curr_year.year_begin > prev_year.year_end THEN
789 -- This ugly kludge works around the fact that older
790 -- versions of PostgreSQL don't support RETURN QUERY SELECT
791 FOR return_rec IN SELECT
794 'Gap between fiscal years'::TEXT
796 RETURN NEXT return_rec;
798 ELSIF curr_year.year_begin < prev_year.year_end THEN
799 FOR return_rec IN SELECT
802 'Overlapping fiscal years'::TEXT
804 RETURN NEXT return_rec;
806 ELSIF curr_year.year < prev_year.year THEN
807 FOR return_rec IN SELECT
810 'Fiscal years out of order'::TEXT
812 RETURN NEXT return_rec;
817 prev_year := curr_year;
824 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
825 SELECT funding_source,
826 SUM(amount) AS amount
827 FROM acq.funding_source_credit
830 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
831 SELECT funding_source,
832 SUM(a.amount)::NUMERIC(100,2) AS amount
833 FROM acq.fund_allocation a
836 CREATE OR REPLACE VIEW acq.funding_source_balance AS
837 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
838 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
839 FROM acq.funding_source_credit_total c
840 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
843 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
845 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
846 FROM acq.fund_allocation a
847 JOIN acq.fund f ON (a.fund = f.id)
848 JOIN acq.funding_source s ON (a.funding_source = s.id)
851 CREATE OR REPLACE VIEW acq.fund_debit_total AS
854 SUM(amount) AS amount
858 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
860 SUM(amount) AS amount
861 FROM acq.fund_debit_total
862 WHERE encumbrance IS TRUE
865 CREATE OR REPLACE VIEW acq.fund_spent_total AS
867 SUM(amount) AS amount
868 FROM acq.fund_debit_total
869 WHERE encumbrance IS FALSE
872 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
874 c.amount - COALESCE(d.amount,0.0) AS amount
875 FROM acq.fund_allocation_total c
876 LEFT JOIN acq.fund_debit_total d USING (fund);
878 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
880 c.amount - COALESCE(d.amount,0.0) AS amount
881 FROM acq.fund_allocation_total c
882 LEFT JOIN acq.fund_spent_total d USING (fund);