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