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