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