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

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