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

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