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