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
107 CREATE TABLE acq.fund (
108 id SERIAL PRIMARY KEY,
109 org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
111 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
112 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
114 rollover BOOL NOT NULL DEFAULT FALSE,
115 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
116 CONSTRAINT code_once_per_org_year UNIQUE (org, code, year)
119 CREATE TABLE acq.fund_debit (
120 id SERIAL PRIMARY KEY,
121 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
122 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
123 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
124 amount NUMERIC NOT NULL,
125 encumbrance BOOL NOT NULL DEFAULT TRUE,
126 debit_type TEXT NOT NULL,
127 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
128 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
131 CREATE TABLE acq.fund_allocation (
132 id SERIAL PRIMARY KEY,
133 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
134 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
135 amount NUMERIC NOT NULL,
136 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
138 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
140 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
142 CREATE TABLE acq.picklist (
143 id SERIAL PRIMARY KEY,
144 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
145 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
146 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
147 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
149 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
150 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
151 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
153 CREATE INDEX acq_picklist_owner_idx ON acq.picklist ( owner );
154 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
155 CREATE INDEX acq_picklist_editor_idx ON acq.picklist ( editor );
157 CREATE TABLE acq.purchase_order (
158 id SERIAL PRIMARY KEY,
159 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
160 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
161 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
162 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
163 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
164 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
165 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
166 state TEXT NOT NULL DEFAULT 'new',
167 order_date TIMESTAMP WITH TIME ZONE,
170 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
171 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
172 CREATE INDEX po_state_idx ON acq.purchase_order (state);
173 CREATE INDEX po_creator_idx ON acq.purchase_order ( creator );
174 CREATE INDEX po_editor_idx ON acq.purchase_order ( editor );
175 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
177 -- The name should default to the id, as text. We can't reference a column
178 -- in a DEFAULT clause, so we use a trigger:
180 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
183 IF NEW.name IS NULL THEN
184 NEW.name := NEW.id::TEXT;
191 CREATE TRIGGER po_name_default_trg
192 BEFORE INSERT OR UPDATE ON acq.purchase_order
193 FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
195 -- The order name should be unique for a given ordering agency on a given order date
196 -- (truncated to midnight), but only where the order_date is not NULL. Conceptually
197 -- this rule requires a check constraint with a subquery. However you can't have a
198 -- subquery in a CHECK constraint, so we fake it with a trigger.
200 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
206 -- If order_date is not null, then make sure we don't have a collision
207 -- on order_date (truncated to day), org, and name
209 IF NEW.order_date IS NULL THEN
213 -- In the WHERE clause, we compare the order_dates without regard to time of day.
214 -- We use a pair of inequalities instead of comparing truncated dates so that the
215 -- query can do an indexed range scan.
217 SELECT 1 INTO collision
218 FROM acq.purchase_order
220 ordering_agency = NEW.ordering_agency
222 AND order_date >= date_trunc( 'day', NEW.order_date )
223 AND order_date < date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
226 IF collision IS NULL THEN
227 -- okay, no collision
230 -- collision; nip it in the bud
231 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
232 NEW.ordering_agency, NEW.order_date, NEW.name;
237 CREATE TRIGGER po_org_name_date_unique_trg
238 BEFORE INSERT OR UPDATE ON acq.purchase_order
239 FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
241 CREATE TABLE acq.po_note (
242 id SERIAL PRIMARY KEY,
243 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
244 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
245 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
246 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
247 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
250 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
251 CREATE INDEX acq_po_note_creator_idx ON acq.po_note ( creator );
252 CREATE INDEX acq_po_note_editor_idx ON acq.po_note ( editor );
254 CREATE TABLE acq.lineitem (
255 id BIGSERIAL PRIMARY KEY,
256 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
257 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
258 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
259 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
260 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
261 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
262 expected_recv_time TIMESTAMP WITH TIME ZONE,
263 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
264 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
266 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
268 item_count INT NOT NULL DEFAULT 0,
269 state TEXT NOT NULL DEFAULT 'new',
270 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
272 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
273 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
274 CREATE INDEX li_creator_idx ON acq.lineitem ( creator );
275 CREATE INDEX li_editor_idx ON acq.lineitem ( editor );
276 CREATE INDEX li_selector_idx ON acq.lineitem ( selector );
278 CREATE TABLE acq.lineitem_note (
279 id SERIAL PRIMARY KEY,
280 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
281 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
282 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
283 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
284 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
287 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
288 CREATE INDEX li_note_creator_idx ON acq.lineitem_note ( creator );
289 CREATE INDEX li_note_editor_idx ON acq.lineitem_note ( editor );
291 CREATE TABLE acq.lineitem_detail (
292 id BIGSERIAL PRIMARY KEY,
293 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
294 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
295 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
296 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
300 collection_code TEXT,
301 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
302 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
303 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
304 recv_time TIMESTAMP WITH TIME ZONE
307 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
309 CREATE TABLE acq.lineitem_attr_definition (
310 id BIGSERIAL PRIMARY KEY,
312 description TEXT NOT NULL,
313 remove TEXT NOT NULL DEFAULT '',
314 ident BOOL NOT NULL DEFAULT FALSE
317 CREATE TABLE acq.lineitem_marc_attr_definition (
318 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
320 ) INHERITS (acq.lineitem_attr_definition);
322 CREATE TABLE acq.lineitem_provider_attr_definition (
323 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
325 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
326 ) INHERITS (acq.lineitem_attr_definition);
328 CREATE TABLE acq.lineitem_generated_attr_definition (
329 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
331 ) INHERITS (acq.lineitem_attr_definition);
333 CREATE TABLE acq.lineitem_usr_attr_definition (
334 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
335 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
336 ) INHERITS (acq.lineitem_attr_definition);
337 CREATE INDEX li_usr_attr_def_usr_idx ON acq.lineitem_usr_attr_definition ( usr );
339 CREATE TABLE acq.lineitem_local_attr_definition (
340 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
341 ) INHERITS (acq.lineitem_attr_definition);
343 CREATE TABLE acq.lineitem_attr (
344 id BIGSERIAL PRIMARY KEY,
345 definition BIGINT NOT NULL,
346 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
347 attr_type TEXT NOT NULL,
348 attr_name TEXT NOT NULL,
349 attr_value TEXT NOT NULL
352 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
353 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
354 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
360 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
361 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)]');
362 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
363 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
364 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
365 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
366 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
367 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
368 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
369 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
370 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
372 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
375 CREATE TABLE acq.distribution_formula (
376 id SERIAL PRIMARY KEY,
378 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
380 skip_count INT NOT NULL DEFAULT 0,
381 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
384 CREATE TABLE acq.distribution_formula_entry (
385 id SERIAL PRIMARY KEY,
386 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
388 DEFERRABLE INITIALLY DEFERRED,
389 position INTEGER NOT NULL,
390 item_count INTEGER NOT NULL,
391 owning_lib INTEGER REFERENCES actor.org_unit(id)
392 DEFERRABLE INITIALLY DEFERRED,
393 location INTEGER REFERENCES asset.copy_location(id),
394 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
395 CONSTRAINT acqdfe_must_be_somewhere
396 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
399 CREATE TABLE acq.fund_tag (
400 id SERIAL PRIMARY KEY,
402 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
404 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
407 CREATE TABLE acq.fund_tag_map (
408 id SERIAL PRIMARY KEY,
409 fund INTEGER NOT NULL REFERENCES acq.fund(id)
410 DEFERRABLE INITIALLY DEFERRED,
411 tag INTEGER REFERENCES acq.fund_tag(id)
413 DEFERRABLE INITIALLY DEFERRED,
414 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
417 CREATE TABLE acq.fund_transfer (
418 id SERIAL PRIMARY KEY,
419 src_fund INT NOT NULL REFERENCES acq.fund( id )
420 DEFERRABLE INITIALLY DEFERRED,
421 src_amount NUMERIC NOT NULL,
422 dest_fund INT NOT NULL REFERENCES acq.fund( id )
423 DEFERRABLE INITIALLY DEFERRED,
424 dest_amount NUMERIC NOT NULL,
425 transfer_time TIMESTAMPTZ NOT NULL DEFAULT now(),
426 transfer_user INT NOT NULL REFERENCES actor.usr( id )
427 DEFERRABLE INITIALLY DEFERRED,
431 CREATE INDEX acqftr_usr_idx
432 ON acq.fund_transfer( transfer_user );
434 COMMENT ON TABLE acq.fund_transfer IS $$
436 * Copyright (C) 2009 Georgia Public Library Service
437 * Scott McKellar <scott@esilibrary.com>
441 * Each row represents the transfer of money from a source fund
442 * to a destination fund. There should be corresponding entries
443 * in acq.fund_allocation. The purpose of acq.fund_transfer is
444 * to record how much money moved from which fund to which other
447 * The presence of two amount fields, rather than one, reflects
448 * the possibility that the two funds are denominated in different
449 * currencies. If they use the same currency type, the two
450 * amounts should be the same.
454 * This program is free software; you can redistribute it and/or
455 * modify it under the terms of the GNU General Public License
456 * as published by the Free Software Foundation; either version 2
457 * of the License, or (at your option) any later version.
459 * This program is distributed in the hope that it will be useful,
460 * but WITHOUT ANY WARRANTY; without even the implied warranty of
461 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
462 * GNU General Public License for more details.
466 CREATE TABLE acq.fiscal_calendar (
467 id SERIAL PRIMARY KEY,
471 -- Create a default calendar (though we don't specify its contents).
472 -- Create a foreign key in actor.org_unit, initially pointing to
473 -- the default calendar.
475 INSERT INTO acq.fiscal_calendar (
482 ALTER TABLE actor.org_unit
483 ADD COLUMN fiscal_calendar INT NOT NULL
484 REFERENCES acq.fiscal_calendar( id )
485 DEFERRABLE INITIALLY DEFERRED
488 CREATE TABLE acq.fiscal_year (
489 id SERIAL PRIMARY KEY,
490 calendar INT NOT NULL
491 REFERENCES acq.fiscal_calendar
493 DEFERRABLE INITIALLY DEFERRED,
495 year_begin TIMESTAMPTZ NOT NULL,
496 year_end TIMESTAMPTZ NOT NULL,
497 CONSTRAINT acq_fy_logical_key UNIQUE ( calendar, year ),
498 CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
503 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
504 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
507 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
510 SELECT COUNT(*) INTO counter
515 '//*[@tag="' || tag || '"]',
517 ) as t(i int,c text);
519 FOR i IN 1 .. counter LOOP
522 FROM ( SELECT id,i,t,v
527 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
528 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
530 ) as t(id int,t text,v text)
541 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
542 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
546 lida acq.flat_lineitem_detail%ROWTYPE;
548 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
549 IF NOT FOUND THEN RETURN; END IF;
551 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
552 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
559 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
560 JOIN acq.provider_holding_subfield_map a USING (subfield)
561 WHERE a.provider = prov_i
570 -- select * from acq.extract_provider_holding_data(699);
572 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
573 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
577 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
578 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
581 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
582 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
586 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
589 Suggested vendor fields:
597 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
605 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
607 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
609 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
610 IF (atype = 'lineitem_provider_attr_definition') THEN
611 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
612 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
615 IF (atype = 'lineitem_provider_attr_definition') THEN
616 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
617 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
618 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
619 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
620 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
623 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
625 IF (value IS NOT NULL AND value <> '') THEN
626 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
627 VALUES (NEW.id, adef.id, atype, adef.code, value);
638 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
640 IF TG_OP = 'UPDATE' THEN
641 DELETE FROM acq.lineitem_attr
642 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
645 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
651 CREATE TRIGGER cleanup_lineitem_trigger
652 BEFORE UPDATE OR DELETE ON acq.lineitem
653 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
655 CREATE TRIGGER ingest_lineitem_trigger
656 AFTER INSERT OR UPDATE ON acq.lineitem
657 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
659 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
663 IF from_ex = to_ex THEN
667 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
672 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
683 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
684 SELECT $3 * acq.exchange_ratio($1, $2);
687 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
689 Examine the acq.fiscal_year table, comparing successive years.
690 Report any inconsistencies, i.e. years that overlap, have gaps
691 between them, or are out of sequence.
693 RETURNS SETOF RECORD AS $$
717 ELSIF curr_year.calendar = prev_year.calendar THEN
718 IF curr_year.year_begin > prev_year.year_end THEN
719 -- This ugly kludge works around the fact that older
720 -- versions of PostgreSQL don't support RETURN QUERY SELECT
721 FOR return_rec IN SELECT
724 'Gap between fiscal years'::TEXT
726 RETURN NEXT return_rec;
728 ELSIF curr_year.year_begin < prev_year.year_end THEN
729 FOR return_rec IN SELECT
732 'Overlapping fiscal years'::TEXT
734 RETURN NEXT return_rec;
736 ELSIF curr_year.year < prev_year.year THEN
737 FOR return_rec IN SELECT
740 'Fiscal years out of order'::TEXT
742 RETURN NEXT return_rec;
747 prev_year := curr_year;
754 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
755 SELECT funding_source,
756 SUM(amount) AS amount
757 FROM acq.funding_source_credit
760 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
761 SELECT funding_source,
762 SUM(a.amount)::NUMERIC(100,2) AS amount
763 FROM acq.fund_allocation a
766 CREATE OR REPLACE VIEW acq.funding_source_balance AS
767 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
768 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
769 FROM acq.funding_source_credit_total c
770 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
773 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
775 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
776 FROM acq.fund_allocation a
777 JOIN acq.fund f ON (a.fund = f.id)
778 JOIN acq.funding_source s ON (a.funding_source = s.id)
781 CREATE OR REPLACE VIEW acq.fund_debit_total AS
784 SUM(amount) AS amount
788 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
790 SUM(amount) AS amount
791 FROM acq.fund_debit_total
792 WHERE encumbrance IS TRUE
795 CREATE OR REPLACE VIEW acq.fund_spent_total AS
797 SUM(amount) AS amount
798 FROM acq.fund_debit_total
799 WHERE encumbrance IS FALSE
802 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
804 c.amount - COALESCE(d.amount,0.0) AS amount
805 FROM acq.fund_allocation_total c
806 LEFT JOIN acq.fund_debit_total d USING (fund);
808 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
810 c.amount - COALESCE(d.amount,0.0) AS amount
811 FROM acq.fund_allocation_total c
812 LEFT JOIN acq.fund_spent_total d USING (fund);