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