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

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