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