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

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