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