Open-ILS/src/sql/Pg/200.schema.acq.sql

   1 DROP SCHEMA acq CASCADE;
   2
   3 BEGIN;
   4
   5 CREATE SCHEMA acq;
   6
   7
   8 -- Tables
   9
  10
  11 CREATE TABLE acq.currency_type (
  12         code    TEXT PRIMARY KEY,
  13         label   TEXT
  14 );
  15
  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');
  20
  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)
  27 );
  28
  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);
  31
  32 CREATE TABLE acq.provider (
  33     id                  SERIAL  PRIMARY KEY,
  34     name                TEXT    NOT NULL,
  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,
  37     code                TEXT    NOT NULL,
  38     holding_tag         TEXT,
  39     san                 TEXT,
  40     edi_default         INT,          -- REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED
  41         active              BOOL    NOT NULL DEFAULT TRUE,
  42         prepayment_required BOOL    NOT NULL DEFAULT FALSE,
  43     url                 TEXT,
  44     email               TEXT,
  45     phone               TEXT,
  46     fax_phone           TEXT,
  47     CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner),
  48         CONSTRAINT code_once_per_owner UNIQUE (code, owner)
  49 );
  50
  51 CREATE TABLE acq.provider_holding_subfield_map (
  52     id          SERIAL  PRIMARY KEY,
  53     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  54     name        TEXT    NOT NULL, -- barcode, price, etc
  55     subfield    TEXT    NOT NULL,
  56     CONSTRAINT name_once_per_provider UNIQUE (provider,name)
  57 );
  58
  59 CREATE TABLE acq.provider_address (
  60         id              SERIAL  PRIMARY KEY,
  61         valid           BOOL    NOT NULL DEFAULT TRUE,
  62         address_type    TEXT,
  63     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  64         street1         TEXT    NOT NULL,
  65         street2         TEXT,
  66         city            TEXT    NOT NULL,
  67         county          TEXT,
  68         state           TEXT    NOT NULL,
  69         country         TEXT    NOT NULL,
  70         post_code       TEXT    NOT NULL,
  71         fax_phone       TEXT    NOT NULL
  72 );
  73
  74 CREATE TABLE acq.provider_contact (
  75         id              SERIAL  PRIMARY KEY,
  76     provider    INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  77     name    TEXT NULL NULL,
  78     role    TEXT, -- free-form.. e.g. "our sales guy"
  79     email   TEXT,
  80     phone   TEXT
  81 );
  82
  83 CREATE TABLE acq.provider_contact_address (
  84         id                      SERIAL  PRIMARY KEY,
  85         valid                   BOOL    NOT NULL DEFAULT TRUE,
  86         address_type    TEXT,
  87         contact                 INT         NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
  88         street1                 TEXT    NOT NULL,
  89         street2                 TEXT,
  90         city                    TEXT    NOT NULL,
  91         county                  TEXT,
  92         state                   TEXT    NOT NULL,
  93         country                 TEXT    NOT NULL,
  94         post_code               TEXT    NOT NULL,
  95         fax_phone               TEXT
  96 );
  97
  98 CREATE TABLE acq.provider_note (
  99         id              SERIAL                          PRIMARY KEY,
 100         provider    INT                         NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 101         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 102         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 103         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 104         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 105         value           TEXT                    NOT NULL
 106 );
 107 CREATE INDEX acq_pro_note_pro_idx      ON acq.provider_note ( provider );
 108 CREATE INDEX acq_pro_note_creator_idx  ON acq.provider_note ( creator );
 109 CREATE INDEX acq_pro_note_editor_idx   ON acq.provider_note ( editor );
 110
 111
 112 CREATE TABLE acq.funding_source (
 113         id              SERIAL  PRIMARY KEY,
 114         name            TEXT    NOT NULL,
 115         owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 116         currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 117         code            TEXT    UNIQUE,
 118         CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
 119 );
 120
 121 CREATE TABLE acq.funding_source_credit (
 122         id      SERIAL     PRIMARY KEY,
 123         funding_source INT      NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
 124         amount         NUMERIC  NOT NULL,
 125         note           TEXT,
 126         deadline_date  TIMESTAMPTZ,
 127         effective_date TIMESTAMPTZ NOT NULL default now()
 128 );
 129
 130 CREATE VIEW acq.ordered_funding_source_credit AS
 131     SELECT
 132         CASE WHEN deadline_date IS NULL THEN
 133             2
 134         ELSE
 135             1
 136         END AS sort_priority,
 137         CASE WHEN deadline_date IS NULL THEN
 138             effective_date
 139         ELSE
 140             deadline_date
 141         END AS sort_date,
 142         id,
 143         funding_source,
 144         amount,
 145         note
 146     FROM
 147         acq.funding_source_credit;
 148
 149 COMMENT ON VIEW acq.ordered_funding_source_credit IS $$
 150 /*
 151  * Copyright (C) 2009  Georgia Public Library Service
 152  * Scott McKellar <scott@gmail.com>
 153  *
 154  * The acq.ordered_funding_source_credit view is a prioritized
 155  * ordering of funding source credits.  When ordered by the first
 156  * three columns, this view defines the order in which the various
 157  * credits are to be tapped for spending, subject to the allocations
 158  * in the acq.fund_allocation table.
 159  *
 160  * The first column reflects the principle that we should spend
 161  * money with deadlines before spending money without deadlines.
 162  *
 163  * The second column reflects the principle that we should spend the
 164  * oldest money first.  For money with deadlines, that means that we
 165  * spend first from the credit with the earliest deadline.  For
 166  * money without deadlines, we spend first from the credit with the
 167  * earliest effective date.
 168  *
 169  * The third column is a tie breaker to ensure a consistent
 170  * ordering.
 171  *
 172  * ****
 173  *
 174  * This program is free software; you can redistribute it and/or
 175  * modify it under the terms of the GNU General Public License
 176  * as published by the Free Software Foundation; either version 2
 177  * of the License, or (at your option) any later version.
 178  *
 179  * This program is distributed in the hope that it will be useful,
 180  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 181  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 182  * GNU General Public License for more details.
 183  */
 184 $$;
 185
 186 CREATE TABLE acq.fund (
 187     id              SERIAL  PRIMARY KEY,
 188     org             INT     NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 189     name            TEXT    NOT NULL,
 190     year            INT     NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
 191     currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 192     code            TEXT,
 193         rollover        BOOL    NOT NULL DEFAULT FALSE,
 194         propagate       BOOL    NOT NULL DEFAULT TRUE,
 195         active          BOOL    NOT NULL DEFAULT TRUE,
 196         balance_warning_percent INT CONSTRAINT balance_warning_percent_limit
 197                                     CHECK( balance_warning_percent <= 100 ),
 198         balance_stop_percent    INT CONSTRAINT balance_stop_percent_limit
 199                                     CHECK( balance_stop_percent <= 100 ),
 200     CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
 201     CONSTRAINT code_once_per_org_year UNIQUE (org, code, year),
 202         CONSTRAINT acq_fund_rollover_implies_propagate CHECK ( propagate OR NOT rollover )
 203 );
 204
 205 CREATE TABLE acq.fund_debit (
 206         id                      SERIAL  PRIMARY KEY,
 207         fund                    INT     NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 208         origin_amount           NUMERIC NOT NULL,  -- pre-exchange-rate amount
 209         origin_currency_type    TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 210         amount                  NUMERIC NOT NULL,
 211         encumbrance             BOOL    NOT NULL DEFAULT TRUE,
 212         debit_type              TEXT    NOT NULL,
 213         xfer_destination        INT     REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 214         create_time     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 215 );
 216
 217 CREATE TABLE acq.fund_allocation (
 218     id          SERIAL  PRIMARY KEY,
 219     funding_source        INT     NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 220     fund        INT     NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 221     amount      NUMERIC NOT NULL,
 222     allocator   INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 223     note        TEXT,
 224         create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 225 );
 226 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
 227
 228 CREATE TABLE acq.fund_allocation_percent
 229 (
 230     id                   SERIAL            PRIMARY KEY,
 231     funding_source       INT               NOT NULL REFERENCES acq.funding_source
 232                                                DEFERRABLE INITIALLY DEFERRED,
 233     org                  INT               NOT NULL REFERENCES actor.org_unit
 234                                                DEFERRABLE INITIALLY DEFERRED,
 235     fund_code            TEXT,
 236     percent              NUMERIC           NOT NULL,
 237     allocator            INTEGER           NOT NULL REFERENCES actor.usr
 238                                                DEFERRABLE INITIALLY DEFERRED,
 239     note                 TEXT,
 240     create_time          TIMESTAMPTZ       NOT NULL DEFAULT now(),
 241     CONSTRAINT logical_key UNIQUE( funding_source, org, fund_code ),
 242     CONSTRAINT percentage_range CHECK( percent >= 0 AND percent <= 100 )
 243 );
 244
 245 -- Trigger function to validate combination of org_unit and fund_code
 246
 247 CREATE OR REPLACE FUNCTION acq.fund_alloc_percent_val()
 248 RETURNS TRIGGER AS $$
 249 --
 250 DECLARE
 251 --
 252 dummy int := 0;
 253 --
 254 BEGIN
 255     SELECT
 256         1
 257     INTO
 258         dummy
 259     FROM
 260         acq.fund
 261     WHERE
 262         org = NEW.org
 263         AND code = NEW.fund_code
 264         LIMIT 1;
 265     --
 266     IF dummy = 1 then
 267         RETURN NEW;
 268     ELSE
 269         RAISE EXCEPTION 'No fund exists for org % and code %', NEW.org, NEW.fund_code;
 270     END IF;
 271 END;
 272 $$ LANGUAGE plpgsql;
 273
 274 CREATE TRIGGER acq_fund_alloc_percent_val_trig
 275     BEFORE INSERT OR UPDATE ON acq.fund_allocation_percent
 276     FOR EACH ROW EXECUTE PROCEDURE acq.fund_alloc_percent_val();
 277
 278 -- To do: trigger to verify that percentages don't add up to more than 100
 279
 280 CREATE OR REPLACE FUNCTION acq.fap_limit_100()
 281 RETURNS TRIGGER AS $$
 282 DECLARE
 283 --
 284 total_percent numeric;
 285 --
 286 BEGIN
 287     SELECT
 288         sum( percent )
 289     INTO
 290         total_percent
 291     FROM
 292         acq.fund_allocation_percent AS fap
 293     WHERE
 294         fap.funding_source = NEW.funding_source;
 295     --
 296     IF total_percent > 100 THEN
 297         RAISE EXCEPTION 'Total percentages exceed 100 for funding_source %',
 298             NEW.funding_source;
 299     ELSE
 300         RETURN NEW;
 301     END IF;
 302 END;
 303 $$ LANGUAGE plpgsql;
 304
 305 CREATE TRIGGER acqfap_limit_100_trig
 306     AFTER INSERT OR UPDATE ON acq.fund_allocation_percent
 307     FOR EACH ROW EXECUTE PROCEDURE acq.fap_limit_100();
 308
 309 CREATE TABLE acq.picklist (
 310         id              SERIAL                          PRIMARY KEY,
 311         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 312         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 313         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 314         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 315         name            TEXT                            NOT NULL,
 316         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 317         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 318         CONSTRAINT name_once_per_owner UNIQUE (name,owner)
 319 );
 320 CREATE INDEX acq_picklist_owner_idx   ON acq.picklist ( owner );
 321 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
 322 CREATE INDEX acq_picklist_editor_idx  ON acq.picklist ( editor );
 323
 324 CREATE TABLE acq.purchase_order (
 325         id              SERIAL                          PRIMARY KEY,
 326         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 327         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 328         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 329         ordering_agency INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 330         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 331         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 332         provider        INT                             NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 333         state                   TEXT                                    NOT NULL DEFAULT 'new',
 334         order_date              TIMESTAMP WITH TIME ZONE,
 335         name                    TEXT                                    NOT NULL
 336 );
 337 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
 338 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
 339 CREATE INDEX po_state_idx ON acq.purchase_order (state);
 340 CREATE INDEX po_creator_idx  ON acq.purchase_order ( creator );
 341 CREATE INDEX po_editor_idx   ON acq.purchase_order ( editor );
 342 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
 343
 344 -- The name should default to the id, as text.  We can't reference a column
 345 -- in a DEFAULT clause, so we use a trigger:
 346
 347 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
 348 AS $$
 349 BEGIN
 350         IF NEW.name IS NULL THEN
 351                 NEW.name := NEW.id::TEXT;
 352         END IF;
 353
 354         RETURN NEW;
 355 END;
 356 $$ LANGUAGE PLPGSQL;
 357
 358 CREATE TRIGGER po_name_default_trg
 359   BEFORE INSERT OR UPDATE ON acq.purchase_order
 360   FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
 361
 362 -- The order name should be unique for a given ordering agency on a given order date
 363 -- (truncated to midnight), but only where the order_date is not NULL.  Conceptually
 364 -- this rule requires a check constraint with a subquery.  However you can't have a
 365 -- subquery in a CHECK constraint, so we fake it with a trigger.
 366
 367 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
 368 AS $$
 369 DECLARE
 370         collision INT;
 371 BEGIN
 372         --
 373         -- If order_date is not null, then make sure we don't have a collision
 374         -- on order_date (truncated to day), org, and name
 375         --
 376         IF NEW.order_date IS NULL THEN
 377                 RETURN NEW;
 378         END IF;
 379         --
 380         -- In the WHERE clause, we compare the order_dates without regard to time of day.
 381         -- We use a pair of inequalities instead of comparing truncated dates so that the
 382         -- query can do an indexed range scan.
 383         --
 384         SELECT 1 INTO collision
 385         FROM acq.purchase_order
 386         WHERE
 387                 ordering_agency = NEW.ordering_agency
 388                 AND name = NEW.name
 389                 AND order_date >= date_trunc( 'day', NEW.order_date )
 390                 AND order_date <  date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
 391                 AND id <> NEW.id;
 392         --
 393         IF collision IS NULL THEN
 394                 -- okay, no collision
 395                 RETURN NEW;
 396         ELSE
 397                 -- collision; nip it in the bud
 398                 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
 399                         NEW.ordering_agency, NEW.order_date, NEW.name;
 400         END IF;
 401 END;
 402 $$ LANGUAGE PLPGSQL;
 403
 404 CREATE TRIGGER po_org_name_date_unique_trg
 405   BEFORE INSERT OR UPDATE ON acq.purchase_order
 406   FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
 407
 408 CREATE TABLE acq.po_note (
 409         id              SERIAL                          PRIMARY KEY,
 410         purchase_order  INT                             NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 411         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 412         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 413         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 414         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 415         value           TEXT                            NOT NULL
 416 );
 417 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
 418 CREATE INDEX acq_po_note_creator_idx  ON acq.po_note ( creator );
 419 CREATE INDEX acq_po_note_editor_idx   ON acq.po_note ( editor );
 420
 421 CREATE TABLE acq.lineitem (
 422         id                  BIGSERIAL                   PRIMARY KEY,
 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         selector            INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 426         provider            INT                         REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 427         purchase_order      INT                         REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 428         picklist            INT                         REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
 429         expected_recv_time  TIMESTAMP WITH TIME ZONE,
 430         create_time         TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 431         edit_time           TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 432         marc                TEXT                        NOT NULL,
 433         eg_bib_id           INT                         REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
 434         source_label        TEXT,
 435         state               TEXT                        NOT NULL DEFAULT 'new',
 436     CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
 437 );
 438 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
 439 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
 440 CREATE INDEX li_creator_idx   ON acq.lineitem ( creator );
 441 CREATE INDEX li_editor_idx    ON acq.lineitem ( editor );
 442 CREATE INDEX li_selector_idx  ON acq.lineitem ( selector );
 443
 444 CREATE TABLE acq.lineitem_alert_text (
 445     id               SERIAL         PRIMARY KEY,
 446     code             TEXT           NOT NULL,
 447     description      TEXT,
 448         owning_lib       INT            NOT NULL
 449                                         REFERENCES actor.org_unit(id)
 450                                         DEFERRABLE INITIALLY DEFERRED,
 451         CONSTRAINT alert_one_code_per_org UNIQUE (code, owning_lib)
 452 );
 453
 454 CREATE TABLE acq.lineitem_note (
 455         id              SERIAL                          PRIMARY KEY,
 456         lineitem        INT                             NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 457         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 458         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 459         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 460         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 461         value           TEXT                    NOT NULL,
 462         alert_text      INT                                              REFERENCES acq.lineitem_alert_text(id)
 463                                                                                  DEFERRABLE INITIALLY DEFERRED
 464 );
 465 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
 466 CREATE INDEX li_note_creator_idx  ON acq.lineitem_note ( creator );
 467 CREATE INDEX li_note_editor_idx   ON acq.lineitem_note ( editor );
 468
 469 CREATE TABLE acq.lineitem_detail (
 470     id          BIGSERIAL       PRIMARY KEY,
 471     lineitem    INT         NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 472     fund        INT         REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 473     fund_debit  INT         REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
 474     eg_copy_id  BIGINT      REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 475     barcode     TEXT,
 476     cn_label    TEXT,
 477     note        TEXT,
 478     collection_code TEXT,
 479     circ_modifier   TEXT    REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 480     owning_lib  INT         REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 481     location    INT         REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 482     recv_time   TIMESTAMP WITH TIME ZONE
 483 );
 484
 485 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
 486
 487 CREATE TABLE acq.lineitem_attr_definition (
 488         id              BIGSERIAL       PRIMARY KEY,
 489         code            TEXT            NOT NULL,
 490         description     TEXT            NOT NULL,
 491         remove          TEXT            NOT NULL DEFAULT '',
 492         ident           BOOL            NOT NULL DEFAULT FALSE
 493 );
 494
 495 CREATE TABLE acq.lineitem_marc_attr_definition (
 496         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 497         xpath           TEXT            NOT NULL
 498 ) INHERITS (acq.lineitem_attr_definition);
 499
 500 CREATE TABLE acq.lineitem_provider_attr_definition (
 501         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 502         xpath           TEXT            NOT NULL,
 503         provider        INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
 504 ) INHERITS (acq.lineitem_attr_definition);
 505
 506 CREATE TABLE acq.lineitem_generated_attr_definition (
 507         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 508         xpath           TEXT            NOT NULL
 509 ) INHERITS (acq.lineitem_attr_definition);
 510
 511 CREATE TABLE acq.lineitem_usr_attr_definition (
 512         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 513         usr             INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
 514 ) INHERITS (acq.lineitem_attr_definition);
 515 CREATE INDEX li_usr_attr_def_usr_idx  ON acq.lineitem_usr_attr_definition ( usr );
 516
 517 CREATE TABLE acq.lineitem_local_attr_definition (
 518         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
 519 ) INHERITS (acq.lineitem_attr_definition);
 520
 521 CREATE TABLE acq.lineitem_attr (
 522         id              BIGSERIAL       PRIMARY KEY,
 523         definition      BIGINT          NOT NULL,
 524         lineitem        BIGINT          NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 525         attr_type       TEXT            NOT NULL,
 526         attr_name       TEXT            NOT NULL,
 527         attr_value      TEXT            NOT NULL
 528 );
 529
 530 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
 531 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
 532 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
 533
 534
 535 -- Seed data
 536
 537
 538 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
 539 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)]');
 540 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
 541 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
 542 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 543 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 544 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
 545 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
 546 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
 547 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
 548 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
 549
 550 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
 551
 552
 553 CREATE TABLE acq.distribution_formula (
 554         id              SERIAL PRIMARY KEY,
 555         owner   INT NOT NULL
 556                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 557         name    TEXT NOT NULL,
 558         skip_count      INT NOT NULL DEFAULT 0,
 559         CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
 560 );
 561
 562 CREATE TABLE acq.distribution_formula_entry (
 563         id                      SERIAL PRIMARY KEY,
 564         formula         INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
 565                                 ON DELETE CASCADE
 566                                 DEFERRABLE INITIALLY DEFERRED,
 567         position        INTEGER NOT NULL,
 568         item_count      INTEGER NOT NULL,
 569         owning_lib      INTEGER REFERENCES actor.org_unit(id)
 570                                 DEFERRABLE INITIALLY DEFERRED,
 571         location        INTEGER REFERENCES asset.copy_location(id),
 572         CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
 573         CONSTRAINT acqdfe_must_be_somewhere
 574                                 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
 575 );
 576
 577 CREATE TABLE acq.distribution_formula_application (
 578     id BIGSERIAL PRIMARY KEY,
 579     creator INT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 580     create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 581     formula INT NOT NULL
 582         REFERENCES acq.distribution_formula(id) DEFERRABLE INITIALLY DEFERRED,
 583     lineitem INT NOT NULL
 584         REFERENCES acq.lineitem(id) DEFERRABLE INITIALLY DEFERRED
 585 );
 586
 587 CREATE INDEX acqdfa_df_idx
 588     ON acq.distribution_formula_application(formula);
 589 CREATE INDEX acqdfa_li_idx
 590     ON acq.distribution_formula_application(lineitem);
 591 CREATE INDEX acqdfa_creator_idx
 592     ON acq.distribution_formula_application(creator);
 593
 594 CREATE TABLE acq.fund_tag (
 595         id              SERIAL PRIMARY KEY,
 596         owner   INT NOT NULL
 597                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 598         name    TEXT NOT NULL,
 599         CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
 600 );
 601
 602 CREATE TABLE acq.fund_tag_map (
 603         id                      SERIAL PRIMARY KEY,
 604         fund            INTEGER NOT NULL REFERENCES acq.fund(id)
 605                                 DEFERRABLE INITIALLY DEFERRED,
 606         tag         INTEGER REFERENCES acq.fund_tag(id)
 607                                 ON DELETE CASCADE
 608                                 DEFERRABLE INITIALLY DEFERRED,
 609         CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
 610 );
 611
 612 CREATE TABLE acq.fund_transfer (
 613     id               SERIAL         PRIMARY KEY,
 614     src_fund         INT            NOT NULL REFERENCES acq.fund( id )
 615                                     DEFERRABLE INITIALLY DEFERRED,
 616     src_amount       NUMERIC        NOT NULL,
 617     dest_fund        INT            NOT NULL REFERENCES acq.fund( id )
 618                                     DEFERRABLE INITIALLY DEFERRED,
 619     dest_amount      NUMERIC        NOT NULL,
 620     transfer_time    TIMESTAMPTZ    NOT NULL DEFAULT now(),
 621     transfer_user    INT            NOT NULL REFERENCES actor.usr( id )
 622                                     DEFERRABLE INITIALLY DEFERRED,
 623     note             TEXT,
 624         funding_source_credit INT       NOT NULL REFERENCES acq.funding_source_credit( id )
 625                                     DEFERRABLE INITIALLY DEFERRED
 626 );
 627
 628 CREATE INDEX acqftr_usr_idx
 629 ON acq.fund_transfer( transfer_user );
 630
 631 COMMENT ON TABLE acq.fund_transfer IS $$
 632 /*
 633  * Copyright (C) 2009  Georgia Public Library Service
 634  * Scott McKellar <scott@esilibrary.com>
 635  *
 636  * Fund Transfer
 637  *
 638  * Each row represents the transfer of money from a source fund
 639  * to a destination fund.  There should be corresponding entries
 640  * in acq.fund_allocation.  The purpose of acq.fund_transfer is
 641  * to record how much money moved from which fund to which other
 642  * fund.
 643  *
 644  * The presence of two amount fields, rather than one, reflects
 645  * the possibility that the two funds are denominated in different
 646  * currencies.  If they use the same currency type, the two
 647  * amounts should be the same.
 648  *
 649  * ****
 650  *
 651  * This program is free software; you can redistribute it and/or
 652  * modify it under the terms of the GNU General Public License
 653  * as published by the Free Software Foundation; either version 2
 654  * of the License, or (at your option) any later version.
 655  *
 656  * This program is distributed in the hope that it will be useful,
 657  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 658  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 659  * GNU General Public License for more details.
 660  */
 661 $$;
 662
 663 CREATE TABLE acq.fiscal_calendar (
 664         id              SERIAL         PRIMARY KEY,
 665         name            TEXT           NOT NULL
 666 );
 667
 668 -- Create a default calendar (though we don't specify its contents).
 669 -- Create a foreign key in actor.org_unit, initially pointing to
 670 -- the default calendar.
 671
 672 INSERT INTO acq.fiscal_calendar (
 673     name
 674 ) VALUES (
 675
 676     'Default'
 677 );
 678
 679 ALTER TABLE actor.org_unit ADD FOREIGN KEY
 680         (fiscal_calendar) REFERENCES acq.fiscal_calendar( id )
 681         DEFERRABLE INITIALLY DEFERRED;
 682
 683 CREATE TABLE acq.fiscal_year (
 684         id              SERIAL         PRIMARY KEY,
 685         calendar        INT            NOT NULL
 686                                        REFERENCES acq.fiscal_calendar
 687                                        ON DELETE CASCADE
 688                                        DEFERRABLE INITIALLY DEFERRED,
 689         year            INT            NOT NULL,
 690         year_begin      TIMESTAMPTZ    NOT NULL,
 691         year_end        TIMESTAMPTZ    NOT NULL,
 692         CONSTRAINT acq_fy_logical_key  UNIQUE ( calendar, year ),
 693     CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
 694 );
 695
 696 CREATE TABLE acq.edi_account (      -- similar tables can extend remote_account for other parts of EG
 697     provider    INT     NOT NULL REFERENCES acq.provider          (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 698     in_dir      TEXT    -- incoming messages dir (probably different than config.remote_account.path, the outgoing dir)
 699 ) INHERITS (config.remote_account);
 700
 701 -- We need a UNIQUE constraint here also, to support the FK from acq.provider.edi_default
 702 ALTER TABLE acq.edi_account ADD CONSTRAINT acq_edi_account_id_unique UNIQUE (id);
 703
 704 -- Note below that the primary key is NOT a SERIAL type.  We will periodically truncate and rebuild
 705 -- the table, assigning ids programmatically instead of using a sequence.
 706 CREATE TABLE acq.debit_attribution (
 707     id                     INT         NOT NULL PRIMARY KEY,
 708     fund_debit             INT         NOT NULL
 709                                        REFERENCES acq.fund_debit
 710                                        DEFERRABLE INITIALLY DEFERRED,
 711     debit_amount           NUMERIC     NOT NULL,
 712     funding_source_credit  INT         REFERENCES acq.funding_source_credit
 713                                        DEFERRABLE INITIALLY DEFERRED,
 714     credit_amount          NUMERIC
 715 );
 716
 717 CREATE INDEX acq_attribution_debit_idx
 718     ON acq.debit_attribution( fund_debit );
 719
 720 CREATE INDEX acq_attribution_credit_idx
 721     ON acq.debit_attribution( funding_source_credit );
 722
 723 -- Invoicing
 724
 725 CREATE TABLE acq.invoice_method (
 726     code    TEXT    PRIMARY KEY,
 727     name    TEXT    NOT NULL -- i18n-ize
 728 );
 729
 730 CREATE TABLE acq.invoice (
 731     id          SERIAL      PRIMARY KEY,
 732     receiver    INT         NOT NULL REFERENCES actor.org_unit (id),
 733     provider    INT         NOT NULL REFERENCES acq.provider (id),
 734     shipper     INT         NOT NULL REFERENCES acq.provider (id),
 735     recv_date   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 736     recv_method TEXT        NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI',
 737     inv_type    TEXT,       -- A "type" field is desired, but no idea what goes here
 738     inv_ident   TEXT        NOT NULL -- vendor-supplied invoice id/number
 739 );
 740
 741 CREATE TABLE acq.invoice_entry (
 742     id              SERIAL      PRIMARY KEY,
 743     invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON DELETE CASCADE,
 744     purchase_order  INT         REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
 745     lineitem        INT         REFERENCES acq.lineitem (id) ON UPDATE CASCADE ON DELETE SET NULL,
 746     inv_item_count  INT         NOT NULL, -- How many acqlids did they say they sent
 747     phys_item_count INT, -- and how many did staff count
 748     note            TEXT,
 749     billed_per_item BOOL,
 750     cost_billed     NUMERIC(8,2),
 751     actual_cost     NUMERIC(8,2)
 752 );
 753
 754 CREATE TABLE acq.invoice_item_type (
 755     code    TEXT    PRIMARY KEY,
 756     name    TEXT    NOT NULL -- i18n-ize
 757 );
 758
 759 CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc
 760     id              SERIAL      PRIMARY KEY,
 761     invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE,
 762     purchase_order  INT         REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
 763     fund_debit      INT         REFERENCES acq.fund_debit (id),
 764     inv_item_type   TEXT        NOT NULL REFERENCES acq.invoice_item_type (code),
 765     title           TEXT,
 766     author          TEXT,
 767     note            TEXT,
 768     cost_billed     NUMERIC(8,2),
 769     actual_cost     NUMERIC(8,2)
 770 );
 771
 772 -- Patron requests
 773 CREATE TABLE acq.user_request_type (
 774     id      SERIAL  PRIMARY KEY,
 775     label   TEXT    NOT NULL UNIQUE -- i18n-ize
 776 );
 777
 778 INSERT INTO acq.user_request_type (id,label) VALUES (1, oils_i18n_gettext('1', 'Books', 'aurt', 'label'));
 779 INSERT INTO acq.user_request_type (id,label) VALUES (2, oils_i18n_gettext('2', 'Journal/Magazine & Newspaper Articles', 'aurt', 'label'));
 780 INSERT INTO acq.user_request_type (id,label) VALUES (3, oils_i18n_gettext('3', 'Audiobooks', 'aurt', 'label'));
 781 INSERT INTO acq.user_request_type (id,label) VALUES (4, oils_i18n_gettext('4', 'Music', 'aurt', 'label'));
 782 INSERT INTO acq.user_request_type (id,label) VALUES (5, oils_i18n_gettext('5', 'DVDs', 'aurt', 'label'));
 783
 784 SELECT SETVAL('acq.user_request_type_id_seq'::TEXT, 6);
 785
 786 CREATE TABLE acq.user_request (
 787     id                  SERIAL  PRIMARY KEY,
 788     usr                 INT     NOT NULL REFERENCES actor.usr (id), -- requesting user
 789     hold                BOOL    NOT NULL DEFAULT TRUE,
 790
 791     pickup_lib          INT     NOT NULL REFERENCES actor.org_unit (id), -- pickup lib
 792     holdable_formats    TEXT,           -- nullable, for use in hold creation
 793     phone_notify        TEXT,
 794     email_notify        BOOL    NOT NULL DEFAULT TRUE,
 795     lineitem            INT     REFERENCES acq.lineitem (id) ON DELETE CASCADE,
 796     eg_bib              BIGINT  REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
 797     request_date        TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- when they requested it
 798     need_before         TIMESTAMPTZ,    -- don't create holds after this
 799     max_fee             TEXT,
 800
 801     request_type        INT     NOT NULL REFERENCES acq.user_request_type (id),
 802     isxn                TEXT,
 803     title               TEXT,
 804     volume              TEXT,
 805     author              TEXT,
 806     article_title       TEXT,
 807     article_pages       TEXT,
 808     publisher           TEXT,
 809     location            TEXT,
 810     pubdate             TEXT,
 811     mentioned           TEXT,
 812     other_info          TEXT
 813 );
 814
 815
 816 -- Functions
 817
 818 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 819 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 820 DECLARE
 821     counter INT;
 822     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 823 BEGIN
 824
 825     SELECT  COUNT(*) INTO counter
 826       FROM  oils_xpath_table(
 827                 'id',
 828                 'marc',
 829                 'acq.lineitem',
 830                 '//*[@tag="' || tag || '"]',
 831                 'id=' || lineitem
 832             ) as t(i int,c text);
 833
 834     FOR i IN 1 .. counter LOOP
 835         FOR lida IN
 836             SELECT  *
 837               FROM  (   SELECT  id,i,t,v
 838                           FROM  oils_xpath_table(
 839                                     'id',
 840                                     'marc',
 841                                     'acq.lineitem',
 842                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 843                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 844                                     'id=' || lineitem
 845                                 ) as t(id int,t text,v text)
 846                     )x
 847         LOOP
 848             RETURN NEXT lida;
 849         END LOOP;
 850     END LOOP;
 851
 852     RETURN;
 853 END;
 854 $$ LANGUAGE PLPGSQL;
 855
 856 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 857 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 858 DECLARE
 859     prov_i  INT;
 860     tag_t   TEXT;
 861     lida    acq.flat_lineitem_detail%ROWTYPE;
 862 BEGIN
 863     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 864     IF NOT FOUND THEN RETURN; END IF;
 865
 866     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 867     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 868
 869     FOR lida IN
 870         SELECT  lineitem_i,
 871                 h.holding,
 872                 a.name,
 873                 h.data
 874           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 875                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 876           WHERE a.provider = prov_i
 877     LOOP
 878         RETURN NEXT lida;
 879     END LOOP;
 880
 881     RETURN;
 882 END;
 883 $$ LANGUAGE PLPGSQL;
 884
 885 -- select * from acq.extract_provider_holding_data(699);
 886
 887 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 888         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 889 $$ LANGUAGE SQL;
 890
 891 /*
 892 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 893         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 894 $$ LANGUAGE SQL;
 895
 896 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 897         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 898 $$ LANGUAGE SQL;
 899 */
 900 -- For example:
 901 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 902
 903 /*
 904 Suggested vendor fields:
 905         vendor_price
 906         vendor_currency
 907         vendor_avail
 908         vendor_po
 909         vendor_identifier
 910 */
 911
 912 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
 913 DECLARE
 914         value           TEXT;
 915         atype           TEXT;
 916         prov            INT;
 917         adef            RECORD;
 918         xpath_string    TEXT;
 919 BEGIN
 920         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 921
 922                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 923
 924                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
 925                         IF (atype = 'lineitem_provider_attr_definition') THEN
 926                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 927                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
 928                         END IF;
 929
 930                         IF (atype = 'lineitem_provider_attr_definition') THEN
 931                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 932                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
 933                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
 934                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
 935                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
 936                         END IF;
 937
 938                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
 939
 940                         IF (value IS NOT NULL AND value <> '') THEN
 941                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
 942                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
 943                         END IF;
 944
 945                 END IF;
 946
 947         END LOOP;
 948
 949         RETURN NULL;
 950 END;
 951 $$ LANGUAGE PLPGSQL;
 952
 953 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
 954 BEGIN
 955         IF TG_OP = 'UPDATE' THEN
 956                 DELETE FROM acq.lineitem_attr
 957                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
 958                 RETURN NEW;
 959         ELSE
 960                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
 961                 RETURN OLD;
 962         END IF;
 963 END;
 964 $$ LANGUAGE PLPGSQL;
 965
 966 CREATE TRIGGER cleanup_lineitem_trigger
 967         BEFORE UPDATE OR DELETE ON acq.lineitem
 968         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
 969
 970 CREATE TRIGGER ingest_lineitem_trigger
 971         AFTER INSERT OR UPDATE ON acq.lineitem
 972         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
 973
 974 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
 975 DECLARE
 976     rat NUMERIC;
 977 BEGIN
 978     IF from_ex = to_ex THEN
 979         RETURN 1.0;
 980     END IF;
 981
 982     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
 983
 984     IF FOUND THEN
 985         RETURN rat;
 986     ELSE
 987         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
 988         IF FOUND THEN
 989             RETURN 1.0/rat;
 990         END IF;
 991     END IF;
 992
 993     RETURN NULL;
 994
 995 END;
 996 $$ LANGUAGE PLPGSQL;
 997
 998 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
 999     SELECT $3 * acq.exchange_ratio($1, $2);
1000 $$ LANGUAGE SQL;
1001
1002 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
1003 /*
1004         Examine the acq.fiscal_year table, comparing successive years.
1005         Report any inconsistencies, i.e. years that overlap, have gaps
1006     between them, or are out of sequence.
1007 */
1008 RETURNS SETOF RECORD AS $$
1009 DECLARE
1010         first_row  BOOLEAN;
1011         curr_year  RECORD;
1012         prev_year  RECORD;
1013         return_rec RECORD;
1014 BEGIN
1015         first_row := true;
1016         FOR curr_year in
1017                 SELECT
1018                         id,
1019                         calendar,
1020                         year,
1021                         year_begin,
1022                         year_end
1023                 FROM
1024                         acq.fiscal_year
1025                 ORDER BY
1026                         calendar,
1027                         year_begin
1028         LOOP
1029                 --
1030                 IF first_row THEN
1031                         first_row := FALSE;
1032                 ELSIF curr_year.calendar    = prev_year.calendar THEN
1033                         IF curr_year.year_begin > prev_year.year_end THEN
1034                                 -- This ugly kludge works around the fact that older
1035                                 -- versions of PostgreSQL don't support RETURN QUERY SELECT
1036                                 FOR return_rec IN SELECT
1037                                         prev_year.id,
1038                                         prev_year.year,
1039                                         'Gap between fiscal years'::TEXT
1040                                 LOOP
1041                                         RETURN NEXT return_rec;
1042                                 END LOOP;
1043                         ELSIF curr_year.year_begin < prev_year.year_end THEN
1044                                 FOR return_rec IN SELECT
1045                                         prev_year.id,
1046                                         prev_year.year,
1047                                         'Overlapping fiscal years'::TEXT
1048                                 LOOP
1049                                         RETURN NEXT return_rec;
1050                                 END LOOP;
1051                         ELSIF curr_year.year < prev_year.year THEN
1052                                 FOR return_rec IN SELECT
1053                                         prev_year.id,
1054                                         prev_year.year,
1055                                         'Fiscal years out of order'::TEXT
1056                                 LOOP
1057                                         RETURN NEXT return_rec;
1058                                 END LOOP;
1059                         END IF;
1060                 END IF;
1061                 --
1062                 prev_year := curr_year;
1063         END LOOP;
1064         --
1065         RETURN;
1066 END;
1067 $$ LANGUAGE plpgsql;
1068
1069 CREATE OR REPLACE FUNCTION acq.transfer_fund(
1070         old_fund   IN INT,
1071         old_amount IN NUMERIC,     -- in currency of old fund
1072         new_fund   IN INT,
1073         new_amount IN NUMERIC,     -- in currency of new fund
1074         user_id    IN INT,
1075         xfer_note  IN TEXT         -- to be recorded in acq.fund_transfer
1076         -- ,funding_source_in IN INT  -- if user wants to specify a funding source (see notes)
1077 ) RETURNS VOID AS $$
1078 /* -------------------------------------------------------------------------------
1079
1080 Function to transfer money from one fund to another.
1081
1082 A transfer is represented as a pair of entries in acq.fund_allocation, with a
1083 negative amount for the old (losing) fund and a positive amount for the new
1084 (gaining) fund.  In some cases there may be more than one such pair of entries
1085 in order to pull the money from different funding sources, or more specifically
1086 from different funding source credits.  For each such pair there is also an
1087 entry in acq.fund_transfer.
1088
1089 Since funding_source is a non-nullable column in acq.fund_allocation, we must
1090 choose a funding source for the transferred money to come from.  This choice
1091 must meet two constraints, so far as possible:
1092
1093 1. The amount transferred from a given funding source must not exceed the
1094 amount allocated to the old fund by the funding source.  To that end we
1095 compare the amount being transferred to the amount allocated.
1096
1097 2. We shouldn't transfer money that has already been spent or encumbered, as
1098 defined by the funding attribution process.  We attribute expenses to the
1099 oldest funding source credits first.  In order to avoid transferring that
1100 attributed money, we reverse the priority, transferring from the newest funding
1101 source credits first.  There can be no guarantee that this approach will
1102 avoid overcommitting a fund, but no other approach can do any better.
1103
1104 In this context the age of a funding source credit is defined by the
1105 deadline_date for credits with deadline_dates, and by the effective_date for
1106 credits without deadline_dates, with the proviso that credits with deadline_dates
1107 are all considered "older" than those without.
1108
1109 ----------
1110
1111 In the signature for this function, there is one last parameter commented out,
1112 named "funding_source_in".  Correspondingly, the WHERE clause for the query
1113 driving the main loop has an OR clause commented out, which references the
1114 funding_source_in parameter.
1115
1116 If these lines are uncommented, this function will allow the user optionally to
1117 restrict a fund transfer to a specified funding source.  If the source
1118 parameter is left NULL, then there will be no such restriction.
1119
1120 ------------------------------------------------------------------------------- */
1121 DECLARE
1122         same_currency      BOOLEAN;
1123         currency_ratio     NUMERIC;
1124         old_fund_currency  TEXT;
1125         old_remaining      NUMERIC;  -- in currency of old fund
1126         new_fund_currency  TEXT;
1127         new_fund_active    BOOLEAN;
1128         new_remaining      NUMERIC;  -- in currency of new fund
1129         curr_old_amt       NUMERIC;  -- in currency of old fund
1130         curr_new_amt       NUMERIC;  -- in currency of new fund
1131         source_addition    NUMERIC;  -- in currency of funding source
1132         source_deduction   NUMERIC;  -- in currency of funding source
1133         orig_allocated_amt NUMERIC;  -- in currency of funding source
1134         allocated_amt      NUMERIC;  -- in currency of fund
1135         source             RECORD;
1136 BEGIN
1137         --
1138         -- Sanity checks
1139         --
1140         IF old_fund IS NULL THEN
1141                 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
1142         END IF;
1143         --
1144         IF old_amount IS NULL THEN
1145                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
1146         END IF;
1147         --
1148         -- The new fund and its amount must be both NULL or both not NULL.
1149         --
1150         IF new_fund IS NOT NULL AND new_amount IS NULL THEN
1151                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
1152         END IF;
1153         --
1154         IF new_fund IS NULL AND new_amount IS NOT NULL THEN
1155                 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
1156         END IF;
1157         --
1158         IF user_id IS NULL THEN
1159                 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
1160         END IF;
1161         --
1162         -- Initialize the amounts to be transferred, each denominated
1163         -- in the currency of its respective fund.  They will be
1164         -- reduced on each iteration of the loop.
1165         --
1166         old_remaining := old_amount;
1167         new_remaining := new_amount;
1168         --
1169         -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
1170         --      old_amount, old_fund, new_amount, new_fund;
1171         --
1172         -- Get the currency types of the old and new funds.
1173         --
1174         SELECT
1175                 currency_type
1176         INTO
1177                 old_fund_currency
1178         FROM
1179                 acq.fund
1180         WHERE
1181                 id = old_fund;
1182         --
1183         IF old_fund_currency IS NULL THEN
1184                 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
1185         END IF;
1186         --
1187         IF new_fund IS NOT NULL THEN
1188                 SELECT
1189                         currency_type,
1190                         active
1191                 INTO
1192                         new_fund_currency,
1193                         new_fund_active
1194                 FROM
1195                         acq.fund
1196                 WHERE
1197                         id = new_fund;
1198                 --
1199                 IF new_fund_currency IS NULL THEN
1200                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
1201                 ELSIF NOT new_fund_active THEN
1202                         --
1203                         -- No point in putting money into a fund from whence you can't spend it
1204                         --
1205                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
1206                 END IF;
1207                 --
1208                 IF new_amount = old_amount THEN
1209                         same_currency := true;
1210                         currency_ratio := 1;
1211                 ELSE
1212                         --
1213                         -- We'll have to translate currency between funds.  We presume that
1214                         -- the calling code has already applied an appropriate exchange rate,
1215                         -- so we'll apply the same conversion to each sub-transfer.
1216                         --
1217                         same_currency := false;
1218                         currency_ratio := new_amount / old_amount;
1219                 END IF;
1220         END IF;
1221         --
1222         -- Identify the funding source(s) from which we want to transfer the money.
1223         -- The principle is that we want to transfer the newest money first, because
1224         -- we spend the oldest money first.  The priority for spending is defined
1225         -- by a sort of the view acq.ordered_funding_source_credit.
1226         --
1227         FOR source in
1228                 SELECT
1229                         ofsc.id,
1230                         ofsc.funding_source,
1231                         ofsc.amount,
1232                         ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
1233                                 AS converted_amt,
1234                         fs.currency_type
1235                 FROM
1236                         acq.ordered_funding_source_credit AS ofsc,
1237                         acq.funding_source fs
1238                 WHERE
1239                         ofsc.funding_source = fs.id
1240                         and ofsc.funding_source IN
1241                         (
1242                                 SELECT funding_source
1243                                 FROM acq.fund_allocation
1244                                 WHERE fund = old_fund
1245                         )
1246                         -- and
1247                         -- (
1248                         --      ofsc.funding_source = funding_source_in
1249                         --      OR funding_source_in IS NULL
1250                         -- )
1251                 ORDER BY
1252                         ofsc.sort_priority desc,
1253                         ofsc.sort_date desc,
1254                         ofsc.id desc
1255         LOOP
1256                 --
1257                 -- Determine how much money the old fund got from this funding source,
1258                 -- denominated in the currency types of the source and of the fund.
1259                 -- This result may reflect transfers from previous iterations.
1260                 --
1261                 SELECT
1262                         COALESCE( sum( amount ), 0 ),
1263                         COALESCE( sum( amount )
1264                                 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
1265                 INTO
1266                         orig_allocated_amt,     -- in currency of the source
1267                         allocated_amt           -- in currency of the old fund
1268                 FROM
1269                         acq.fund_allocation
1270                 WHERE
1271                         fund = old_fund
1272                         and funding_source = source.funding_source;
1273                 --
1274                 -- Determine how much to transfer from this credit, in the currency
1275                 -- of the fund.   Begin with the amount remaining to be attributed:
1276                 --
1277                 curr_old_amt := old_remaining;
1278                 --
1279                 -- Can't attribute more than was allocated from the fund:
1280                 --
1281                 IF curr_old_amt > allocated_amt THEN
1282                         curr_old_amt := allocated_amt;
1283                 END IF;
1284                 --
1285                 -- Can't attribute more than the amount of the current credit:
1286                 --
1287                 IF curr_old_amt > source.converted_amt THEN
1288                         curr_old_amt := source.converted_amt;
1289                 END IF;
1290                 --
1291                 curr_old_amt := trunc( curr_old_amt, 2 );
1292                 --
1293                 old_remaining := old_remaining - curr_old_amt;
1294                 --
1295                 -- Determine the amount to be deducted, if any,
1296                 -- from the old allocation.
1297                 --
1298                 IF old_remaining > 0 THEN
1299                         --
1300                         -- In this case we're using the whole allocation, so use that
1301                         -- amount directly instead of applying a currency translation
1302                         -- and thereby inviting round-off errors.
1303                         --
1304                         source_deduction := - orig_allocated_amt;
1305                 ELSE
1306                         source_deduction := trunc(
1307                                 ( - curr_old_amt ) *
1308                                         acq.exchange_ratio( old_fund_currency, source.currency_type ),
1309                                 2 );
1310                 END IF;
1311                 --
1312                 IF source_deduction <> 0 THEN
1313                         --
1314                         -- Insert negative allocation for old fund in fund_allocation,
1315                         -- converted into the currency of the funding source
1316                         --
1317                         INSERT INTO acq.fund_allocation (
1318                                 funding_source,
1319                                 fund,
1320                                 amount,
1321                                 allocator,
1322                                 note
1323                         ) VALUES (
1324                                 source.funding_source,
1325                                 old_fund,
1326                                 source_deduction,
1327                                 user_id,
1328                                 'Transfer to fund ' || new_fund
1329                         );
1330                 END IF;
1331                 --
1332                 IF new_fund IS NOT NULL THEN
1333                         --
1334                         -- Determine how much to add to the new fund, in
1335                         -- its currency, and how much remains to be added:
1336                         --
1337                         IF same_currency THEN
1338                                 curr_new_amt := curr_old_amt;
1339                         ELSE
1340                                 IF old_remaining = 0 THEN
1341                                         --
1342                                         -- This is the last iteration, so nothing should be left
1343                                         --
1344                                         curr_new_amt := new_remaining;
1345                                         new_remaining := 0;
1346                                 ELSE
1347                                         curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
1348                                         new_remaining := new_remaining - curr_new_amt;
1349                                 END IF;
1350                         END IF;
1351                         --
1352                         -- Determine how much to add, if any,
1353                         -- to the new fund's allocation.
1354                         --
1355                         IF old_remaining > 0 THEN
1356                                 --
1357                                 -- In this case we're using the whole allocation, so use that amount
1358                                 -- amount directly instead of applying a currency translation and
1359                                 -- thereby inviting round-off errors.
1360                                 --
1361                                 source_addition := orig_allocated_amt;
1362                         ELSIF source.currency_type = old_fund_currency THEN
1363                                 --
1364                                 -- In this case we don't need a round trip currency translation,
1365                                 -- thereby inviting round-off errors:
1366                                 --
1367                                 source_addition := curr_old_amt;
1368                         ELSE
1369                                 source_addition := trunc(
1370                                         curr_new_amt *
1371                                                 acq.exchange_ratio( new_fund_currency, source.currency_type ),
1372                                         2 );
1373                         END IF;
1374                         --
1375                         IF source_addition <> 0 THEN
1376                                 --
1377                                 -- Insert positive allocation for new fund in fund_allocation,
1378                                 -- converted to the currency of the founding source
1379                                 --
1380                                 INSERT INTO acq.fund_allocation (
1381                                         funding_source,
1382                                         fund,
1383                                         amount,
1384                                         allocator,
1385                                         note
1386                                 ) VALUES (
1387                                         source.funding_source,
1388                                         new_fund,
1389                                         source_addition,
1390                                         user_id,
1391                                         'Transfer from fund ' || old_fund
1392                                 );
1393                         END IF;
1394                 END IF;
1395                 --
1396                 IF trunc( curr_old_amt, 2 ) <> 0
1397                 OR trunc( curr_new_amt, 2 ) <> 0 THEN
1398                         --
1399                         -- Insert row in fund_transfer, using amounts in the currency of the funds
1400                         --
1401                         INSERT INTO acq.fund_transfer (
1402                                 src_fund,
1403                                 src_amount,
1404                                 dest_fund,
1405                                 dest_amount,
1406                                 transfer_user,
1407                                 note,
1408                                 funding_source_credit
1409                         ) VALUES (
1410                                 old_fund,
1411                                 trunc( curr_old_amt, 2 ),
1412                                 new_fund,
1413                                 trunc( curr_new_amt, 2 ),
1414                                 user_id,
1415                                 xfer_note,
1416                                 source.id
1417                         );
1418                 END IF;
1419                 --
1420                 if old_remaining <= 0 THEN
1421                         EXIT;                   -- Nothing more to be transferred
1422                 END IF;
1423         END LOOP;
1424 END;
1425 $$ LANGUAGE plpgsql;
1426
1427 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
1428 /*
1429 Function to attribute expenditures and encumbrances to funding source credits,
1430 and thereby to funding sources.
1431
1432 Read the debits in chonological order, attributing each one to one or
1433 more funding source credits.  Constraints:
1434
1435 1. Don't attribute more to a credit than the amount of the credit.
1436
1437 2. For a given fund, don't attribute more to a funding source than the
1438 source has allocated to that fund.
1439
1440 3. Attribute debits to credits with deadlines before attributing them to
1441 credits without deadlines.  Otherwise attribute to the earliest credits
1442 first, based on the deadline date when present, or on the effective date
1443 when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
1444 This ordering is defined by an ORDER BY clause on the view
1445 acq.ordered_funding_source_credit.
1446
1447 Start by truncating the table acq.debit_attribution.  Then insert a row
1448 into that table for each attribution.  If a debit cannot be fully
1449 attributed, insert a row for the unattributable balance, with the
1450 funding_source_credit and credit_amount columns NULL.
1451 */
1452 DECLARE
1453         curr_fund_source_bal RECORD;
1454         seqno                INT;     -- sequence num for credits applicable to a fund
1455         fund_credit          RECORD;  -- current row in temp t_fund_credit table
1456         fc                   RECORD;  -- used for loading t_fund_credit table
1457         sc                   RECORD;  -- used for loading t_fund_credit table
1458         --
1459         -- Used exclusively in the main loop:
1460         --
1461         deb                 RECORD;   -- current row from acq.fund_debit table
1462         curr_credit_bal     RECORD;   -- current row from temp t_credit table
1463         debit_balance       NUMERIC;  -- amount left to attribute for current debit
1464         conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
1465         attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
1466         conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
1467         conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
1468         conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
1469         attrib_count        INT;      -- populates id of acq.debit_attribution
1470 BEGIN
1471         --
1472         -- Load a temporary table.  For each combination of fund and funding source,
1473         -- load an entry with the total amount allocated to that fund by that source.
1474         -- This sum may reflect transfers as well as original allocations.  We will
1475         -- reduce this balance whenever we attribute debits to it.
1476         --
1477         CREATE TEMP TABLE t_fund_source_bal
1478         ON COMMIT DROP AS
1479                 SELECT
1480                         fund AS fund,
1481                         funding_source AS source,
1482                         sum( amount ) AS balance
1483                 FROM
1484                         acq.fund_allocation
1485                 GROUP BY
1486                         fund,
1487                         funding_source
1488                 HAVING
1489                         sum( amount ) > 0;
1490         --
1491         CREATE INDEX t_fund_source_bal_idx
1492                 ON t_fund_source_bal( fund, source );
1493         -------------------------------------------------------------------------------
1494         --
1495         -- Load another temporary table.  For each fund, load zero or more
1496         -- funding source credits from which that fund can get money.
1497         --
1498         CREATE TEMP TABLE t_fund_credit (
1499                 fund        INT,
1500                 seq         INT,
1501                 credit      INT
1502         ) ON COMMIT DROP;
1503         --
1504         FOR fc IN
1505                 SELECT DISTINCT fund
1506                 FROM acq.fund_allocation
1507                 ORDER BY fund
1508         LOOP                  -- Loop over the funds
1509                 seqno := 1;
1510                 FOR sc IN
1511                         SELECT
1512                                 ofsc.id
1513                         FROM
1514                                 acq.ordered_funding_source_credit AS ofsc
1515                         WHERE
1516                                 ofsc.funding_source IN
1517                                 (
1518                                         SELECT funding_source
1519                                         FROM acq.fund_allocation
1520                                         WHERE fund = fc.fund
1521                                 )
1522                 ORDER BY
1523                     ofsc.sort_priority,
1524                     ofsc.sort_date,
1525                     ofsc.id
1526                 LOOP                        -- Add each credit to the list
1527                         INSERT INTO t_fund_credit (
1528                                 fund,
1529                                 seq,
1530                                 credit
1531                         ) VALUES (
1532                                 fc.fund,
1533                                 seqno,
1534                                 sc.id
1535                         );
1536                         --RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
1537                         seqno := seqno + 1;
1538                 END LOOP;     -- Loop over credits for a given fund
1539         END LOOP;         -- Loop over funds
1540         --
1541         CREATE INDEX t_fund_credit_idx
1542                 ON t_fund_credit( fund, seq );
1543         -------------------------------------------------------------------------------
1544         --
1545         -- Load yet another temporary table.  This one is a list of funding source
1546         -- credits, with their balances.  We shall reduce those balances as we
1547         -- attribute debits to them.
1548         --
1549         CREATE TEMP TABLE t_credit
1550         ON COMMIT DROP AS
1551         SELECT
1552             fsc.id AS credit,
1553             fsc.funding_source AS source,
1554             fsc.amount AS balance,
1555             fs.currency_type AS currency_type
1556         FROM
1557             acq.funding_source_credit AS fsc,
1558             acq.funding_source fs
1559         WHERE
1560             fsc.funding_source = fs.id
1561                         AND fsc.amount > 0;
1562         --
1563         CREATE INDEX t_credit_idx
1564                 ON t_credit( credit );
1565         --
1566         -------------------------------------------------------------------------------
1567         --
1568         -- Now that we have loaded the lookup tables: loop through the debits,
1569         -- attributing each one to one or more funding source credits.
1570         --
1571         truncate table acq.debit_attribution;
1572         --
1573         attrib_count := 0;
1574         FOR deb in
1575                 SELECT
1576                         fd.id,
1577                         fd.fund,
1578                         fd.amount,
1579                         f.currency_type,
1580                         fd.encumbrance
1581                 FROM
1582                         acq.fund_debit fd,
1583                         acq.fund f
1584                 WHERE
1585                         fd.fund = f.id
1586                 ORDER BY
1587                         fd.id
1588         LOOP
1589                 --RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
1590                 --
1591                 debit_balance := deb.amount;
1592                 --
1593                 -- Loop over the funding source credits that are eligible
1594                 -- to pay for this debit
1595                 --
1596                 FOR fund_credit IN
1597                         SELECT
1598                                 credit
1599                         FROM
1600                                 t_fund_credit
1601                         WHERE
1602                                 fund = deb.fund
1603                         ORDER BY
1604                                 seq
1605                 LOOP
1606                         --RAISE NOTICE '   Examining credit %', fund_credit.credit;
1607                         --
1608                         -- Look up the balance for this credit.  If it's zero, then
1609                         -- it's not useful, so treat it as if you didn't find it.
1610                         -- (Actually there shouldn't be any zero balances in the table,
1611                         -- but we check just to make sure.)
1612                         --
1613                         SELECT *
1614                         INTO curr_credit_bal
1615                         FROM t_credit
1616                         WHERE
1617                                 credit = fund_credit.credit
1618                                 AND balance > 0;
1619                         --
1620                         IF curr_credit_bal IS NULL THEN
1621                                 --
1622                                 -- This credit is exhausted; try the next one.
1623                                 --
1624                                 CONTINUE;
1625                         END IF;
1626                         --
1627                         --
1628                         -- At this point we have an applicable credit with some money left.
1629                         -- Now see if the relevant funding_source has any money left.
1630                         --
1631                         -- Look up the balance of the allocation for this combination of
1632                         -- fund and source.  If you find such an entry, but it has a zero
1633                         -- balance, then it's not useful, so treat it as unfound.
1634                         -- (Actually there shouldn't be any zero balances in the table,
1635                         -- but we check just to make sure.)
1636                         --
1637                         SELECT *
1638                         INTO curr_fund_source_bal
1639                         FROM t_fund_source_bal
1640                         WHERE
1641                                 fund = deb.fund
1642                                 AND source = curr_credit_bal.source
1643                                 AND balance > 0;
1644                         --
1645                         IF curr_fund_source_bal IS NULL THEN
1646                                 --
1647                                 -- This fund/source doesn't exist or is already exhausted,
1648                                 -- so we can't use this credit.  Go on to the next one.
1649                                 --
1650                                 CONTINUE;
1651                         END IF;
1652                         --
1653                         -- Convert the available balances to the currency of the fund
1654                         --
1655                         conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
1656                                 curr_credit_bal.currency_type, deb.currency_type );
1657                         conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
1658                                 curr_credit_bal.currency_type, deb.currency_type );
1659                         --
1660                         -- Determine how much we can attribute to this credit: the minimum
1661                         -- of the debit amount, the fund/source balance, and the
1662                         -- credit balance
1663                         --
1664                         --RAISE NOTICE '   deb bal %', debit_balance;
1665                         --RAISE NOTICE '      source % balance %', curr_credit_bal.source, conv_alloc_balance;
1666                         --RAISE NOTICE '      credit % balance %', curr_credit_bal.credit, conv_cred_balance;
1667                         --
1668                         conv_attr_amount := NULL;
1669                         attr_amount := debit_balance;
1670                         --
1671                         IF attr_amount > conv_alloc_balance THEN
1672                                 attr_amount := conv_alloc_balance;
1673                                 conv_attr_amount := curr_fund_source_bal.balance;
1674                         END IF;
1675                         IF attr_amount > conv_cred_balance THEN
1676                                 attr_amount := conv_cred_balance;
1677                                 conv_attr_amount := curr_credit_bal.balance;
1678                         END IF;
1679                         --
1680                         -- If we're attributing all of one of the balances, then that's how
1681                         -- much we will deduct from the balances, and we already captured
1682                         -- that amount above.  Otherwise we must convert the amount of the
1683                         -- attribution from the currency of the fund back to the currency of
1684                         -- the funding source.
1685                         --
1686                         IF conv_attr_amount IS NULL THEN
1687                                 conv_attr_amount := attr_amount * acq.exchange_ratio(
1688                                         deb.currency_type, curr_credit_bal.currency_type );
1689                         END IF;
1690                         --
1691                         -- Insert a row to record the attribution
1692                         --
1693                         attrib_count := attrib_count + 1;
1694                         INSERT INTO acq.debit_attribution (
1695                                 id,
1696                                 fund_debit,
1697                                 debit_amount,
1698                                 funding_source_credit,
1699                                 credit_amount
1700                         ) VALUES (
1701                                 attrib_count,
1702                                 deb.id,
1703                                 attr_amount,
1704                                 curr_credit_bal.credit,
1705                                 conv_attr_amount
1706                         );
1707                         --
1708                         -- Subtract the attributed amount from the various balances
1709                         --
1710                         debit_balance := debit_balance - attr_amount;
1711                         curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
1712                         --
1713                         IF curr_fund_source_bal.balance <= 0 THEN
1714                                 --
1715                                 -- This allocation is exhausted.  Delete it so
1716                                 -- that we don't waste time looking at it again.
1717                                 --
1718                                 DELETE FROM t_fund_source_bal
1719                                 WHERE
1720                                         fund = curr_fund_source_bal.fund
1721                                         AND source = curr_fund_source_bal.source;
1722                         ELSE
1723                                 UPDATE t_fund_source_bal
1724                                 SET balance = balance - conv_attr_amount
1725                                 WHERE
1726                                         fund = curr_fund_source_bal.fund
1727                                         AND source = curr_fund_source_bal.source;
1728                         END IF;
1729                         --
1730                         IF curr_credit_bal.balance <= 0 THEN
1731                                 --
1732                                 -- This funding source credit is exhausted.  Delete it
1733                                 -- so that we don't waste time looking at it again.
1734                                 --
1735                                 --DELETE FROM t_credit
1736                                 --WHERE
1737                                 --      credit = curr_credit_bal.credit;
1738                                 --
1739                                 DELETE FROM t_fund_credit
1740                                 WHERE
1741                                         credit = curr_credit_bal.credit;
1742                         ELSE
1743                                 UPDATE t_credit
1744                                 SET balance = curr_credit_bal.balance
1745                                 WHERE
1746                                         credit = curr_credit_bal.credit;
1747                         END IF;
1748                         --
1749                         -- Are we done with this debit yet?
1750                         --
1751                         IF debit_balance <= 0 THEN
1752                                 EXIT;       -- We've fully attributed this debit; stop looking at credits.
1753                         END IF;
1754                 END LOOP;       -- End loop over credits
1755                 --
1756                 IF debit_balance <> 0 THEN
1757                         --
1758                         -- We weren't able to attribute this debit, or at least not
1759                         -- all of it.  Insert a row for the unattributed balance.
1760                         --
1761                         attrib_count := attrib_count + 1;
1762                         INSERT INTO acq.debit_attribution (
1763                                 id,
1764                                 fund_debit,
1765                                 debit_amount,
1766                                 funding_source_credit,
1767                                 credit_amount
1768                         ) VALUES (
1769                                 attrib_count,
1770                                 deb.id,
1771                                 debit_balance,
1772                                 NULL,
1773                                 NULL
1774                         );
1775                 END IF;
1776         END LOOP;   -- End of loop over debits
1777 END;
1778 $$ LANGUAGE 'plpgsql';
1779
1780 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit(
1781         old_year INTEGER,
1782         user_id INTEGER,
1783         org_unit_id INTEGER
1784 ) RETURNS VOID AS $$
1785 DECLARE
1786 --
1787 new_id      INT;
1788 old_fund    RECORD;
1789 org_found   BOOLEAN;
1790 --
1791 BEGIN
1792         --
1793         -- Sanity checks
1794         --
1795         IF old_year IS NULL THEN
1796                 RAISE EXCEPTION 'Input year argument is NULL';
1797         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1798                 RAISE EXCEPTION 'Input year is out of range';
1799         END IF;
1800         --
1801         IF user_id IS NULL THEN
1802                 RAISE EXCEPTION 'Input user id argument is NULL';
1803         END IF;
1804         --
1805         IF org_unit_id IS NULL THEN
1806                 RAISE EXCEPTION 'Org unit id argument is NULL';
1807         ELSE
1808                 SELECT TRUE INTO org_found
1809                 FROM actor.org_unit
1810                 WHERE id = org_unit_id;
1811                 --
1812                 IF org_found IS NULL THEN
1813                         RAISE EXCEPTION 'Org unit id is invalid';
1814                 END IF;
1815         END IF;
1816         --
1817         -- Loop over the applicable funds
1818         --
1819         FOR old_fund in SELECT * FROM acq.fund
1820         WHERE
1821                 year = old_year
1822                 AND propagate
1823                 AND org = org_unit_id
1824         LOOP
1825                 BEGIN
1826                         INSERT INTO acq.fund (
1827                                 org,
1828                                 name,
1829                                 year,
1830                                 currency_type,
1831                                 code,
1832                                 rollover,
1833                                 propagate
1834                         ) VALUES (
1835                                 old_fund.org,
1836                                 old_fund.name,
1837                                 old_year + 1,
1838                                 old_fund.currency_type,
1839                                 old_fund.code,
1840                                 old_fund.rollover,
1841                                 true
1842                         )
1843                         RETURNING id INTO new_id;
1844                 EXCEPTION
1845                         WHEN unique_violation THEN
1846                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1847                                 CONTINUE;
1848                 END;
1849                 --RAISE NOTICE 'Propagating fund % to fund %',
1850                 --      old_fund.code, new_id;
1851         END LOOP;
1852 END;
1853 $$ LANGUAGE plpgsql;
1854
1855 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1856         old_year INTEGER,
1857         user_id INTEGER,
1858         org_unit_id INTEGER
1859 ) RETURNS VOID AS $$
1860 DECLARE
1861 --
1862 new_id      INT;
1863 old_fund    RECORD;
1864 org_found   BOOLEAN;
1865 --
1866 BEGIN
1867         --
1868         -- Sanity checks
1869         --
1870         IF old_year IS NULL THEN
1871                 RAISE EXCEPTION 'Input year argument is NULL';
1872         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1873                 RAISE EXCEPTION 'Input year is out of range';
1874         END IF;
1875         --
1876         IF user_id IS NULL THEN
1877                 RAISE EXCEPTION 'Input user id argument is NULL';
1878         END IF;
1879         --
1880         IF org_unit_id IS NULL THEN
1881                 RAISE EXCEPTION 'Org unit id argument is NULL';
1882         ELSE
1883                 SELECT TRUE INTO org_found
1884                 FROM actor.org_unit
1885                 WHERE id = org_unit_id;
1886                 --
1887                 IF org_found IS NULL THEN
1888                         RAISE EXCEPTION 'Org unit id is invalid';
1889                 END IF;
1890         END IF;
1891         --
1892         -- Loop over the applicable funds
1893         --
1894         FOR old_fund in SELECT * FROM acq.fund
1895         WHERE
1896                 year = old_year
1897                 AND propagate
1898                 AND org in (
1899                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
1900                 )
1901         LOOP
1902                 BEGIN
1903                         INSERT INTO acq.fund (
1904                                 org,
1905                                 name,
1906                                 year,
1907                                 currency_type,
1908                                 code,
1909                                 rollover,
1910                                 propagate
1911                         ) VALUES (
1912                                 old_fund.org,
1913                                 old_fund.name,
1914                                 old_year + 1,
1915                                 old_fund.currency_type,
1916                                 old_fund.code,
1917                                 old_fund.rollover,
1918                                 true
1919                         )
1920                         RETURNING id INTO new_id;
1921                 EXCEPTION
1922                         WHEN unique_violation THEN
1923                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1924                                 CONTINUE;
1925                 END;
1926                 --RAISE NOTICE 'Propagating fund % to fund %',
1927                 --      old_fund.code, new_id;
1928         END LOOP;
1929 END;
1930 $$ LANGUAGE plpgsql;
1931
1932 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
1933         old_year INTEGER,
1934         user_id INTEGER,
1935         org_unit_id INTEGER
1936 ) RETURNS VOID AS $$
1937 DECLARE
1938 --
1939 new_fund    INT;
1940 new_year    INT := old_year + 1;
1941 org_found   BOOL;
1942 xfer_amount NUMERIC;
1943 roll_fund   RECORD;
1944 deb         RECORD;
1945 detail      RECORD;
1946 --
1947 BEGIN
1948         --
1949         -- Sanity checks
1950         --
1951         IF old_year IS NULL THEN
1952                 RAISE EXCEPTION 'Input year argument is NULL';
1953     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1954         RAISE EXCEPTION 'Input year is out of range';
1955         END IF;
1956         --
1957         IF user_id IS NULL THEN
1958                 RAISE EXCEPTION 'Input user id argument is NULL';
1959         END IF;
1960         --
1961         IF org_unit_id IS NULL THEN
1962                 RAISE EXCEPTION 'Org unit id argument is NULL';
1963         ELSE
1964                 --
1965                 -- Validate the org unit
1966                 --
1967                 SELECT TRUE
1968                 INTO org_found
1969                 FROM actor.org_unit
1970                 WHERE id = org_unit_id;
1971                 --
1972                 IF org_found IS NULL THEN
1973                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
1974                 END IF;
1975         END IF;
1976         --
1977         -- Loop over the propagable funds to identify the details
1978         -- from the old fund plus the id of the new one, if it exists.
1979         --
1980         FOR roll_fund in
1981         SELECT
1982             oldf.id AS old_fund,
1983             oldf.org,
1984             oldf.name,
1985             oldf.currency_type,
1986             oldf.code,
1987                 oldf.rollover,
1988             newf.id AS new_fund_id
1989         FROM
1990         acq.fund AS oldf
1991         LEFT JOIN acq.fund AS newf
1992                 ON ( oldf.code = newf.code )
1993         WHERE
1994                     oldf.org = org_unit_id
1995                 and oldf.year = old_year
1996                 and oldf.propagate
1997         and newf.year = new_year
1998         LOOP
1999                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
2000                 --
2001                 IF roll_fund.new_fund_id IS NULL THEN
2002                         --
2003                         -- The old fund hasn't been propagated yet.  Propagate it now.
2004                         --
2005                         INSERT INTO acq.fund (
2006                                 org,
2007                                 name,
2008                                 year,
2009                                 currency_type,
2010                                 code,
2011                                 rollover,
2012                                 propagate
2013                         ) VALUES (
2014                                 roll_fund.org,
2015                                 roll_fund.name,
2016                                 new_year,
2017                                 roll_fund.currency_type,
2018                                 roll_fund.code,
2019                                 true,
2020                                 true
2021                         )
2022                         RETURNING id INTO new_fund;
2023                 ELSE
2024                         new_fund = roll_fund.new_fund_id;
2025                 END IF;
2026                 --
2027                 -- Determine the amount to transfer
2028                 --
2029                 SELECT amount
2030                 INTO xfer_amount
2031                 FROM acq.fund_spent_balance
2032                 WHERE fund = roll_fund.old_fund;
2033                 --
2034                 IF xfer_amount <> 0 THEN
2035                         IF roll_fund.rollover THEN
2036                                 --
2037                                 -- Transfer balance from old fund to new
2038                                 --
2039                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
2040                                 --
2041                                 PERFORM acq.transfer_fund(
2042                                         roll_fund.old_fund,
2043                                         xfer_amount,
2044                                         new_fund,
2045                                         xfer_amount,
2046                                         user_id,
2047                                         'Rollover'
2048                                 );
2049                         ELSE
2050                                 --
2051                                 -- Transfer balance from old fund to the void
2052                                 --
2053                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
2054                                 --
2055                                 PERFORM acq.transfer_fund(
2056                                         roll_fund.old_fund,
2057                                         xfer_amount,
2058                                         NULL,
2059                                         NULL,
2060                                         user_id,
2061                                         'Rollover'
2062                                 );
2063                         END IF;
2064                 END IF;
2065                 --
2066                 IF roll_fund.rollover THEN
2067                         --
2068                         -- Move any lineitems from the old fund to the new one
2069                         -- where the associated debit is an encumbrance.
2070                         --
2071                         -- Any other tables tying expenditure details to funds should
2072                         -- receive similar treatment.  At this writing there are none.
2073                         --
2074                         UPDATE acq.lineitem_detail
2075                         SET fund = new_fund
2076                         WHERE
2077                         fund = roll_fund.old_fund -- this condition may be redundant
2078                         AND fund_debit in
2079                         (
2080                                 SELECT id
2081                                 FROM acq.fund_debit
2082                                 WHERE
2083                                 fund = roll_fund.old_fund
2084                                 AND encumbrance
2085                         );
2086                         --
2087                         -- Move encumbrance debits from the old fund to the new fund
2088                         --
2089                         UPDATE acq.fund_debit
2090                         SET fund = new_fund
2091                         wHERE
2092                                 fund = roll_fund.old_fund
2093                                 AND encumbrance;
2094                 END IF;
2095                 --
2096                 -- Mark old fund as inactive, now that we've closed it
2097                 --
2098                 UPDATE acq.fund
2099                 SET active = FALSE
2100                 WHERE id = roll_fund.old_fund;
2101         END LOOP;
2102 END;
2103 $$ LANGUAGE plpgsql;
2104
2105 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
2106         old_year INTEGER,
2107         user_id INTEGER,
2108         org_unit_id INTEGER
2109 ) RETURNS VOID AS $$
2110 DECLARE
2111 --
2112 new_fund    INT;
2113 new_year    INT := old_year + 1;
2114 org_found   BOOL;
2115 xfer_amount NUMERIC;
2116 roll_fund   RECORD;
2117 deb         RECORD;
2118 detail      RECORD;
2119 --
2120 BEGIN
2121         --
2122         -- Sanity checks
2123         --
2124         IF old_year IS NULL THEN
2125                 RAISE EXCEPTION 'Input year argument is NULL';
2126     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
2127         RAISE EXCEPTION 'Input year is out of range';
2128         END IF;
2129         --
2130         IF user_id IS NULL THEN
2131                 RAISE EXCEPTION 'Input user id argument is NULL';
2132         END IF;
2133         --
2134         IF org_unit_id IS NULL THEN
2135                 RAISE EXCEPTION 'Org unit id argument is NULL';
2136         ELSE
2137                 --
2138                 -- Validate the org unit
2139                 --
2140                 SELECT TRUE
2141                 INTO org_found
2142                 FROM actor.org_unit
2143                 WHERE id = org_unit_id;
2144                 --
2145                 IF org_found IS NULL THEN
2146                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
2147                 END IF;
2148         END IF;
2149         --
2150         -- Loop over the propagable funds to identify the details
2151         -- from the old fund plus the id of the new one, if it exists.
2152         --
2153         FOR roll_fund in
2154         SELECT
2155             oldf.id AS old_fund,
2156             oldf.org,
2157             oldf.name,
2158             oldf.currency_type,
2159             oldf.code,
2160                 oldf.rollover,
2161             newf.id AS new_fund_id
2162         FROM
2163         acq.fund AS oldf
2164         LEFT JOIN acq.fund AS newf
2165                 ON ( oldf.code = newf.code )
2166         WHERE
2167                     oldf.year = old_year
2168                 AND oldf.propagate
2169         AND newf.year = new_year
2170                 AND oldf.org in (
2171                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
2172                 )
2173         LOOP
2174                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
2175                 --
2176                 IF roll_fund.new_fund_id IS NULL THEN
2177                         --
2178                         -- The old fund hasn't been propagated yet.  Propagate it now.
2179                         --
2180                         INSERT INTO acq.fund (
2181                                 org,
2182                                 name,
2183                                 year,
2184                                 currency_type,
2185                                 code,
2186                                 rollover,
2187                                 propagate
2188                         ) VALUES (
2189                                 roll_fund.org,
2190                                 roll_fund.name,
2191                                 new_year,
2192                                 roll_fund.currency_type,
2193                                 roll_fund.code,
2194                                 true,
2195                                 true
2196                         )
2197                         RETURNING id INTO new_fund;
2198                 ELSE
2199                         new_fund = roll_fund.new_fund_id;
2200                 END IF;
2201                 --
2202                 -- Determine the amount to transfer
2203                 --
2204                 SELECT amount
2205                 INTO xfer_amount
2206                 FROM acq.fund_spent_balance
2207                 WHERE fund = roll_fund.old_fund;
2208                 --
2209                 IF xfer_amount <> 0 THEN
2210                         IF roll_fund.rollover THEN
2211                                 --
2212                                 -- Transfer balance from old fund to new
2213                                 --
2214                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
2215                                 --
2216                                 PERFORM acq.transfer_fund(
2217                                         roll_fund.old_fund,
2218                                         xfer_amount,
2219                                         new_fund,
2220                                         xfer_amount,
2221                                         user_id,
2222                                         'Rollover'
2223                                 );
2224                         ELSE
2225                                 --
2226                                 -- Transfer balance from old fund to the void
2227                                 --
2228                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
2229                                 --
2230                                 PERFORM acq.transfer_fund(
2231                                         roll_fund.old_fund,
2232                                         xfer_amount,
2233                                         NULL,
2234                                         NULL,
2235                                         user_id,
2236                                         'Rollover'
2237                                 );
2238                         END IF;
2239                 END IF;
2240                 --
2241                 IF roll_fund.rollover THEN
2242                         --
2243                         -- Move any lineitems from the old fund to the new one
2244                         -- where the associated debit is an encumbrance.
2245                         --
2246                         -- Any other tables tying expenditure details to funds should
2247                         -- receive similar treatment.  At this writing there are none.
2248                         --
2249                         UPDATE acq.lineitem_detail
2250                         SET fund = new_fund
2251                         WHERE
2252                         fund = roll_fund.old_fund -- this condition may be redundant
2253                         AND fund_debit in
2254                         (
2255                                 SELECT id
2256                                 FROM acq.fund_debit
2257                                 WHERE
2258                                 fund = roll_fund.old_fund
2259                                 AND encumbrance
2260                         );
2261                         --
2262                         -- Move encumbrance debits from the old fund to the new fund
2263                         --
2264                         UPDATE acq.fund_debit
2265                         SET fund = new_fund
2266                         wHERE
2267                                 fund = roll_fund.old_fund
2268                                 AND encumbrance;
2269                 END IF;
2270                 --
2271                 -- Mark old fund as inactive, now that we've closed it
2272                 --
2273                 UPDATE acq.fund
2274                 SET active = FALSE
2275                 WHERE id = roll_fund.old_fund;
2276         END LOOP;
2277 END;
2278 $$ LANGUAGE plpgsql;
2279
2280 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
2281     SELECT  funding_source,
2282             SUM(amount) AS amount
2283       FROM  acq.funding_source_credit
2284       GROUP BY 1;
2285
2286 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
2287     SELECT  funding_source,
2288             SUM(a.amount)::NUMERIC(100,2) AS amount
2289     FROM  acq.fund_allocation a
2290     GROUP BY 1;
2291
2292 CREATE OR REPLACE VIEW acq.funding_source_balance AS
2293     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
2294             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
2295       FROM  acq.funding_source_credit_total c
2296             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
2297       GROUP BY 1;
2298
2299 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
2300     SELECT  fund,
2301             SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
2302     FROM acq.fund_allocation a
2303          JOIN acq.fund f ON (a.fund = f.id)
2304          JOIN acq.funding_source s ON (a.funding_source = s.id)
2305     GROUP BY 1;
2306
2307 CREATE OR REPLACE VIEW acq.fund_debit_total AS
2308     SELECT  fund.id AS fund,
2309             fund_debit.encumbrance AS encumbrance,
2310                         SUM( COALESCE( fund_debit.amount, 0 ) ) AS amount
2311       FROM acq.fund AS fund
2312             LEFT JOIN acq.fund_debit AS fund_debit
2313                 ON ( fund.id = fund_debit.fund )
2314       GROUP BY 1,2;
2315
2316 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
2317     SELECT  fund,
2318             SUM(amount) AS amount
2319       FROM  acq.fund_debit_total
2320       WHERE encumbrance IS TRUE
2321       GROUP BY 1;
2322
2323 CREATE OR REPLACE VIEW acq.fund_spent_total AS
2324     SELECT  fund,
2325             SUM(amount) AS amount
2326       FROM  acq.fund_debit_total
2327       WHERE encumbrance IS FALSE
2328       GROUP BY 1;
2329
2330 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
2331     SELECT  c.fund,
2332             c.amount - COALESCE(d.amount,0.0) AS amount
2333       FROM  acq.fund_allocation_total c
2334             LEFT JOIN acq.fund_debit_total d USING (fund);
2335
2336 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
2337     SELECT  c.fund,
2338             c.amount - COALESCE(d.amount,0.0) AS amount
2339       FROM  acq.fund_allocation_total c
2340             LEFT JOIN acq.fund_spent_total d USING (fund);
2341
2342 COMMIT;
2343
2344
2345
2346