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