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