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,
106 CREATE TABLE acq.fund (
107 id SERIAL PRIMARY KEY,
108 org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
110 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
111 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
113 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
114 CONSTRAINT code_once_per_org_year UNIQUE (org, code, year)
117 CREATE TABLE acq.fund_debit (
118 id SERIAL PRIMARY KEY,
119 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
120 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
121 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
122 amount NUMERIC NOT NULL,
123 encumbrance BOOL NOT NULL DEFAULT TRUE,
124 debit_type TEXT NOT NULL,
125 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
126 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
129 CREATE TABLE acq.fund_allocation (
130 id SERIAL PRIMARY KEY,
131 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
132 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
133 amount NUMERIC NOT NULL,
134 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
136 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
138 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
140 CREATE TABLE acq.picklist (
141 id SERIAL PRIMARY KEY,
142 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
143 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
144 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
145 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
147 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
148 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
149 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
151 CREATE INDEX acq_picklist_owner_idx ON acq.picklist ( owner );
152 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
153 CREATE INDEX acq_picklist_editor_idx ON acq.picklist ( editor );
155 CREATE TABLE acq.purchase_order (
156 id SERIAL PRIMARY KEY,
157 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
158 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
159 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
160 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
161 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
162 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
163 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
164 state TEXT NOT NULL DEFAULT 'new',
165 order_date TIMESTAMP WITH TIME ZONE,
168 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
169 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
170 CREATE INDEX po_state_idx ON acq.purchase_order (state);
171 CREATE INDEX po_creator_idx ON acq.purchase_order ( creator );
172 CREATE INDEX po_editor_idx ON acq.purchase_order ( editor );
173 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
175 -- The name should default to the id, as text. We can't reference a column
176 -- in a DEFAULT clause, so we use a trigger:
178 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
181 IF NEW.name IS NULL THEN
182 NEW.name := NEW.id::TEXT;
189 CREATE TRIGGER po_name_default_trg
190 BEFORE INSERT OR UPDATE ON acq.purchase_order
191 FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
193 -- The order name should be unique for a given ordering agency on a given order date
194 -- (truncated to midnight), but only where the order_date is not NULL. Conceptually
195 -- this rule requires a check constraint with a subquery. However you can't have a
196 -- subquery in a CHECK constraint, so we fake it with a trigger.
198 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
204 -- If order_date is not null, then make sure we don't have a collision
205 -- on order_date (truncated to day), org, and name
207 IF NEW.order_date IS NULL THEN
211 -- In the WHERE clause, we compare the order_dates without regard to time of day.
212 -- We use a pair of inequalities instead of comparing truncated dates so that the
213 -- query can do an indexed range scan.
215 SELECT 1 INTO collision
216 FROM acq.purchase_order
218 ordering_agency = NEW.ordering_agency
220 AND order_date >= date_trunc( 'day', NEW.order_date )
221 AND order_date < date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
224 IF collision IS NULL THEN
225 -- okay, no collision
228 -- collision; nip it in the bud
229 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
230 NEW.ordering_agency, NEW.order_date, NEW.name;
235 CREATE TRIGGER po_org_name_date_unique_trg
236 BEFORE INSERT OR UPDATE ON acq.purchase_order
237 FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
239 CREATE TABLE acq.po_note (
240 id SERIAL PRIMARY KEY,
241 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
242 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
243 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
244 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
245 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
248 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
249 CREATE INDEX acq_po_note_creator_idx ON acq.po_note ( creator );
250 CREATE INDEX acq_po_note_editor_idx ON acq.po_note ( editor );
252 CREATE TABLE acq.lineitem (
253 id BIGSERIAL PRIMARY KEY,
254 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
255 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
256 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
257 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
258 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
259 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
260 expected_recv_time TIMESTAMP WITH TIME ZONE,
261 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
262 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
264 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
266 item_count INT NOT NULL DEFAULT 0,
267 state TEXT NOT NULL DEFAULT 'new',
268 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
270 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
271 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
272 CREATE INDEX li_creator_idx ON acq.lineitem ( creator );
273 CREATE INDEX li_editor_idx ON acq.lineitem ( editor );
274 CREATE INDEX li_selector_idx ON acq.lineitem ( selector );
276 CREATE TABLE acq.lineitem_note (
277 id SERIAL PRIMARY KEY,
278 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
279 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
280 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
281 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
282 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
285 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
286 CREATE INDEX li_note_creator_idx ON acq.lineitem_note ( creator );
287 CREATE INDEX li_note_editor_idx ON acq.lineitem_note ( editor );
289 CREATE TABLE acq.lineitem_detail (
290 id BIGSERIAL PRIMARY KEY,
291 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
292 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
293 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
294 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
298 collection_code TEXT,
299 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
300 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
301 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
302 recv_time TIMESTAMP WITH TIME ZONE
305 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
307 CREATE TABLE acq.lineitem_attr_definition (
308 id BIGSERIAL PRIMARY KEY,
310 description TEXT NOT NULL,
311 remove TEXT NOT NULL DEFAULT '',
312 ident BOOL NOT NULL DEFAULT FALSE
315 CREATE TABLE acq.lineitem_marc_attr_definition (
316 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
318 ) INHERITS (acq.lineitem_attr_definition);
320 CREATE TABLE acq.lineitem_provider_attr_definition (
321 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
323 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
324 ) INHERITS (acq.lineitem_attr_definition);
326 CREATE TABLE acq.lineitem_generated_attr_definition (
327 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
329 ) INHERITS (acq.lineitem_attr_definition);
331 CREATE TABLE acq.lineitem_usr_attr_definition (
332 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
333 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
334 ) INHERITS (acq.lineitem_attr_definition);
335 CREATE INDEX li_usr_attr_def_usr_idx ON acq.lineitem_usr_attr_definition ( usr );
337 CREATE TABLE acq.lineitem_local_attr_definition (
338 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
339 ) INHERITS (acq.lineitem_attr_definition);
341 CREATE TABLE acq.lineitem_attr (
342 id BIGSERIAL PRIMARY KEY,
343 definition BIGINT NOT NULL,
344 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
345 attr_type TEXT NOT NULL,
346 attr_name TEXT NOT NULL,
347 attr_value TEXT NOT NULL
350 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
351 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
352 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
358 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
359 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)]');
360 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
361 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
362 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
363 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
364 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
365 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
366 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
367 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
368 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
370 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
373 CREATE TABLE acq.distribution_formula (
374 id SERIAL PRIMARY KEY,
376 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
378 skip_count INT NOT NULL DEFAULT 0,
379 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
382 CREATE TABLE acq.distribution_formula_entry (
383 id SERIAL PRIMARY KEY,
384 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
386 DEFERRABLE INITIALLY DEFERRED,
387 position INTEGER NOT NULL,
388 item_count INTEGER NOT NULL,
389 owning_lib INTEGER REFERENCES actor.org_unit(id)
390 DEFERRABLE INITIALLY DEFERRED,
391 location INTEGER REFERENCES asset.copy_location(id),
392 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
393 CONSTRAINT acqdfe_must_be_somewhere
394 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
397 CREATE TABLE acq.fund_tag (
398 id SERIAL PRIMARY KEY,
400 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
402 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
405 CREATE TABLE acq.fund_tag_map (
406 id SERIAL PRIMARY KEY,
407 fund INTEGER NOT NULL REFERENCES acq.fund(id)
408 DEFERRABLE INITIALLY DEFERRED,
409 tag INTEGER REFERENCES acq.fund_tag(id)
411 DEFERRABLE INITIALLY DEFERRED,
412 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
415 CREATE TABLE acq.fiscal_calendar (
416 id SERIAL PRIMARY KEY,
420 -- Create a default calendar (though we don't specify its contents).
421 -- Create a foreign key in actor.org_unit, initially pointing to
422 -- the default calendar.
424 INSERT INTO acq.fiscal_calendar (
431 ALTER TABLE actor.org_unit
432 ADD COLUMN fiscal_calendar INT NOT NULL
433 REFERENCES acq.fiscal_calendar( id )
434 DEFERRABLE INITIALLY DEFERRED
437 CREATE TABLE acq.fiscal_year (
438 id SERIAL PRIMARY KEY,
439 calendar INT NOT NULL
440 REFERENCES acq.fiscal_calendar
442 DEFERRABLE INITIALLY DEFERRED,
444 year_begin TIMESTAMPTZ NOT NULL,
445 year_end TIMESTAMPTZ NOT NULL,
446 CONSTRAINT acq_fy_logical_key UNIQUE ( calendar, year ),
447 CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
452 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
453 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
456 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
459 SELECT COUNT(*) INTO counter
464 '//*[@tag="' || tag || '"]',
466 ) as t(i int,c text);
468 FOR i IN 1 .. counter LOOP
471 FROM ( SELECT id,i,t,v
476 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
477 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
479 ) as t(id int,t text,v text)
490 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
491 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
495 lida acq.flat_lineitem_detail%ROWTYPE;
497 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
498 IF NOT FOUND THEN RETURN; END IF;
500 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
501 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
508 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
509 JOIN acq.provider_holding_subfield_map a USING (subfield)
510 WHERE a.provider = prov_i
519 -- select * from acq.extract_provider_holding_data(699);
521 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
522 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
526 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
527 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
530 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
531 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
535 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
538 Suggested vendor fields:
546 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
554 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
556 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
558 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
559 IF (atype = 'lineitem_provider_attr_definition') THEN
560 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
561 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
564 IF (atype = 'lineitem_provider_attr_definition') THEN
565 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
566 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
567 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
568 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
569 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
572 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
574 IF (value IS NOT NULL AND value <> '') THEN
575 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
576 VALUES (NEW.id, adef.id, atype, adef.code, value);
587 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
589 IF TG_OP = 'UPDATE' THEN
590 DELETE FROM acq.lineitem_attr
591 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
594 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
600 CREATE TRIGGER cleanup_lineitem_trigger
601 BEFORE UPDATE OR DELETE ON acq.lineitem
602 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
604 CREATE TRIGGER ingest_lineitem_trigger
605 AFTER INSERT OR UPDATE ON acq.lineitem
606 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
608 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
612 IF from_ex = to_ex THEN
616 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
621 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
632 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
633 SELECT $3 * acq.exchange_ratio($1, $2);
636 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
638 Examine the acq.fiscal_year table, comparing successive years.
639 Report any inconsistencies, i.e. years that overlap, have gaps
640 between them, or are out of sequence.
642 RETURNS SETOF RECORD AS $$
666 ELSIF curr_year.calendar = prev_year.calendar THEN
667 IF curr_year.year_begin > prev_year.year_end THEN
668 -- This ugly kludge works around the fact that older
669 -- versions of PostgreSQL don't support RETURN QUERY SELECT
670 FOR return_rec IN SELECT
673 'Gap between fiscal years'::TEXT
675 RETURN NEXT return_rec;
677 ELSIF curr_year.year_begin < prev_year.year_end THEN
678 FOR return_rec IN SELECT
681 'Overlapping fiscal years'::TEXT
683 RETURN NEXT return_rec;
685 ELSIF curr_year.year < prev_year.year THEN
686 FOR return_rec IN SELECT
689 'Fiscal years out of order'::TEXT
691 RETURN NEXT return_rec;
696 prev_year := curr_year;
703 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
704 SELECT funding_source,
705 SUM(amount) AS amount
706 FROM acq.funding_source_credit
709 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
710 SELECT funding_source,
711 SUM(a.amount)::NUMERIC(100,2) AS amount
712 FROM acq.fund_allocation a
715 CREATE OR REPLACE VIEW acq.funding_source_balance AS
716 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
717 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
718 FROM acq.funding_source_credit_total c
719 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
722 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
724 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
725 FROM acq.fund_allocation a
726 JOIN acq.fund f ON (a.fund = f.id)
727 JOIN acq.funding_source s ON (a.funding_source = s.id)
730 CREATE OR REPLACE VIEW acq.fund_debit_total AS
733 SUM(amount) AS amount
737 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
739 SUM(amount) AS amount
740 FROM acq.fund_debit_total
741 WHERE encumbrance IS TRUE
744 CREATE OR REPLACE VIEW acq.fund_spent_total AS
746 SUM(amount) AS amount
747 FROM acq.fund_debit_total
748 WHERE encumbrance IS FALSE
751 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
753 c.amount - COALESCE(d.amount,0.0) AS amount
754 FROM acq.fund_allocation_total c
755 LEFT JOIN acq.fund_debit_total d USING (fund);
757 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
759 c.amount - COALESCE(d.amount,0.0) AS amount
760 FROM acq.fund_allocation_total c
761 LEFT JOIN acq.fund_spent_total d USING (fund);