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

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