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

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