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