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