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

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