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 TABLE acq.invoice_item_type (
 846     code    TEXT    PRIMARY KEY,
 847     name    TEXT    NOT NULL,  -- i18n-ize
 848         prorate BOOL    NOT NULL DEFAULT FALSE
 849 );
 850
 851 CREATE TABLE acq.po_item (
 852         id              SERIAL      PRIMARY KEY,
 853         purchase_order  INT         REFERENCES acq.purchase_order (id)
 854                                     ON UPDATE CASCADE ON DELETE SET NULL
 855                                     DEFERRABLE INITIALLY DEFERRED,
 856         fund_debit      INT         REFERENCES acq.fund_debit (id)
 857                                     DEFERRABLE INITIALLY DEFERRED,
 858         inv_item_type   TEXT        NOT NULL
 859                                     REFERENCES acq.invoice_item_type (code)
 860                                     DEFERRABLE INITIALLY DEFERRED,
 861         title           TEXT,
 862         author          TEXT,
 863         note            TEXT,
 864         estimated_cost  NUMERIC(8,2),
 865         fund            INT         REFERENCES acq.fund (id)
 866                                     DEFERRABLE INITIALLY DEFERRED,
 867     target          BIGINT
 868 );
 869
 870 CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc
 871     id              SERIAL      PRIMARY KEY,
 872     invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE,
 873     purchase_order  INT         REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
 874     fund_debit      INT         REFERENCES acq.fund_debit (id),
 875     inv_item_type   TEXT        NOT NULL REFERENCES acq.invoice_item_type (code),
 876     title           TEXT,
 877     author          TEXT,
 878     note            TEXT,
 879     cost_billed     NUMERIC(8,2),
 880     actual_cost     NUMERIC(8,2),
 881         fund            INT         REFERENCES acq.fund (id)
 882                                     DEFERRABLE INITIALLY DEFERRED,
 883         amount_paid     NUMERIC (8,2),
 884         po_item         INT         REFERENCES acq.po_item (id)
 885                                     DEFERRABLE INITIALLY DEFERRED,
 886     target          BIGINT
 887 );
 888
 889 -- Patron requests
 890 CREATE TABLE acq.user_request_type (
 891     id      SERIAL  PRIMARY KEY,
 892     label   TEXT    NOT NULL UNIQUE -- i18n-ize
 893 );
 894
 895 INSERT INTO acq.user_request_type (id,label) VALUES (1, oils_i18n_gettext('1', 'Books', 'aurt', 'label'));
 896 INSERT INTO acq.user_request_type (id,label) VALUES (2, oils_i18n_gettext('2', 'Journal/Magazine & Newspaper Articles', 'aurt', 'label'));
 897 INSERT INTO acq.user_request_type (id,label) VALUES (3, oils_i18n_gettext('3', 'Audiobooks', 'aurt', 'label'));
 898 INSERT INTO acq.user_request_type (id,label) VALUES (4, oils_i18n_gettext('4', 'Music', 'aurt', 'label'));
 899 INSERT INTO acq.user_request_type (id,label) VALUES (5, oils_i18n_gettext('5', 'DVDs', 'aurt', 'label'));
 900
 901 SELECT SETVAL('acq.user_request_type_id_seq'::TEXT, 6);
 902
 903 CREATE TABLE acq.user_request (
 904     id                  SERIAL  PRIMARY KEY,
 905     usr                 INT     NOT NULL REFERENCES actor.usr (id), -- requesting user
 906     hold                BOOL    NOT NULL DEFAULT TRUE,
 907
 908     pickup_lib          INT     NOT NULL REFERENCES actor.org_unit (id), -- pickup lib
 909     holdable_formats    TEXT,           -- nullable, for use in hold creation
 910     phone_notify        TEXT,
 911     email_notify        BOOL    NOT NULL DEFAULT TRUE,
 912     lineitem            INT     REFERENCES acq.lineitem (id) ON DELETE CASCADE,
 913     eg_bib              BIGINT  REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
 914     request_date        TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- when they requested it
 915     need_before         TIMESTAMPTZ,    -- don't create holds after this
 916     max_fee             TEXT,
 917
 918     request_type        INT     NOT NULL REFERENCES acq.user_request_type (id),
 919     isxn                TEXT,
 920     title               TEXT,
 921     volume              TEXT,
 922     author              TEXT,
 923     article_title       TEXT,
 924     article_pages       TEXT,
 925     publisher           TEXT,
 926     location            TEXT,
 927     pubdate             TEXT,
 928     mentioned           TEXT,
 929     other_info          TEXT,
 930         cancel_reason       INT    REFERENCES acq.cancel_reason( id )
 931                                    DEFERRABLE INITIALLY DEFERRED
 932 );
 933
 934
 935 -- Functions
 936
 937 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 938 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 939 DECLARE
 940     counter INT;
 941     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 942 BEGIN
 943
 944     SELECT  COUNT(*) INTO counter
 945       FROM  oils_xpath_table(
 946                 'id',
 947                 'marc',
 948                 'acq.lineitem',
 949                 '//*[@tag="' || tag || '"]',
 950                 'id=' || lineitem
 951             ) as t(i int,c text);
 952
 953     FOR i IN 1 .. counter LOOP
 954         FOR lida IN
 955             SELECT  *
 956               FROM  (   SELECT  id,i,t,v
 957                           FROM  oils_xpath_table(
 958                                     'id',
 959                                     'marc',
 960                                     'acq.lineitem',
 961                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 962                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 963                                     'id=' || lineitem
 964                                 ) as t(id int,t text,v text)
 965                     )x
 966         LOOP
 967             RETURN NEXT lida;
 968         END LOOP;
 969     END LOOP;
 970
 971     RETURN;
 972 END;
 973 $$ LANGUAGE PLPGSQL;
 974
 975 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 976 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 977 DECLARE
 978     prov_i  INT;
 979     tag_t   TEXT;
 980     lida    acq.flat_lineitem_detail%ROWTYPE;
 981 BEGIN
 982     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 983     IF NOT FOUND THEN RETURN; END IF;
 984
 985     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 986     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 987
 988     FOR lida IN
 989         SELECT  lineitem_i,
 990                 h.holding,
 991                 a.name,
 992                 h.data
 993           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 994                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 995           WHERE a.provider = prov_i
 996     LOOP
 997         RETURN NEXT lida;
 998     END LOOP;
 999
1000     RETURN;
1001 END;
1002 $$ LANGUAGE PLPGSQL;
1003
1004 -- select * from acq.extract_provider_holding_data(699);
1005
1006 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
1007         SELECT extract_marc_field('acq.lineitem', $1, $2, $3);
1008 $$ LANGUAGE SQL;
1009
1010 /*
1011 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
1012         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
1013 $$ LANGUAGE SQL;
1014
1015 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
1016         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
1017 $$ LANGUAGE SQL;
1018 */
1019 -- For example:
1020 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
1021
1022 /*
1023 Suggested vendor fields:
1024         vendor_price
1025         vendor_currency
1026         vendor_avail
1027         vendor_po
1028         vendor_identifier
1029 */
1030
1031 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $function$
1032 DECLARE
1033         value           TEXT;
1034         atype           TEXT;
1035         prov            INT;
1036         pos             INT;
1037         adef            RECORD;
1038         xpath_string    TEXT;
1039 BEGIN
1040         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
1041
1042                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
1043
1044                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
1045                         IF (atype = 'lineitem_provider_attr_definition') THEN
1046                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
1047                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
1048                         END IF;
1049
1050                         IF (atype = 'lineitem_provider_attr_definition') THEN
1051                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
1052                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
1053                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
1054                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
1055                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
1056                         END IF;
1057
1058             xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,'');
1059
1060             IF (adef.code = 'title' OR adef.code = 'author') THEN
1061                 -- title and author should not be split
1062                 -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use
1063                 -- string-join in the xpath and remove this special case
1064                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
1065                         IF (value IS NOT NULL AND value <> '') THEN
1066                                     INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
1067                                     VALUES (NEW.id, adef.id, atype, adef.code, value);
1068                 END IF;
1069             ELSE
1070                 pos := 1;
1071
1072                 LOOP
1073                             SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
1074
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                     ELSE
1079                         EXIT;
1080                                 END IF;
1081
1082                     pos := pos + 1;
1083                 END LOOP;
1084             END IF;
1085
1086                 END IF;
1087
1088         END LOOP;
1089
1090         RETURN NULL;
1091 END;
1092 $function$ LANGUAGE PLPGSQL;
1093
1094 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
1095 BEGIN
1096         IF TG_OP = 'UPDATE' THEN
1097                 DELETE FROM acq.lineitem_attr
1098                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
1099                 RETURN NEW;
1100         ELSE
1101                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
1102                 RETURN OLD;
1103         END IF;
1104 END;
1105 $$ LANGUAGE PLPGSQL;
1106
1107 CREATE TRIGGER cleanup_lineitem_trigger
1108         BEFORE UPDATE OR DELETE ON acq.lineitem
1109         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
1110
1111 CREATE TRIGGER ingest_lineitem_trigger
1112         AFTER INSERT OR UPDATE ON acq.lineitem
1113         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
1114
1115 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
1116 DECLARE
1117     rat NUMERIC;
1118 BEGIN
1119     IF from_ex = to_ex THEN
1120         RETURN 1.0;
1121     END IF;
1122
1123     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
1124
1125     IF FOUND THEN
1126         RETURN rat;
1127     ELSE
1128         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
1129         IF FOUND THEN
1130             RETURN 1.0/rat;
1131         END IF;
1132     END IF;
1133
1134     RETURN NULL;
1135
1136 END;
1137 $$ LANGUAGE PLPGSQL;
1138
1139 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
1140     SELECT $3 * acq.exchange_ratio($1, $2);
1141 $$ LANGUAGE SQL;
1142
1143 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
1144 /*
1145         Examine the acq.fiscal_year table, comparing successive years.
1146         Report any inconsistencies, i.e. years that overlap, have gaps
1147     between them, or are out of sequence.
1148 */
1149 RETURNS SETOF RECORD AS $$
1150 DECLARE
1151         first_row  BOOLEAN;
1152         curr_year  RECORD;
1153         prev_year  RECORD;
1154         return_rec RECORD;
1155 BEGIN
1156         first_row := true;
1157         FOR curr_year in
1158                 SELECT
1159                         id,
1160                         calendar,
1161                         year,
1162                         year_begin,
1163                         year_end
1164                 FROM
1165                         acq.fiscal_year
1166                 ORDER BY
1167                         calendar,
1168                         year_begin
1169         LOOP
1170                 --
1171                 IF first_row THEN
1172                         first_row := FALSE;
1173                 ELSIF curr_year.calendar    = prev_year.calendar THEN
1174                         IF curr_year.year_begin > prev_year.year_end THEN
1175                                 -- This ugly kludge works around the fact that older
1176                                 -- versions of PostgreSQL don't support RETURN QUERY SELECT
1177                                 FOR return_rec IN SELECT
1178                                         prev_year.id,
1179                                         prev_year.year,
1180                                         'Gap between fiscal years'::TEXT
1181                                 LOOP
1182                                         RETURN NEXT return_rec;
1183                                 END LOOP;
1184                         ELSIF curr_year.year_begin < prev_year.year_end THEN
1185                                 FOR return_rec IN SELECT
1186                                         prev_year.id,
1187                                         prev_year.year,
1188                                         'Overlapping fiscal years'::TEXT
1189                                 LOOP
1190                                         RETURN NEXT return_rec;
1191                                 END LOOP;
1192                         ELSIF curr_year.year < prev_year.year THEN
1193                                 FOR return_rec IN SELECT
1194                                         prev_year.id,
1195                                         prev_year.year,
1196                                         'Fiscal years out of order'::TEXT
1197                                 LOOP
1198                                         RETURN NEXT return_rec;
1199                                 END LOOP;
1200                         END IF;
1201                 END IF;
1202                 --
1203                 prev_year := curr_year;
1204         END LOOP;
1205         --
1206         RETURN;
1207 END;
1208 $$ LANGUAGE plpgsql;
1209
1210 CREATE OR REPLACE FUNCTION acq.transfer_fund(
1211         old_fund   IN INT,
1212         old_amount IN NUMERIC,     -- in currency of old fund
1213         new_fund   IN INT,
1214         new_amount IN NUMERIC,     -- in currency of new fund
1215         user_id    IN INT,
1216         xfer_note  IN TEXT         -- to be recorded in acq.fund_transfer
1217         -- ,funding_source_in IN INT  -- if user wants to specify a funding source (see notes)
1218 ) RETURNS VOID AS $$
1219 /* -------------------------------------------------------------------------------
1220
1221 Function to transfer money from one fund to another.
1222
1223 A transfer is represented as a pair of entries in acq.fund_allocation, with a
1224 negative amount for the old (losing) fund and a positive amount for the new
1225 (gaining) fund.  In some cases there may be more than one such pair of entries
1226 in order to pull the money from different funding sources, or more specifically
1227 from different funding source credits.  For each such pair there is also an
1228 entry in acq.fund_transfer.
1229
1230 Since funding_source is a non-nullable column in acq.fund_allocation, we must
1231 choose a funding source for the transferred money to come from.  This choice
1232 must meet two constraints, so far as possible:
1233
1234 1. The amount transferred from a given funding source must not exceed the
1235 amount allocated to the old fund by the funding source.  To that end we
1236 compare the amount being transferred to the amount allocated.
1237
1238 2. We shouldn't transfer money that has already been spent or encumbered, as
1239 defined by the funding attribution process.  We attribute expenses to the
1240 oldest funding source credits first.  In order to avoid transferring that
1241 attributed money, we reverse the priority, transferring from the newest funding
1242 source credits first.  There can be no guarantee that this approach will
1243 avoid overcommitting a fund, but no other approach can do any better.
1244
1245 In this context the age of a funding source credit is defined by the
1246 deadline_date for credits with deadline_dates, and by the effective_date for
1247 credits without deadline_dates, with the proviso that credits with deadline_dates
1248 are all considered "older" than those without.
1249
1250 ----------
1251
1252 In the signature for this function, there is one last parameter commented out,
1253 named "funding_source_in".  Correspondingly, the WHERE clause for the query
1254 driving the main loop has an OR clause commented out, which references the
1255 funding_source_in parameter.
1256
1257 If these lines are uncommented, this function will allow the user optionally to
1258 restrict a fund transfer to a specified funding source.  If the source
1259 parameter is left NULL, then there will be no such restriction.
1260
1261 ------------------------------------------------------------------------------- */
1262 DECLARE
1263         same_currency      BOOLEAN;
1264         currency_ratio     NUMERIC;
1265         old_fund_currency  TEXT;
1266         old_remaining      NUMERIC;  -- in currency of old fund
1267         new_fund_currency  TEXT;
1268         new_fund_active    BOOLEAN;
1269         new_remaining      NUMERIC;  -- in currency of new fund
1270         curr_old_amt       NUMERIC;  -- in currency of old fund
1271         curr_new_amt       NUMERIC;  -- in currency of new fund
1272         source_addition    NUMERIC;  -- in currency of funding source
1273         source_deduction   NUMERIC;  -- in currency of funding source
1274         orig_allocated_amt NUMERIC;  -- in currency of funding source
1275         allocated_amt      NUMERIC;  -- in currency of fund
1276         source             RECORD;
1277 BEGIN
1278         --
1279         -- Sanity checks
1280         --
1281         IF old_fund IS NULL THEN
1282                 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
1283         END IF;
1284         --
1285         IF old_amount IS NULL THEN
1286                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
1287         END IF;
1288         --
1289         -- The new fund and its amount must be both NULL or both not NULL.
1290         --
1291         IF new_fund IS NOT NULL AND new_amount IS NULL THEN
1292                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
1293         END IF;
1294         --
1295         IF new_fund IS NULL AND new_amount IS NOT NULL THEN
1296                 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
1297         END IF;
1298         --
1299         IF user_id IS NULL THEN
1300                 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
1301         END IF;
1302         --
1303         -- Initialize the amounts to be transferred, each denominated
1304         -- in the currency of its respective fund.  They will be
1305         -- reduced on each iteration of the loop.
1306         --
1307         old_remaining := old_amount;
1308         new_remaining := new_amount;
1309         --
1310         -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
1311         --      old_amount, old_fund, new_amount, new_fund;
1312         --
1313         -- Get the currency types of the old and new funds.
1314         --
1315         SELECT
1316                 currency_type
1317         INTO
1318                 old_fund_currency
1319         FROM
1320                 acq.fund
1321         WHERE
1322                 id = old_fund;
1323         --
1324         IF old_fund_currency IS NULL THEN
1325                 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
1326         END IF;
1327         --
1328         IF new_fund IS NOT NULL THEN
1329                 SELECT
1330                         currency_type,
1331                         active
1332                 INTO
1333                         new_fund_currency,
1334                         new_fund_active
1335                 FROM
1336                         acq.fund
1337                 WHERE
1338                         id = new_fund;
1339                 --
1340                 IF new_fund_currency IS NULL THEN
1341                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
1342                 ELSIF NOT new_fund_active THEN
1343                         --
1344                         -- No point in putting money into a fund from whence you can't spend it
1345                         --
1346                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
1347                 END IF;
1348                 --
1349                 IF new_amount = old_amount THEN
1350                         same_currency := true;
1351                         currency_ratio := 1;
1352                 ELSE
1353                         --
1354                         -- We'll have to translate currency between funds.  We presume that
1355                         -- the calling code has already applied an appropriate exchange rate,
1356                         -- so we'll apply the same conversion to each sub-transfer.
1357                         --
1358                         same_currency := false;
1359                         currency_ratio := new_amount / old_amount;
1360                 END IF;
1361         END IF;
1362         --
1363         -- Identify the funding source(s) from which we want to transfer the money.
1364         -- The principle is that we want to transfer the newest money first, because
1365         -- we spend the oldest money first.  The priority for spending is defined
1366         -- by a sort of the view acq.ordered_funding_source_credit.
1367         --
1368         FOR source in
1369                 SELECT
1370                         ofsc.id,
1371                         ofsc.funding_source,
1372                         ofsc.amount,
1373                         ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
1374                                 AS converted_amt,
1375                         fs.currency_type
1376                 FROM
1377                         acq.ordered_funding_source_credit AS ofsc,
1378                         acq.funding_source fs
1379                 WHERE
1380                         ofsc.funding_source = fs.id
1381                         and ofsc.funding_source IN
1382                         (
1383                                 SELECT funding_source
1384                                 FROM acq.fund_allocation
1385                                 WHERE fund = old_fund
1386                         )
1387                         -- and
1388                         -- (
1389                         --      ofsc.funding_source = funding_source_in
1390                         --      OR funding_source_in IS NULL
1391                         -- )
1392                 ORDER BY
1393                         ofsc.sort_priority desc,
1394                         ofsc.sort_date desc,
1395                         ofsc.id desc
1396         LOOP
1397                 --
1398                 -- Determine how much money the old fund got from this funding source,
1399                 -- denominated in the currency types of the source and of the fund.
1400                 -- This result may reflect transfers from previous iterations.
1401                 --
1402                 SELECT
1403                         COALESCE( sum( amount ), 0 ),
1404                         COALESCE( sum( amount )
1405                                 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
1406                 INTO
1407                         orig_allocated_amt,     -- in currency of the source
1408                         allocated_amt           -- in currency of the old fund
1409                 FROM
1410                         acq.fund_allocation
1411                 WHERE
1412                         fund = old_fund
1413                         and funding_source = source.funding_source;
1414                 --
1415                 -- Determine how much to transfer from this credit, in the currency
1416                 -- of the fund.   Begin with the amount remaining to be attributed:
1417                 --
1418                 curr_old_amt := old_remaining;
1419                 --
1420                 -- Can't attribute more than was allocated from the fund:
1421                 --
1422                 IF curr_old_amt > allocated_amt THEN
1423                         curr_old_amt := allocated_amt;
1424                 END IF;
1425                 --
1426                 -- Can't attribute more than the amount of the current credit:
1427                 --
1428                 IF curr_old_amt > source.converted_amt THEN
1429                         curr_old_amt := source.converted_amt;
1430                 END IF;
1431                 --
1432                 curr_old_amt := trunc( curr_old_amt, 2 );
1433                 --
1434                 old_remaining := old_remaining - curr_old_amt;
1435                 --
1436                 -- Determine the amount to be deducted, if any,
1437                 -- from the old allocation.
1438                 --
1439                 IF old_remaining > 0 THEN
1440                         --
1441                         -- In this case we're using the whole allocation, so use that
1442                         -- amount directly instead of applying a currency translation
1443                         -- and thereby inviting round-off errors.
1444                         --
1445                         source_deduction := - orig_allocated_amt;
1446                 ELSE
1447                         source_deduction := trunc(
1448                                 ( - curr_old_amt ) *
1449                                         acq.exchange_ratio( old_fund_currency, source.currency_type ),
1450                                 2 );
1451                 END IF;
1452                 --
1453                 IF source_deduction <> 0 THEN
1454                         --
1455                         -- Insert negative allocation for old fund in fund_allocation,
1456                         -- converted into the currency of the funding source
1457                         --
1458                         INSERT INTO acq.fund_allocation (
1459                                 funding_source,
1460                                 fund,
1461                                 amount,
1462                                 allocator,
1463                                 note
1464                         ) VALUES (
1465                                 source.funding_source,
1466                                 old_fund,
1467                                 source_deduction,
1468                                 user_id,
1469                                 'Transfer to fund ' || new_fund
1470                         );
1471                 END IF;
1472                 --
1473                 IF new_fund IS NOT NULL THEN
1474                         --
1475                         -- Determine how much to add to the new fund, in
1476                         -- its currency, and how much remains to be added:
1477                         --
1478                         IF same_currency THEN
1479                                 curr_new_amt := curr_old_amt;
1480                         ELSE
1481                                 IF old_remaining = 0 THEN
1482                                         --
1483                                         -- This is the last iteration, so nothing should be left
1484                                         --
1485                                         curr_new_amt := new_remaining;
1486                                         new_remaining := 0;
1487                                 ELSE
1488                                         curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
1489                                         new_remaining := new_remaining - curr_new_amt;
1490                                 END IF;
1491                         END IF;
1492                         --
1493                         -- Determine how much to add, if any,
1494                         -- to the new fund's allocation.
1495                         --
1496                         IF old_remaining > 0 THEN
1497                                 --
1498                                 -- In this case we're using the whole allocation, so use that amount
1499                                 -- amount directly instead of applying a currency translation and
1500                                 -- thereby inviting round-off errors.
1501                                 --
1502                                 source_addition := orig_allocated_amt;
1503                         ELSIF source.currency_type = old_fund_currency THEN
1504                                 --
1505                                 -- In this case we don't need a round trip currency translation,
1506                                 -- thereby inviting round-off errors:
1507                                 --
1508                                 source_addition := curr_old_amt;
1509                         ELSE
1510                                 source_addition := trunc(
1511                                         curr_new_amt *
1512                                                 acq.exchange_ratio( new_fund_currency, source.currency_type ),
1513                                         2 );
1514                         END IF;
1515                         --
1516                         IF source_addition <> 0 THEN
1517                                 --
1518                                 -- Insert positive allocation for new fund in fund_allocation,
1519                                 -- converted to the currency of the founding source
1520                                 --
1521                                 INSERT INTO acq.fund_allocation (
1522                                         funding_source,
1523                                         fund,
1524                                         amount,
1525                                         allocator,
1526                                         note
1527                                 ) VALUES (
1528                                         source.funding_source,
1529                                         new_fund,
1530                                         source_addition,
1531                                         user_id,
1532                                         'Transfer from fund ' || old_fund
1533                                 );
1534                         END IF;
1535                 END IF;
1536                 --
1537                 IF trunc( curr_old_amt, 2 ) <> 0
1538                 OR trunc( curr_new_amt, 2 ) <> 0 THEN
1539                         --
1540                         -- Insert row in fund_transfer, using amounts in the currency of the funds
1541                         --
1542                         INSERT INTO acq.fund_transfer (
1543                                 src_fund,
1544                                 src_amount,
1545                                 dest_fund,
1546                                 dest_amount,
1547                                 transfer_user,
1548                                 note,
1549                                 funding_source_credit
1550                         ) VALUES (
1551                                 old_fund,
1552                                 trunc( curr_old_amt, 2 ),
1553                                 new_fund,
1554                                 trunc( curr_new_amt, 2 ),
1555                                 user_id,
1556                                 xfer_note,
1557                                 source.id
1558                         );
1559                 END IF;
1560                 --
1561                 if old_remaining <= 0 THEN
1562                         EXIT;                   -- Nothing more to be transferred
1563                 END IF;
1564         END LOOP;
1565 END;
1566 $$ LANGUAGE plpgsql;
1567
1568 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
1569 /*
1570 Function to attribute expenditures and encumbrances to funding source credits,
1571 and thereby to funding sources.
1572
1573 Read the debits in chonological order, attributing each one to one or
1574 more funding source credits.  Constraints:
1575
1576 1. Don't attribute more to a credit than the amount of the credit.
1577
1578 2. For a given fund, don't attribute more to a funding source than the
1579 source has allocated to that fund.
1580
1581 3. Attribute debits to credits with deadlines before attributing them to
1582 credits without deadlines.  Otherwise attribute to the earliest credits
1583 first, based on the deadline date when present, or on the effective date
1584 when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
1585 This ordering is defined by an ORDER BY clause on the view
1586 acq.ordered_funding_source_credit.
1587
1588 Start by truncating the table acq.debit_attribution.  Then insert a row
1589 into that table for each attribution.  If a debit cannot be fully
1590 attributed, insert a row for the unattributable balance, with the
1591 funding_source_credit and credit_amount columns NULL.
1592 */
1593 DECLARE
1594         curr_fund_source_bal RECORD;
1595         seqno                INT;     -- sequence num for credits applicable to a fund
1596         fund_credit          RECORD;  -- current row in temp t_fund_credit table
1597         fc                   RECORD;  -- used for loading t_fund_credit table
1598         sc                   RECORD;  -- used for loading t_fund_credit table
1599         --
1600         -- Used exclusively in the main loop:
1601         --
1602         deb                 RECORD;   -- current row from acq.fund_debit table
1603         curr_credit_bal     RECORD;   -- current row from temp t_credit table
1604         debit_balance       NUMERIC;  -- amount left to attribute for current debit
1605         conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
1606         attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
1607         conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
1608         conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
1609         conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
1610         attrib_count        INT;      -- populates id of acq.debit_attribution
1611 BEGIN
1612         --
1613         -- Load a temporary table.  For each combination of fund and funding source,
1614         -- load an entry with the total amount allocated to that fund by that source.
1615         -- This sum may reflect transfers as well as original allocations.  We will
1616         -- reduce this balance whenever we attribute debits to it.
1617         --
1618         CREATE TEMP TABLE t_fund_source_bal
1619         ON COMMIT DROP AS
1620                 SELECT
1621                         fund AS fund,
1622                         funding_source AS source,
1623                         sum( amount ) AS balance
1624                 FROM
1625                         acq.fund_allocation
1626                 GROUP BY
1627                         fund,
1628                         funding_source
1629                 HAVING
1630                         sum( amount ) > 0;
1631         --
1632         CREATE INDEX t_fund_source_bal_idx
1633                 ON t_fund_source_bal( fund, source );
1634         -------------------------------------------------------------------------------
1635         --
1636         -- Load another temporary table.  For each fund, load zero or more
1637         -- funding source credits from which that fund can get money.
1638         --
1639         CREATE TEMP TABLE t_fund_credit (
1640                 fund        INT,
1641                 seq         INT,
1642                 credit      INT
1643         ) ON COMMIT DROP;
1644         --
1645         FOR fc IN
1646                 SELECT DISTINCT fund
1647                 FROM acq.fund_allocation
1648                 ORDER BY fund
1649         LOOP                  -- Loop over the funds
1650                 seqno := 1;
1651                 FOR sc IN
1652                         SELECT
1653                                 ofsc.id
1654                         FROM
1655                                 acq.ordered_funding_source_credit AS ofsc
1656                         WHERE
1657                                 ofsc.funding_source IN
1658                                 (
1659                                         SELECT funding_source
1660                                         FROM acq.fund_allocation
1661                                         WHERE fund = fc.fund
1662                                 )
1663                 ORDER BY
1664                     ofsc.sort_priority,
1665                     ofsc.sort_date,
1666                     ofsc.id
1667                 LOOP                        -- Add each credit to the list
1668                         INSERT INTO t_fund_credit (
1669                                 fund,
1670                                 seq,
1671                                 credit
1672                         ) VALUES (
1673                                 fc.fund,
1674                                 seqno,
1675                                 sc.id
1676                         );
1677                         --RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
1678                         seqno := seqno + 1;
1679                 END LOOP;     -- Loop over credits for a given fund
1680         END LOOP;         -- Loop over funds
1681         --
1682         CREATE INDEX t_fund_credit_idx
1683                 ON t_fund_credit( fund, seq );
1684         -------------------------------------------------------------------------------
1685         --
1686         -- Load yet another temporary table.  This one is a list of funding source
1687         -- credits, with their balances.  We shall reduce those balances as we
1688         -- attribute debits to them.
1689         --
1690         CREATE TEMP TABLE t_credit
1691         ON COMMIT DROP AS
1692         SELECT
1693             fsc.id AS credit,
1694             fsc.funding_source AS source,
1695             fsc.amount AS balance,
1696             fs.currency_type AS currency_type
1697         FROM
1698             acq.funding_source_credit AS fsc,
1699             acq.funding_source fs
1700         WHERE
1701             fsc.funding_source = fs.id
1702                         AND fsc.amount > 0;
1703         --
1704         CREATE INDEX t_credit_idx
1705                 ON t_credit( credit );
1706         --
1707         -------------------------------------------------------------------------------
1708         --
1709         -- Now that we have loaded the lookup tables: loop through the debits,
1710         -- attributing each one to one or more funding source credits.
1711         --
1712         truncate table acq.debit_attribution;
1713         --
1714         attrib_count := 0;
1715         FOR deb in
1716                 SELECT
1717                         fd.id,
1718                         fd.fund,
1719                         fd.amount,
1720                         f.currency_type,
1721                         fd.encumbrance
1722                 FROM
1723                         acq.fund_debit fd,
1724                         acq.fund f
1725                 WHERE
1726                         fd.fund = f.id
1727                 ORDER BY
1728                         fd.id
1729         LOOP
1730                 --RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
1731                 --
1732                 debit_balance := deb.amount;
1733                 --
1734                 -- Loop over the funding source credits that are eligible
1735                 -- to pay for this debit
1736                 --
1737                 FOR fund_credit IN
1738                         SELECT
1739                                 credit
1740                         FROM
1741                                 t_fund_credit
1742                         WHERE
1743                                 fund = deb.fund
1744                         ORDER BY
1745                                 seq
1746                 LOOP
1747                         --RAISE NOTICE '   Examining credit %', fund_credit.credit;
1748                         --
1749                         -- Look up the balance for this credit.  If it's zero, then
1750                         -- it's not useful, so treat it as if you didn't find it.
1751                         -- (Actually there shouldn't be any zero balances in the table,
1752                         -- but we check just to make sure.)
1753                         --
1754                         SELECT *
1755                         INTO curr_credit_bal
1756                         FROM t_credit
1757                         WHERE
1758                                 credit = fund_credit.credit
1759                                 AND balance > 0;
1760                         --
1761                         IF curr_credit_bal IS NULL THEN
1762                                 --
1763                                 -- This credit is exhausted; try the next one.
1764                                 --
1765                                 CONTINUE;
1766                         END IF;
1767                         --
1768                         --
1769                         -- At this point we have an applicable credit with some money left.
1770                         -- Now see if the relevant funding_source has any money left.
1771                         --
1772                         -- Look up the balance of the allocation for this combination of
1773                         -- fund and source.  If you find such an entry, but it has a zero
1774                         -- balance, then it's not useful, so treat it as unfound.
1775                         -- (Actually there shouldn't be any zero balances in the table,
1776                         -- but we check just to make sure.)
1777                         --
1778                         SELECT *
1779                         INTO curr_fund_source_bal
1780                         FROM t_fund_source_bal
1781                         WHERE
1782                                 fund = deb.fund
1783                                 AND source = curr_credit_bal.source
1784                                 AND balance > 0;
1785                         --
1786                         IF curr_fund_source_bal IS NULL THEN
1787                                 --
1788                                 -- This fund/source doesn't exist or is already exhausted,
1789                                 -- so we can't use this credit.  Go on to the next one.
1790                                 --
1791                                 CONTINUE;
1792                         END IF;
1793                         --
1794                         -- Convert the available balances to the currency of the fund
1795                         --
1796                         conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
1797                                 curr_credit_bal.currency_type, deb.currency_type );
1798                         conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
1799                                 curr_credit_bal.currency_type, deb.currency_type );
1800                         --
1801                         -- Determine how much we can attribute to this credit: the minimum
1802                         -- of the debit amount, the fund/source balance, and the
1803                         -- credit balance
1804                         --
1805                         --RAISE NOTICE '   deb bal %', debit_balance;
1806                         --RAISE NOTICE '      source % balance %', curr_credit_bal.source, conv_alloc_balance;
1807                         --RAISE NOTICE '      credit % balance %', curr_credit_bal.credit, conv_cred_balance;
1808                         --
1809                         conv_attr_amount := NULL;
1810                         attr_amount := debit_balance;
1811                         --
1812                         IF attr_amount > conv_alloc_balance THEN
1813                                 attr_amount := conv_alloc_balance;
1814                                 conv_attr_amount := curr_fund_source_bal.balance;
1815                         END IF;
1816                         IF attr_amount > conv_cred_balance THEN
1817                                 attr_amount := conv_cred_balance;
1818                                 conv_attr_amount := curr_credit_bal.balance;
1819                         END IF;
1820                         --
1821                         -- If we're attributing all of one of the balances, then that's how
1822                         -- much we will deduct from the balances, and we already captured
1823                         -- that amount above.  Otherwise we must convert the amount of the
1824                         -- attribution from the currency of the fund back to the currency of
1825                         -- the funding source.
1826                         --
1827                         IF conv_attr_amount IS NULL THEN
1828                                 conv_attr_amount := attr_amount * acq.exchange_ratio(
1829                                         deb.currency_type, curr_credit_bal.currency_type );
1830                         END IF;
1831                         --
1832                         -- Insert a row to record the attribution
1833                         --
1834                         attrib_count := attrib_count + 1;
1835                         INSERT INTO acq.debit_attribution (
1836                                 id,
1837                                 fund_debit,
1838                                 debit_amount,
1839                                 funding_source_credit,
1840                                 credit_amount
1841                         ) VALUES (
1842                                 attrib_count,
1843                                 deb.id,
1844                                 attr_amount,
1845                                 curr_credit_bal.credit,
1846                                 conv_attr_amount
1847                         );
1848                         --
1849                         -- Subtract the attributed amount from the various balances
1850                         --
1851                         debit_balance := debit_balance - attr_amount;
1852                         curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
1853                         --
1854                         IF curr_fund_source_bal.balance <= 0 THEN
1855                                 --
1856                                 -- This allocation is exhausted.  Delete it so
1857                                 -- that we don't waste time looking at it again.
1858                                 --
1859                                 DELETE FROM t_fund_source_bal
1860                                 WHERE
1861                                         fund = curr_fund_source_bal.fund
1862                                         AND source = curr_fund_source_bal.source;
1863                         ELSE
1864                                 UPDATE t_fund_source_bal
1865                                 SET balance = balance - conv_attr_amount
1866                                 WHERE
1867                                         fund = curr_fund_source_bal.fund
1868                                         AND source = curr_fund_source_bal.source;
1869                         END IF;
1870                         --
1871                         IF curr_credit_bal.balance <= 0 THEN
1872                                 --
1873                                 -- This funding source credit is exhausted.  Delete it
1874                                 -- so that we don't waste time looking at it again.
1875                                 --
1876                                 --DELETE FROM t_credit
1877                                 --WHERE
1878                                 --      credit = curr_credit_bal.credit;
1879                                 --
1880                                 DELETE FROM t_fund_credit
1881                                 WHERE
1882                                         credit = curr_credit_bal.credit;
1883                         ELSE
1884                                 UPDATE t_credit
1885                                 SET balance = curr_credit_bal.balance
1886                                 WHERE
1887                                         credit = curr_credit_bal.credit;
1888                         END IF;
1889                         --
1890                         -- Are we done with this debit yet?
1891                         --
1892                         IF debit_balance <= 0 THEN
1893                                 EXIT;       -- We've fully attributed this debit; stop looking at credits.
1894                         END IF;
1895                 END LOOP;       -- End loop over credits
1896                 --
1897                 IF debit_balance <> 0 THEN
1898                         --
1899                         -- We weren't able to attribute this debit, or at least not
1900                         -- all of it.  Insert a row for the unattributed balance.
1901                         --
1902                         attrib_count := attrib_count + 1;
1903                         INSERT INTO acq.debit_attribution (
1904                                 id,
1905                                 fund_debit,
1906                                 debit_amount,
1907                                 funding_source_credit,
1908                                 credit_amount
1909                         ) VALUES (
1910                                 attrib_count,
1911                                 deb.id,
1912                                 debit_balance,
1913                                 NULL,
1914                                 NULL
1915                         );
1916                 END IF;
1917         END LOOP;   -- End of loop over debits
1918 END;
1919 $$ LANGUAGE 'plpgsql';
1920
1921 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit(
1922         old_year INTEGER,
1923         user_id INTEGER,
1924         org_unit_id INTEGER
1925 ) RETURNS VOID AS $$
1926 DECLARE
1927 --
1928 new_id      INT;
1929 old_fund    RECORD;
1930 org_found   BOOLEAN;
1931 --
1932 BEGIN
1933         --
1934         -- Sanity checks
1935         --
1936         IF old_year IS NULL THEN
1937                 RAISE EXCEPTION 'Input year argument is NULL';
1938         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1939                 RAISE EXCEPTION 'Input year is out of range';
1940         END IF;
1941         --
1942         IF user_id IS NULL THEN
1943                 RAISE EXCEPTION 'Input user id argument is NULL';
1944         END IF;
1945         --
1946         IF org_unit_id IS NULL THEN
1947                 RAISE EXCEPTION 'Org unit id argument is NULL';
1948         ELSE
1949                 SELECT TRUE INTO org_found
1950                 FROM actor.org_unit
1951                 WHERE id = org_unit_id;
1952                 --
1953                 IF org_found IS NULL THEN
1954                         RAISE EXCEPTION 'Org unit id is invalid';
1955                 END IF;
1956         END IF;
1957         --
1958         -- Loop over the applicable funds
1959         --
1960         FOR old_fund in SELECT * FROM acq.fund
1961         WHERE
1962                 year = old_year
1963                 AND propagate
1964                 AND org = org_unit_id
1965         LOOP
1966                 BEGIN
1967                         INSERT INTO acq.fund (
1968                                 org,
1969                                 name,
1970                                 year,
1971                                 currency_type,
1972                                 code,
1973                                 rollover,
1974                                 propagate,
1975                                 balance_warning_percent,
1976                                 balance_stop_percent
1977                         ) VALUES (
1978                                 old_fund.org,
1979                                 old_fund.name,
1980                                 old_year + 1,
1981                                 old_fund.currency_type,
1982                                 old_fund.code,
1983                                 old_fund.rollover,
1984                                 true,
1985                                 old_fund.balance_warning_percent,
1986                                 old_fund.balance_stop_percent
1987                         )
1988                         RETURNING id INTO new_id;
1989                 EXCEPTION
1990                         WHEN unique_violation THEN
1991                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1992                                 CONTINUE;
1993                 END;
1994                 --RAISE NOTICE 'Propagating fund % to fund %',
1995                 --      old_fund.code, new_id;
1996         END LOOP;
1997 END;
1998 $$ LANGUAGE plpgsql;
1999
2000 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
2001         old_year INTEGER,
2002         user_id INTEGER,
2003         org_unit_id INTEGER
2004 ) RETURNS VOID AS $$
2005 DECLARE
2006 --
2007 new_id      INT;
2008 old_fund    RECORD;
2009 org_found   BOOLEAN;
2010 --
2011 BEGIN
2012         --
2013         -- Sanity checks
2014         --
2015         IF old_year IS NULL THEN
2016                 RAISE EXCEPTION 'Input year argument is NULL';
2017         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
2018                 RAISE EXCEPTION 'Input year is out of range';
2019         END IF;
2020         --
2021         IF user_id IS NULL THEN
2022                 RAISE EXCEPTION 'Input user id argument is NULL';
2023         END IF;
2024         --
2025         IF org_unit_id IS NULL THEN
2026                 RAISE EXCEPTION 'Org unit id argument is NULL';
2027         ELSE
2028                 SELECT TRUE INTO org_found
2029                 FROM actor.org_unit
2030                 WHERE id = org_unit_id;
2031                 --
2032                 IF org_found IS NULL THEN
2033                         RAISE EXCEPTION 'Org unit id is invalid';
2034                 END IF;
2035         END IF;
2036         --
2037         -- Loop over the applicable funds
2038         --
2039         FOR old_fund in SELECT * FROM acq.fund
2040         WHERE
2041                 year = old_year
2042                 AND propagate
2043                 AND org in (
2044                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
2045                 )
2046         LOOP
2047                 BEGIN
2048                         INSERT INTO acq.fund (
2049                                 org,
2050                                 name,
2051                                 year,
2052                                 currency_type,
2053                                 code,
2054                                 rollover,
2055                                 propagate,
2056                                 balance_warning_percent,
2057                                 balance_stop_percent
2058                         ) VALUES (
2059                                 old_fund.org,
2060                                 old_fund.name,
2061                                 old_year + 1,
2062                                 old_fund.currency_type,
2063                                 old_fund.code,
2064                                 old_fund.rollover,
2065                                 true,
2066                                 old_fund.balance_warning_percent,
2067                                 old_fund.balance_stop_percent
2068                         )
2069                         RETURNING id INTO new_id;
2070                 EXCEPTION
2071                         WHEN unique_violation THEN
2072                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
2073                                 CONTINUE;
2074                 END;
2075                 --RAISE NOTICE 'Propagating fund % to fund %',
2076                 --      old_fund.code, new_id;
2077         END LOOP;
2078 END;
2079 $$ LANGUAGE plpgsql;
2080
2081 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
2082         old_year INTEGER,
2083         user_id INTEGER,
2084         org_unit_id INTEGER
2085 ) RETURNS VOID AS $$
2086 DECLARE
2087 --
2088 new_fund    INT;
2089 new_year    INT := old_year + 1;
2090 org_found   BOOL;
2091 xfer_amount NUMERIC;
2092 roll_fund   RECORD;
2093 deb         RECORD;
2094 detail      RECORD;
2095 --
2096 BEGIN
2097         --
2098         -- Sanity checks
2099         --
2100         IF old_year IS NULL THEN
2101                 RAISE EXCEPTION 'Input year argument is NULL';
2102     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
2103         RAISE EXCEPTION 'Input year is out of range';
2104         END IF;
2105         --
2106         IF user_id IS NULL THEN
2107                 RAISE EXCEPTION 'Input user id argument is NULL';
2108         END IF;
2109         --
2110         IF org_unit_id IS NULL THEN
2111                 RAISE EXCEPTION 'Org unit id argument is NULL';
2112         ELSE
2113                 --
2114                 -- Validate the org unit
2115                 --
2116                 SELECT TRUE
2117                 INTO org_found
2118                 FROM actor.org_unit
2119                 WHERE id = org_unit_id;
2120                 --
2121                 IF org_found IS NULL THEN
2122                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
2123                 END IF;
2124         END IF;
2125         --
2126         -- Loop over the propagable funds to identify the details
2127         -- from the old fund plus the id of the new one, if it exists.
2128         --
2129         FOR roll_fund in
2130         SELECT
2131             oldf.id AS old_fund,
2132             oldf.org,
2133             oldf.name,
2134             oldf.currency_type,
2135             oldf.code,
2136                 oldf.rollover,
2137             newf.id AS new_fund_id
2138         FROM
2139         acq.fund AS oldf
2140         LEFT JOIN acq.fund AS newf
2141                 ON ( oldf.code = newf.code )
2142         WHERE
2143                     oldf.org = org_unit_id
2144                 and oldf.year = old_year
2145                 and oldf.propagate
2146         and newf.year = new_year
2147         LOOP
2148                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
2149                 --
2150                 IF roll_fund.new_fund_id IS NULL THEN
2151                         --
2152                         -- The old fund hasn't been propagated yet.  Propagate it now.
2153                         --
2154                         INSERT INTO acq.fund (
2155                                 org,
2156                                 name,
2157                                 year,
2158                                 currency_type,
2159                                 code,
2160                                 rollover,
2161                                 propagate,
2162                                 balance_warning_percent,
2163                                 balance_stop_percent
2164                         ) VALUES (
2165                                 roll_fund.org,
2166                                 roll_fund.name,
2167                                 new_year,
2168                                 roll_fund.currency_type,
2169                                 roll_fund.code,
2170                                 true,
2171                                 true,
2172                                 roll_fund.balance_warning_percent,
2173                                 roll_fund.balance_stop_percent
2174                         )
2175                         RETURNING id INTO new_fund;
2176                 ELSE
2177                         new_fund = roll_fund.new_fund_id;
2178                 END IF;
2179                 --
2180                 -- Determine the amount to transfer
2181                 --
2182                 SELECT amount
2183                 INTO xfer_amount
2184                 FROM acq.fund_spent_balance
2185                 WHERE fund = roll_fund.old_fund;
2186                 --
2187                 IF xfer_amount <> 0 THEN
2188                         IF roll_fund.rollover THEN
2189                                 --
2190                                 -- Transfer balance from old fund to new
2191                                 --
2192                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
2193                                 --
2194                                 PERFORM acq.transfer_fund(
2195                                         roll_fund.old_fund,
2196                                         xfer_amount,
2197                                         new_fund,
2198                                         xfer_amount,
2199                                         user_id,
2200                                         'Rollover'
2201                                 );
2202                         ELSE
2203                                 --
2204                                 -- Transfer balance from old fund to the void
2205                                 --
2206                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
2207                                 --
2208                                 PERFORM acq.transfer_fund(
2209                                         roll_fund.old_fund,
2210                                         xfer_amount,
2211                                         NULL,
2212                                         NULL,
2213                                         user_id,
2214                                         'Rollover'
2215                                 );
2216                         END IF;
2217                 END IF;
2218                 --
2219                 IF roll_fund.rollover THEN
2220                         --
2221                         -- Move any lineitems from the old fund to the new one
2222                         -- where the associated debit is an encumbrance.
2223                         --
2224                         -- Any other tables tying expenditure details to funds should
2225                         -- receive similar treatment.  At this writing there are none.
2226                         --
2227                         UPDATE acq.lineitem_detail
2228                         SET fund = new_fund
2229                         WHERE
2230                         fund = roll_fund.old_fund -- this condition may be redundant
2231                         AND fund_debit in
2232                         (
2233                                 SELECT id
2234                                 FROM acq.fund_debit
2235                                 WHERE
2236                                 fund = roll_fund.old_fund
2237                                 AND encumbrance
2238                         );
2239                         --
2240                         -- Move encumbrance debits from the old fund to the new fund
2241                         --
2242                         UPDATE acq.fund_debit
2243                         SET fund = new_fund
2244                         wHERE
2245                                 fund = roll_fund.old_fund
2246                                 AND encumbrance;
2247                 END IF;
2248                 --
2249                 -- Mark old fund as inactive, now that we've closed it
2250                 --
2251                 UPDATE acq.fund
2252                 SET active = FALSE
2253                 WHERE id = roll_fund.old_fund;
2254         END LOOP;
2255 END;
2256 $$ LANGUAGE plpgsql;
2257
2258 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
2259         old_year INTEGER,
2260         user_id INTEGER,
2261         org_unit_id INTEGER
2262 ) RETURNS VOID AS $$
2263 DECLARE
2264 --
2265 new_fund    INT;
2266 new_year    INT := old_year + 1;
2267 org_found   BOOL;
2268 xfer_amount NUMERIC;
2269 roll_fund   RECORD;
2270 deb         RECORD;
2271 detail      RECORD;
2272 --
2273 BEGIN
2274         --
2275         -- Sanity checks
2276         --
2277         IF old_year IS NULL THEN
2278                 RAISE EXCEPTION 'Input year argument is NULL';
2279     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
2280         RAISE EXCEPTION 'Input year is out of range';
2281         END IF;
2282         --
2283         IF user_id IS NULL THEN
2284                 RAISE EXCEPTION 'Input user id argument is NULL';
2285         END IF;
2286         --
2287         IF org_unit_id IS NULL THEN
2288                 RAISE EXCEPTION 'Org unit id argument is NULL';
2289         ELSE
2290                 --
2291                 -- Validate the org unit
2292                 --
2293                 SELECT TRUE
2294                 INTO org_found
2295                 FROM actor.org_unit
2296                 WHERE id = org_unit_id;
2297                 --
2298                 IF org_found IS NULL THEN
2299                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
2300                 END IF;
2301         END IF;
2302         --
2303         -- Loop over the propagable funds to identify the details
2304         -- from the old fund plus the id of the new one, if it exists.
2305         --
2306         FOR roll_fund in
2307         SELECT
2308             oldf.id AS old_fund,
2309             oldf.org,
2310             oldf.name,
2311             oldf.currency_type,
2312             oldf.code,
2313                 oldf.rollover,
2314             newf.id AS new_fund_id
2315         FROM
2316         acq.fund AS oldf
2317         LEFT JOIN acq.fund AS newf
2318                 ON ( oldf.code = newf.code )
2319         WHERE
2320                     oldf.year = old_year
2321                 AND oldf.propagate
2322         AND newf.year = new_year
2323                 AND oldf.org in (
2324                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
2325                 )
2326         LOOP
2327                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
2328                 --
2329                 IF roll_fund.new_fund_id IS NULL THEN
2330                         --
2331                         -- The old fund hasn't been propagated yet.  Propagate it now.
2332                         --
2333                         INSERT INTO acq.fund (
2334                                 org,
2335                                 name,
2336                                 year,
2337                                 currency_type,
2338                                 code,
2339                                 rollover,
2340                                 propagate,
2341                                 balance_warning_percent,
2342                                 balance_stop_percent
2343                         ) VALUES (
2344                                 roll_fund.org,
2345                                 roll_fund.name,
2346                                 new_year,
2347                                 roll_fund.currency_type,
2348                                 roll_fund.code,
2349                                 true,
2350                                 true,
2351                                 roll_fund.balance_warning_percent,
2352                                 roll_fund.balance_stop_percent
2353                         )
2354                         RETURNING id INTO new_fund;
2355                 ELSE
2356                         new_fund = roll_fund.new_fund_id;
2357                 END IF;
2358                 --
2359                 -- Determine the amount to transfer
2360                 --
2361                 SELECT amount
2362                 INTO xfer_amount
2363                 FROM acq.fund_spent_balance
2364                 WHERE fund = roll_fund.old_fund;
2365                 --
2366                 IF xfer_amount <> 0 THEN
2367                         IF roll_fund.rollover THEN
2368                                 --
2369                                 -- Transfer balance from old fund to new
2370                                 --
2371                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
2372                                 --
2373                                 PERFORM acq.transfer_fund(
2374                                         roll_fund.old_fund,
2375                                         xfer_amount,
2376                                         new_fund,
2377                                         xfer_amount,
2378                                         user_id,
2379                                         'Rollover'
2380                                 );
2381                         ELSE
2382                                 --
2383                                 -- Transfer balance from old fund to the void
2384                                 --
2385                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
2386                                 --
2387                                 PERFORM acq.transfer_fund(
2388                                         roll_fund.old_fund,
2389                                         xfer_amount,
2390                                         NULL,
2391                                         NULL,
2392                                         user_id,
2393                                         'Rollover'
2394                                 );
2395                         END IF;
2396                 END IF;
2397                 --
2398                 IF roll_fund.rollover THEN
2399                         --
2400                         -- Move any lineitems from the old fund to the new one
2401                         -- where the associated debit is an encumbrance.
2402                         --
2403                         -- Any other tables tying expenditure details to funds should
2404                         -- receive similar treatment.  At this writing there are none.
2405                         --
2406                         UPDATE acq.lineitem_detail
2407                         SET fund = new_fund
2408                         WHERE
2409                         fund = roll_fund.old_fund -- this condition may be redundant
2410                         AND fund_debit in
2411                         (
2412                                 SELECT id
2413                                 FROM acq.fund_debit
2414                                 WHERE
2415                                 fund = roll_fund.old_fund
2416                                 AND encumbrance
2417                         );
2418                         --
2419                         -- Move encumbrance debits from the old fund to the new fund
2420                         --
2421                         UPDATE acq.fund_debit
2422                         SET fund = new_fund
2423                         wHERE
2424                                 fund = roll_fund.old_fund
2425                                 AND encumbrance;
2426                 END IF;
2427                 --
2428                 -- Mark old fund as inactive, now that we've closed it
2429                 --
2430                 UPDATE acq.fund
2431                 SET active = FALSE
2432                 WHERE id = roll_fund.old_fund;
2433         END LOOP;
2434 END;
2435 $$ LANGUAGE plpgsql;
2436
2437 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
2438     SELECT  funding_source,
2439             SUM(amount) AS amount
2440       FROM  acq.funding_source_credit
2441       GROUP BY 1;
2442
2443 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
2444     SELECT  funding_source,
2445             SUM(a.amount)::NUMERIC(100,2) AS amount
2446     FROM  acq.fund_allocation a
2447     GROUP BY 1;
2448
2449 CREATE OR REPLACE VIEW acq.funding_source_balance AS
2450     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
2451             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
2452       FROM  acq.funding_source_credit_total c
2453             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
2454       GROUP BY 1;
2455
2456 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
2457     SELECT  fund,
2458             SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
2459     FROM acq.fund_allocation a
2460          JOIN acq.fund f ON (a.fund = f.id)
2461          JOIN acq.funding_source s ON (a.funding_source = s.id)
2462     GROUP BY 1;
2463
2464 CREATE OR REPLACE VIEW acq.fund_debit_total AS
2465     SELECT  fund.id AS fund,
2466             sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
2467     FROM acq.fund fund
2468         LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
2469     GROUP BY fund.id;
2470
2471 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
2472     SELECT
2473         fund.id AS fund,
2474         sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
2475     FROM acq.fund fund
2476         LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
2477     WHERE fund_debit.encumbrance GROUP BY fund.id;
2478
2479 CREATE OR REPLACE VIEW acq.fund_spent_total AS
2480     SELECT  fund.id AS fund,
2481             sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
2482     FROM acq.fund fund
2483         LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
2484     WHERE NOT fund_debit.encumbrance
2485     GROUP BY fund.id;
2486
2487 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
2488     SELECT  c.fund,
2489             c.amount - COALESCE(d.amount, 0.0) AS amount
2490     FROM acq.fund_allocation_total c
2491     LEFT JOIN acq.fund_debit_total d USING (fund);
2492
2493 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
2494     SELECT  c.fund,
2495             c.amount - COALESCE(d.amount,0.0) AS amount
2496       FROM  acq.fund_allocation_total c
2497             LEFT JOIN acq.fund_spent_total d USING (fund);
2498
2499 -- For each fund: the total allocation from all sources, in the
2500 -- currency of the fund (or 0 if there are no allocations)
2501
2502 CREATE VIEW acq.all_fund_allocation_total AS
2503 SELECT
2504     f.id AS fund,
2505     COALESCE( SUM( a.amount * acq.exchange_ratio(
2506         s.currency_type, f.currency_type))::numeric(100,2), 0 )
2507     AS amount
2508 FROM
2509     acq.fund f
2510         LEFT JOIN acq.fund_allocation a
2511             ON a.fund = f.id
2512         LEFT JOIN acq.funding_source s
2513             ON a.funding_source = s.id
2514 GROUP BY
2515     f.id;
2516
2517 -- For every fund: the total encumbrances (or 0 if none),
2518 -- in the currency of the fund.
2519
2520 CREATE VIEW acq.all_fund_encumbrance_total AS
2521 SELECT
2522         f.id AS fund,
2523         COALESCE( encumb.amount, 0 ) AS amount
2524 FROM
2525         acq.fund AS f
2526                 LEFT JOIN (
2527                         SELECT
2528                                 fund,
2529                                 sum( amount ) AS amount
2530                         FROM
2531                                 acq.fund_debit
2532                         WHERE
2533                                 encumbrance
2534                         GROUP BY fund
2535                 ) AS encumb
2536                         ON f.id = encumb.fund;
2537
2538 -- For every fund: the total spent (or 0 if none),
2539 -- in the currency of the fund.
2540
2541 CREATE VIEW acq.all_fund_spent_total AS
2542 SELECT
2543     f.id AS fund,
2544     COALESCE( spent.amount, 0 ) AS amount
2545 FROM
2546     acq.fund AS f
2547         LEFT JOIN (
2548             SELECT
2549                 fund,
2550                 sum( amount ) AS amount
2551             FROM
2552                 acq.fund_debit
2553             WHERE
2554                 NOT encumbrance
2555             GROUP BY fund
2556         ) AS spent
2557             ON f.id = spent.fund;
2558
2559 -- For each fund: the amount not yet spent, in the currency
2560 -- of the fund.  May include encumbrances.
2561
2562 CREATE VIEW acq.all_fund_spent_balance AS
2563 SELECT
2564         c.fund,
2565         c.amount - d.amount AS amount
2566 FROM acq.all_fund_allocation_total c
2567     LEFT JOIN acq.all_fund_spent_total d USING (fund);
2568
2569 -- For each fund: the amount neither spent nor encumbered,
2570 -- in the currency of the fund
2571
2572 CREATE VIEW acq.all_fund_combined_balance AS
2573 SELECT
2574      a.fund,
2575      a.amount - COALESCE( c.amount, 0 ) AS amount
2576 FROM
2577      acq.all_fund_allocation_total a
2578         LEFT OUTER JOIN (
2579             SELECT
2580                 fund,
2581                 SUM( amount ) AS amount
2582             FROM
2583                 acq.fund_debit
2584             GROUP BY
2585                 fund
2586         ) AS c USING ( fund );
2587
2588 CREATE TABLE acq.claim_type (
2589         id             SERIAL           PRIMARY KEY,
2590         org_unit       INT              NOT NULL REFERENCES actor.org_unit(id)
2591                                                  DEFERRABLE INITIALLY DEFERRED,
2592         code           TEXT             NOT NULL,
2593         description    TEXT             NOT NULL,
2594         CONSTRAINT claim_type_once_per_org UNIQUE ( org_unit, code )
2595 );
2596
2597 CREATE TABLE acq.claim (
2598         id             SERIAL           PRIMARY KEY,
2599         type           INT              NOT NULL REFERENCES acq.claim_type
2600                                                  DEFERRABLE INITIALLY DEFERRED,
2601         lineitem_detail BIGINT          NOT NULL REFERENCES acq.lineitem_detail
2602                                                  DEFERRABLE INITIALLY DEFERRED
2603 );
2604
2605 CREATE INDEX claim_lid_idx ON acq.claim( lineitem_detail );
2606
2607 CREATE TABLE acq.claim_event (
2608         id             BIGSERIAL        PRIMARY KEY,
2609         type           INT              NOT NULL REFERENCES acq.claim_event_type
2610                                                  DEFERRABLE INITIALLY DEFERRED,
2611         claim          SERIAL           NOT NULL REFERENCES acq.claim
2612                                                  DEFERRABLE INITIALLY DEFERRED,
2613         event_date     TIMESTAMPTZ      NOT NULL DEFAULT now(),
2614         creator        INT              NOT NULL REFERENCES actor.usr
2615                                                  DEFERRABLE INITIALLY DEFERRED,
2616         note           TEXT
2617 );
2618
2619 CREATE INDEX claim_event_claim_date_idx ON acq.claim_event( claim, event_date );
2620
2621 -- And the serials version of claiming
2622 CREATE TABLE acq.serial_claim (
2623     id     SERIAL           PRIMARY KEY,
2624     type   INT              NOT NULL REFERENCES acq.claim_type
2625                                      DEFERRABLE INITIALLY DEFERRED,
2626     item    BIGINT          NOT NULL REFERENCES serial.item
2627                                      DEFERRABLE INITIALLY DEFERRED
2628 );
2629
2630 CREATE INDEX serial_claim_lid_idx ON acq.serial_claim( item );
2631
2632 CREATE TABLE acq.serial_claim_event (
2633     id             BIGSERIAL        PRIMARY KEY,
2634     type           INT              NOT NULL REFERENCES acq.claim_event_type
2635                                              DEFERRABLE INITIALLY DEFERRED,
2636     claim          SERIAL           NOT NULL REFERENCES acq.serial_claim
2637                                              DEFERRABLE INITIALLY DEFERRED,
2638     event_date     TIMESTAMPTZ      NOT NULL DEFAULT now(),
2639     creator        INT              NOT NULL REFERENCES actor.usr
2640                                              DEFERRABLE INITIALLY DEFERRED,
2641     note           TEXT
2642 );
2643
2644 CREATE INDEX serial_claim_event_claim_date_idx ON acq.serial_claim_event( claim, event_date );
2645
2646 COMMIT;