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