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