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