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