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

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