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

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