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