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

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