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_payment_method (
 789     code    TEXT    PRIMARY KEY,
 790     name    TEXT    NOT NULL -- i18n-ize
 791 );
 792
 793 CREATE TABLE acq.invoice (
 794     id          SERIAL      PRIMARY KEY,
 795     receiver    INT         NOT NULL REFERENCES actor.org_unit (id),
 796     provider    INT         NOT NULL REFERENCES acq.provider (id),
 797     shipper     INT         NOT NULL REFERENCES acq.provider (id),
 798     recv_date   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 799     recv_method TEXT        NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI',
 800     inv_type    TEXT,       -- A "type" field is desired, but no idea what goes here
 801     inv_ident   TEXT        NOT NULL, -- vendor-supplied invoice id/number
 802         payment_auth TEXT,
 803         payment_method TEXT     REFERENCES acq.invoice_payment_method (code)
 804                                 DEFERRABLE INITIALLY DEFERRED,
 805         note        TEXT
 806 );
 807
 808 CREATE TABLE acq.invoice_entry (
 809     id              SERIAL      PRIMARY KEY,
 810     invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON DELETE CASCADE,
 811     purchase_order  INT         REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
 812     lineitem        INT         REFERENCES acq.lineitem (id) ON UPDATE CASCADE ON DELETE SET NULL,
 813     inv_item_count  INT         NOT NULL, -- How many acqlids did they say they sent
 814     phys_item_count INT, -- and how many did staff count
 815     note            TEXT,
 816     billed_per_item BOOL,
 817     cost_billed     NUMERIC(8,2),
 818     actual_cost     NUMERIC(8,2),
 819         amount_paid     NUMERIC (8,2)
 820 );
 821
 822 CREATE TABLE acq.invoice_item_type (
 823     code    TEXT    PRIMARY KEY,
 824     name    TEXT    NOT NULL,  -- i18n-ize
 825         prorate BOOL    NOT NULL DEFAULT FALSE
 826 );
 827
 828 CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc
 829     id              SERIAL      PRIMARY KEY,
 830     invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE,
 831     purchase_order  INT         REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
 832     fund_debit      INT         REFERENCES acq.fund_debit (id),
 833     inv_item_type   TEXT        NOT NULL REFERENCES acq.invoice_item_type (code),
 834     title           TEXT,
 835     author          TEXT,
 836     note            TEXT,
 837     cost_billed     NUMERIC(8,2),
 838     actual_cost     NUMERIC(8,2),
 839         fund            INT         REFERENCES acq.fund (id)
 840                                     DEFERRABLE INITIALLY DEFERRED,
 841         amount_paid     NUMERIC (8,2)
 842 );
 843
 844 -- Patron requests
 845 CREATE TABLE acq.user_request_type (
 846     id      SERIAL  PRIMARY KEY,
 847     label   TEXT    NOT NULL UNIQUE -- i18n-ize
 848 );
 849
 850 INSERT INTO acq.user_request_type (id,label) VALUES (1, oils_i18n_gettext('1', 'Books', 'aurt', 'label'));
 851 INSERT INTO acq.user_request_type (id,label) VALUES (2, oils_i18n_gettext('2', 'Journal/Magazine & Newspaper Articles', 'aurt', 'label'));
 852 INSERT INTO acq.user_request_type (id,label) VALUES (3, oils_i18n_gettext('3', 'Audiobooks', 'aurt', 'label'));
 853 INSERT INTO acq.user_request_type (id,label) VALUES (4, oils_i18n_gettext('4', 'Music', 'aurt', 'label'));
 854 INSERT INTO acq.user_request_type (id,label) VALUES (5, oils_i18n_gettext('5', 'DVDs', 'aurt', 'label'));
 855
 856 SELECT SETVAL('acq.user_request_type_id_seq'::TEXT, 6);
 857
 858 CREATE TABLE acq.user_request (
 859     id                  SERIAL  PRIMARY KEY,
 860     usr                 INT     NOT NULL REFERENCES actor.usr (id), -- requesting user
 861     hold                BOOL    NOT NULL DEFAULT TRUE,
 862
 863     pickup_lib          INT     NOT NULL REFERENCES actor.org_unit (id), -- pickup lib
 864     holdable_formats    TEXT,           -- nullable, for use in hold creation
 865     phone_notify        TEXT,
 866     email_notify        BOOL    NOT NULL DEFAULT TRUE,
 867     lineitem            INT     REFERENCES acq.lineitem (id) ON DELETE CASCADE,
 868     eg_bib              BIGINT  REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
 869     request_date        TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- when they requested it
 870     need_before         TIMESTAMPTZ,    -- don't create holds after this
 871     max_fee             TEXT,
 872
 873     request_type        INT     NOT NULL REFERENCES acq.user_request_type (id),
 874     isxn                TEXT,
 875     title               TEXT,
 876     volume              TEXT,
 877     author              TEXT,
 878     article_title       TEXT,
 879     article_pages       TEXT,
 880     publisher           TEXT,
 881     location            TEXT,
 882     pubdate             TEXT,
 883     mentioned           TEXT,
 884     other_info          TEXT,
 885         cancel_reason       INT    REFERENCES acq.cancel_reason( id )
 886                                    DEFERRABLE INITIALLY DEFERRED
 887 );
 888
 889
 890 -- Functions
 891
 892 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 893 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 894 DECLARE
 895     counter INT;
 896     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 897 BEGIN
 898
 899     SELECT  COUNT(*) INTO counter
 900       FROM  oils_xpath_table(
 901                 'id',
 902                 'marc',
 903                 'acq.lineitem',
 904                 '//*[@tag="' || tag || '"]',
 905                 'id=' || lineitem
 906             ) as t(i int,c text);
 907
 908     FOR i IN 1 .. counter LOOP
 909         FOR lida IN
 910             SELECT  *
 911               FROM  (   SELECT  id,i,t,v
 912                           FROM  oils_xpath_table(
 913                                     'id',
 914                                     'marc',
 915                                     'acq.lineitem',
 916                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 917                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 918                                     'id=' || lineitem
 919                                 ) as t(id int,t text,v text)
 920                     )x
 921         LOOP
 922             RETURN NEXT lida;
 923         END LOOP;
 924     END LOOP;
 925
 926     RETURN;
 927 END;
 928 $$ LANGUAGE PLPGSQL;
 929
 930 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 931 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 932 DECLARE
 933     prov_i  INT;
 934     tag_t   TEXT;
 935     lida    acq.flat_lineitem_detail%ROWTYPE;
 936 BEGIN
 937     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 938     IF NOT FOUND THEN RETURN; END IF;
 939
 940     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 941     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 942
 943     FOR lida IN
 944         SELECT  lineitem_i,
 945                 h.holding,
 946                 a.name,
 947                 h.data
 948           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 949                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 950           WHERE a.provider = prov_i
 951     LOOP
 952         RETURN NEXT lida;
 953     END LOOP;
 954
 955     RETURN;
 956 END;
 957 $$ LANGUAGE PLPGSQL;
 958
 959 -- select * from acq.extract_provider_holding_data(699);
 960
 961 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 962         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 963 $$ LANGUAGE SQL;
 964
 965 /*
 966 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 967         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 968 $$ LANGUAGE SQL;
 969
 970 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 971         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 972 $$ LANGUAGE SQL;
 973 */
 974 -- For example:
 975 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 976
 977 /*
 978 Suggested vendor fields:
 979         vendor_price
 980         vendor_currency
 981         vendor_avail
 982         vendor_po
 983         vendor_identifier
 984 */
 985
 986 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $function$
 987 DECLARE
 988         value           TEXT;
 989         atype           TEXT;
 990         prov            INT;
 991         pos             INT;
 992         adef            RECORD;
 993         xpath_string    TEXT;
 994 BEGIN
 995         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 996
 997                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 998
 999                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
1000                         IF (atype = 'lineitem_provider_attr_definition') THEN
1001                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
1002                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
1003                         END IF;
1004
1005                         IF (atype = 'lineitem_provider_attr_definition') THEN
1006                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
1007                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
1008                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
1009                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
1010                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
1011                         END IF;
1012
1013             xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,'');
1014
1015             pos := 1;
1016
1017             LOOP
1018                         SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
1019
1020                         IF (value IS NOT NULL AND value <> '') THEN
1021                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
1022                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
1023                 ELSE
1024                     EXIT;
1025                             END IF;
1026
1027                 pos := pos + 1;
1028             END LOOP;
1029
1030                 END IF;
1031
1032         END LOOP;
1033
1034         RETURN NULL;
1035 END;
1036 $function$ LANGUAGE PLPGSQL;
1037
1038 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
1039 BEGIN
1040         IF TG_OP = 'UPDATE' THEN
1041                 DELETE FROM acq.lineitem_attr
1042                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
1043                 RETURN NEW;
1044         ELSE
1045                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
1046                 RETURN OLD;
1047         END IF;
1048 END;
1049 $$ LANGUAGE PLPGSQL;
1050
1051 CREATE TRIGGER cleanup_lineitem_trigger
1052         BEFORE UPDATE OR DELETE ON acq.lineitem
1053         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
1054
1055 CREATE TRIGGER ingest_lineitem_trigger
1056         AFTER INSERT OR UPDATE ON acq.lineitem
1057         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
1058
1059 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
1060 DECLARE
1061     rat NUMERIC;
1062 BEGIN
1063     IF from_ex = to_ex THEN
1064         RETURN 1.0;
1065     END IF;
1066
1067     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
1068
1069     IF FOUND THEN
1070         RETURN rat;
1071     ELSE
1072         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
1073         IF FOUND THEN
1074             RETURN 1.0/rat;
1075         END IF;
1076     END IF;
1077
1078     RETURN NULL;
1079
1080 END;
1081 $$ LANGUAGE PLPGSQL;
1082
1083 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
1084     SELECT $3 * acq.exchange_ratio($1, $2);
1085 $$ LANGUAGE SQL;
1086
1087 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
1088 /*
1089         Examine the acq.fiscal_year table, comparing successive years.
1090         Report any inconsistencies, i.e. years that overlap, have gaps
1091     between them, or are out of sequence.
1092 */
1093 RETURNS SETOF RECORD AS $$
1094 DECLARE
1095         first_row  BOOLEAN;
1096         curr_year  RECORD;
1097         prev_year  RECORD;
1098         return_rec RECORD;
1099 BEGIN
1100         first_row := true;
1101         FOR curr_year in
1102                 SELECT
1103                         id,
1104                         calendar,
1105                         year,
1106                         year_begin,
1107                         year_end
1108                 FROM
1109                         acq.fiscal_year
1110                 ORDER BY
1111                         calendar,
1112                         year_begin
1113         LOOP
1114                 --
1115                 IF first_row THEN
1116                         first_row := FALSE;
1117                 ELSIF curr_year.calendar    = prev_year.calendar THEN
1118                         IF curr_year.year_begin > prev_year.year_end THEN
1119                                 -- This ugly kludge works around the fact that older
1120                                 -- versions of PostgreSQL don't support RETURN QUERY SELECT
1121                                 FOR return_rec IN SELECT
1122                                         prev_year.id,
1123                                         prev_year.year,
1124                                         'Gap between fiscal years'::TEXT
1125                                 LOOP
1126                                         RETURN NEXT return_rec;
1127                                 END LOOP;
1128                         ELSIF curr_year.year_begin < prev_year.year_end THEN
1129                                 FOR return_rec IN SELECT
1130                                         prev_year.id,
1131                                         prev_year.year,
1132                                         'Overlapping fiscal years'::TEXT
1133                                 LOOP
1134                                         RETURN NEXT return_rec;
1135                                 END LOOP;
1136                         ELSIF curr_year.year < prev_year.year THEN
1137                                 FOR return_rec IN SELECT
1138                                         prev_year.id,
1139                                         prev_year.year,
1140                                         'Fiscal years out of order'::TEXT
1141                                 LOOP
1142                                         RETURN NEXT return_rec;
1143                                 END LOOP;
1144                         END IF;
1145                 END IF;
1146                 --
1147                 prev_year := curr_year;
1148         END LOOP;
1149         --
1150         RETURN;
1151 END;
1152 $$ LANGUAGE plpgsql;
1153
1154 CREATE OR REPLACE FUNCTION acq.transfer_fund(
1155         old_fund   IN INT,
1156         old_amount IN NUMERIC,     -- in currency of old fund
1157         new_fund   IN INT,
1158         new_amount IN NUMERIC,     -- in currency of new fund
1159         user_id    IN INT,
1160         xfer_note  IN TEXT         -- to be recorded in acq.fund_transfer
1161         -- ,funding_source_in IN INT  -- if user wants to specify a funding source (see notes)
1162 ) RETURNS VOID AS $$
1163 /* -------------------------------------------------------------------------------
1164
1165 Function to transfer money from one fund to another.
1166
1167 A transfer is represented as a pair of entries in acq.fund_allocation, with a
1168 negative amount for the old (losing) fund and a positive amount for the new
1169 (gaining) fund.  In some cases there may be more than one such pair of entries
1170 in order to pull the money from different funding sources, or more specifically
1171 from different funding source credits.  For each such pair there is also an
1172 entry in acq.fund_transfer.
1173
1174 Since funding_source is a non-nullable column in acq.fund_allocation, we must
1175 choose a funding source for the transferred money to come from.  This choice
1176 must meet two constraints, so far as possible:
1177
1178 1. The amount transferred from a given funding source must not exceed the
1179 amount allocated to the old fund by the funding source.  To that end we
1180 compare the amount being transferred to the amount allocated.
1181
1182 2. We shouldn't transfer money that has already been spent or encumbered, as
1183 defined by the funding attribution process.  We attribute expenses to the
1184 oldest funding source credits first.  In order to avoid transferring that
1185 attributed money, we reverse the priority, transferring from the newest funding
1186 source credits first.  There can be no guarantee that this approach will
1187 avoid overcommitting a fund, but no other approach can do any better.
1188
1189 In this context the age of a funding source credit is defined by the
1190 deadline_date for credits with deadline_dates, and by the effective_date for
1191 credits without deadline_dates, with the proviso that credits with deadline_dates
1192 are all considered "older" than those without.
1193
1194 ----------
1195
1196 In the signature for this function, there is one last parameter commented out,
1197 named "funding_source_in".  Correspondingly, the WHERE clause for the query
1198 driving the main loop has an OR clause commented out, which references the
1199 funding_source_in parameter.
1200
1201 If these lines are uncommented, this function will allow the user optionally to
1202 restrict a fund transfer to a specified funding source.  If the source
1203 parameter is left NULL, then there will be no such restriction.
1204
1205 ------------------------------------------------------------------------------- */
1206 DECLARE
1207         same_currency      BOOLEAN;
1208         currency_ratio     NUMERIC;
1209         old_fund_currency  TEXT;
1210         old_remaining      NUMERIC;  -- in currency of old fund
1211         new_fund_currency  TEXT;
1212         new_fund_active    BOOLEAN;
1213         new_remaining      NUMERIC;  -- in currency of new fund
1214         curr_old_amt       NUMERIC;  -- in currency of old fund
1215         curr_new_amt       NUMERIC;  -- in currency of new fund
1216         source_addition    NUMERIC;  -- in currency of funding source
1217         source_deduction   NUMERIC;  -- in currency of funding source
1218         orig_allocated_amt NUMERIC;  -- in currency of funding source
1219         allocated_amt      NUMERIC;  -- in currency of fund
1220         source             RECORD;
1221 BEGIN
1222         --
1223         -- Sanity checks
1224         --
1225         IF old_fund IS NULL THEN
1226                 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
1227         END IF;
1228         --
1229         IF old_amount IS NULL THEN
1230                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
1231         END IF;
1232         --
1233         -- The new fund and its amount must be both NULL or both not NULL.
1234         --
1235         IF new_fund IS NOT NULL AND new_amount IS NULL THEN
1236                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
1237         END IF;
1238         --
1239         IF new_fund IS NULL AND new_amount IS NOT NULL THEN
1240                 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
1241         END IF;
1242         --
1243         IF user_id IS NULL THEN
1244                 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
1245         END IF;
1246         --
1247         -- Initialize the amounts to be transferred, each denominated
1248         -- in the currency of its respective fund.  They will be
1249         -- reduced on each iteration of the loop.
1250         --
1251         old_remaining := old_amount;
1252         new_remaining := new_amount;
1253         --
1254         -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
1255         --      old_amount, old_fund, new_amount, new_fund;
1256         --
1257         -- Get the currency types of the old and new funds.
1258         --
1259         SELECT
1260                 currency_type
1261         INTO
1262                 old_fund_currency
1263         FROM
1264                 acq.fund
1265         WHERE
1266                 id = old_fund;
1267         --
1268         IF old_fund_currency IS NULL THEN
1269                 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
1270         END IF;
1271         --
1272         IF new_fund IS NOT NULL THEN
1273                 SELECT
1274                         currency_type,
1275                         active
1276                 INTO
1277                         new_fund_currency,
1278                         new_fund_active
1279                 FROM
1280                         acq.fund
1281                 WHERE
1282                         id = new_fund;
1283                 --
1284                 IF new_fund_currency IS NULL THEN
1285                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
1286                 ELSIF NOT new_fund_active THEN
1287                         --
1288                         -- No point in putting money into a fund from whence you can't spend it
1289                         --
1290                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
1291                 END IF;
1292                 --
1293                 IF new_amount = old_amount THEN
1294                         same_currency := true;
1295                         currency_ratio := 1;
1296                 ELSE
1297                         --
1298                         -- We'll have to translate currency between funds.  We presume that
1299                         -- the calling code has already applied an appropriate exchange rate,
1300                         -- so we'll apply the same conversion to each sub-transfer.
1301                         --
1302                         same_currency := false;
1303                         currency_ratio := new_amount / old_amount;
1304                 END IF;
1305         END IF;
1306         --
1307         -- Identify the funding source(s) from which we want to transfer the money.
1308         -- The principle is that we want to transfer the newest money first, because
1309         -- we spend the oldest money first.  The priority for spending is defined
1310         -- by a sort of the view acq.ordered_funding_source_credit.
1311         --
1312         FOR source in
1313                 SELECT
1314                         ofsc.id,
1315                         ofsc.funding_source,
1316                         ofsc.amount,
1317                         ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
1318                                 AS converted_amt,
1319                         fs.currency_type
1320                 FROM
1321                         acq.ordered_funding_source_credit AS ofsc,
1322                         acq.funding_source fs
1323                 WHERE
1324                         ofsc.funding_source = fs.id
1325                         and ofsc.funding_source IN
1326                         (
1327                                 SELECT funding_source
1328                                 FROM acq.fund_allocation
1329                                 WHERE fund = old_fund
1330                         )
1331                         -- and
1332                         -- (
1333                         --      ofsc.funding_source = funding_source_in
1334                         --      OR funding_source_in IS NULL
1335                         -- )
1336                 ORDER BY
1337                         ofsc.sort_priority desc,
1338                         ofsc.sort_date desc,
1339                         ofsc.id desc
1340         LOOP
1341                 --
1342                 -- Determine how much money the old fund got from this funding source,
1343                 -- denominated in the currency types of the source and of the fund.
1344                 -- This result may reflect transfers from previous iterations.
1345                 --
1346                 SELECT
1347                         COALESCE( sum( amount ), 0 ),
1348                         COALESCE( sum( amount )
1349                                 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
1350                 INTO
1351                         orig_allocated_amt,     -- in currency of the source
1352                         allocated_amt           -- in currency of the old fund
1353                 FROM
1354                         acq.fund_allocation
1355                 WHERE
1356                         fund = old_fund
1357                         and funding_source = source.funding_source;
1358                 --
1359                 -- Determine how much to transfer from this credit, in the currency
1360                 -- of the fund.   Begin with the amount remaining to be attributed:
1361                 --
1362                 curr_old_amt := old_remaining;
1363                 --
1364                 -- Can't attribute more than was allocated from the fund:
1365                 --
1366                 IF curr_old_amt > allocated_amt THEN
1367                         curr_old_amt := allocated_amt;
1368                 END IF;
1369                 --
1370                 -- Can't attribute more than the amount of the current credit:
1371                 --
1372                 IF curr_old_amt > source.converted_amt THEN
1373                         curr_old_amt := source.converted_amt;
1374                 END IF;
1375                 --
1376                 curr_old_amt := trunc( curr_old_amt, 2 );
1377                 --
1378                 old_remaining := old_remaining - curr_old_amt;
1379                 --
1380                 -- Determine the amount to be deducted, if any,
1381                 -- from the old allocation.
1382                 --
1383                 IF old_remaining > 0 THEN
1384                         --
1385                         -- In this case we're using the whole allocation, so use that
1386                         -- amount directly instead of applying a currency translation
1387                         -- and thereby inviting round-off errors.
1388                         --
1389                         source_deduction := - orig_allocated_amt;
1390                 ELSE
1391                         source_deduction := trunc(
1392                                 ( - curr_old_amt ) *
1393                                         acq.exchange_ratio( old_fund_currency, source.currency_type ),
1394                                 2 );
1395                 END IF;
1396                 --
1397                 IF source_deduction <> 0 THEN
1398                         --
1399                         -- Insert negative allocation for old fund in fund_allocation,
1400                         -- converted into the currency of the funding source
1401                         --
1402                         INSERT INTO acq.fund_allocation (
1403                                 funding_source,
1404                                 fund,
1405                                 amount,
1406                                 allocator,
1407                                 note
1408                         ) VALUES (
1409                                 source.funding_source,
1410                                 old_fund,
1411                                 source_deduction,
1412                                 user_id,
1413                                 'Transfer to fund ' || new_fund
1414                         );
1415                 END IF;
1416                 --
1417                 IF new_fund IS NOT NULL THEN
1418                         --
1419                         -- Determine how much to add to the new fund, in
1420                         -- its currency, and how much remains to be added:
1421                         --
1422                         IF same_currency THEN
1423                                 curr_new_amt := curr_old_amt;
1424                         ELSE
1425                                 IF old_remaining = 0 THEN
1426                                         --
1427                                         -- This is the last iteration, so nothing should be left
1428                                         --
1429                                         curr_new_amt := new_remaining;
1430                                         new_remaining := 0;
1431                                 ELSE
1432                                         curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
1433                                         new_remaining := new_remaining - curr_new_amt;
1434                                 END IF;
1435                         END IF;
1436                         --
1437                         -- Determine how much to add, if any,
1438                         -- to the new fund's allocation.
1439                         --
1440                         IF old_remaining > 0 THEN
1441                                 --
1442                                 -- In this case we're using the whole allocation, so use that amount
1443                                 -- amount directly instead of applying a currency translation and
1444                                 -- thereby inviting round-off errors.
1445                                 --
1446                                 source_addition := orig_allocated_amt;
1447                         ELSIF source.currency_type = old_fund_currency THEN
1448                                 --
1449                                 -- In this case we don't need a round trip currency translation,
1450                                 -- thereby inviting round-off errors:
1451                                 --
1452                                 source_addition := curr_old_amt;
1453                         ELSE
1454                                 source_addition := trunc(
1455                                         curr_new_amt *
1456                                                 acq.exchange_ratio( new_fund_currency, source.currency_type ),
1457                                         2 );
1458                         END IF;
1459                         --
1460                         IF source_addition <> 0 THEN
1461                                 --
1462                                 -- Insert positive allocation for new fund in fund_allocation,
1463                                 -- converted to the currency of the founding source
1464                                 --
1465                                 INSERT INTO acq.fund_allocation (
1466                                         funding_source,
1467                                         fund,
1468                                         amount,
1469                                         allocator,
1470                                         note
1471                                 ) VALUES (
1472                                         source.funding_source,
1473                                         new_fund,
1474                                         source_addition,
1475                                         user_id,
1476                                         'Transfer from fund ' || old_fund
1477                                 );
1478                         END IF;
1479                 END IF;
1480                 --
1481                 IF trunc( curr_old_amt, 2 ) <> 0
1482                 OR trunc( curr_new_amt, 2 ) <> 0 THEN
1483                         --
1484                         -- Insert row in fund_transfer, using amounts in the currency of the funds
1485                         --
1486                         INSERT INTO acq.fund_transfer (
1487                                 src_fund,
1488                                 src_amount,
1489                                 dest_fund,
1490                                 dest_amount,
1491                                 transfer_user,
1492                                 note,
1493                                 funding_source_credit
1494                         ) VALUES (
1495                                 old_fund,
1496                                 trunc( curr_old_amt, 2 ),
1497                                 new_fund,
1498                                 trunc( curr_new_amt, 2 ),
1499                                 user_id,
1500                                 xfer_note,
1501                                 source.id
1502                         );
1503                 END IF;
1504                 --
1505                 if old_remaining <= 0 THEN
1506                         EXIT;                   -- Nothing more to be transferred
1507                 END IF;
1508         END LOOP;
1509 END;
1510 $$ LANGUAGE plpgsql;
1511
1512 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
1513 /*
1514 Function to attribute expenditures and encumbrances to funding source credits,
1515 and thereby to funding sources.
1516
1517 Read the debits in chonological order, attributing each one to one or
1518 more funding source credits.  Constraints:
1519
1520 1. Don't attribute more to a credit than the amount of the credit.
1521
1522 2. For a given fund, don't attribute more to a funding source than the
1523 source has allocated to that fund.
1524
1525 3. Attribute debits to credits with deadlines before attributing them to
1526 credits without deadlines.  Otherwise attribute to the earliest credits
1527 first, based on the deadline date when present, or on the effective date
1528 when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
1529 This ordering is defined by an ORDER BY clause on the view
1530 acq.ordered_funding_source_credit.
1531
1532 Start by truncating the table acq.debit_attribution.  Then insert a row
1533 into that table for each attribution.  If a debit cannot be fully
1534 attributed, insert a row for the unattributable balance, with the
1535 funding_source_credit and credit_amount columns NULL.
1536 */
1537 DECLARE
1538         curr_fund_source_bal RECORD;
1539         seqno                INT;     -- sequence num for credits applicable to a fund
1540         fund_credit          RECORD;  -- current row in temp t_fund_credit table
1541         fc                   RECORD;  -- used for loading t_fund_credit table
1542         sc                   RECORD;  -- used for loading t_fund_credit table
1543         --
1544         -- Used exclusively in the main loop:
1545         --
1546         deb                 RECORD;   -- current row from acq.fund_debit table
1547         curr_credit_bal     RECORD;   -- current row from temp t_credit table
1548         debit_balance       NUMERIC;  -- amount left to attribute for current debit
1549         conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
1550         attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
1551         conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
1552         conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
1553         conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
1554         attrib_count        INT;      -- populates id of acq.debit_attribution
1555 BEGIN
1556         --
1557         -- Load a temporary table.  For each combination of fund and funding source,
1558         -- load an entry with the total amount allocated to that fund by that source.
1559         -- This sum may reflect transfers as well as original allocations.  We will
1560         -- reduce this balance whenever we attribute debits to it.
1561         --
1562         CREATE TEMP TABLE t_fund_source_bal
1563         ON COMMIT DROP AS
1564                 SELECT
1565                         fund AS fund,
1566                         funding_source AS source,
1567                         sum( amount ) AS balance
1568                 FROM
1569                         acq.fund_allocation
1570                 GROUP BY
1571                         fund,
1572                         funding_source
1573                 HAVING
1574                         sum( amount ) > 0;
1575         --
1576         CREATE INDEX t_fund_source_bal_idx
1577                 ON t_fund_source_bal( fund, source );
1578         -------------------------------------------------------------------------------
1579         --
1580         -- Load another temporary table.  For each fund, load zero or more
1581         -- funding source credits from which that fund can get money.
1582         --
1583         CREATE TEMP TABLE t_fund_credit (
1584                 fund        INT,
1585                 seq         INT,
1586                 credit      INT
1587         ) ON COMMIT DROP;
1588         --
1589         FOR fc IN
1590                 SELECT DISTINCT fund
1591                 FROM acq.fund_allocation
1592                 ORDER BY fund
1593         LOOP                  -- Loop over the funds
1594                 seqno := 1;
1595                 FOR sc IN
1596                         SELECT
1597                                 ofsc.id
1598                         FROM
1599                                 acq.ordered_funding_source_credit AS ofsc
1600                         WHERE
1601                                 ofsc.funding_source IN
1602                                 (
1603                                         SELECT funding_source
1604                                         FROM acq.fund_allocation
1605                                         WHERE fund = fc.fund
1606                                 )
1607                 ORDER BY
1608                     ofsc.sort_priority,
1609                     ofsc.sort_date,
1610                     ofsc.id
1611                 LOOP                        -- Add each credit to the list
1612                         INSERT INTO t_fund_credit (
1613                                 fund,
1614                                 seq,
1615                                 credit
1616                         ) VALUES (
1617                                 fc.fund,
1618                                 seqno,
1619                                 sc.id
1620                         );
1621                         --RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
1622                         seqno := seqno + 1;
1623                 END LOOP;     -- Loop over credits for a given fund
1624         END LOOP;         -- Loop over funds
1625         --
1626         CREATE INDEX t_fund_credit_idx
1627                 ON t_fund_credit( fund, seq );
1628         -------------------------------------------------------------------------------
1629         --
1630         -- Load yet another temporary table.  This one is a list of funding source
1631         -- credits, with their balances.  We shall reduce those balances as we
1632         -- attribute debits to them.
1633         --
1634         CREATE TEMP TABLE t_credit
1635         ON COMMIT DROP AS
1636         SELECT
1637             fsc.id AS credit,
1638             fsc.funding_source AS source,
1639             fsc.amount AS balance,
1640             fs.currency_type AS currency_type
1641         FROM
1642             acq.funding_source_credit AS fsc,
1643             acq.funding_source fs
1644         WHERE
1645             fsc.funding_source = fs.id
1646                         AND fsc.amount > 0;
1647         --
1648         CREATE INDEX t_credit_idx
1649                 ON t_credit( credit );
1650         --
1651         -------------------------------------------------------------------------------
1652         --
1653         -- Now that we have loaded the lookup tables: loop through the debits,
1654         -- attributing each one to one or more funding source credits.
1655         --
1656         truncate table acq.debit_attribution;
1657         --
1658         attrib_count := 0;
1659         FOR deb in
1660                 SELECT
1661                         fd.id,
1662                         fd.fund,
1663                         fd.amount,
1664                         f.currency_type,
1665                         fd.encumbrance
1666                 FROM
1667                         acq.fund_debit fd,
1668                         acq.fund f
1669                 WHERE
1670                         fd.fund = f.id
1671                 ORDER BY
1672                         fd.id
1673         LOOP
1674                 --RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
1675                 --
1676                 debit_balance := deb.amount;
1677                 --
1678                 -- Loop over the funding source credits that are eligible
1679                 -- to pay for this debit
1680                 --
1681                 FOR fund_credit IN
1682                         SELECT
1683                                 credit
1684                         FROM
1685                                 t_fund_credit
1686                         WHERE
1687                                 fund = deb.fund
1688                         ORDER BY
1689                                 seq
1690                 LOOP
1691                         --RAISE NOTICE '   Examining credit %', fund_credit.credit;
1692                         --
1693                         -- Look up the balance for this credit.  If it's zero, then
1694                         -- it's not useful, so treat it as if you didn't find it.
1695                         -- (Actually there shouldn't be any zero balances in the table,
1696                         -- but we check just to make sure.)
1697                         --
1698                         SELECT *
1699                         INTO curr_credit_bal
1700                         FROM t_credit
1701                         WHERE
1702                                 credit = fund_credit.credit
1703                                 AND balance > 0;
1704                         --
1705                         IF curr_credit_bal IS NULL THEN
1706                                 --
1707                                 -- This credit is exhausted; try the next one.
1708                                 --
1709                                 CONTINUE;
1710                         END IF;
1711                         --
1712                         --
1713                         -- At this point we have an applicable credit with some money left.
1714                         -- Now see if the relevant funding_source has any money left.
1715                         --
1716                         -- Look up the balance of the allocation for this combination of
1717                         -- fund and source.  If you find such an entry, but it has a zero
1718                         -- balance, then it's not useful, so treat it as unfound.
1719                         -- (Actually there shouldn't be any zero balances in the table,
1720                         -- but we check just to make sure.)
1721                         --
1722                         SELECT *
1723                         INTO curr_fund_source_bal
1724                         FROM t_fund_source_bal
1725                         WHERE
1726                                 fund = deb.fund
1727                                 AND source = curr_credit_bal.source
1728                                 AND balance > 0;
1729                         --
1730                         IF curr_fund_source_bal IS NULL THEN
1731                                 --
1732                                 -- This fund/source doesn't exist or is already exhausted,
1733                                 -- so we can't use this credit.  Go on to the next one.
1734                                 --
1735                                 CONTINUE;
1736                         END IF;
1737                         --
1738                         -- Convert the available balances to the currency of the fund
1739                         --
1740                         conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
1741                                 curr_credit_bal.currency_type, deb.currency_type );
1742                         conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
1743                                 curr_credit_bal.currency_type, deb.currency_type );
1744                         --
1745                         -- Determine how much we can attribute to this credit: the minimum
1746                         -- of the debit amount, the fund/source balance, and the
1747                         -- credit balance
1748                         --
1749                         --RAISE NOTICE '   deb bal %', debit_balance;
1750                         --RAISE NOTICE '      source % balance %', curr_credit_bal.source, conv_alloc_balance;
1751                         --RAISE NOTICE '      credit % balance %', curr_credit_bal.credit, conv_cred_balance;
1752                         --
1753                         conv_attr_amount := NULL;
1754                         attr_amount := debit_balance;
1755                         --
1756                         IF attr_amount > conv_alloc_balance THEN
1757                                 attr_amount := conv_alloc_balance;
1758                                 conv_attr_amount := curr_fund_source_bal.balance;
1759                         END IF;
1760                         IF attr_amount > conv_cred_balance THEN
1761                                 attr_amount := conv_cred_balance;
1762                                 conv_attr_amount := curr_credit_bal.balance;
1763                         END IF;
1764                         --
1765                         -- If we're attributing all of one of the balances, then that's how
1766                         -- much we will deduct from the balances, and we already captured
1767                         -- that amount above.  Otherwise we must convert the amount of the
1768                         -- attribution from the currency of the fund back to the currency of
1769                         -- the funding source.
1770                         --
1771                         IF conv_attr_amount IS NULL THEN
1772                                 conv_attr_amount := attr_amount * acq.exchange_ratio(
1773                                         deb.currency_type, curr_credit_bal.currency_type );
1774                         END IF;
1775                         --
1776                         -- Insert a row to record the attribution
1777                         --
1778                         attrib_count := attrib_count + 1;
1779                         INSERT INTO acq.debit_attribution (
1780                                 id,
1781                                 fund_debit,
1782                                 debit_amount,
1783                                 funding_source_credit,
1784                                 credit_amount
1785                         ) VALUES (
1786                                 attrib_count,
1787                                 deb.id,
1788                                 attr_amount,
1789                                 curr_credit_bal.credit,
1790                                 conv_attr_amount
1791                         );
1792                         --
1793                         -- Subtract the attributed amount from the various balances
1794                         --
1795                         debit_balance := debit_balance - attr_amount;
1796                         curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
1797                         --
1798                         IF curr_fund_source_bal.balance <= 0 THEN
1799                                 --
1800                                 -- This allocation is exhausted.  Delete it so
1801                                 -- that we don't waste time looking at it again.
1802                                 --
1803                                 DELETE FROM t_fund_source_bal
1804                                 WHERE
1805                                         fund = curr_fund_source_bal.fund
1806                                         AND source = curr_fund_source_bal.source;
1807                         ELSE
1808                                 UPDATE t_fund_source_bal
1809                                 SET balance = balance - conv_attr_amount
1810                                 WHERE
1811                                         fund = curr_fund_source_bal.fund
1812                                         AND source = curr_fund_source_bal.source;
1813                         END IF;
1814                         --
1815                         IF curr_credit_bal.balance <= 0 THEN
1816                                 --
1817                                 -- This funding source credit is exhausted.  Delete it
1818                                 -- so that we don't waste time looking at it again.
1819                                 --
1820                                 --DELETE FROM t_credit
1821                                 --WHERE
1822                                 --      credit = curr_credit_bal.credit;
1823                                 --
1824                                 DELETE FROM t_fund_credit
1825                                 WHERE
1826                                         credit = curr_credit_bal.credit;
1827                         ELSE
1828                                 UPDATE t_credit
1829                                 SET balance = curr_credit_bal.balance
1830                                 WHERE
1831                                         credit = curr_credit_bal.credit;
1832                         END IF;
1833                         --
1834                         -- Are we done with this debit yet?
1835                         --
1836                         IF debit_balance <= 0 THEN
1837                                 EXIT;       -- We've fully attributed this debit; stop looking at credits.
1838                         END IF;
1839                 END LOOP;       -- End loop over credits
1840                 --
1841                 IF debit_balance <> 0 THEN
1842                         --
1843                         -- We weren't able to attribute this debit, or at least not
1844                         -- all of it.  Insert a row for the unattributed balance.
1845                         --
1846                         attrib_count := attrib_count + 1;
1847                         INSERT INTO acq.debit_attribution (
1848                                 id,
1849                                 fund_debit,
1850                                 debit_amount,
1851                                 funding_source_credit,
1852                                 credit_amount
1853                         ) VALUES (
1854                                 attrib_count,
1855                                 deb.id,
1856                                 debit_balance,
1857                                 NULL,
1858                                 NULL
1859                         );
1860                 END IF;
1861         END LOOP;   -- End of loop over debits
1862 END;
1863 $$ LANGUAGE 'plpgsql';
1864
1865 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit(
1866         old_year INTEGER,
1867         user_id INTEGER,
1868         org_unit_id INTEGER
1869 ) RETURNS VOID AS $$
1870 DECLARE
1871 --
1872 new_id      INT;
1873 old_fund    RECORD;
1874 org_found   BOOLEAN;
1875 --
1876 BEGIN
1877         --
1878         -- Sanity checks
1879         --
1880         IF old_year IS NULL THEN
1881                 RAISE EXCEPTION 'Input year argument is NULL';
1882         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1883                 RAISE EXCEPTION 'Input year is out of range';
1884         END IF;
1885         --
1886         IF user_id IS NULL THEN
1887                 RAISE EXCEPTION 'Input user id argument is NULL';
1888         END IF;
1889         --
1890         IF org_unit_id IS NULL THEN
1891                 RAISE EXCEPTION 'Org unit id argument is NULL';
1892         ELSE
1893                 SELECT TRUE INTO org_found
1894                 FROM actor.org_unit
1895                 WHERE id = org_unit_id;
1896                 --
1897                 IF org_found IS NULL THEN
1898                         RAISE EXCEPTION 'Org unit id is invalid';
1899                 END IF;
1900         END IF;
1901         --
1902         -- Loop over the applicable funds
1903         --
1904         FOR old_fund in SELECT * FROM acq.fund
1905         WHERE
1906                 year = old_year
1907                 AND propagate
1908                 AND org = org_unit_id
1909         LOOP
1910                 BEGIN
1911                         INSERT INTO acq.fund (
1912                                 org,
1913                                 name,
1914                                 year,
1915                                 currency_type,
1916                                 code,
1917                                 rollover,
1918                                 propagate,
1919                                 balance_warning_percent,
1920                                 balance_stop_percent
1921                         ) VALUES (
1922                                 old_fund.org,
1923                                 old_fund.name,
1924                                 old_year + 1,
1925                                 old_fund.currency_type,
1926                                 old_fund.code,
1927                                 old_fund.rollover,
1928                                 true,
1929                                 old_fund.balance_warning_percent,
1930                                 old_fund.balance_stop_percent
1931                         )
1932                         RETURNING id INTO new_id;
1933                 EXCEPTION
1934                         WHEN unique_violation THEN
1935                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1936                                 CONTINUE;
1937                 END;
1938                 --RAISE NOTICE 'Propagating fund % to fund %',
1939                 --      old_fund.code, new_id;
1940         END LOOP;
1941 END;
1942 $$ LANGUAGE plpgsql;
1943
1944 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1945         old_year INTEGER,
1946         user_id INTEGER,
1947         org_unit_id INTEGER
1948 ) RETURNS VOID AS $$
1949 DECLARE
1950 --
1951 new_id      INT;
1952 old_fund    RECORD;
1953 org_found   BOOLEAN;
1954 --
1955 BEGIN
1956         --
1957         -- Sanity checks
1958         --
1959         IF old_year IS NULL THEN
1960                 RAISE EXCEPTION 'Input year argument is NULL';
1961         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1962                 RAISE EXCEPTION 'Input year is out of range';
1963         END IF;
1964         --
1965         IF user_id IS NULL THEN
1966                 RAISE EXCEPTION 'Input user id argument is NULL';
1967         END IF;
1968         --
1969         IF org_unit_id IS NULL THEN
1970                 RAISE EXCEPTION 'Org unit id argument is NULL';
1971         ELSE
1972                 SELECT TRUE INTO org_found
1973                 FROM actor.org_unit
1974                 WHERE id = org_unit_id;
1975                 --
1976                 IF org_found IS NULL THEN
1977                         RAISE EXCEPTION 'Org unit id is invalid';
1978                 END IF;
1979         END IF;
1980         --
1981         -- Loop over the applicable funds
1982         --
1983         FOR old_fund in SELECT * FROM acq.fund
1984         WHERE
1985                 year = old_year
1986                 AND propagate
1987                 AND org in (
1988                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
1989                 )
1990         LOOP
1991                 BEGIN
1992                         INSERT INTO acq.fund (
1993                                 org,
1994                                 name,
1995                                 year,
1996                                 currency_type,
1997                                 code,
1998                                 rollover,
1999                                 propagate,
2000                                 balance_warning_percent,
2001                                 balance_stop_percent
2002                         ) VALUES (
2003                                 old_fund.org,
2004                                 old_fund.name,
2005                                 old_year + 1,
2006                                 old_fund.currency_type,
2007                                 old_fund.code,
2008                                 old_fund.rollover,
2009                                 true,
2010                                 old_fund.balance_warning_percent,
2011                                 old_fund.balance_stop_percent
2012                         )
2013                         RETURNING id INTO new_id;
2014                 EXCEPTION
2015                         WHEN unique_violation THEN
2016                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
2017                                 CONTINUE;
2018                 END;
2019                 --RAISE NOTICE 'Propagating fund % to fund %',
2020                 --      old_fund.code, new_id;
2021         END LOOP;
2022 END;
2023 $$ LANGUAGE plpgsql;
2024
2025 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
2026         old_year INTEGER,
2027         user_id INTEGER,
2028         org_unit_id INTEGER
2029 ) RETURNS VOID AS $$
2030 DECLARE
2031 --
2032 new_fund    INT;
2033 new_year    INT := old_year + 1;
2034 org_found   BOOL;
2035 xfer_amount NUMERIC;
2036 roll_fund   RECORD;
2037 deb         RECORD;
2038 detail      RECORD;
2039 --
2040 BEGIN
2041         --
2042         -- Sanity checks
2043         --
2044         IF old_year IS NULL THEN
2045                 RAISE EXCEPTION 'Input year argument is NULL';
2046     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
2047         RAISE EXCEPTION 'Input year is out of range';
2048         END IF;
2049         --
2050         IF user_id IS NULL THEN
2051                 RAISE EXCEPTION 'Input user id argument is NULL';
2052         END IF;
2053         --
2054         IF org_unit_id IS NULL THEN
2055                 RAISE EXCEPTION 'Org unit id argument is NULL';
2056         ELSE
2057                 --
2058                 -- Validate the org unit
2059                 --
2060                 SELECT TRUE
2061                 INTO org_found
2062                 FROM actor.org_unit
2063                 WHERE id = org_unit_id;
2064                 --
2065                 IF org_found IS NULL THEN
2066                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
2067                 END IF;
2068         END IF;
2069         --
2070         -- Loop over the propagable funds to identify the details
2071         -- from the old fund plus the id of the new one, if it exists.
2072         --
2073         FOR roll_fund in
2074         SELECT
2075             oldf.id AS old_fund,
2076             oldf.org,
2077             oldf.name,
2078             oldf.currency_type,
2079             oldf.code,
2080                 oldf.rollover,
2081             newf.id AS new_fund_id
2082         FROM
2083         acq.fund AS oldf
2084         LEFT JOIN acq.fund AS newf
2085                 ON ( oldf.code = newf.code )
2086         WHERE
2087                     oldf.org = org_unit_id
2088                 and oldf.year = old_year
2089                 and oldf.propagate
2090         and newf.year = new_year
2091         LOOP
2092                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
2093                 --
2094                 IF roll_fund.new_fund_id IS NULL THEN
2095                         --
2096                         -- The old fund hasn't been propagated yet.  Propagate it now.
2097                         --
2098                         INSERT INTO acq.fund (
2099                                 org,
2100                                 name,
2101                                 year,
2102                                 currency_type,
2103                                 code,
2104                                 rollover,
2105                                 propagate,
2106                                 balance_warning_percent,
2107                                 balance_stop_percent
2108                         ) VALUES (
2109                                 roll_fund.org,
2110                                 roll_fund.name,
2111                                 new_year,
2112                                 roll_fund.currency_type,
2113                                 roll_fund.code,
2114                                 true,
2115                                 true,
2116                                 roll_fund.balance_warning_percent,
2117                                 roll_fund.balance_stop_percent
2118                         )
2119                         RETURNING id INTO new_fund;
2120                 ELSE
2121                         new_fund = roll_fund.new_fund_id;
2122                 END IF;
2123                 --
2124                 -- Determine the amount to transfer
2125                 --
2126                 SELECT amount
2127                 INTO xfer_amount
2128                 FROM acq.fund_spent_balance
2129                 WHERE fund = roll_fund.old_fund;
2130                 --
2131                 IF xfer_amount <> 0 THEN
2132                         IF roll_fund.rollover THEN
2133                                 --
2134                                 -- Transfer balance from old fund to new
2135                                 --
2136                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
2137                                 --
2138                                 PERFORM acq.transfer_fund(
2139                                         roll_fund.old_fund,
2140                                         xfer_amount,
2141                                         new_fund,
2142                                         xfer_amount,
2143                                         user_id,
2144                                         'Rollover'
2145                                 );
2146                         ELSE
2147                                 --
2148                                 -- Transfer balance from old fund to the void
2149                                 --
2150                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
2151                                 --
2152                                 PERFORM acq.transfer_fund(
2153                                         roll_fund.old_fund,
2154                                         xfer_amount,
2155                                         NULL,
2156                                         NULL,
2157                                         user_id,
2158                                         'Rollover'
2159                                 );
2160                         END IF;
2161                 END IF;
2162                 --
2163                 IF roll_fund.rollover THEN
2164                         --
2165                         -- Move any lineitems from the old fund to the new one
2166                         -- where the associated debit is an encumbrance.
2167                         --
2168                         -- Any other tables tying expenditure details to funds should
2169                         -- receive similar treatment.  At this writing there are none.
2170                         --
2171                         UPDATE acq.lineitem_detail
2172                         SET fund = new_fund
2173                         WHERE
2174                         fund = roll_fund.old_fund -- this condition may be redundant
2175                         AND fund_debit in
2176                         (
2177                                 SELECT id
2178                                 FROM acq.fund_debit
2179                                 WHERE
2180                                 fund = roll_fund.old_fund
2181                                 AND encumbrance
2182                         );
2183                         --
2184                         -- Move encumbrance debits from the old fund to the new fund
2185                         --
2186                         UPDATE acq.fund_debit
2187                         SET fund = new_fund
2188                         wHERE
2189                                 fund = roll_fund.old_fund
2190                                 AND encumbrance;
2191                 END IF;
2192                 --
2193                 -- Mark old fund as inactive, now that we've closed it
2194                 --
2195                 UPDATE acq.fund
2196                 SET active = FALSE
2197                 WHERE id = roll_fund.old_fund;
2198         END LOOP;
2199 END;
2200 $$ LANGUAGE plpgsql;
2201
2202 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
2203         old_year INTEGER,
2204         user_id INTEGER,
2205         org_unit_id INTEGER
2206 ) RETURNS VOID AS $$
2207 DECLARE
2208 --
2209 new_fund    INT;
2210 new_year    INT := old_year + 1;
2211 org_found   BOOL;
2212 xfer_amount NUMERIC;
2213 roll_fund   RECORD;
2214 deb         RECORD;
2215 detail      RECORD;
2216 --
2217 BEGIN
2218         --
2219         -- Sanity checks
2220         --
2221         IF old_year IS NULL THEN
2222                 RAISE EXCEPTION 'Input year argument is NULL';
2223     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
2224         RAISE EXCEPTION 'Input year is out of range';
2225         END IF;
2226         --
2227         IF user_id IS NULL THEN
2228                 RAISE EXCEPTION 'Input user id argument is NULL';
2229         END IF;
2230         --
2231         IF org_unit_id IS NULL THEN
2232                 RAISE EXCEPTION 'Org unit id argument is NULL';
2233         ELSE
2234                 --
2235                 -- Validate the org unit
2236                 --
2237                 SELECT TRUE
2238                 INTO org_found
2239                 FROM actor.org_unit
2240                 WHERE id = org_unit_id;
2241                 --
2242                 IF org_found IS NULL THEN
2243                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
2244                 END IF;
2245         END IF;
2246         --
2247         -- Loop over the propagable funds to identify the details
2248         -- from the old fund plus the id of the new one, if it exists.
2249         --
2250         FOR roll_fund in
2251         SELECT
2252             oldf.id AS old_fund,
2253             oldf.org,
2254             oldf.name,
2255             oldf.currency_type,
2256             oldf.code,
2257                 oldf.rollover,
2258             newf.id AS new_fund_id
2259         FROM
2260         acq.fund AS oldf
2261         LEFT JOIN acq.fund AS newf
2262                 ON ( oldf.code = newf.code )
2263         WHERE
2264                     oldf.year = old_year
2265                 AND oldf.propagate
2266         AND newf.year = new_year
2267                 AND oldf.org in (
2268                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
2269                 )
2270         LOOP
2271                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
2272                 --
2273                 IF roll_fund.new_fund_id IS NULL THEN
2274                         --
2275                         -- The old fund hasn't been propagated yet.  Propagate it now.
2276                         --
2277                         INSERT INTO acq.fund (
2278                                 org,
2279                                 name,
2280                                 year,
2281                                 currency_type,
2282                                 code,
2283                                 rollover,
2284                                 propagate,
2285                                 balance_warning_percent,
2286                                 balance_stop_percent
2287                         ) VALUES (
2288                                 roll_fund.org,
2289                                 roll_fund.name,
2290                                 new_year,
2291                                 roll_fund.currency_type,
2292                                 roll_fund.code,
2293                                 true,
2294                                 true,
2295                                 roll_fund.balance_warning_percent,
2296                                 roll_fund.balance_stop_percent
2297                         )
2298                         RETURNING id INTO new_fund;
2299                 ELSE
2300                         new_fund = roll_fund.new_fund_id;
2301                 END IF;
2302                 --
2303                 -- Determine the amount to transfer
2304                 --
2305                 SELECT amount
2306                 INTO xfer_amount
2307                 FROM acq.fund_spent_balance
2308                 WHERE fund = roll_fund.old_fund;
2309                 --
2310                 IF xfer_amount <> 0 THEN
2311                         IF roll_fund.rollover THEN
2312                                 --
2313                                 -- Transfer balance from old fund to new
2314                                 --
2315                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
2316                                 --
2317                                 PERFORM acq.transfer_fund(
2318                                         roll_fund.old_fund,
2319                                         xfer_amount,
2320                                         new_fund,
2321                                         xfer_amount,
2322                                         user_id,
2323                                         'Rollover'
2324                                 );
2325                         ELSE
2326                                 --
2327                                 -- Transfer balance from old fund to the void
2328                                 --
2329                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
2330                                 --
2331                                 PERFORM acq.transfer_fund(
2332                                         roll_fund.old_fund,
2333                                         xfer_amount,
2334                                         NULL,
2335                                         NULL,
2336                                         user_id,
2337                                         'Rollover'
2338                                 );
2339                         END IF;
2340                 END IF;
2341                 --
2342                 IF roll_fund.rollover THEN
2343                         --
2344                         -- Move any lineitems from the old fund to the new one
2345                         -- where the associated debit is an encumbrance.
2346                         --
2347                         -- Any other tables tying expenditure details to funds should
2348                         -- receive similar treatment.  At this writing there are none.
2349                         --
2350                         UPDATE acq.lineitem_detail
2351                         SET fund = new_fund
2352                         WHERE
2353                         fund = roll_fund.old_fund -- this condition may be redundant
2354                         AND fund_debit in
2355                         (
2356                                 SELECT id
2357                                 FROM acq.fund_debit
2358                                 WHERE
2359                                 fund = roll_fund.old_fund
2360                                 AND encumbrance
2361                         );
2362                         --
2363                         -- Move encumbrance debits from the old fund to the new fund
2364                         --
2365                         UPDATE acq.fund_debit
2366                         SET fund = new_fund
2367                         wHERE
2368                                 fund = roll_fund.old_fund
2369                                 AND encumbrance;
2370                 END IF;
2371                 --
2372                 -- Mark old fund as inactive, now that we've closed it
2373                 --
2374                 UPDATE acq.fund
2375                 SET active = FALSE
2376                 WHERE id = roll_fund.old_fund;
2377         END LOOP;
2378 END;
2379 $$ LANGUAGE plpgsql;
2380
2381 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
2382     SELECT  funding_source,
2383             SUM(amount) AS amount
2384       FROM  acq.funding_source_credit
2385       GROUP BY 1;
2386
2387 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
2388     SELECT  funding_source,
2389             SUM(a.amount)::NUMERIC(100,2) AS amount
2390     FROM  acq.fund_allocation a
2391     GROUP BY 1;
2392
2393 CREATE OR REPLACE VIEW acq.funding_source_balance AS
2394     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
2395             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
2396       FROM  acq.funding_source_credit_total c
2397             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
2398       GROUP BY 1;
2399
2400 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
2401     SELECT  fund,
2402             SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
2403     FROM acq.fund_allocation a
2404          JOIN acq.fund f ON (a.fund = f.id)
2405          JOIN acq.funding_source s ON (a.funding_source = s.id)
2406     GROUP BY 1;
2407
2408 CREATE OR REPLACE VIEW acq.fund_debit_total AS
2409     SELECT  fund.id AS fund,
2410             fund_debit.encumbrance AS encumbrance,
2411                         SUM( COALESCE( fund_debit.amount, 0 ) ) AS amount
2412       FROM acq.fund AS fund
2413             LEFT JOIN acq.fund_debit AS fund_debit
2414                 ON ( fund.id = fund_debit.fund )
2415       GROUP BY 1,2;
2416
2417 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
2418     SELECT  fund,
2419             SUM(amount) AS amount
2420       FROM  acq.fund_debit_total
2421       WHERE encumbrance IS TRUE
2422       GROUP BY 1;
2423
2424 CREATE OR REPLACE VIEW acq.fund_spent_total AS
2425     SELECT  fund,
2426             SUM(amount) AS amount
2427       FROM  acq.fund_debit_total
2428       WHERE encumbrance IS FALSE
2429       GROUP BY 1;
2430
2431 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
2432     SELECT  c.fund,
2433             c.amount - COALESCE(d.amount,0.0) AS amount
2434       FROM  acq.fund_allocation_total c
2435             LEFT JOIN acq.fund_debit_total d USING (fund);
2436
2437 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
2438     SELECT  c.fund,
2439             c.amount - COALESCE(d.amount,0.0) AS amount
2440       FROM  acq.fund_allocation_total c
2441             LEFT JOIN acq.fund_spent_total d USING (fund);
2442
2443 -- For each fund: the total allocation from all sources, in the
2444 -- currency of the fund (or 0 if there are no allocations)
2445
2446 CREATE VIEW acq.all_fund_allocation_total AS
2447 SELECT
2448     f.id AS fund,
2449     COALESCE( SUM( a.amount * acq.exchange_ratio(
2450         s.currency_type, f.currency_type))::numeric(100,2), 0 )
2451     AS amount
2452 FROM
2453     acq.fund f
2454         LEFT JOIN acq.fund_allocation a
2455             ON a.fund = f.id
2456         LEFT JOIN acq.funding_source s
2457             ON a.funding_source = s.id
2458 GROUP BY
2459     f.id;
2460
2461 -- For every fund: the total encumbrances (or 0 if none),
2462 -- in the currency of the fund.
2463
2464 CREATE VIEW acq.all_fund_encumbrance_total AS
2465 SELECT
2466         f.id AS fund,
2467         COALESCE( encumb.amount, 0 ) AS amount
2468 FROM
2469         acq.fund AS f
2470                 LEFT JOIN (
2471                         SELECT
2472                                 fund,
2473                                 sum( amount ) AS amount
2474                         FROM
2475                                 acq.fund_debit
2476                         WHERE
2477                                 encumbrance
2478                         GROUP BY fund
2479                 ) AS encumb
2480                         ON f.id = encumb.fund;
2481
2482 -- For every fund: the total spent (or 0 if none),
2483 -- in the currency of the fund.
2484
2485 CREATE VIEW acq.all_fund_spent_total AS
2486 SELECT
2487     f.id AS fund,
2488     COALESCE( spent.amount, 0 ) AS amount
2489 FROM
2490     acq.fund AS f
2491         LEFT JOIN (
2492             SELECT
2493                 fund,
2494                 sum( amount ) AS amount
2495             FROM
2496                 acq.fund_debit
2497             WHERE
2498                 NOT encumbrance
2499             GROUP BY fund
2500         ) AS spent
2501             ON f.id = spent.fund;
2502
2503 -- For each fund: the amount not yet spent, in the currency
2504 -- of the fund.  May include encumbrances.
2505
2506 CREATE VIEW acq.all_fund_spent_balance AS
2507 SELECT
2508         c.fund,
2509         c.amount - d.amount AS amount
2510 FROM acq.all_fund_allocation_total c
2511     LEFT JOIN acq.all_fund_spent_total d USING (fund);
2512
2513 -- For each fund: the amount neither spent nor encumbered,
2514 -- in the currency of the fund
2515
2516 CREATE VIEW acq.all_fund_combined_balance AS
2517 SELECT
2518      a.fund,
2519      a.amount - COALESCE( c.amount, 0 ) AS amount
2520 FROM
2521      acq.all_fund_allocation_total a
2522         LEFT OUTER JOIN (
2523             SELECT
2524                 fund,
2525                 SUM( amount ) AS amount
2526             FROM
2527                 acq.fund_debit
2528             GROUP BY
2529                 fund
2530         ) AS c USING ( fund );
2531
2532 CREATE TABLE acq.claim_type (
2533         id             SERIAL           PRIMARY KEY,
2534         org_unit       INT              NOT NULL REFERENCES actor.org_unit(id)
2535                                                  DEFERRABLE INITIALLY DEFERRED,
2536         code           TEXT             NOT NULL,
2537         description    TEXT             NOT NULL,
2538         CONSTRAINT claim_type_once_per_org UNIQUE ( org_unit, code )
2539 );
2540
2541 CREATE TABLE acq.claim (
2542         id             SERIAL           PRIMARY KEY,
2543         type           INT              NOT NULL REFERENCES acq.claim_type
2544                                                  DEFERRABLE INITIALLY DEFERRED,
2545         lineitem_detail BIGINT          NOT NULL REFERENCES acq.lineitem_detail
2546                                                  DEFERRABLE INITIALLY DEFERRED
2547 );
2548
2549 CREATE INDEX claim_lid_idx ON acq.claim( lineitem_detail );
2550
2551 CREATE TABLE acq.claim_event (
2552         id             BIGSERIAL        PRIMARY KEY,
2553         type           INT              NOT NULL REFERENCES acq.claim_event_type
2554                                                  DEFERRABLE INITIALLY DEFERRED,
2555         claim          SERIAL           NOT NULL REFERENCES acq.claim
2556                                                  DEFERRABLE INITIALLY DEFERRED,
2557         event_date     TIMESTAMPTZ      NOT NULL DEFAULT now(),
2558         creator        INT              NOT NULL REFERENCES actor.usr
2559                                                  DEFERRABLE INITIALLY DEFERRED,
2560         note           TEXT
2561 );
2562
2563 CREATE INDEX claim_event_claim_date_idx ON acq.claim_event( claim, event_date );
2564
2565 COMMIT;