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