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