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

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