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