Open-ILS/src/sql/Pg/200.schema.acq.sql

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