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 active BOOL NOT NULL DEFAULT TRUE,
175 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
176 CONSTRAINT code_once_per_org_year UNIQUE (org, code, year),
177 CONSTRAINT acq_fund_rollover_implies_propagate CHECK ( propagate OR NOT rollover )
180 CREATE TABLE acq.fund_debit (
181 id SERIAL PRIMARY KEY,
182 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
183 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
184 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
185 amount NUMERIC NOT NULL,
186 encumbrance BOOL NOT NULL DEFAULT TRUE,
187 debit_type TEXT NOT NULL,
188 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
189 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
192 CREATE TABLE acq.fund_allocation (
193 id SERIAL PRIMARY KEY,
194 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
195 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
196 amount NUMERIC NOT NULL,
197 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
199 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
201 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
203 CREATE TABLE acq.fund_allocation_percent
205 id SERIAL PRIMARY KEY,
206 funding_source INT NOT NULL REFERENCES acq.funding_source
207 DEFERRABLE INITIALLY DEFERRED,
208 org INT NOT NULL REFERENCES actor.org_unit
209 DEFERRABLE INITIALLY DEFERRED,
211 percent NUMERIC NOT NULL,
212 allocator INTEGER NOT NULL REFERENCES actor.usr
213 DEFERRABLE INITIALLY DEFERRED,
215 create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
216 CONSTRAINT logical_key UNIQUE( funding_source, org, fund_code ),
217 CONSTRAINT percentage_range CHECK( percent >= 0 AND percent <= 100 )
220 -- Trigger function to validate combination of org_unit and fund_code
222 CREATE OR REPLACE FUNCTION acq.fund_alloc_percent_val()
223 RETURNS TRIGGER AS $$
238 AND code = NEW.fund_code
244 RAISE EXCEPTION 'No fund exists for org % and code %', NEW.org, NEW.fund_code;
249 CREATE TRIGGER acq_fund_alloc_percent_val_trig
250 BEFORE INSERT OR UPDATE ON acq.fund_allocation_percent
251 FOR EACH ROW EXECUTE PROCEDURE acq.fund_alloc_percent_val();
253 -- To do: trigger to verify that percentages don't add up to more than 100
255 CREATE OR REPLACE FUNCTION acq.fap_limit_100()
256 RETURNS TRIGGER AS $$
259 total_percent numeric;
267 acq.fund_allocation_percent AS fap
269 fap.funding_source = NEW.funding_source;
271 IF total_percent > 100 THEN
272 RAISE EXCEPTION 'Total percentages exceed 100 for funding_source %',
280 CREATE TRIGGER acqfap_limit_100_trig
281 AFTER INSERT OR UPDATE ON acq.fund_allocation_percent
282 FOR EACH ROW EXECUTE PROCEDURE acq.fap_limit_100();
284 CREATE TABLE acq.picklist (
285 id SERIAL PRIMARY KEY,
286 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
287 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
288 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
289 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
291 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
292 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
293 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
295 CREATE INDEX acq_picklist_owner_idx ON acq.picklist ( owner );
296 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
297 CREATE INDEX acq_picklist_editor_idx ON acq.picklist ( editor );
299 CREATE TABLE acq.purchase_order (
300 id SERIAL PRIMARY KEY,
301 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
302 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
303 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
304 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
305 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
306 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
307 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
308 state TEXT NOT NULL DEFAULT 'new',
309 order_date TIMESTAMP WITH TIME ZONE,
312 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
313 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
314 CREATE INDEX po_state_idx ON acq.purchase_order (state);
315 CREATE INDEX po_creator_idx ON acq.purchase_order ( creator );
316 CREATE INDEX po_editor_idx ON acq.purchase_order ( editor );
317 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
319 -- The name should default to the id, as text. We can't reference a column
320 -- in a DEFAULT clause, so we use a trigger:
322 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
325 IF NEW.name IS NULL THEN
326 NEW.name := NEW.id::TEXT;
333 CREATE TRIGGER po_name_default_trg
334 BEFORE INSERT OR UPDATE ON acq.purchase_order
335 FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
337 -- The order name should be unique for a given ordering agency on a given order date
338 -- (truncated to midnight), but only where the order_date is not NULL. Conceptually
339 -- this rule requires a check constraint with a subquery. However you can't have a
340 -- subquery in a CHECK constraint, so we fake it with a trigger.
342 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
348 -- If order_date is not null, then make sure we don't have a collision
349 -- on order_date (truncated to day), org, and name
351 IF NEW.order_date IS NULL THEN
355 -- In the WHERE clause, we compare the order_dates without regard to time of day.
356 -- We use a pair of inequalities instead of comparing truncated dates so that the
357 -- query can do an indexed range scan.
359 SELECT 1 INTO collision
360 FROM acq.purchase_order
362 ordering_agency = NEW.ordering_agency
364 AND order_date >= date_trunc( 'day', NEW.order_date )
365 AND order_date < date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
368 IF collision IS NULL THEN
369 -- okay, no collision
372 -- collision; nip it in the bud
373 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
374 NEW.ordering_agency, NEW.order_date, NEW.name;
379 CREATE TRIGGER po_org_name_date_unique_trg
380 BEFORE INSERT OR UPDATE ON acq.purchase_order
381 FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
383 CREATE TABLE acq.po_note (
384 id SERIAL PRIMARY KEY,
385 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
386 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
387 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
388 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
389 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
392 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
393 CREATE INDEX acq_po_note_creator_idx ON acq.po_note ( creator );
394 CREATE INDEX acq_po_note_editor_idx ON acq.po_note ( editor );
396 CREATE TABLE acq.lineitem (
397 id BIGSERIAL PRIMARY KEY,
398 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
399 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
400 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
401 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
402 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
403 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
404 expected_recv_time TIMESTAMP WITH TIME ZONE,
405 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
406 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
408 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
410 item_count INT NOT NULL DEFAULT 0,
411 state TEXT NOT NULL DEFAULT 'new',
412 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
414 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
415 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
416 CREATE INDEX li_creator_idx ON acq.lineitem ( creator );
417 CREATE INDEX li_editor_idx ON acq.lineitem ( editor );
418 CREATE INDEX li_selector_idx ON acq.lineitem ( selector );
420 CREATE TABLE acq.lineitem_note (
421 id SERIAL PRIMARY KEY,
422 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
423 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
424 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
425 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
426 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
429 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
430 CREATE INDEX li_note_creator_idx ON acq.lineitem_note ( creator );
431 CREATE INDEX li_note_editor_idx ON acq.lineitem_note ( editor );
433 CREATE TABLE acq.lineitem_detail (
434 id BIGSERIAL PRIMARY KEY,
435 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
436 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
437 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
438 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
442 collection_code TEXT,
443 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
444 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
445 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
446 recv_time TIMESTAMP WITH TIME ZONE
449 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
451 CREATE TABLE acq.lineitem_attr_definition (
452 id BIGSERIAL PRIMARY KEY,
454 description TEXT NOT NULL,
455 remove TEXT NOT NULL DEFAULT '',
456 ident BOOL NOT NULL DEFAULT FALSE
459 CREATE TABLE acq.lineitem_marc_attr_definition (
460 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
462 ) INHERITS (acq.lineitem_attr_definition);
464 CREATE TABLE acq.lineitem_provider_attr_definition (
465 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
467 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
468 ) INHERITS (acq.lineitem_attr_definition);
470 CREATE TABLE acq.lineitem_generated_attr_definition (
471 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
473 ) INHERITS (acq.lineitem_attr_definition);
475 CREATE TABLE acq.lineitem_usr_attr_definition (
476 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
477 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
478 ) INHERITS (acq.lineitem_attr_definition);
479 CREATE INDEX li_usr_attr_def_usr_idx ON acq.lineitem_usr_attr_definition ( usr );
481 CREATE TABLE acq.lineitem_local_attr_definition (
482 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
483 ) INHERITS (acq.lineitem_attr_definition);
485 CREATE TABLE acq.lineitem_attr (
486 id BIGSERIAL PRIMARY KEY,
487 definition BIGINT NOT NULL,
488 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
489 attr_type TEXT NOT NULL,
490 attr_name TEXT NOT NULL,
491 attr_value TEXT NOT NULL
494 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
495 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
496 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
502 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
503 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)]');
504 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
505 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
506 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
507 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
508 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
509 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
510 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
511 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
512 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
514 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
517 CREATE TABLE acq.distribution_formula (
518 id SERIAL PRIMARY KEY,
520 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
522 skip_count INT NOT NULL DEFAULT 0,
523 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
526 CREATE TABLE acq.distribution_formula_entry (
527 id SERIAL PRIMARY KEY,
528 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
530 DEFERRABLE INITIALLY DEFERRED,
531 position INTEGER NOT NULL,
532 item_count INTEGER NOT NULL,
533 owning_lib INTEGER REFERENCES actor.org_unit(id)
534 DEFERRABLE INITIALLY DEFERRED,
535 location INTEGER REFERENCES asset.copy_location(id),
536 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
537 CONSTRAINT acqdfe_must_be_somewhere
538 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
541 CREATE TABLE acq.fund_tag (
542 id SERIAL PRIMARY KEY,
544 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
546 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
549 CREATE TABLE acq.fund_tag_map (
550 id SERIAL PRIMARY KEY,
551 fund INTEGER NOT NULL REFERENCES acq.fund(id)
552 DEFERRABLE INITIALLY DEFERRED,
553 tag INTEGER REFERENCES acq.fund_tag(id)
555 DEFERRABLE INITIALLY DEFERRED,
556 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
559 CREATE TABLE acq.fund_transfer (
560 id SERIAL PRIMARY KEY,
561 src_fund INT NOT NULL REFERENCES acq.fund( id )
562 DEFERRABLE INITIALLY DEFERRED,
563 src_amount NUMERIC NOT NULL,
564 dest_fund INT NOT NULL REFERENCES acq.fund( id )
565 DEFERRABLE INITIALLY DEFERRED,
566 dest_amount NUMERIC NOT NULL,
567 transfer_time TIMESTAMPTZ NOT NULL DEFAULT now(),
568 transfer_user INT NOT NULL REFERENCES actor.usr( id )
569 DEFERRABLE INITIALLY DEFERRED,
571 funding_source_credit INT NOT NULL REFERENCES acq.funding_source_credit( id )
572 DEFERRABLE INITIALLY DEFERRED
575 CREATE INDEX acqftr_usr_idx
576 ON acq.fund_transfer( transfer_user );
578 COMMENT ON TABLE acq.fund_transfer IS $$
580 * Copyright (C) 2009 Georgia Public Library Service
581 * Scott McKellar <scott@esilibrary.com>
585 * Each row represents the transfer of money from a source fund
586 * to a destination fund. There should be corresponding entries
587 * in acq.fund_allocation. The purpose of acq.fund_transfer is
588 * to record how much money moved from which fund to which other
591 * The presence of two amount fields, rather than one, reflects
592 * the possibility that the two funds are denominated in different
593 * currencies. If they use the same currency type, the two
594 * amounts should be the same.
598 * This program is free software; you can redistribute it and/or
599 * modify it under the terms of the GNU General Public License
600 * as published by the Free Software Foundation; either version 2
601 * of the License, or (at your option) any later version.
603 * This program is distributed in the hope that it will be useful,
604 * but WITHOUT ANY WARRANTY; without even the implied warranty of
605 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
606 * GNU General Public License for more details.
610 CREATE TABLE acq.fiscal_calendar (
611 id SERIAL PRIMARY KEY,
615 -- Create a default calendar (though we don't specify its contents).
616 -- Create a foreign key in actor.org_unit, initially pointing to
617 -- the default calendar.
619 INSERT INTO acq.fiscal_calendar (
626 ALTER TABLE actor.org_unit ADD FOREIGN KEY
627 (fiscal_calendar) REFERENCES acq.fiscal_calendar( id )
628 DEFERRABLE INITIALLY DEFERRED;
630 CREATE TABLE acq.fiscal_year (
631 id SERIAL PRIMARY KEY,
632 calendar INT NOT NULL
633 REFERENCES acq.fiscal_calendar
635 DEFERRABLE INITIALLY DEFERRED,
637 year_begin TIMESTAMPTZ NOT NULL,
638 year_end TIMESTAMPTZ NOT NULL,
639 CONSTRAINT acq_fy_logical_key UNIQUE ( calendar, year ),
640 CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
643 CREATE TABLE acq.edi_account ( -- similar tables can extend remote_account for other parts of EG
644 provider INT NOT NULL REFERENCES acq.provider (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
645 in_dir TEXT -- incoming messages dir (probably different than config.remote_account.path, the outgoing dir)
646 ) INHERITS (config.remote_account);
648 -- We need a UNIQUE constraint here also, to support the FK from acq.provider.edi_default
649 ALTER TABLE acq.edi_account ADD CONSTRAINT acq_edi_account_id_unique UNIQUE (id);
651 -- Note below that the primary key is NOT a SERIAL type. We will periodically truncate and rebuild
652 -- the table, assigning ids programmatically instead of using a sequence.
653 CREATE TABLE acq.debit_attribution (
654 id INT NOT NULL PRIMARY KEY,
655 fund_debit INT NOT NULL
656 REFERENCES acq.fund_debit
657 DEFERRABLE INITIALLY DEFERRED,
658 debit_amount NUMERIC NOT NULL,
659 funding_source_credit INT REFERENCES acq.funding_source_credit
660 DEFERRABLE INITIALLY DEFERRED,
661 credit_amount NUMERIC
664 CREATE INDEX acq_attribution_debit_idx
665 ON acq.debit_attribution( fund_debit );
667 CREATE INDEX acq_attribution_credit_idx
668 ON acq.debit_attribution( funding_source_credit );
672 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
673 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
676 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
679 SELECT COUNT(*) INTO counter
680 FROM oils_xpath_table(
684 '//*[@tag="' || tag || '"]',
686 ) as t(i int,c text);
688 FOR i IN 1 .. counter LOOP
691 FROM ( SELECT id,i,t,v
692 FROM oils_xpath_table(
696 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
697 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
699 ) as t(id int,t text,v text)
710 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
711 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
715 lida acq.flat_lineitem_detail%ROWTYPE;
717 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
718 IF NOT FOUND THEN RETURN; END IF;
720 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
721 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
728 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
729 JOIN acq.provider_holding_subfield_map a USING (subfield)
730 WHERE a.provider = prov_i
739 -- select * from acq.extract_provider_holding_data(699);
741 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
742 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
746 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
747 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
750 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
751 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
755 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
758 Suggested vendor fields:
766 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
774 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
776 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
778 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
779 IF (atype = 'lineitem_provider_attr_definition') THEN
780 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
781 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
784 IF (atype = 'lineitem_provider_attr_definition') THEN
785 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
786 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
787 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
788 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
789 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
792 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
794 IF (value IS NOT NULL AND value <> '') THEN
795 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
796 VALUES (NEW.id, adef.id, atype, adef.code, value);
807 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
809 IF TG_OP = 'UPDATE' THEN
810 DELETE FROM acq.lineitem_attr
811 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
814 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
820 CREATE TRIGGER cleanup_lineitem_trigger
821 BEFORE UPDATE OR DELETE ON acq.lineitem
822 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
824 CREATE TRIGGER ingest_lineitem_trigger
825 AFTER INSERT OR UPDATE ON acq.lineitem
826 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
828 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
832 IF from_ex = to_ex THEN
836 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
841 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
852 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
853 SELECT $3 * acq.exchange_ratio($1, $2);
856 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
858 Examine the acq.fiscal_year table, comparing successive years.
859 Report any inconsistencies, i.e. years that overlap, have gaps
860 between them, or are out of sequence.
862 RETURNS SETOF RECORD AS $$
886 ELSIF curr_year.calendar = prev_year.calendar THEN
887 IF curr_year.year_begin > prev_year.year_end THEN
888 -- This ugly kludge works around the fact that older
889 -- versions of PostgreSQL don't support RETURN QUERY SELECT
890 FOR return_rec IN SELECT
893 'Gap between fiscal years'::TEXT
895 RETURN NEXT return_rec;
897 ELSIF curr_year.year_begin < prev_year.year_end THEN
898 FOR return_rec IN SELECT
901 'Overlapping fiscal years'::TEXT
903 RETURN NEXT return_rec;
905 ELSIF curr_year.year < prev_year.year THEN
906 FOR return_rec IN SELECT
909 'Fiscal years out of order'::TEXT
911 RETURN NEXT return_rec;
916 prev_year := curr_year;
923 CREATE OR REPLACE FUNCTION acq.transfer_fund(
925 old_amount IN NUMERIC, -- in currency of old fund
927 new_amount IN NUMERIC, -- in currency of new fund
929 xfer_note IN TEXT -- to be recorded in acq.fund_transfer
930 -- ,funding_source_in IN INT -- if user wants to specify a funding source (see notes)
932 /* -------------------------------------------------------------------------------
934 Function to transfer money from one fund to another.
936 A transfer is represented as a pair of entries in acq.fund_allocation, with a
937 negative amount for the old (losing) fund and a positive amount for the new
938 (gaining) fund. In some cases there may be more than one such pair of entries
939 in order to pull the money from different funding sources, or more specifically
940 from different funding source credits. For each such pair there is also an
941 entry in acq.fund_transfer.
943 Since funding_source is a non-nullable column in acq.fund_allocation, we must
944 choose a funding source for the transferred money to come from. This choice
945 must meet two constraints, so far as possible:
947 1. The amount transferred from a given funding source must not exceed the
948 amount allocated to the old fund by the funding source. To that end we
949 compare the amount being transferred to the amount allocated.
951 2. We shouldn't transfer money that has already been spent or encumbered, as
952 defined by the funding attribution process. We attribute expenses to the
953 oldest funding source credits first. In order to avoid transferring that
954 attributed money, we reverse the priority, transferring from the newest funding
955 source credits first. There can be no guarantee that this approach will
956 avoid overcommitting a fund, but no other approach can do any better.
958 In this context the age of a funding source credit is defined by the
959 deadline_date for credits with deadline_dates, and by the effective_date for
960 credits without deadline_dates, with the proviso that credits with deadline_dates
961 are all considered "older" than those without.
965 In the signature for this function, there is one last parameter commented out,
966 named "funding_source_in". Correspondingly, the WHERE clause for the query
967 driving the main loop has an OR clause commented out, which references the
968 funding_source_in parameter.
970 If these lines are uncommented, this function will allow the user optionally to
971 restrict a fund transfer to a specified funding source. If the source
972 parameter is left NULL, then there will be no such restriction.
974 ------------------------------------------------------------------------------- */
976 same_currency BOOLEAN;
977 currency_ratio NUMERIC;
978 old_fund_currency TEXT;
979 old_remaining NUMERIC; -- in currency of old fund
980 new_fund_currency TEXT;
981 new_fund_active BOOLEAN;
982 new_remaining NUMERIC; -- in currency of new fund
983 curr_old_amt NUMERIC; -- in currency of old fund
984 curr_new_amt NUMERIC; -- in currency of new fund
985 source_addition NUMERIC; -- in currency of funding source
986 source_deduction NUMERIC; -- in currency of funding source
987 orig_allocated_amt NUMERIC; -- in currency of funding source
988 allocated_amt NUMERIC; -- in currency of fund
994 IF old_fund IS NULL THEN
995 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
998 IF old_amount IS NULL THEN
999 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
1002 -- The new fund and its amount must be both NULL or both not NULL.
1004 IF new_fund IS NOT NULL AND new_amount IS NULL THEN
1005 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
1008 IF new_fund IS NULL AND new_amount IS NOT NULL THEN
1009 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
1012 IF user_id IS NULL THEN
1013 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
1016 -- Initialize the amounts to be transferred, each denominated
1017 -- in the currency of its respective fund. They will be
1018 -- reduced on each iteration of the loop.
1020 old_remaining := old_amount;
1021 new_remaining := new_amount;
1023 -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
1024 -- old_amount, old_fund, new_amount, new_fund;
1026 -- Get the currency types of the old and new funds.
1037 IF old_fund_currency IS NULL THEN
1038 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
1041 IF new_fund IS NOT NULL THEN
1053 IF new_fund_currency IS NULL THEN
1054 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
1055 ELSIF NOT new_fund_active THEN
1057 -- No point in putting money into a fund from whence you can't spend it
1059 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
1062 IF new_amount = old_amount THEN
1063 same_currency := true;
1064 currency_ratio := 1;
1067 -- We'll have to translate currency between funds. We presume that
1068 -- the calling code has already applied an appropriate exchange rate,
1069 -- so we'll apply the same conversion to each sub-transfer.
1071 same_currency := false;
1072 currency_ratio := new_amount / old_amount;
1076 -- Identify the funding source(s) from which we want to transfer the money.
1077 -- The principle is that we want to transfer the newest money first, because
1078 -- we spend the oldest money first. The priority for spending is defined
1079 -- by a sort of the view acq.ordered_funding_source_credit.
1084 ofsc.funding_source,
1086 ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
1090 acq.ordered_funding_source_credit AS ofsc,
1091 acq.funding_source fs
1093 ofsc.funding_source = fs.id
1094 and ofsc.funding_source IN
1096 SELECT funding_source
1097 FROM acq.fund_allocation
1098 WHERE fund = old_fund
1102 -- ofsc.funding_source = funding_source_in
1103 -- OR funding_source_in IS NULL
1106 ofsc.sort_priority desc,
1107 ofsc.sort_date desc,
1111 -- Determine how much money the old fund got from this funding source,
1112 -- denominated in the currency types of the source and of the fund.
1113 -- This result may reflect transfers from previous iterations.
1116 COALESCE( sum( amount ), 0 ),
1117 COALESCE( sum( amount )
1118 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
1120 orig_allocated_amt, -- in currency of the source
1121 allocated_amt -- in currency of the old fund
1126 and funding_source = source.funding_source;
1128 -- Determine how much to transfer from this credit, in the currency
1129 -- of the fund. Begin with the amount remaining to be attributed:
1131 curr_old_amt := old_remaining;
1133 -- Can't attribute more than was allocated from the fund:
1135 IF curr_old_amt > allocated_amt THEN
1136 curr_old_amt := allocated_amt;
1139 -- Can't attribute more than the amount of the current credit:
1141 IF curr_old_amt > source.converted_amt THEN
1142 curr_old_amt := source.converted_amt;
1145 curr_old_amt := trunc( curr_old_amt, 2 );
1147 old_remaining := old_remaining - curr_old_amt;
1149 -- Determine the amount to be deducted, if any,
1150 -- from the old allocation.
1152 IF old_remaining > 0 THEN
1154 -- In this case we're using the whole allocation, so use that
1155 -- amount directly instead of applying a currency translation
1156 -- and thereby inviting round-off errors.
1158 source_deduction := - orig_allocated_amt;
1160 source_deduction := trunc(
1161 ( - curr_old_amt ) *
1162 acq.exchange_ratio( old_fund_currency, source.currency_type ),
1166 IF source_deduction <> 0 THEN
1168 -- Insert negative allocation for old fund in fund_allocation,
1169 -- converted into the currency of the funding source
1171 INSERT INTO acq.fund_allocation (
1178 source.funding_source,
1182 'Transfer to fund ' || new_fund
1186 IF new_fund IS NOT NULL THEN
1188 -- Determine how much to add to the new fund, in
1189 -- its currency, and how much remains to be added:
1191 IF same_currency THEN
1192 curr_new_amt := curr_old_amt;
1194 IF old_remaining = 0 THEN
1196 -- This is the last iteration, so nothing should be left
1198 curr_new_amt := new_remaining;
1201 curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
1202 new_remaining := new_remaining - curr_new_amt;
1206 -- Determine how much to add, if any,
1207 -- to the new fund's allocation.
1209 IF old_remaining > 0 THEN
1211 -- In this case we're using the whole allocation, so use that amount
1212 -- amount directly instead of applying a currency translation and
1213 -- thereby inviting round-off errors.
1215 source_addition := orig_allocated_amt;
1216 ELSIF source.currency_type = old_fund_currency THEN
1218 -- In this case we don't need a round trip currency translation,
1219 -- thereby inviting round-off errors:
1221 source_addition := curr_old_amt;
1223 source_addition := trunc(
1225 acq.exchange_ratio( new_fund_currency, source.currency_type ),
1229 IF source_addition <> 0 THEN
1231 -- Insert positive allocation for new fund in fund_allocation,
1232 -- converted to the currency of the founding source
1234 INSERT INTO acq.fund_allocation (
1241 source.funding_source,
1245 'Transfer from fund ' || old_fund
1250 IF trunc( curr_old_amt, 2 ) <> 0
1251 OR trunc( curr_new_amt, 2 ) <> 0 THEN
1253 -- Insert row in fund_transfer, using amounts in the currency of the funds
1255 INSERT INTO acq.fund_transfer (
1262 funding_source_credit
1265 trunc( curr_old_amt, 2 ),
1267 trunc( curr_new_amt, 2 ),
1274 if old_remaining <= 0 THEN
1275 EXIT; -- Nothing more to be transferred
1279 $$ LANGUAGE plpgsql;
1281 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
1283 Function to attribute expenditures and encumbrances to funding source credits,
1284 and thereby to funding sources.
1286 Read the debits in chonological order, attributing each one to one or
1287 more funding source credits. Constraints:
1289 1. Don't attribute more to a credit than the amount of the credit.
1291 2. For a given fund, don't attribute more to a funding source than the
1292 source has allocated to that fund.
1294 3. Attribute debits to credits with deadlines before attributing them to
1295 credits without deadlines. Otherwise attribute to the earliest credits
1296 first, based on the deadline date when present, or on the effective date
1297 when there is no deadline. Use funding_source_credit.id as a tie-breaker.
1298 This ordering is defined by an ORDER BY clause on the view
1299 acq.ordered_funding_source_credit.
1301 Start by truncating the table acq.debit_attribution. Then insert a row
1302 into that table for each attribution. If a debit cannot be fully
1303 attributed, insert a row for the unattributable balance, with the
1304 funding_source_credit and credit_amount columns NULL.
1307 curr_fund_source_bal RECORD;
1308 seqno INT; -- sequence num for credits applicable to a fund
1309 fund_credit RECORD; -- current row in temp t_fund_credit table
1310 fc RECORD; -- used for loading t_fund_credit table
1311 sc RECORD; -- used for loading t_fund_credit table
1313 -- Used exclusively in the main loop:
1315 deb RECORD; -- current row from acq.fund_debit table
1316 curr_credit_bal RECORD; -- current row from temp t_credit table
1317 debit_balance NUMERIC; -- amount left to attribute for current debit
1318 conv_debit_balance NUMERIC; -- debit balance in currency of the fund
1319 attr_amount NUMERIC; -- amount being attributed, in currency of debit
1320 conv_attr_amount NUMERIC; -- amount being attributed, in currency of source
1321 conv_cred_balance NUMERIC; -- credit_balance in the currency of the fund
1322 conv_alloc_balance NUMERIC; -- allocated balance in the currency of the fund
1323 attrib_count INT; -- populates id of acq.debit_attribution
1326 -- Load a temporary table. For each combination of fund and funding source,
1327 -- load an entry with the total amount allocated to that fund by that source.
1328 -- This sum may reflect transfers as well as original allocations. We will
1329 -- reduce this balance whenever we attribute debits to it.
1331 CREATE TEMP TABLE t_fund_source_bal
1335 funding_source AS source,
1336 sum( amount ) AS balance
1345 CREATE INDEX t_fund_source_bal_idx
1346 ON t_fund_source_bal( fund, source );
1347 -------------------------------------------------------------------------------
1349 -- Load another temporary table. For each fund, load zero or more
1350 -- funding source credits from which that fund can get money.
1352 CREATE TEMP TABLE t_fund_credit (
1359 SELECT DISTINCT fund
1360 FROM acq.fund_allocation
1362 LOOP -- Loop over the funds
1368 acq.ordered_funding_source_credit AS ofsc
1370 ofsc.funding_source IN
1372 SELECT funding_source
1373 FROM acq.fund_allocation
1374 WHERE fund = fc.fund
1380 LOOP -- Add each credit to the list
1381 INSERT INTO t_fund_credit (
1390 --RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
1392 END LOOP; -- Loop over credits for a given fund
1393 END LOOP; -- Loop over funds
1395 CREATE INDEX t_fund_credit_idx
1396 ON t_fund_credit( fund, seq );
1397 -------------------------------------------------------------------------------
1399 -- Load yet another temporary table. This one is a list of funding source
1400 -- credits, with their balances. We shall reduce those balances as we
1401 -- attribute debits to them.
1403 CREATE TEMP TABLE t_credit
1407 fsc.funding_source AS source,
1408 fsc.amount AS balance,
1409 fs.currency_type AS currency_type
1411 acq.funding_source_credit AS fsc,
1412 acq.funding_source fs
1414 fsc.funding_source = fs.id
1417 CREATE INDEX t_credit_idx
1418 ON t_credit( credit );
1420 -------------------------------------------------------------------------------
1422 -- Now that we have loaded the lookup tables: loop through the debits,
1423 -- attributing each one to one or more funding source credits.
1425 truncate table acq.debit_attribution;
1443 --RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
1445 debit_balance := deb.amount;
1447 -- Loop over the funding source credits that are eligible
1448 -- to pay for this debit
1460 --RAISE NOTICE ' Examining credit %', fund_credit.credit;
1462 -- Look up the balance for this credit. If it's zero, then
1463 -- it's not useful, so treat it as if you didn't find it.
1464 -- (Actually there shouldn't be any zero balances in the table,
1465 -- but we check just to make sure.)
1468 INTO curr_credit_bal
1471 credit = fund_credit.credit
1474 IF curr_credit_bal IS NULL THEN
1476 -- This credit is exhausted; try the next one.
1482 -- At this point we have an applicable credit with some money left.
1483 -- Now see if the relevant funding_source has any money left.
1485 -- Look up the balance of the allocation for this combination of
1486 -- fund and source. If you find such an entry, but it has a zero
1487 -- balance, then it's not useful, so treat it as unfound.
1488 -- (Actually there shouldn't be any zero balances in the table,
1489 -- but we check just to make sure.)
1492 INTO curr_fund_source_bal
1493 FROM t_fund_source_bal
1496 AND source = curr_credit_bal.source
1499 IF curr_fund_source_bal IS NULL THEN
1501 -- This fund/source doesn't exist or is already exhausted,
1502 -- so we can't use this credit. Go on to the next one.
1507 -- Convert the available balances to the currency of the fund
1509 conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
1510 curr_credit_bal.currency_type, deb.currency_type );
1511 conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
1512 curr_credit_bal.currency_type, deb.currency_type );
1514 -- Determine how much we can attribute to this credit: the minimum
1515 -- of the debit amount, the fund/source balance, and the
1518 --RAISE NOTICE ' deb bal %', debit_balance;
1519 --RAISE NOTICE ' source % balance %', curr_credit_bal.source, conv_alloc_balance;
1520 --RAISE NOTICE ' credit % balance %', curr_credit_bal.credit, conv_cred_balance;
1522 conv_attr_amount := NULL;
1523 attr_amount := debit_balance;
1525 IF attr_amount > conv_alloc_balance THEN
1526 attr_amount := conv_alloc_balance;
1527 conv_attr_amount := curr_fund_source_bal.balance;
1529 IF attr_amount > conv_cred_balance THEN
1530 attr_amount := conv_cred_balance;
1531 conv_attr_amount := curr_credit_bal.balance;
1534 -- If we're attributing all of one of the balances, then that's how
1535 -- much we will deduct from the balances, and we already captured
1536 -- that amount above. Otherwise we must convert the amount of the
1537 -- attribution from the currency of the fund back to the currency of
1538 -- the funding source.
1540 IF conv_attr_amount IS NULL THEN
1541 conv_attr_amount := attr_amount * acq.exchange_ratio(
1542 deb.currency_type, curr_credit_bal.currency_type );
1545 -- Insert a row to record the attribution
1547 attrib_count := attrib_count + 1;
1548 INSERT INTO acq.debit_attribution (
1552 funding_source_credit,
1558 curr_credit_bal.credit,
1562 -- Subtract the attributed amount from the various balances
1564 debit_balance := debit_balance - attr_amount;
1565 curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
1567 IF curr_fund_source_bal.balance <= 0 THEN
1569 -- This allocation is exhausted. Delete it so
1570 -- that we don't waste time looking at it again.
1572 DELETE FROM t_fund_source_bal
1574 fund = curr_fund_source_bal.fund
1575 AND source = curr_fund_source_bal.source;
1577 UPDATE t_fund_source_bal
1578 SET balance = balance - conv_attr_amount
1580 fund = curr_fund_source_bal.fund
1581 AND source = curr_fund_source_bal.source;
1584 IF curr_credit_bal.balance <= 0 THEN
1586 -- This funding source credit is exhausted. Delete it
1587 -- so that we don't waste time looking at it again.
1589 --DELETE FROM t_credit
1591 -- credit = curr_credit_bal.credit;
1593 DELETE FROM t_fund_credit
1595 credit = curr_credit_bal.credit;
1598 SET balance = curr_credit_bal.balance
1600 credit = curr_credit_bal.credit;
1603 -- Are we done with this debit yet?
1605 IF debit_balance <= 0 THEN
1606 EXIT; -- We've fully attributed this debit; stop looking at credits.
1608 END LOOP; -- End loop over credits
1610 IF debit_balance <> 0 THEN
1612 -- We weren't able to attribute this debit, or at least not
1613 -- all of it. Insert a row for the unattributed balance.
1615 attrib_count := attrib_count + 1;
1616 INSERT INTO acq.debit_attribution (
1620 funding_source_credit,
1630 END LOOP; -- End of loop over debits
1632 $$ LANGUAGE 'plpgsql';
1634 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit(
1638 ) RETURNS VOID AS $$
1649 IF old_year IS NULL THEN
1650 RAISE EXCEPTION 'Input year argument is NULL';
1651 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1652 RAISE EXCEPTION 'Input year is out of range';
1655 IF user_id IS NULL THEN
1656 RAISE EXCEPTION 'Input user id argument is NULL';
1659 IF org_unit_id IS NULL THEN
1660 RAISE EXCEPTION 'Org unit id argument is NULL';
1662 SELECT TRUE INTO org_found
1664 WHERE id = org_unit_id;
1666 IF org_found IS NULL THEN
1667 RAISE EXCEPTION 'Org unit id is invalid';
1671 -- Loop over the applicable funds
1673 FOR old_fund in SELECT * FROM acq.fund
1677 AND org = org_unit_id
1680 INSERT INTO acq.fund (
1692 old_fund.currency_type,
1697 RETURNING id INTO new_id;
1699 WHEN unique_violation THEN
1700 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1703 --RAISE NOTICE 'Propagating fund % to fund %',
1704 -- old_fund.code, new_id;
1707 $$ LANGUAGE plpgsql;
1709 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1713 ) RETURNS VOID AS $$
1724 IF old_year IS NULL THEN
1725 RAISE EXCEPTION 'Input year argument is NULL';
1726 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1727 RAISE EXCEPTION 'Input year is out of range';
1730 IF user_id IS NULL THEN
1731 RAISE EXCEPTION 'Input user id argument is NULL';
1734 IF org_unit_id IS NULL THEN
1735 RAISE EXCEPTION 'Org unit id argument is NULL';
1737 SELECT TRUE INTO org_found
1739 WHERE id = org_unit_id;
1741 IF org_found IS NULL THEN
1742 RAISE EXCEPTION 'Org unit id is invalid';
1746 -- Loop over the applicable funds
1748 FOR old_fund in SELECT * FROM acq.fund
1753 SELECT id FROM actor.org_unit_descendants( org_unit_id )
1757 INSERT INTO acq.fund (
1769 old_fund.currency_type,
1774 RETURNING id INTO new_id;
1776 WHEN unique_violation THEN
1777 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1780 --RAISE NOTICE 'Propagating fund % to fund %',
1781 -- old_fund.code, new_id;
1784 $$ LANGUAGE plpgsql;
1786 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
1790 ) RETURNS VOID AS $$
1794 new_year INT := old_year + 1;
1796 xfer_amount NUMERIC;
1805 IF old_year IS NULL THEN
1806 RAISE EXCEPTION 'Input year argument is NULL';
1807 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1808 RAISE EXCEPTION 'Input year is out of range';
1811 IF user_id IS NULL THEN
1812 RAISE EXCEPTION 'Input user id argument is NULL';
1815 IF org_unit_id IS NULL THEN
1816 RAISE EXCEPTION 'Org unit id argument is NULL';
1819 -- Validate the org unit
1824 WHERE id = org_unit_id;
1826 IF org_found IS NULL THEN
1827 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
1831 -- Loop over the propagable funds to identify the details
1832 -- from the old fund plus the id of the new one, if it exists.
1836 oldf.id AS old_fund,
1842 newf.id AS new_fund_id
1845 LEFT JOIN acq.fund AS newf
1846 ON ( oldf.code = newf.code )
1848 oldf.org = org_unit_id
1849 and oldf.year = old_year
1851 and newf.year = new_year
1853 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
1855 IF roll_fund.new_fund_id IS NULL THEN
1857 -- The old fund hasn't been propagated yet. Propagate it now.
1859 INSERT INTO acq.fund (
1871 roll_fund.currency_type,
1876 RETURNING id INTO new_fund;
1878 new_fund = roll_fund.new_fund_id;
1881 -- Determine the amount to transfer
1885 FROM acq.fund_spent_balance
1886 WHERE fund = roll_fund.old_fund;
1888 IF xfer_amount <> 0 THEN
1889 IF roll_fund.rollover THEN
1891 -- Transfer balance from old fund to new
1893 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
1895 PERFORM acq.transfer_fund(
1905 -- Transfer balance from old fund to the void
1907 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
1909 PERFORM acq.transfer_fund(
1920 IF roll_fund.rollover THEN
1922 -- Move any lineitems from the old fund to the new one
1923 -- where the associated debit is an encumbrance.
1925 -- Any other tables tying expenditure details to funds should
1926 -- receive similar treatment. At this writing there are none.
1928 UPDATE acq.lineitem_detail
1931 fund = roll_fund.old_fund -- this condition may be redundant
1937 fund = roll_fund.old_fund
1941 -- Move encumbrance debits from the old fund to the new fund
1943 UPDATE acq.fund_debit
1946 fund = roll_fund.old_fund
1950 -- Mark old fund as inactive, now that we've closed it
1954 WHERE id = roll_fund.old_fund;
1957 $$ LANGUAGE plpgsql;
1959 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
1963 ) RETURNS VOID AS $$
1967 new_year INT := old_year + 1;
1969 xfer_amount NUMERIC;
1978 IF old_year IS NULL THEN
1979 RAISE EXCEPTION 'Input year argument is NULL';
1980 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1981 RAISE EXCEPTION 'Input year is out of range';
1984 IF user_id IS NULL THEN
1985 RAISE EXCEPTION 'Input user id argument is NULL';
1988 IF org_unit_id IS NULL THEN
1989 RAISE EXCEPTION 'Org unit id argument is NULL';
1992 -- Validate the org unit
1997 WHERE id = org_unit_id;
1999 IF org_found IS NULL THEN
2000 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
2004 -- Loop over the propagable funds to identify the details
2005 -- from the old fund plus the id of the new one, if it exists.
2009 oldf.id AS old_fund,
2015 newf.id AS new_fund_id
2018 LEFT JOIN acq.fund AS newf
2019 ON ( oldf.code = newf.code )
2021 oldf.year = old_year
2023 AND newf.year = new_year
2025 SELECT id FROM actor.org_unit_descendants( org_unit_id )
2028 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
2030 IF roll_fund.new_fund_id IS NULL THEN
2032 -- The old fund hasn't been propagated yet. Propagate it now.
2034 INSERT INTO acq.fund (
2046 roll_fund.currency_type,
2051 RETURNING id INTO new_fund;
2053 new_fund = roll_fund.new_fund_id;
2056 -- Determine the amount to transfer
2060 FROM acq.fund_spent_balance
2061 WHERE fund = roll_fund.old_fund;
2063 IF xfer_amount <> 0 THEN
2064 IF roll_fund.rollover THEN
2066 -- Transfer balance from old fund to new
2068 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
2070 PERFORM acq.transfer_fund(
2080 -- Transfer balance from old fund to the void
2082 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
2084 PERFORM acq.transfer_fund(
2095 IF roll_fund.rollover THEN
2097 -- Move any lineitems from the old fund to the new one
2098 -- where the associated debit is an encumbrance.
2100 -- Any other tables tying expenditure details to funds should
2101 -- receive similar treatment. At this writing there are none.
2103 UPDATE acq.lineitem_detail
2106 fund = roll_fund.old_fund -- this condition may be redundant
2112 fund = roll_fund.old_fund
2116 -- Move encumbrance debits from the old fund to the new fund
2118 UPDATE acq.fund_debit
2121 fund = roll_fund.old_fund
2125 -- Mark old fund as inactive, now that we've closed it
2129 WHERE id = roll_fund.old_fund;
2132 $$ LANGUAGE plpgsql;
2134 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
2135 SELECT funding_source,
2136 SUM(amount) AS amount
2137 FROM acq.funding_source_credit
2140 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
2141 SELECT funding_source,
2142 SUM(a.amount)::NUMERIC(100,2) AS amount
2143 FROM acq.fund_allocation a
2146 CREATE OR REPLACE VIEW acq.funding_source_balance AS
2147 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
2148 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
2149 FROM acq.funding_source_credit_total c
2150 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
2153 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
2155 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
2156 FROM acq.fund_allocation a
2157 JOIN acq.fund f ON (a.fund = f.id)
2158 JOIN acq.funding_source s ON (a.funding_source = s.id)
2161 CREATE OR REPLACE VIEW acq.fund_debit_total AS
2162 SELECT fund.id AS fund,
2163 fund_debit.encumbrance AS encumbrance,
2164 SUM( COALESCE( fund_debit.amount, 0 ) ) AS amount
2165 FROM acq.fund AS fund
2166 LEFT JOIN acq.fund_debit AS fund_debit
2167 ON ( fund.id = fund_debit.fund )
2170 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
2172 SUM(amount) AS amount
2173 FROM acq.fund_debit_total
2174 WHERE encumbrance IS TRUE
2177 CREATE OR REPLACE VIEW acq.fund_spent_total AS
2179 SUM(amount) AS amount
2180 FROM acq.fund_debit_total
2181 WHERE encumbrance IS FALSE
2184 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
2186 c.amount - COALESCE(d.amount,0.0) AS amount
2187 FROM acq.fund_allocation_total c
2188 LEFT JOIN acq.fund_debit_total d USING (fund);
2190 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
2192 c.amount - COALESCE(d.amount,0.0) AS amount
2193 FROM acq.fund_allocation_total c
2194 LEFT JOIN acq.fund_spent_total d USING (fund);