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

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