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

   1 DROP SCHEMA acq CASCADE;
   2
   3 BEGIN;
   4
   5 CREATE SCHEMA acq;
   6
   7
   8 -- Tables
   9
  10
  11 CREATE TABLE acq.currency_type (
  12         code    TEXT PRIMARY KEY,
  13         label   TEXT
  14 );
  15
  16 -- Use the ISO 4217 abbreviations for currency codes
  17 INSERT INTO acq.currency_type (code, label) VALUES ('USD','US Dollars');
  18 INSERT INTO acq.currency_type (code, label) VALUES ('CAN','Canadian Dollars');
  19 INSERT INTO acq.currency_type (code, label) VALUES ('EUR','Euros');
  20
  21 CREATE TABLE acq.exchange_rate (
  22     id              SERIAL  PRIMARY KEY,
  23     from_currency   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  24     to_currency     TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  25     ratio           NUMERIC NOT NULL,
  26     CONSTRAINT exchange_rate_from_to_once UNIQUE (from_currency,to_currency)
  27 );
  28
  29 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','CAN',1.2);
  30 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','EUR',0.5);
  31
  32 CREATE TABLE acq.provider (
  33     id                  SERIAL  PRIMARY KEY,
  34     name                TEXT    NOT NULL,
  35     owner               INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  36     currency_type       TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  37     code                TEXT    NOT NULL,
  38     holding_tag         TEXT,
  39     san                 TEXT,
  40     edi_default         INT,          -- REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED
  41     CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner),
  42         CONSTRAINT code_once_per_owner UNIQUE (code, owner)
  43 );
  44
  45 CREATE TABLE acq.provider_holding_subfield_map (
  46     id          SERIAL  PRIMARY KEY,
  47     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  48     name        TEXT    NOT NULL, -- barcode, price, etc
  49     subfield    TEXT    NOT NULL,
  50     CONSTRAINT name_once_per_provider UNIQUE (provider,name)
  51 );
  52
  53 CREATE TABLE acq.provider_address (
  54         id              SERIAL  PRIMARY KEY,
  55         valid           BOOL    NOT NULL DEFAULT TRUE,
  56         address_type    TEXT,
  57     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  58         street1         TEXT    NOT NULL,
  59         street2         TEXT,
  60         city            TEXT    NOT NULL,
  61         county          TEXT,
  62         state           TEXT    NOT NULL,
  63         country         TEXT    NOT NULL,
  64         post_code       TEXT    NOT NULL
  65 );
  66
  67 CREATE TABLE acq.provider_contact (
  68         id              SERIAL  PRIMARY KEY,
  69     provider    INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  70     name    TEXT NULL NULL,
  71     role    TEXT, -- free-form.. e.g. "our sales guy"
  72     email   TEXT,
  73     phone   TEXT
  74 );
  75
  76 CREATE TABLE acq.provider_contact_address (
  77         id                      SERIAL  PRIMARY KEY,
  78         valid                   BOOL    NOT NULL DEFAULT TRUE,
  79         address_type    TEXT,
  80         contact                 INT         NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
  81         street1                 TEXT    NOT NULL,
  82         street2                 TEXT,
  83         city                    TEXT    NOT NULL,
  84         county                  TEXT,
  85         state                   TEXT    NOT NULL,
  86         country                 TEXT    NOT NULL,
  87         post_code               TEXT    NOT NULL
  88 );
  89
  90
  91 CREATE TABLE acq.funding_source (
  92         id              SERIAL  PRIMARY KEY,
  93         name            TEXT    NOT NULL,
  94         owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  95         currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  96         code            TEXT    UNIQUE,
  97         CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
  98 );
  99
 100 CREATE TABLE acq.funding_source_credit (
 101         id      SERIAL     PRIMARY KEY,
 102         funding_source INT      NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
 103         amount         NUMERIC  NOT NULL,
 104         note           TEXT,
 105         deadline_date  TIMESTAMPTZ,
 106         effective_date TIMESTAMPTZ NOT NULL default now()
 107 );
 108
 109 CREATE VIEW acq.ordered_funding_source_credit AS
 110     SELECT
 111         CASE WHEN deadline_date IS NULL THEN
 112             2
 113         ELSE
 114             1
 115         END AS sort_priority,
 116         CASE WHEN deadline_date IS NULL THEN
 117             effective_date
 118         ELSE
 119             deadline_date
 120         END AS sort_date,
 121         id,
 122         funding_source,
 123         amount,
 124         note
 125     FROM
 126         acq.funding_source_credit;
 127
 128 COMMENT ON VIEW acq.ordered_funding_source_credit IS $$
 129 /*
 130  * Copyright (C) 2009  Georgia Public Library Service
 131  * Scott McKellar <scott@gmail.com>
 132  *
 133  * The acq.ordered_funding_source_credit view is a prioritized
 134  * ordering of funding source credits.  When ordered by the first
 135  * three columns, this view defines the order in which the various
 136  * credits are to be tapped for spending, subject to the allocations
 137  * in the acq.fund_allocation table.
 138  *
 139  * The first column reflects the principle that we should spend
 140  * money with deadlines before spending money without deadlines.
 141  *
 142  * The second column reflects the principle that we should spend the
 143  * oldest money first.  For money with deadlines, that means that we
 144  * spend first from the credit with the earliest deadline.  For
 145  * money without deadlines, we spend first from the credit with the
 146  * earliest effective date.
 147  *
 148  * The third column is a tie breaker to ensure a consistent
 149  * ordering.
 150  *
 151  * ****
 152  *
 153  * This program is free software; you can redistribute it and/or
 154  * modify it under the terms of the GNU General Public License
 155  * as published by the Free Software Foundation; either version 2
 156  * of the License, or (at your option) any later version.
 157  *
 158  * This program is distributed in the hope that it will be useful,
 159  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 160  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 161  * GNU General Public License for more details.
 162  */
 163 $$;
 164
 165 CREATE TABLE acq.fund (
 166     id              SERIAL  PRIMARY KEY,
 167     org             INT     NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 168     name            TEXT    NOT NULL,
 169     year            INT     NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
 170     currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 171     code            TEXT,
 172         rollover        BOOL    NOT NULL DEFAULT FALSE,
 173         propagate       BOOL    NOT NULL DEFAULT TRUE,
 174     CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
 175     CONSTRAINT code_once_per_org_year UNIQUE (org, code, year),
 176         CONSTRAINT acq_fund_rollover_implies_propagate CHECK ( propagate OR NOT rollover )
 177 );
 178
 179 CREATE TABLE acq.fund_debit (
 180         id                      SERIAL  PRIMARY KEY,
 181         fund                    INT     NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 182         origin_amount           NUMERIC NOT NULL,  -- pre-exchange-rate amount
 183         origin_currency_type    TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 184         amount                  NUMERIC NOT NULL,
 185         encumbrance             BOOL    NOT NULL DEFAULT TRUE,
 186         debit_type              TEXT    NOT NULL,
 187         xfer_destination        INT     REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 188         create_time     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 189 );
 190
 191 CREATE TABLE acq.fund_allocation (
 192     id          SERIAL  PRIMARY KEY,
 193     funding_source        INT     NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 194     fund        INT     NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 195     amount      NUMERIC NOT NULL,
 196     allocator   INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 197     note        TEXT,
 198         create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 199 );
 200 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
 201
 202 CREATE TABLE acq.picklist (
 203         id              SERIAL                          PRIMARY KEY,
 204         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 205         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 206         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 207         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 208         name            TEXT                            NOT NULL,
 209         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 210         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 211         CONSTRAINT name_once_per_owner UNIQUE (name,owner)
 212 );
 213 CREATE INDEX acq_picklist_owner_idx   ON acq.picklist ( owner );
 214 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
 215 CREATE INDEX acq_picklist_editor_idx  ON acq.picklist ( editor );
 216
 217 CREATE TABLE acq.purchase_order (
 218         id              SERIAL                          PRIMARY KEY,
 219         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 220         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 221         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 222         ordering_agency INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 223         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 224         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 225         provider        INT                             NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 226         state                   TEXT                                    NOT NULL DEFAULT 'new',
 227         order_date              TIMESTAMP WITH TIME ZONE,
 228         name                    TEXT                                    NOT NULL
 229 );
 230 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
 231 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
 232 CREATE INDEX po_state_idx ON acq.purchase_order (state);
 233 CREATE INDEX po_creator_idx  ON acq.purchase_order ( creator );
 234 CREATE INDEX po_editor_idx   ON acq.purchase_order ( editor );
 235 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
 236
 237 -- The name should default to the id, as text.  We can't reference a column
 238 -- in a DEFAULT clause, so we use a trigger:
 239
 240 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
 241 AS $$
 242 BEGIN
 243         IF NEW.name IS NULL THEN
 244                 NEW.name := NEW.id::TEXT;
 245         END IF;
 246
 247         RETURN NEW;
 248 END;
 249 $$ LANGUAGE PLPGSQL;
 250
 251 CREATE TRIGGER po_name_default_trg
 252   BEFORE INSERT OR UPDATE ON acq.purchase_order
 253   FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
 254
 255 -- The order name should be unique for a given ordering agency on a given order date
 256 -- (truncated to midnight), but only where the order_date is not NULL.  Conceptually
 257 -- this rule requires a check constraint with a subquery.  However you can't have a
 258 -- subquery in a CHECK constraint, so we fake it with a trigger.
 259
 260 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
 261 AS $$
 262 DECLARE
 263         collision INT;
 264 BEGIN
 265         --
 266         -- If order_date is not null, then make sure we don't have a collision
 267         -- on order_date (truncated to day), org, and name
 268         --
 269         IF NEW.order_date IS NULL THEN
 270                 RETURN NEW;
 271         END IF;
 272         --
 273         -- In the WHERE clause, we compare the order_dates without regard to time of day.
 274         -- We use a pair of inequalities instead of comparing truncated dates so that the
 275         -- query can do an indexed range scan.
 276         --
 277         SELECT 1 INTO collision
 278         FROM acq.purchase_order
 279         WHERE
 280                 ordering_agency = NEW.ordering_agency
 281                 AND name = NEW.name
 282                 AND order_date >= date_trunc( 'day', NEW.order_date )
 283                 AND order_date <  date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
 284                 AND id <> NEW.id;
 285         --
 286         IF collision IS NULL THEN
 287                 -- okay, no collision
 288                 RETURN NEW;
 289         ELSE
 290                 -- collision; nip it in the bud
 291                 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
 292                         NEW.ordering_agency, NEW.order_date, NEW.name;
 293         END IF;
 294 END;
 295 $$ LANGUAGE PLPGSQL;
 296
 297 CREATE TRIGGER po_org_name_date_unique_trg
 298   BEFORE INSERT OR UPDATE ON acq.purchase_order
 299   FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
 300
 301 CREATE TABLE acq.po_note (
 302         id              SERIAL                          PRIMARY KEY,
 303         purchase_order  INT                             NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 304         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 305         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 306         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 307         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 308         value           TEXT                            NOT NULL
 309 );
 310 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
 311 CREATE INDEX acq_po_note_creator_idx  ON acq.po_note ( creator );
 312 CREATE INDEX acq_po_note_editor_idx   ON acq.po_note ( editor );
 313
 314 CREATE TABLE acq.lineitem (
 315         id                  BIGSERIAL                   PRIMARY KEY,
 316         creator             INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 317         editor              INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 318         selector            INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 319         provider            INT                         REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 320         purchase_order      INT                         REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 321         picklist            INT                         REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
 322         expected_recv_time  TIMESTAMP WITH TIME ZONE,
 323         create_time         TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 324         edit_time           TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 325         marc                TEXT                        NOT NULL,
 326         eg_bib_id           INT                         REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
 327         source_label        TEXT,
 328         item_count          INT                         NOT NULL DEFAULT 0,
 329         state               TEXT                        NOT NULL DEFAULT 'new',
 330     CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
 331 );
 332 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
 333 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
 334 CREATE INDEX li_creator_idx   ON acq.lineitem ( creator );
 335 CREATE INDEX li_editor_idx    ON acq.lineitem ( editor );
 336 CREATE INDEX li_selector_idx  ON acq.lineitem ( selector );
 337
 338 CREATE TABLE acq.lineitem_note (
 339         id              SERIAL                          PRIMARY KEY,
 340         lineitem        INT                             NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 341         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 342         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 343         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 344         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 345         value           TEXT                            NOT NULL
 346 );
 347 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
 348 CREATE INDEX li_note_creator_idx  ON acq.lineitem_note ( creator );
 349 CREATE INDEX li_note_editor_idx   ON acq.lineitem_note ( editor );
 350
 351 CREATE TABLE acq.lineitem_detail (
 352     id          BIGSERIAL       PRIMARY KEY,
 353     lineitem    INT         NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 354     fund        INT         REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 355     fund_debit  INT         REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
 356     eg_copy_id  BIGINT      REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 357     barcode     TEXT,
 358     cn_label    TEXT,
 359     note        TEXT,
 360     collection_code TEXT,
 361     circ_modifier   TEXT    REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 362     owning_lib  INT         REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 363     location    INT         REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 364     recv_time   TIMESTAMP WITH TIME ZONE
 365 );
 366
 367 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
 368
 369 CREATE TABLE acq.lineitem_attr_definition (
 370         id              BIGSERIAL       PRIMARY KEY,
 371         code            TEXT            NOT NULL,
 372         description     TEXT            NOT NULL,
 373         remove          TEXT            NOT NULL DEFAULT '',
 374         ident           BOOL            NOT NULL DEFAULT FALSE
 375 );
 376
 377 CREATE TABLE acq.lineitem_marc_attr_definition (
 378         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 379         xpath           TEXT            NOT NULL
 380 ) INHERITS (acq.lineitem_attr_definition);
 381
 382 CREATE TABLE acq.lineitem_provider_attr_definition (
 383         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 384         xpath           TEXT            NOT NULL,
 385         provider        INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
 386 ) INHERITS (acq.lineitem_attr_definition);
 387
 388 CREATE TABLE acq.lineitem_generated_attr_definition (
 389         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 390         xpath           TEXT            NOT NULL
 391 ) INHERITS (acq.lineitem_attr_definition);
 392
 393 CREATE TABLE acq.lineitem_usr_attr_definition (
 394         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 395         usr             INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
 396 ) INHERITS (acq.lineitem_attr_definition);
 397 CREATE INDEX li_usr_attr_def_usr_idx  ON acq.lineitem_usr_attr_definition ( usr );
 398
 399 CREATE TABLE acq.lineitem_local_attr_definition (
 400         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
 401 ) INHERITS (acq.lineitem_attr_definition);
 402
 403 CREATE TABLE acq.lineitem_attr (
 404         id              BIGSERIAL       PRIMARY KEY,
 405         definition      BIGINT          NOT NULL,
 406         lineitem        BIGINT          NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 407         attr_type       TEXT            NOT NULL,
 408         attr_name       TEXT            NOT NULL,
 409         attr_value      TEXT            NOT NULL
 410 );
 411
 412 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
 413 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
 414 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
 415
 416
 417 -- Seed data
 418
 419
 420 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
 421 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)]');
 422 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
 423 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
 424 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 425 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 426 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
 427 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
 428 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
 429 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
 430 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
 431
 432 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
 433
 434
 435 CREATE TABLE acq.distribution_formula (
 436         id              SERIAL PRIMARY KEY,
 437         owner   INT NOT NULL
 438                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 439         name    TEXT NOT NULL,
 440         skip_count      INT NOT NULL DEFAULT 0,
 441         CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
 442 );
 443
 444 CREATE TABLE acq.distribution_formula_entry (
 445         id                      SERIAL PRIMARY KEY,
 446         formula         INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
 447                                 ON DELETE CASCADE
 448                                 DEFERRABLE INITIALLY DEFERRED,
 449         position        INTEGER NOT NULL,
 450         item_count      INTEGER NOT NULL,
 451         owning_lib      INTEGER REFERENCES actor.org_unit(id)
 452                                 DEFERRABLE INITIALLY DEFERRED,
 453         location        INTEGER REFERENCES asset.copy_location(id),
 454         CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
 455         CONSTRAINT acqdfe_must_be_somewhere
 456                                 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
 457 );
 458
 459 CREATE TABLE acq.fund_tag (
 460         id              SERIAL PRIMARY KEY,
 461         owner   INT NOT NULL
 462                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 463         name    TEXT NOT NULL,
 464         CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
 465 );
 466
 467 CREATE TABLE acq.fund_tag_map (
 468         id                      SERIAL PRIMARY KEY,
 469         fund            INTEGER NOT NULL REFERENCES acq.fund(id)
 470                                 DEFERRABLE INITIALLY DEFERRED,
 471         tag         INTEGER REFERENCES acq.fund_tag(id)
 472                                 ON DELETE CASCADE
 473                                 DEFERRABLE INITIALLY DEFERRED,
 474         CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
 475 );
 476
 477 CREATE TABLE acq.fund_transfer (
 478     id               SERIAL         PRIMARY KEY,
 479     src_fund         INT            NOT NULL REFERENCES acq.fund( id )
 480                                     DEFERRABLE INITIALLY DEFERRED,
 481     src_amount       NUMERIC        NOT NULL,
 482     dest_fund        INT            NOT NULL REFERENCES acq.fund( id )
 483                                     DEFERRABLE INITIALLY DEFERRED,
 484     dest_amount      NUMERIC        NOT NULL,
 485     transfer_time    TIMESTAMPTZ    NOT NULL DEFAULT now(),
 486     transfer_user    INT            NOT NULL REFERENCES actor.usr( id )
 487                                     DEFERRABLE INITIALLY DEFERRED,
 488     note             TEXT,
 489         funding_source_credit INT       NOT NULL REFERENCES acq.funding_source_credit( id )
 490                                     DEFERRABLE INITIALLY DEFERRED
 491 );
 492
 493 CREATE INDEX acqftr_usr_idx
 494 ON acq.fund_transfer( transfer_user );
 495
 496 COMMENT ON TABLE acq.fund_transfer IS $$
 497 /*
 498  * Copyright (C) 2009  Georgia Public Library Service
 499  * Scott McKellar <scott@esilibrary.com>
 500  *
 501  * Fund Transfer
 502  *
 503  * Each row represents the transfer of money from a source fund
 504  * to a destination fund.  There should be corresponding entries
 505  * in acq.fund_allocation.  The purpose of acq.fund_transfer is
 506  * to record how much money moved from which fund to which other
 507  * fund.
 508  *
 509  * The presence of two amount fields, rather than one, reflects
 510  * the possibility that the two funds are denominated in different
 511  * currencies.  If they use the same currency type, the two
 512  * amounts should be the same.
 513  *
 514  * ****
 515  *
 516  * This program is free software; you can redistribute it and/or
 517  * modify it under the terms of the GNU General Public License
 518  * as published by the Free Software Foundation; either version 2
 519  * of the License, or (at your option) any later version.
 520  *
 521  * This program is distributed in the hope that it will be useful,
 522  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 523  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 524  * GNU General Public License for more details.
 525  */
 526 $$;
 527
 528 CREATE TABLE acq.fiscal_calendar (
 529         id              SERIAL         PRIMARY KEY,
 530         name            TEXT           NOT NULL
 531 );
 532
 533 -- Create a default calendar (though we don't specify its contents).
 534 -- Create a foreign key in actor.org_unit, initially pointing to
 535 -- the default calendar.
 536
 537 INSERT INTO acq.fiscal_calendar (
 538     name
 539 ) VALUES (
 540
 541     'Default'
 542 );
 543
 544 ALTER TABLE actor.org_unit
 545 ADD COLUMN fiscal_calendar INT NOT NULL
 546     REFERENCES acq.fiscal_calendar( id )
 547     DEFERRABLE INITIALLY DEFERRED
 548     DEFAULT 1;
 549
 550 CREATE TABLE acq.fiscal_year (
 551         id              SERIAL         PRIMARY KEY,
 552         calendar        INT            NOT NULL
 553                                        REFERENCES acq.fiscal_calendar
 554                                        ON DELETE CASCADE
 555                                        DEFERRABLE INITIALLY DEFERRED,
 556         year            INT            NOT NULL,
 557         year_begin      TIMESTAMPTZ    NOT NULL,
 558         year_end        TIMESTAMPTZ    NOT NULL,
 559         CONSTRAINT acq_fy_logical_key  UNIQUE ( calendar, year ),
 560     CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
 561 );
 562
 563 CREATE TABLE acq.edi_account (      -- similar tables can extend remote_account for other parts of EG
 564     provider    INT     NOT NULL REFERENCES acq.provider          (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 565     in_dir      TEXT    -- incoming messages dir (probably different than config.remote_account.path, the outgoing dir)
 566 ) INHERITS (config.remote_account);
 567
 568 -- We need a UNIQUE constraint here also, to support the FK from acq.provider.edi_default
 569 ALTER TABLE acq.edi_account ADD CONSTRAINT acq_edi_account_id_unique UNIQUE (id);
 570
 571 -- Note below that the primary key is NOT a SERIAL type.  We will periodically truncate and rebuild
 572 -- the table, assigning ids programmatically instead of using a sequence.
 573 CREATE TABLE acq.debit_attribution (
 574     id                     INT         NOT NULL PRIMARY KEY,
 575     fund_debit             INT         NOT NULL
 576                                        REFERENCES acq.fund_debit
 577                                        DEFERRABLE INITIALLY DEFERRED,
 578     debit_amount           NUMERIC     NOT NULL,
 579     funding_source_credit  INT         REFERENCES acq.funding_source_credit
 580                                        DEFERRABLE INITIALLY DEFERRED,
 581     credit_amount          NUMERIC
 582 );
 583
 584 CREATE INDEX acq_attribution_debit_idx
 585     ON acq.debit_attribution( fund_debit );
 586
 587 CREATE INDEX acq_attribution_credit_idx
 588     ON acq.debit_attribution( funding_source_credit );
 589
 590 -- Functions
 591
 592 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 593 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 594 DECLARE
 595     counter INT;
 596     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 597 BEGIN
 598
 599     SELECT  COUNT(*) INTO counter
 600       FROM  xpath_table(
 601                 'id',
 602                 'marc',
 603                 'acq.lineitem',
 604                 '//*[@tag="' || tag || '"]',
 605                 'id=' || lineitem
 606             ) as t(i int,c text);
 607
 608     FOR i IN 1 .. counter LOOP
 609         FOR lida IN
 610             SELECT  *
 611               FROM  (   SELECT  id,i,t,v
 612                           FROM  xpath_table(
 613                                     'id',
 614                                     'marc',
 615                                     'acq.lineitem',
 616                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 617                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 618                                     'id=' || lineitem
 619                                 ) as t(id int,t text,v text)
 620                     )x
 621         LOOP
 622             RETURN NEXT lida;
 623         END LOOP;
 624     END LOOP;
 625
 626     RETURN;
 627 END;
 628 $$ LANGUAGE PLPGSQL;
 629
 630 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 631 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 632 DECLARE
 633     prov_i  INT;
 634     tag_t   TEXT;
 635     lida    acq.flat_lineitem_detail%ROWTYPE;
 636 BEGIN
 637     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 638     IF NOT FOUND THEN RETURN; END IF;
 639
 640     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 641     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 642
 643     FOR lida IN
 644         SELECT  lineitem_i,
 645                 h.holding,
 646                 a.name,
 647                 h.data
 648           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 649                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 650           WHERE a.provider = prov_i
 651     LOOP
 652         RETURN NEXT lida;
 653     END LOOP;
 654
 655     RETURN;
 656 END;
 657 $$ LANGUAGE PLPGSQL;
 658
 659 -- select * from acq.extract_provider_holding_data(699);
 660
 661 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 662         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 663 $$ LANGUAGE SQL;
 664
 665 /*
 666 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 667         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 668 $$ LANGUAGE SQL;
 669
 670 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 671         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 672 $$ LANGUAGE SQL;
 673 */
 674 -- For example:
 675 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 676
 677 /*
 678 Suggested vendor fields:
 679         vendor_price
 680         vendor_currency
 681         vendor_avail
 682         vendor_po
 683         vendor_identifier
 684 */
 685
 686 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
 687 DECLARE
 688         value           TEXT;
 689         atype           TEXT;
 690         prov            INT;
 691         adef            RECORD;
 692         xpath_string    TEXT;
 693 BEGIN
 694         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 695
 696                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 697
 698                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
 699                         IF (atype = 'lineitem_provider_attr_definition') THEN
 700                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 701                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
 702                         END IF;
 703
 704                         IF (atype = 'lineitem_provider_attr_definition') THEN
 705                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 706                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
 707                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
 708                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
 709                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
 710                         END IF;
 711
 712                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
 713
 714                         IF (value IS NOT NULL AND value <> '') THEN
 715                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
 716                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
 717                         END IF;
 718
 719                 END IF;
 720
 721         END LOOP;
 722
 723         RETURN NULL;
 724 END;
 725 $$ LANGUAGE PLPGSQL;
 726
 727 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
 728 BEGIN
 729         IF TG_OP = 'UPDATE' THEN
 730                 DELETE FROM acq.lineitem_attr
 731                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
 732                 RETURN NEW;
 733         ELSE
 734                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
 735                 RETURN OLD;
 736         END IF;
 737 END;
 738 $$ LANGUAGE PLPGSQL;
 739
 740 CREATE TRIGGER cleanup_lineitem_trigger
 741         BEFORE UPDATE OR DELETE ON acq.lineitem
 742         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
 743
 744 CREATE TRIGGER ingest_lineitem_trigger
 745         AFTER INSERT OR UPDATE ON acq.lineitem
 746         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
 747
 748 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
 749 DECLARE
 750     rat NUMERIC;
 751 BEGIN
 752     IF from_ex = to_ex THEN
 753         RETURN 1.0;
 754     END IF;
 755
 756     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
 757
 758     IF FOUND THEN
 759         RETURN rat;
 760     ELSE
 761         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
 762         IF FOUND THEN
 763             RETURN 1.0/rat;
 764         END IF;
 765     END IF;
 766
 767     RETURN NULL;
 768
 769 END;
 770 $$ LANGUAGE PLPGSQL;
 771
 772 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
 773     SELECT $3 * acq.exchange_ratio($1, $2);
 774 $$ LANGUAGE SQL;
 775
 776 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
 777 /*
 778         Examine the acq.fiscal_year table, comparing successive years.
 779         Report any inconsistencies, i.e. years that overlap, have gaps
 780     between them, or are out of sequence.
 781 */
 782 RETURNS SETOF RECORD AS $$
 783 DECLARE
 784         first_row  BOOLEAN;
 785         curr_year  RECORD;
 786         prev_year  RECORD;
 787         return_rec RECORD;
 788 BEGIN
 789         first_row := true;
 790         FOR curr_year in
 791                 SELECT
 792                         id,
 793                         calendar,
 794                         year,
 795                         year_begin,
 796                         year_end
 797                 FROM
 798                         acq.fiscal_year
 799                 ORDER BY
 800                         calendar,
 801                         year_begin
 802         LOOP
 803                 --
 804                 IF first_row THEN
 805                         first_row := FALSE;
 806                 ELSIF curr_year.calendar    = prev_year.calendar THEN
 807                         IF curr_year.year_begin > prev_year.year_end THEN
 808                                 -- This ugly kludge works around the fact that older
 809                                 -- versions of PostgreSQL don't support RETURN QUERY SELECT
 810                                 FOR return_rec IN SELECT
 811                                         prev_year.id,
 812                                         prev_year.year,
 813                                         'Gap between fiscal years'::TEXT
 814                                 LOOP
 815                                         RETURN NEXT return_rec;
 816                                 END LOOP;
 817                         ELSIF curr_year.year_begin < prev_year.year_end THEN
 818                                 FOR return_rec IN SELECT
 819                                         prev_year.id,
 820                                         prev_year.year,
 821                                         'Overlapping fiscal years'::TEXT
 822                                 LOOP
 823                                         RETURN NEXT return_rec;
 824                                 END LOOP;
 825                         ELSIF curr_year.year < prev_year.year THEN
 826                                 FOR return_rec IN SELECT
 827                                         prev_year.id,
 828                                         prev_year.year,
 829                                         'Fiscal years out of order'::TEXT
 830                                 LOOP
 831                                         RETURN NEXT return_rec;
 832                                 END LOOP;
 833                         END IF;
 834                 END IF;
 835                 --
 836                 prev_year := curr_year;
 837         END LOOP;
 838         --
 839         RETURN;
 840 END;
 841 $$ LANGUAGE plpgsql;
 842
 843 -- The following three types are intended for internal use
 844 -- by the acq.attribute_debits() function.
 845
 846 -- For a combination of fund and funding_source: How much that source
 847 -- allocated to that fund, and how much is left.
 848 CREATE TYPE acq.fund_source_balance AS
 849 (
 850     fund       INT,        -- fund id
 851     source     INT,        -- funding source id
 852     amount     NUMERIC,    -- original total allocation
 853     balance    NUMERIC     -- what's left
 854 );
 855
 856 -- For a fund: a list of funding_source_credits to which
 857 -- the fund's debits can be attributed.
 858 CREATE TYPE acq.fund_credits AS
 859 (
 860     fund       INT,        -- fund id
 861     credit_count INT,      -- number of entries in the following array
 862     credit     INT []      -- funding source credits from which a fund may draw
 863 );
 864
 865 -- For a funding source credit: the funding source, the currency type
 866 -- of the funding source, and the current balance.
 867 CREATE TYPE acq.funding_source_credit_balance AS
 868 (
 869     credit_id       INT,        -- if for funding source credit
 870     funding_source  INT,        -- id of funding source
 871     currency_type   TEXT,       -- currency type of funding source
 872     amount          NUMERIC,    -- original amount of credit
 873     balance         NUMERIC     -- how much is left
 874 );
 875
 876 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
 877 /*
 878         Function to attribute expenditures and encumbrances to funding source credits,
 879         and thereby to funding sources.
 880
 881         Read the debits in chonological order, attributing each one to one or
 882         more funding source credits.  Constraints:
 883
 884         1. Don't attribute more to a credit than the amount of the credit.
 885
 886         2. For a given fund, don't attribute more to a funding source than the
 887         source has allocated to that fund.
 888
 889         3. Attribute debits to credits with deadlines before attributing them to
 890         credits without deadlines.  Otherwise attribute to the earliest credits
 891         first, based on the deadline date when present, or on the effective date
 892         when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
 893         This ordering is defined by an ORDER BY clause on the view
 894         acq.ordered_funding_source_credit.
 895
 896         Start by truncating the table acq.debit_attribution.  Then insert a row
 897         into that table for each attribution.  If a debit cannot be fully
 898         attributed, insert a row for the unattributable balance, with the
 899         funding_source_credit and credit_amount columns NULL.
 900 */
 901 DECLARE
 902         curr_fund_src_bal   acq.fund_source_balance;
 903         fund_source_balance acq.fund_source_balance [];
 904         curr_fund_cr_list   acq.fund_credits;
 905         fund_credit_list    acq.fund_credits [];
 906         curr_cr_bal         acq.funding_source_credit_balance;
 907         cr_bal              acq.funding_source_credit_balance[];
 908         crl_max             INT;     -- Number of entries in fund_credits[]
 909         fcr_max             INT;     -- Number of entries in a credit list
 910         fsa_max             INT;     -- Number of entries in fund_source_balance[]
 911         fscr_max            INT;     -- Number of entries in cr_bal[]
 912         fsa                 RECORD;
 913         fc                  RECORD;
 914         sc                  RECORD;
 915         cr                  RECORD;
 916         --
 917         -- Used exclusively in the main loop:
 918         --
 919         deb                 RECORD;
 920         debit_balance       NUMERIC;  -- amount left to attribute for current debit
 921         conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
 922         attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
 923         conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
 924         conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
 925         conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
 926         fund_found          BOOL;
 927         credit_found        BOOL;
 928         alloc_found         BOOL;
 929         curr_cred_x         INT;   -- index of current credit in cr_bal[]
 930         curr_fund_src_x     INT;   -- index of current credit in fund_source_balance[]
 931         attrib_count        INT;   -- populates id of acq.debit_attribution
 932 BEGIN
 933         --
 934         -- Load an array.  For each combination of fund and funding source, load an
 935         -- entry with the total amount allocated to that fund by that source.  This
 936         -- sum may reflect transfers as well as original allocations.  The balance
 937         -- is initially equal to the original amount.
 938         --
 939         fsa_max := 0;
 940         FOR fsa IN
 941                 SELECT
 942                         fund AS fund,
 943                         funding_source AS source,
 944                         sum( amount ) AS amount
 945                 FROM
 946                         acq.fund_allocation
 947                 GROUP BY
 948                         fund,
 949                         funding_source
 950                 HAVING
 951                         sum( amount ) <> 0
 952                 ORDER BY
 953                         fund,
 954                         funding_source
 955         LOOP
 956                 IF fsa.amount > 0 THEN
 957                         --
 958                         -- Add this fund/source combination to the list
 959                         --
 960                         curr_fund_src_bal.fund    := fsa.fund;
 961                         curr_fund_src_bal.source  := fsa.source;
 962                         curr_fund_src_bal.amount  := fsa.amount;
 963                         curr_fund_src_bal.balance := fsa.amount;
 964                         --
 965                         fsa_max := fsa_max + 1;
 966                         fund_source_balance[ fsa_max ] := curr_fund_src_bal;
 967                 END IF;
 968                 --
 969         END LOOP;
 970         -------------------------------------------------------------------------------
 971         --
 972         -- Load another array.  For each fund, load a list of funding
 973         -- source credits from which that fund can get money.
 974         --
 975         crl_max := 0;
 976         FOR fc IN
 977                 SELECT DISTINCT fund
 978                 FROM acq.fund_allocation
 979                 ORDER BY fund
 980         LOOP                  -- Loop over the funds
 981                 --
 982                 -- Initialize the array entry
 983                 --
 984                 curr_fund_cr_list.fund := fc.fund;
 985                 fcr_max := 0;
 986                 curr_fund_cr_list.credit := NULL;
 987                 --
 988                 -- Make a list of the funding source credits
 989                 -- applicable to this fund
 990                 --
 991                 FOR sc IN
 992                         SELECT
 993                                 ofsc.id
 994                         FROM
 995                                 acq.ordered_funding_source_credit AS ofsc
 996                         WHERE
 997                                 ofsc.funding_source IN
 998                                 (
 999                                         SELECT funding_source
1000                                         FROM acq.fund_allocation
1001                                         WHERE fund = fc.fund
1002                                 )
1003                 ORDER BY
1004                     ofsc.sort_priority,
1005                     ofsc.sort_date,
1006                     ofsc.id
1007                 LOOP                        -- Add each credit to the list
1008                         fcr_max := fcr_max + 1;
1009                         curr_fund_cr_list.credit[ fcr_max ] := sc.id;
1010                         --
1011                 END LOOP;
1012                 --
1013                 -- If there are any credits applicable to this fund,
1014                 -- add the credit list to the list of credit lists.
1015                 --
1016                 IF fcr_max > 0 THEN
1017                         curr_fund_cr_list.credit_count := fcr_max;
1018                         crl_max := crl_max + 1;
1019                         fund_credit_list[ crl_max ] := curr_fund_cr_list;
1020                 END IF;
1021                 --
1022         END LOOP;
1023         -------------------------------------------------------------------------------
1024         --
1025         -- Load yet another array.  This one is a list of funding source credits, with
1026         -- their balances.
1027         --
1028         fscr_max := 0;
1029     FOR cr in
1030         SELECT
1031             ofsc.id,
1032             ofsc.funding_source,
1033             ofsc.amount,
1034             fs.currency_type
1035         FROM
1036             acq.ordered_funding_source_credit AS ofsc,
1037             acq.funding_source fs
1038         WHERE
1039             ofsc.funding_source = fs.id
1040        ORDER BY
1041             ofsc.sort_priority,
1042             ofsc.sort_date,
1043             ofsc.id
1044         LOOP
1045                 --
1046                 curr_cr_bal.credit_id      := cr.id;
1047                 curr_cr_bal.funding_source := cr.funding_source;
1048                 curr_cr_bal.amount         := cr.amount;
1049                 curr_cr_bal.balance        := cr.amount;
1050                 curr_cr_bal.currency_type  := cr.currency_type;
1051                 --
1052                 fscr_max := fscr_max + 1;
1053                 cr_bal[ fscr_max ] := curr_cr_bal;
1054         END LOOP;
1055         --
1056         -------------------------------------------------------------------------------
1057         --
1058         -- Now that we have loaded the lookup tables: loop through the debits,
1059         -- attributing each one to one or more funding source credits.
1060         --
1061         truncate table acq.debit_attribution;
1062         --
1063         attrib_count := 0;
1064         FOR deb in
1065                 SELECT
1066                         fd.id,
1067                         fd.fund,
1068                         fd.amount,
1069                         f.currency_type,
1070                         fd.encumbrance
1071                 FROM
1072                         acq.fund_debit fd,
1073                         acq.fund f
1074                 WHERE
1075                         fd.fund = f.id
1076                 ORDER BY
1077                         id
1078         LOOP
1079                 debit_balance := deb.amount;
1080                 --
1081                 -- Find the list of credits applicable to this fund
1082                 --
1083                 fund_found := false;
1084                 FOR i in 1 .. crl_max LOOP
1085                         IF fund_credit_list[ i ].fund = deb.fund THEN
1086                                 curr_fund_cr_list := fund_credit_list[ i ];
1087                                 fund_found := true;
1088                                 exit;
1089                         END IF;
1090                 END LOOP;
1091                 --
1092                 -- If we didn't find an entry for this fund, then there are no applicable
1093                 -- funding sources for this fund, and the debit is hence unattributable.
1094                 --
1095                 -- If we did find an entry for this fund, then we have a list of funding source
1096                 -- credits that we can apply to it.  Go through that list and attribute the
1097                 -- debit accordingly.
1098                 --
1099                 IF fund_found THEN
1100                         --
1101                         -- For each applicable credit
1102                         --
1103                         FOR i in 1 .. curr_fund_cr_list.credit_count LOOP
1104                                 --
1105                                 -- Find the entry in the credit list for this credit.  If you find it but
1106                                 -- it has a zero balance, it's not useful, so treat it as if you didn't
1107                                 -- find it.
1108                                 --
1109                                 credit_found := false;
1110                                 FOR j in 1 .. fscr_max LOOP
1111                                         IF cr_bal[ j ].credit_id = curr_fund_cr_list.credit[i] THEN
1112                                                 curr_cr_bal  := cr_bal[ j ];
1113                                                 IF curr_cr_bal.balance <> 0 THEN
1114                                                         curr_cred_x  := j;
1115                                                         credit_found := true;
1116                                                 END IF;
1117                                                 EXIT;
1118                                         END IF;
1119                                 END LOOP;
1120                                 --
1121                                 IF NOT credit_found THEN
1122                                         --
1123                                         -- This credit is not usable; try the next one.
1124                                         --
1125                                         CONTINUE;
1126                                 END IF;
1127                                 --
1128                                 -- At this point we have an applicable credit with some money left.
1129                                 -- Now see if the relevant funding_source has any money left.
1130                                 --
1131                                 -- Search the fund/source list for an entry with this combination
1132                                 -- of fund and source.  If you find such an entry, but it has a zero
1133                                 -- balance, then it's not useful, so treat it as unfound.
1134                                 --
1135                                 alloc_found := false;
1136                                 FOR j in 1 .. fsa_max LOOP
1137                                         IF fund_source_balance[ j ].fund = deb.fund
1138                                         AND fund_source_balance[ j ].source = curr_cr_bal.funding_source THEN
1139                                                 curr_fund_src_bal := fund_source_balance[ j ];
1140                                                 IF curr_fund_src_bal.balance <> 0 THEN
1141                                                         curr_fund_src_x := j;
1142                                                         alloc_found := true;
1143                                                 END IF;
1144                                                 EXIT;
1145                                         END IF;
1146                                 END LOOP;
1147                                 --
1148                                 IF NOT alloc_found THEN
1149                                         --
1150                                         -- This fund/source doesn't exist is already exhausted,
1151                                         -- so we can't use this credit.  Go on to the next on.
1152                                         --
1153                                         CONTINUE;
1154                                 END IF;
1155                                 --
1156                                 -- Convert the available balances to the currency of the fund
1157                                 --
1158                                 conv_alloc_balance := curr_fund_src_bal.balance * acq.exchange_ratio(
1159                                         curr_cr_bal.currency_type, deb.currency_type );
1160                                 conv_cred_balance := curr_cr_bal.balance * acq.exchange_ratio(
1161                                         curr_cr_bal.currency_type, deb.currency_type );
1162                                 --
1163                                 -- Determine how much we can attribute to this credit: the minimum
1164                                 -- of the debit amount, the fund/source balance, and the
1165                                 -- credit balance
1166                                 --
1167                                 attr_amount := debit_balance;
1168                                 IF attr_amount > conv_alloc_balance THEN
1169                                         attr_amount := conv_alloc_balance;
1170                                 END IF;
1171                                 IF attr_amount > conv_cred_balance THEN
1172                                         attr_amount := conv_cred_balance;
1173                                 END IF;
1174                                 --
1175                                 -- Convert the amount of the attribution to the
1176                                 -- currency of the funding source.
1177                                 --
1178                                 conv_attr_amount := attr_amount * acq.exchange_ratio(
1179                                         deb.currency_type, curr_cr_bal.currency_type );
1180                                 --
1181                                 -- Insert a row to record the attribution
1182                                 --
1183                                 attrib_count := attrib_count + 1;
1184                                 INSERT INTO acq.debit_attribution (
1185                                         id,
1186                                         fund_debit,
1187                                         debit_amount,
1188                                         funding_source_credit,
1189                                         credit_amount
1190                                 ) VALUES (
1191                                         attrib_count,
1192                                         deb.id,
1193                                         attr_amount,
1194                                         curr_cr_bal.credit_id,
1195                                         conv_attr_amount
1196                                 );
1197                                 --
1198                                 -- Subtract the attributed amount from the various balances
1199                                 --
1200                                 debit_balance := debit_balance - attr_amount;
1201                                 --
1202                                 curr_fund_src_bal.balance := curr_fund_src_bal.balance - conv_attr_amount;
1203                                 fund_source_balance[ curr_fund_src_x ] := curr_fund_src_bal;
1204                                 IF curr_fund_src_bal.balance <= 0 THEN
1205                                         --
1206                                         -- This allocation is exhausted.  Take it out of the list
1207                                         -- so that we don't waste time looking at it again.
1208                                         --
1209                                         FOR i IN curr_fund_src_x .. fsa_max - 1 LOOP
1210                                                 fund_source_balance[ i ] := fund_source_balance[ i + 1 ];
1211                                         END LOOP;
1212                                         fund_source_balance[ fsa_max ] := NULL;
1213                                         fsa_max := fsa_max - 1;
1214                                 END IF;
1215                                 --
1216                                 curr_cr_bal.balance   := curr_cr_bal.balance - conv_attr_amount;
1217                                 cr_bal[ curr_cred_x ] := curr_cr_bal;
1218                                 IF curr_cr_bal.balance <= 0 THEN
1219                                         --
1220                                         -- This funding source credit is exhausted.  Take it out of
1221                                         -- the list so that we don't waste time looking at it again.
1222                                         --
1223                                         FOR i IN curr_cred_x .. fscr_max - 1 LOOP
1224                                                 cr_bal[ i ] := cr_bal[ i + 1 ];
1225                                         END LOOP;
1226                                         cr_bal[ fscr_max ] := NULL;
1227                                         fscr_max := fscr_max - 1;
1228                                 END IF;
1229                                 --
1230                                 -- Are we done with this debit yet?
1231                                 --
1232                                 IF debit_balance <= 0 THEN
1233                                         EXIT;       -- We've fully attributed this debit; stop looking at credits.
1234                                 END IF;
1235                         END LOOP;           -- End of loop over applicable credits
1236                 END IF;
1237                 --
1238                 IF debit_balance <> 0 THEN
1239                         --
1240                         -- We weren't able to attribute this debit, or at least not
1241                         -- all of it.  Insert a row for the unattributed balance.
1242                         --
1243                         attrib_count := attrib_count + 1;
1244                         INSERT INTO acq.debit_attribution (
1245                                 id,
1246                                 fund_debit,
1247                                 debit_amount,
1248                                 funding_source_credit,
1249                                 credit_amount
1250                         ) VALUES (
1251                                 attrib_count,
1252                                 deb.id,
1253                                 debit_balance,
1254                                 NULL,
1255                                 NULL
1256                         );
1257                 END IF;
1258         END LOOP;   -- End of loop over debits
1259 END;
1260 $$ LANGUAGE 'plpgsql';
1261
1262 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
1263     SELECT  funding_source,
1264             SUM(amount) AS amount
1265       FROM  acq.funding_source_credit
1266       GROUP BY 1;
1267
1268 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
1269     SELECT  funding_source,
1270             SUM(a.amount)::NUMERIC(100,2) AS amount
1271     FROM  acq.fund_allocation a
1272     GROUP BY 1;
1273
1274 CREATE OR REPLACE VIEW acq.funding_source_balance AS
1275     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
1276             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
1277       FROM  acq.funding_source_credit_total c
1278             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
1279       GROUP BY 1;
1280
1281 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
1282     SELECT  fund,
1283             SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
1284     FROM acq.fund_allocation a
1285          JOIN acq.fund f ON (a.fund = f.id)
1286          JOIN acq.funding_source s ON (a.funding_source = s.id)
1287     GROUP BY 1;
1288
1289 CREATE OR REPLACE VIEW acq.fund_debit_total AS
1290     SELECT  fund.id AS fund,
1291             fund_debit.encumbrance AS encumbrance,
1292                         SUM( COALESCE( fund_debit.amount, 0 ) ) AS amount
1293       FROM acq.fund AS fund
1294             LEFT JOIN acq.fund_debit AS fund_debit
1295                 ON ( fund.id = fund_debit.fund )
1296       GROUP BY 1,2;
1297
1298 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
1299     SELECT  fund,
1300             SUM(amount) AS amount
1301       FROM  acq.fund_debit_total
1302       WHERE encumbrance IS TRUE
1303       GROUP BY 1;
1304
1305 CREATE OR REPLACE VIEW acq.fund_spent_total AS
1306     SELECT  fund,
1307             SUM(amount) AS amount
1308       FROM  acq.fund_debit_total
1309       WHERE encumbrance IS FALSE
1310       GROUP BY 1;
1311
1312 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
1313     SELECT  c.fund,
1314             c.amount - COALESCE(d.amount,0.0) AS amount
1315       FROM  acq.fund_allocation_total c
1316             LEFT JOIN acq.fund_debit_total d USING (fund);
1317
1318 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
1319     SELECT  c.fund,
1320             c.amount - COALESCE(d.amount,0.0) AS amount
1321       FROM  acq.fund_allocation_total c
1322             LEFT JOIN acq.fund_spent_total d USING (fund);
1323
1324 COMMIT;
1325
1326
1327
1328