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