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