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    UNIQUE,
  38         CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner)
  39 );
  40
  41 CREATE TABLE acq.provider_address (
  42         id              SERIAL  PRIMARY KEY,
  43         valid           BOOL    NOT NULL DEFAULT TRUE,
  44         address_type    TEXT,
  45     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  46         street1         TEXT    NOT NULL,
  47         street2         TEXT,
  48         city            TEXT    NOT NULL,
  49         county          TEXT,
  50         state           TEXT    NOT NULL,
  51         country         TEXT    NOT NULL,
  52         post_code       TEXT    NOT NULL
  53 );
  54
  55 CREATE TABLE acq.provider_contact (
  56         id              SERIAL  PRIMARY KEY,
  57     provider    INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  58     name    TEXT NULL NULL,
  59     role    TEXT, -- free-form.. e.g. "our sales guy"
  60     email   TEXT,
  61     phone   TEXT
  62 );
  63
  64 CREATE TABLE acq.provider_contact_address (
  65         id                      SERIAL  PRIMARY KEY,
  66         valid                   BOOL    NOT NULL DEFAULT TRUE,
  67         address_type    TEXT,
  68         contact                 INT         NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
  69         street1                 TEXT    NOT NULL,
  70         street2                 TEXT,
  71         city                    TEXT    NOT NULL,
  72         county                  TEXT,
  73         state                   TEXT    NOT NULL,
  74         country                 TEXT    NOT NULL,
  75         post_code               TEXT    NOT NULL
  76 );
  77
  78
  79 CREATE TABLE acq.funding_source (
  80         id              SERIAL  PRIMARY KEY,
  81         name            TEXT    NOT NULL,
  82         owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  83         currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  84         code            TEXT    UNIQUE,
  85         CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
  86 );
  87
  88 CREATE TABLE acq.funding_source_credit (
  89         id      SERIAL  PRIMARY KEY,
  90         funding_source    INT     NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
  91         amount  NUMERIC NOT NULL,
  92         note    TEXT
  93 );
  94
  95 CREATE TABLE acq.fund (
  96     id              SERIAL  PRIMARY KEY,
  97     org             INT     NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  98     name            TEXT    NOT NULL,
  99     year            INT     NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
 100     currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 101     code            TEXT    UNIQUE,
 102     CONSTRAINT name_once_per_org_year UNIQUE (org,name,year)
 103 );
 104
 105 CREATE TABLE acq.fund_debit (
 106         id                      SERIAL  PRIMARY KEY,
 107         fund                    INT     NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 108         origin_amount           NUMERIC NOT NULL,  -- pre-exchange-rate amount
 109         origin_currency_type    TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 110         amount                  NUMERIC NOT NULL,
 111         encumbrance             BOOL    NOT NULL DEFAULT TRUE,
 112         debit_type              TEXT    NOT NULL,
 113         xfer_destination        INT     REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED
 114 );
 115
 116 CREATE TABLE acq.fund_allocation (
 117     id          SERIAL  PRIMARY KEY,
 118     funding_source        INT     NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 119     fund        INT     NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 120     amount      NUMERIC,
 121     percent     NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
 122     allocator   INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 123     note        TEXT,
 124     CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
 125 );
 126
 127
 128 CREATE TABLE acq.picklist (
 129         id              SERIAL                          PRIMARY KEY,
 130         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 131         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 132         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 133         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 134         name            TEXT                            NOT NULL,
 135         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 136         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 137         CONSTRAINT name_once_per_owner UNIQUE (name,owner)
 138 );
 139
 140 CREATE TABLE acq.purchase_order (
 141         id              SERIAL                          PRIMARY KEY,
 142         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 143         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 144         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 145         ordering_agency INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 146         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 147         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 148         provider        INT                             NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 149         state           TEXT                            NOT NULL DEFAULT 'new'
 150 );
 151 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
 152 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
 153 CREATE INDEX po_state_idx ON acq.purchase_order (state);
 154
 155 CREATE TABLE acq.po_note (
 156         id              SERIAL                          PRIMARY KEY,
 157         purchase_order  INT                             NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 158         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 159         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 160         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 161         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 162         value           TEXT                            NOT NULL
 163 );
 164 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
 165
 166 CREATE TABLE acq.lineitem (
 167         id                  BIGSERIAL                   PRIMARY KEY,
 168         creator             INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 169         editor              INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 170         selector            INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 171         provider            INT                         REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 172         purchase_order      INT                         REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 173         picklist            INT                         REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
 174         expected_recv_time  TIMESTAMP WITH TIME ZONE,
 175         create_time         TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 176         edit_time           TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 177         marc                TEXT                        NOT NULL,
 178         eg_bib_id           INT                         REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
 179         source_label        TEXT,
 180         item_count          INT                         NOT NULL DEFAULT 0,
 181         state               TEXT                        NOT NULL DEFAULT 'new',
 182     CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
 183 );
 184 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
 185 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
 186
 187 CREATE TABLE acq.lineitem_note (
 188         id              SERIAL                          PRIMARY KEY,
 189         lineitem        INT                             NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 190         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 191         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 192         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 193         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 194         value           TEXT                            NOT NULL
 195 );
 196 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
 197
 198 CREATE TABLE acq.lineitem_detail (
 199         id              BIGSERIAL       PRIMARY KEY,
 200         lineitem        INT             NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 201         fund            INT             REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 202         fund_debit      INT             REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
 203         eg_copy_id      BIGINT          REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 204         barcode         TEXT,
 205         cn_label        TEXT,
 206         owning_lib      INT             REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 207         location        INT             REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 208         recv_time       TIMESTAMP WITH TIME ZONE
 209 );
 210
 211 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
 212
 213 CREATE TABLE acq.lineitem_attr_definition (
 214         id              BIGSERIAL       PRIMARY KEY,
 215         code            TEXT            NOT NULL,
 216         description     TEXT            NOT NULL,
 217         remove          TEXT            NOT NULL DEFAULT '',
 218         ident           BOOL            NOT NULL DEFAULT FALSE
 219 );
 220
 221 CREATE TABLE acq.lineitem_marc_attr_definition (
 222         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 223         xpath           TEXT            NOT NULL
 224 ) INHERITS (acq.lineitem_attr_definition);
 225
 226 CREATE TABLE acq.lineitem_provider_attr_definition (
 227         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 228         xpath           TEXT            NOT NULL,
 229         provider        INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
 230 ) INHERITS (acq.lineitem_attr_definition);
 231
 232 CREATE TABLE acq.lineitem_generated_attr_definition (
 233         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 234         xpath           TEXT            NOT NULL
 235 ) INHERITS (acq.lineitem_attr_definition);
 236
 237 CREATE TABLE acq.lineitem_usr_attr_definition (
 238         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 239         usr             INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
 240 ) INHERITS (acq.lineitem_attr_definition);
 241
 242 CREATE TABLE acq.lineitem_local_attr_definition (
 243         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
 244 ) INHERITS (acq.lineitem_attr_definition);
 245
 246 CREATE TABLE acq.lineitem_attr (
 247         id              BIGSERIAL       PRIMARY KEY,
 248         definition      BIGINT          NOT NULL,
 249         lineitem        BIGINT          NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 250         attr_type       TEXT            NOT NULL,
 251         attr_name       TEXT            NOT NULL,
 252         attr_value      TEXT            NOT NULL
 253 );
 254
 255 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
 256 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
 257 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
 258
 259
 260 -- Seed data
 261
 262
 263 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
 264 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)]');
 265 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
 266 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
 267 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 268 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 269 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
 270 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
 271 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
 272 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
 273 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
 274
 275
 276 CREATE TABLE acq.distribution_formula (
 277         id              SERIAL PRIMARY KEY,
 278         owner   INT NOT NULL
 279                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 280         name    TEXT NOT NULL,
 281         skip_count      INT NOT NULL DEFAULT 0,
 282         CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
 283 );
 284
 285 CREATE TABLE acq.distribution_formula_entry (
 286         id                      SERIAL PRIMARY KEY,
 287         formula         INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
 288                                 ON DELETE CASCADE
 289                                 DEFERRABLE INITIALLY DEFERRED,
 290         position        INTEGER NOT NULL,
 291         item_count      INTEGER NOT NULL,
 292         owning_lib      INTEGER REFERENCES actor.org_unit(id)
 293                                 DEFERRABLE INITIALLY DEFERRED,
 294         location        INTEGER REFERENCES asset.copy_location(id),
 295         CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
 296         CONSTRAINT acqdfe_must_be_somewhere
 297                                 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
 298 );
 299
 300
 301 -- Functions
 302
 303
 304 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 305         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 306 $$ LANGUAGE SQL;
 307
 308 /*
 309 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 310         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 311 $$ LANGUAGE SQL;
 312
 313 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 314         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 315 $$ LANGUAGE SQL;
 316 */
 317 -- For example:
 318 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 319
 320 /*
 321 Suggested vendor fields:
 322         vendor_price
 323         vendor_currency
 324         vendor_avail
 325         vendor_po
 326         vendor_identifier
 327 */
 328
 329 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
 330 DECLARE
 331         value           TEXT;
 332         atype           TEXT;
 333         prov            INT;
 334         adef            RECORD;
 335         xpath_string    TEXT;
 336 BEGIN
 337         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 338
 339                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 340
 341                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
 342                         IF (atype = 'lineitem_provider_attr_definition') THEN
 343                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 344                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
 345                         END IF;
 346
 347                         IF (atype = 'lineitem_provider_attr_definition') THEN
 348                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 349                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
 350                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
 351                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
 352                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
 353                         END IF;
 354
 355                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
 356
 357                         IF (value IS NOT NULL AND value <> '') THEN
 358                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
 359                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
 360                         END IF;
 361
 362                 END IF;
 363
 364         END LOOP;
 365
 366         RETURN NULL;
 367 END;
 368 $$ LANGUAGE PLPGSQL;
 369
 370 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
 371 BEGIN
 372         IF TG_OP = 'UPDATE' THEN
 373                 DELETE FROM acq.lineitem_attr
 374                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
 375                 RETURN NEW;
 376         ELSE
 377                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
 378                 RETURN OLD;
 379         END IF;
 380 END;
 381 $$ LANGUAGE PLPGSQL;
 382
 383 CREATE TRIGGER cleanup_lineitem_trigger
 384         BEFORE UPDATE OR DELETE ON acq.lineitem
 385         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
 386
 387 CREATE TRIGGER ingest_lineitem_trigger
 388         AFTER INSERT OR UPDATE ON acq.lineitem
 389         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
 390
 391 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
 392 DECLARE
 393     rat NUMERIC;
 394 BEGIN
 395     IF from_ex = to_ex THEN
 396         RETURN 1.0;
 397     END IF;
 398
 399     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
 400
 401     IF FOUND THEN
 402         RETURN rat;
 403     ELSE
 404         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
 405         IF FOUND THEN
 406             RETURN 1.0/rat;
 407         END IF;
 408     END IF;
 409
 410     RETURN NULL;
 411
 412 END;
 413 $$ LANGUAGE PLPGSQL;
 414
 415 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
 416     SELECT $3 * acq.exchange_ratio($1, $2);
 417 $$ LANGUAGE SQL;
 418
 419 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
 420     SELECT  funding_source,
 421             SUM(amount) AS amount
 422       FROM  acq.funding_source_credit
 423       GROUP BY 1;
 424
 425 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
 426     SELECT  funding_source,
 427             SUM(amount)::NUMERIC(100,2) AS amount
 428       FROM (
 429             SELECT  funding_source,
 430                     SUM(a.amount)::NUMERIC(100,2) AS amount
 431               FROM  acq.fund_allocation a
 432               WHERE a.percent IS NULL
 433               GROUP BY 1
 434                             UNION ALL
 435             SELECT  funding_source,
 436                     SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
 437               FROM  acq.fund_allocation a
 438               WHERE a.amount IS NULL
 439               GROUP BY 1
 440         ) x
 441       GROUP BY 1;
 442
 443 CREATE OR REPLACE VIEW acq.funding_source_balance AS
 444     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
 445             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
 446       FROM  acq.funding_source_credit_total c
 447             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
 448       GROUP BY 1;
 449
 450 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
 451     SELECT  fund,
 452             SUM(amount)::NUMERIC(100,2) AS amount
 453       FROM (
 454             SELECT  fund,
 455                     SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
 456               FROM  acq.fund_allocation a
 457                     JOIN acq.fund f ON (a.fund = f.id)
 458                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 459               WHERE a.percent IS NULL
 460               GROUP BY 1
 461                             UNION ALL
 462             SELECT  fund,
 463                     SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * acq.exchange_ratio(s.currency_type, f.currency_type) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
 464               FROM  acq.fund_allocation a
 465                     JOIN acq.fund f ON (a.fund = f.id)
 466                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 467               WHERE a.amount IS NULL
 468               GROUP BY 1
 469         ) x
 470       GROUP BY 1;
 471
 472 CREATE OR REPLACE VIEW acq.fund_debit_total AS
 473     SELECT  id AS fund,
 474             encumbrance,
 475             SUM(amount) AS amount
 476       FROM  acq.fund_debit
 477       GROUP BY 1,2;
 478
 479 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
 480     SELECT  fund,
 481             SUM(amount) AS amount
 482       FROM  acq.fund_debit_total
 483       WHERE encumbrance IS TRUE
 484       GROUP BY 1;
 485
 486 CREATE OR REPLACE VIEW acq.fund_spent_total AS
 487     SELECT  fund,
 488             SUM(amount) AS amount
 489       FROM  acq.fund_debit_total
 490       WHERE encumbrance IS FALSE
 491       GROUP BY 1;
 492
 493 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
 494     SELECT  c.fund,
 495             c.amount - COALESCE(d.amount,0.0) AS amount
 496       FROM  acq.fund_allocation_total c
 497             LEFT JOIN acq.fund_debit_total d USING (fund);
 498
 499 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
 500     SELECT  c.fund,
 501             c.amount - COALESCE(d.amount,0.0) AS amount
 502       FROM  acq.fund_allocation_total c
 503             LEFT JOIN acq.fund_spent_total d USING (fund);
 504
 505 COMMIT;
 506
 507
 508
 509