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

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