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

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