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 CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner),
41 CONSTRAINT code_once_per_owner UNIQUE (code, owner)
44 CREATE TABLE acq.provider_holding_subfield_map (
45 id SERIAL PRIMARY KEY,
46 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
47 name TEXT NOT NULL, -- barcode, price, etc
48 subfield TEXT NOT NULL,
49 CONSTRAINT name_once_per_provider UNIQUE (provider,name)
52 CREATE TABLE acq.provider_address (
53 id SERIAL PRIMARY KEY,
54 valid BOOL NOT NULL DEFAULT TRUE,
56 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
57 street1 TEXT NOT NULL,
62 country TEXT NOT NULL,
63 post_code TEXT NOT NULL
66 CREATE TABLE acq.provider_contact (
67 id SERIAL PRIMARY KEY,
68 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
70 role TEXT, -- free-form.. e.g. "our sales guy"
75 CREATE TABLE acq.provider_contact_address (
76 id SERIAL PRIMARY KEY,
77 valid BOOL NOT NULL DEFAULT TRUE,
79 contact INT NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
80 street1 TEXT NOT NULL,
85 country TEXT NOT NULL,
86 post_code TEXT NOT NULL
90 CREATE TABLE acq.funding_source (
91 id SERIAL PRIMARY KEY,
93 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
94 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
96 CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
99 CREATE TABLE acq.funding_source_credit (
100 id SERIAL PRIMARY KEY,
101 funding_source INT NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
102 amount NUMERIC NOT NULL,
104 deadline_date TIMESTAMPTZ,
105 effective_date TIMESTAMPTZ NOT NULL default now()
108 CREATE VIEW acq.ordered_funding_source_credit AS
110 CASE WHEN deadline_date IS NULL THEN
114 END AS sort_priority,
115 CASE WHEN deadline_date IS NULL THEN
125 acq.funding_source_credit;
127 COMMENT ON VIEW acq.ordered_funding_source_credit IS $$
129 * Copyright (C) 2009 Georgia Public Library Service
130 * Scott McKellar <scott@gmail.com>
132 * The acq.ordered_funding_source_credit view is a prioritized
133 * ordering of funding source credits. When ordered by the first
134 * three columns, this view defines the order in which the various
135 * credits are to be tapped for spending, subject to the allocations
136 * in the acq.fund_allocation table.
138 * The first column reflects the principle that we should spend
139 * money with deadlines before spending money without deadlines.
141 * The second column reflects the principle that we should spend the
142 * oldest money first. For money with deadlines, that means that we
143 * spend first from the credit with the earliest deadline. For
144 * money without deadlines, we spend first from the credit with the
145 * earliest effective date.
147 * The third column is a tie breaker to ensure a consistent
152 * This program is free software; you can redistribute it and/or
153 * modify it under the terms of the GNU General Public License
154 * as published by the Free Software Foundation; either version 2
155 * of the License, or (at your option) any later version.
157 * This program is distributed in the hope that it will be useful,
158 * but WITHOUT ANY WARRANTY; without even the implied warranty of
159 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
160 * GNU General Public License for more details.
164 CREATE TABLE acq.fund (
165 id SERIAL PRIMARY KEY,
166 org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
168 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
169 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
171 rollover BOOL NOT NULL DEFAULT FALSE,
172 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
173 CONSTRAINT code_once_per_org_year UNIQUE (org, code, year)
176 CREATE TABLE acq.fund_debit (
177 id SERIAL PRIMARY KEY,
178 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
179 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
180 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
181 amount NUMERIC NOT NULL,
182 encumbrance BOOL NOT NULL DEFAULT TRUE,
183 debit_type TEXT NOT NULL,
184 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
185 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
188 CREATE TABLE acq.fund_allocation (
189 id SERIAL PRIMARY KEY,
190 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
191 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
192 amount NUMERIC NOT NULL,
193 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
195 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
197 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
199 CREATE TABLE acq.picklist (
200 id SERIAL PRIMARY KEY,
201 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
202 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
203 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
204 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
206 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
207 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
208 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
210 CREATE INDEX acq_picklist_owner_idx ON acq.picklist ( owner );
211 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
212 CREATE INDEX acq_picklist_editor_idx ON acq.picklist ( editor );
214 CREATE TABLE acq.purchase_order (
215 id SERIAL PRIMARY KEY,
216 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
217 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
218 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
219 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
220 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
221 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
222 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
223 state TEXT NOT NULL DEFAULT 'new',
224 order_date TIMESTAMP WITH TIME ZONE,
227 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
228 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
229 CREATE INDEX po_state_idx ON acq.purchase_order (state);
230 CREATE INDEX po_creator_idx ON acq.purchase_order ( creator );
231 CREATE INDEX po_editor_idx ON acq.purchase_order ( editor );
232 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
234 -- The name should default to the id, as text. We can't reference a column
235 -- in a DEFAULT clause, so we use a trigger:
237 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
240 IF NEW.name IS NULL THEN
241 NEW.name := NEW.id::TEXT;
248 CREATE TRIGGER po_name_default_trg
249 BEFORE INSERT OR UPDATE ON acq.purchase_order
250 FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
252 -- The order name should be unique for a given ordering agency on a given order date
253 -- (truncated to midnight), but only where the order_date is not NULL. Conceptually
254 -- this rule requires a check constraint with a subquery. However you can't have a
255 -- subquery in a CHECK constraint, so we fake it with a trigger.
257 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
263 -- If order_date is not null, then make sure we don't have a collision
264 -- on order_date (truncated to day), org, and name
266 IF NEW.order_date IS NULL THEN
270 -- In the WHERE clause, we compare the order_dates without regard to time of day.
271 -- We use a pair of inequalities instead of comparing truncated dates so that the
272 -- query can do an indexed range scan.
274 SELECT 1 INTO collision
275 FROM acq.purchase_order
277 ordering_agency = NEW.ordering_agency
279 AND order_date >= date_trunc( 'day', NEW.order_date )
280 AND order_date < date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
283 IF collision IS NULL THEN
284 -- okay, no collision
287 -- collision; nip it in the bud
288 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
289 NEW.ordering_agency, NEW.order_date, NEW.name;
294 CREATE TRIGGER po_org_name_date_unique_trg
295 BEFORE INSERT OR UPDATE ON acq.purchase_order
296 FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
298 CREATE TABLE acq.po_note (
299 id SERIAL PRIMARY KEY,
300 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
301 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
302 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
303 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
304 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
307 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
308 CREATE INDEX acq_po_note_creator_idx ON acq.po_note ( creator );
309 CREATE INDEX acq_po_note_editor_idx ON acq.po_note ( editor );
311 CREATE TABLE acq.lineitem (
312 id BIGSERIAL PRIMARY KEY,
313 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
314 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
315 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
316 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
317 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
318 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
319 expected_recv_time TIMESTAMP WITH TIME ZONE,
320 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
321 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
323 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
325 item_count INT NOT NULL DEFAULT 0,
326 state TEXT NOT NULL DEFAULT 'new',
327 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
329 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
330 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
331 CREATE INDEX li_creator_idx ON acq.lineitem ( creator );
332 CREATE INDEX li_editor_idx ON acq.lineitem ( editor );
333 CREATE INDEX li_selector_idx ON acq.lineitem ( selector );
335 CREATE TABLE acq.lineitem_note (
336 id SERIAL PRIMARY KEY,
337 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
338 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
339 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
340 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
341 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
344 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
345 CREATE INDEX li_note_creator_idx ON acq.lineitem_note ( creator );
346 CREATE INDEX li_note_editor_idx ON acq.lineitem_note ( editor );
348 CREATE TABLE acq.lineitem_detail (
349 id BIGSERIAL PRIMARY KEY,
350 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
351 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
352 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
353 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
357 collection_code TEXT,
358 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
359 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
360 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
361 recv_time TIMESTAMP WITH TIME ZONE
364 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
366 CREATE TABLE acq.lineitem_attr_definition (
367 id BIGSERIAL PRIMARY KEY,
369 description TEXT NOT NULL,
370 remove TEXT NOT NULL DEFAULT '',
371 ident BOOL NOT NULL DEFAULT FALSE
374 CREATE TABLE acq.lineitem_marc_attr_definition (
375 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
377 ) INHERITS (acq.lineitem_attr_definition);
379 CREATE TABLE acq.lineitem_provider_attr_definition (
380 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
382 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
383 ) INHERITS (acq.lineitem_attr_definition);
385 CREATE TABLE acq.lineitem_generated_attr_definition (
386 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
388 ) INHERITS (acq.lineitem_attr_definition);
390 CREATE TABLE acq.lineitem_usr_attr_definition (
391 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
392 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
393 ) INHERITS (acq.lineitem_attr_definition);
394 CREATE INDEX li_usr_attr_def_usr_idx ON acq.lineitem_usr_attr_definition ( usr );
396 CREATE TABLE acq.lineitem_local_attr_definition (
397 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
398 ) INHERITS (acq.lineitem_attr_definition);
400 CREATE TABLE acq.lineitem_attr (
401 id BIGSERIAL PRIMARY KEY,
402 definition BIGINT NOT NULL,
403 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
404 attr_type TEXT NOT NULL,
405 attr_name TEXT NOT NULL,
406 attr_value TEXT NOT NULL
409 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
410 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
411 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
417 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
418 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)]');
419 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
420 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
421 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
422 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
423 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
424 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
425 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
426 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
427 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
429 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
432 CREATE TABLE acq.distribution_formula (
433 id SERIAL PRIMARY KEY,
435 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
437 skip_count INT NOT NULL DEFAULT 0,
438 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
441 CREATE TABLE acq.distribution_formula_entry (
442 id SERIAL PRIMARY KEY,
443 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
445 DEFERRABLE INITIALLY DEFERRED,
446 position INTEGER NOT NULL,
447 item_count INTEGER NOT NULL,
448 owning_lib INTEGER REFERENCES actor.org_unit(id)
449 DEFERRABLE INITIALLY DEFERRED,
450 location INTEGER REFERENCES asset.copy_location(id),
451 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
452 CONSTRAINT acqdfe_must_be_somewhere
453 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
456 CREATE TABLE acq.fund_tag (
457 id SERIAL PRIMARY KEY,
459 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
461 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
464 CREATE TABLE acq.fund_tag_map (
465 id SERIAL PRIMARY KEY,
466 fund INTEGER NOT NULL REFERENCES acq.fund(id)
467 DEFERRABLE INITIALLY DEFERRED,
468 tag INTEGER REFERENCES acq.fund_tag(id)
470 DEFERRABLE INITIALLY DEFERRED,
471 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
474 CREATE TABLE acq.fund_transfer (
475 id SERIAL PRIMARY KEY,
476 src_fund INT NOT NULL REFERENCES acq.fund( id )
477 DEFERRABLE INITIALLY DEFERRED,
478 src_amount NUMERIC NOT NULL,
479 dest_fund INT NOT NULL REFERENCES acq.fund( id )
480 DEFERRABLE INITIALLY DEFERRED,
481 dest_amount NUMERIC NOT NULL,
482 transfer_time TIMESTAMPTZ NOT NULL DEFAULT now(),
483 transfer_user INT NOT NULL REFERENCES actor.usr( id )
484 DEFERRABLE INITIALLY DEFERRED,
486 funding_source_credit INT NOT NULL REFERENCES acq.funding_source_credit( id )
487 DEFERRABLE INITIALLY DEFERRED
490 CREATE INDEX acqftr_usr_idx
491 ON acq.fund_transfer( transfer_user );
493 COMMENT ON TABLE acq.fund_transfer IS $$
495 * Copyright (C) 2009 Georgia Public Library Service
496 * Scott McKellar <scott@esilibrary.com>
500 * Each row represents the transfer of money from a source fund
501 * to a destination fund. There should be corresponding entries
502 * in acq.fund_allocation. The purpose of acq.fund_transfer is
503 * to record how much money moved from which fund to which other
506 * The presence of two amount fields, rather than one, reflects
507 * the possibility that the two funds are denominated in different
508 * currencies. If they use the same currency type, the two
509 * amounts should be the same.
513 * This program is free software; you can redistribute it and/or
514 * modify it under the terms of the GNU General Public License
515 * as published by the Free Software Foundation; either version 2
516 * of the License, or (at your option) any later version.
518 * This program is distributed in the hope that it will be useful,
519 * but WITHOUT ANY WARRANTY; without even the implied warranty of
520 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
521 * GNU General Public License for more details.
525 CREATE TABLE acq.fiscal_calendar (
526 id SERIAL PRIMARY KEY,
530 -- Create a default calendar (though we don't specify its contents).
531 -- Create a foreign key in actor.org_unit, initially pointing to
532 -- the default calendar.
534 INSERT INTO acq.fiscal_calendar (
541 ALTER TABLE actor.org_unit
542 ADD COLUMN fiscal_calendar INT NOT NULL
543 REFERENCES acq.fiscal_calendar( id )
544 DEFERRABLE INITIALLY DEFERRED
547 CREATE TABLE acq.fiscal_year (
548 id SERIAL PRIMARY KEY,
549 calendar INT NOT NULL
550 REFERENCES acq.fiscal_calendar
552 DEFERRABLE INITIALLY DEFERRED,
554 year_begin TIMESTAMPTZ NOT NULL,
555 year_end TIMESTAMPTZ NOT NULL,
556 CONSTRAINT acq_fy_logical_key UNIQUE ( calendar, year ),
557 CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
562 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
563 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
566 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
569 SELECT COUNT(*) INTO counter
574 '//*[@tag="' || tag || '"]',
576 ) as t(i int,c text);
578 FOR i IN 1 .. counter LOOP
581 FROM ( SELECT id,i,t,v
586 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
587 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
589 ) as t(id int,t text,v text)
600 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
601 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
605 lida acq.flat_lineitem_detail%ROWTYPE;
607 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
608 IF NOT FOUND THEN RETURN; END IF;
610 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
611 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
618 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
619 JOIN acq.provider_holding_subfield_map a USING (subfield)
620 WHERE a.provider = prov_i
629 -- select * from acq.extract_provider_holding_data(699);
631 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
632 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
636 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
637 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
640 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
641 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
645 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
648 Suggested vendor fields:
656 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
664 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
666 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
668 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
669 IF (atype = 'lineitem_provider_attr_definition') THEN
670 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
671 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
674 IF (atype = 'lineitem_provider_attr_definition') THEN
675 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
676 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
677 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
678 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
679 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
682 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
684 IF (value IS NOT NULL AND value <> '') THEN
685 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
686 VALUES (NEW.id, adef.id, atype, adef.code, value);
697 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
699 IF TG_OP = 'UPDATE' THEN
700 DELETE FROM acq.lineitem_attr
701 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
704 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
710 CREATE TRIGGER cleanup_lineitem_trigger
711 BEFORE UPDATE OR DELETE ON acq.lineitem
712 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
714 CREATE TRIGGER ingest_lineitem_trigger
715 AFTER INSERT OR UPDATE ON acq.lineitem
716 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
718 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
722 IF from_ex = to_ex THEN
726 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
731 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
742 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
743 SELECT $3 * acq.exchange_ratio($1, $2);
746 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
748 Examine the acq.fiscal_year table, comparing successive years.
749 Report any inconsistencies, i.e. years that overlap, have gaps
750 between them, or are out of sequence.
752 RETURNS SETOF RECORD AS $$
776 ELSIF curr_year.calendar = prev_year.calendar THEN
777 IF curr_year.year_begin > prev_year.year_end THEN
778 -- This ugly kludge works around the fact that older
779 -- versions of PostgreSQL don't support RETURN QUERY SELECT
780 FOR return_rec IN SELECT
783 'Gap between fiscal years'::TEXT
785 RETURN NEXT return_rec;
787 ELSIF curr_year.year_begin < prev_year.year_end THEN
788 FOR return_rec IN SELECT
791 'Overlapping fiscal years'::TEXT
793 RETURN NEXT return_rec;
795 ELSIF curr_year.year < prev_year.year THEN
796 FOR return_rec IN SELECT
799 'Fiscal years out of order'::TEXT
801 RETURN NEXT return_rec;
806 prev_year := curr_year;
813 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
814 SELECT funding_source,
815 SUM(amount) AS amount
816 FROM acq.funding_source_credit
819 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
820 SELECT funding_source,
821 SUM(a.amount)::NUMERIC(100,2) AS amount
822 FROM acq.fund_allocation a
825 CREATE OR REPLACE VIEW acq.funding_source_balance AS
826 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
827 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
828 FROM acq.funding_source_credit_total c
829 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
832 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
834 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
835 FROM acq.fund_allocation a
836 JOIN acq.fund f ON (a.fund = f.id)
837 JOIN acq.funding_source s ON (a.funding_source = s.id)
840 CREATE OR REPLACE VIEW acq.fund_debit_total AS
843 SUM(amount) AS amount
847 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
849 SUM(amount) AS amount
850 FROM acq.fund_debit_total
851 WHERE encumbrance IS TRUE
854 CREATE OR REPLACE VIEW acq.fund_spent_total AS
856 SUM(amount) AS amount
857 FROM acq.fund_debit_total
858 WHERE encumbrance IS FALSE
861 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
863 c.amount - COALESCE(d.amount,0.0) AS amount
864 FROM acq.fund_allocation_total c
865 LEFT JOIN acq.fund_debit_total d USING (fund);
867 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
869 c.amount - COALESCE(d.amount,0.0) AS amount
870 FROM acq.fund_allocation_total c
871 LEFT JOIN acq.fund_spent_total d USING (fund);