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