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

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