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