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