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