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