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