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