1 DROP SCHEMA acq CASCADE;
11 CREATE TABLE acq.currency_type (
12 code TEXT PRIMARY KEY,
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');
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)
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);
32 CREATE TABLE acq.provider (
33 id SERIAL PRIMARY KEY,
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,
39 CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner)
42 CREATE TABLE acq.provider_holding_subfield_map (
43 id SERIAL PRIMARY KEY,
44 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
45 name TEXT NOT NULL, -- barcode, price, etc
46 subfield TEXT NOT NULL,
47 CONSTRAINT name_once_per_provider UNIQUE (provider,name)
50 CREATE TABLE acq.provider_address (
51 id SERIAL PRIMARY KEY,
52 valid BOOL NOT NULL DEFAULT TRUE,
54 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
55 street1 TEXT NOT NULL,
60 country TEXT NOT NULL,
61 post_code TEXT NOT NULL
64 CREATE TABLE acq.provider_contact (
65 id SERIAL PRIMARY KEY,
66 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
68 role TEXT, -- free-form.. e.g. "our sales guy"
73 CREATE TABLE acq.provider_contact_address (
74 id SERIAL PRIMARY KEY,
75 valid BOOL NOT NULL DEFAULT TRUE,
77 contact INT NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
78 street1 TEXT NOT NULL,
83 country TEXT NOT NULL,
84 post_code TEXT NOT NULL
88 CREATE TABLE acq.funding_source (
89 id SERIAL PRIMARY KEY,
91 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
92 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
94 CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
97 CREATE TABLE acq.funding_source_credit (
98 id SERIAL PRIMARY KEY,
99 funding_source INT NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
100 amount NUMERIC NOT NULL,
104 CREATE TABLE acq.fund (
105 id SERIAL PRIMARY KEY,
106 org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
108 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
109 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
111 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year)
114 CREATE TABLE acq.fund_debit (
115 id SERIAL PRIMARY KEY,
116 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
117 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
118 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
119 amount NUMERIC NOT NULL,
120 encumbrance BOOL NOT NULL DEFAULT TRUE,
121 debit_type TEXT NOT NULL,
122 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED
125 CREATE TABLE acq.fund_allocation (
126 id SERIAL PRIMARY KEY,
127 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
128 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
130 percent NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
131 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
133 CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
137 CREATE TABLE acq.picklist (
138 id SERIAL PRIMARY KEY,
139 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
140 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
141 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
142 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
144 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
145 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
146 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
149 CREATE TABLE acq.purchase_order (
150 id SERIAL PRIMARY KEY,
151 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
152 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
153 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
154 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
155 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
156 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
157 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
158 state TEXT NOT NULL DEFAULT 'new'
160 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
161 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
162 CREATE INDEX po_state_idx ON acq.purchase_order (state);
164 CREATE TABLE acq.po_note (
165 id SERIAL PRIMARY KEY,
166 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
167 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
168 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
169 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
170 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
173 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
175 CREATE TABLE acq.lineitem (
176 id BIGSERIAL PRIMARY KEY,
177 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
178 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
179 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
180 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
181 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
182 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
183 expected_recv_time TIMESTAMP WITH TIME ZONE,
184 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
185 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
187 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
189 item_count INT NOT NULL DEFAULT 0,
190 state TEXT NOT NULL DEFAULT 'new',
191 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
193 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
194 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
196 CREATE TABLE acq.lineitem_note (
197 id SERIAL PRIMARY KEY,
198 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
199 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
200 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
201 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
202 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
205 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
207 CREATE TABLE acq.lineitem_detail (
208 id BIGSERIAL PRIMARY KEY,
209 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
210 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
211 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
212 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
215 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
216 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
217 recv_time TIMESTAMP WITH TIME ZONE
220 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
222 CREATE TABLE acq.lineitem_attr_definition (
223 id BIGSERIAL PRIMARY KEY,
225 description TEXT NOT NULL,
226 remove TEXT NOT NULL DEFAULT '',
227 ident BOOL NOT NULL DEFAULT FALSE
230 CREATE TABLE acq.lineitem_marc_attr_definition (
231 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
233 ) INHERITS (acq.lineitem_attr_definition);
235 CREATE TABLE acq.lineitem_provider_attr_definition (
236 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
238 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
239 ) INHERITS (acq.lineitem_attr_definition);
241 CREATE TABLE acq.lineitem_generated_attr_definition (
242 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
244 ) INHERITS (acq.lineitem_attr_definition);
246 CREATE TABLE acq.lineitem_usr_attr_definition (
247 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
248 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
249 ) INHERITS (acq.lineitem_attr_definition);
251 CREATE TABLE acq.lineitem_local_attr_definition (
252 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
253 ) INHERITS (acq.lineitem_attr_definition);
255 CREATE TABLE acq.lineitem_attr (
256 id BIGSERIAL PRIMARY KEY,
257 definition BIGINT NOT NULL,
258 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
259 attr_type TEXT NOT NULL,
260 attr_name TEXT NOT NULL,
261 attr_value TEXT NOT NULL
264 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
265 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
266 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
272 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
273 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)]');
274 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
275 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
276 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
277 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
278 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
279 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
280 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
281 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
282 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
285 CREATE TABLE acq.distribution_formula (
286 id SERIAL PRIMARY KEY,
288 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
290 skip_count INT NOT NULL DEFAULT 0,
291 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
294 CREATE TABLE acq.distribution_formula_entry (
295 id SERIAL PRIMARY KEY,
296 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
298 DEFERRABLE INITIALLY DEFERRED,
299 position INTEGER NOT NULL,
300 item_count INTEGER NOT NULL,
301 owning_lib INTEGER REFERENCES actor.org_unit(id)
302 DEFERRABLE INITIALLY DEFERRED,
303 location INTEGER REFERENCES asset.copy_location(id),
304 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
305 CONSTRAINT acqdfe_must_be_somewhere
306 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
312 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
313 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
316 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
319 SELECT COUNT(*) INTO counter
324 '//*[@tag="' || tag || '"]',
326 ) as t(i int,c text);
328 FOR i IN 1 .. counter LOOP
331 FROM ( SELECT id,i,t,v
336 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
337 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
339 ) as t(id int,t text,v text)
350 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
351 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
355 lida acq.flat_lineitem_detail%ROWTYPE;
357 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
358 IF NOT FOUND THEN RETURN; END IF;
360 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
361 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
368 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
369 JOIN acq.provider_holding_subfield_map a USING (subfield)
370 WHERE a.provider = prov_i
379 -- select * from acq.extract_provider_holding_data(699);
381 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
382 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
386 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
387 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
390 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
391 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
395 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
398 Suggested vendor fields:
406 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
414 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
416 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
418 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
419 IF (atype = 'lineitem_provider_attr_definition') THEN
420 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
421 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
424 IF (atype = 'lineitem_provider_attr_definition') THEN
425 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
426 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
427 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
428 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
429 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
432 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
434 IF (value IS NOT NULL AND value <> '') THEN
435 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
436 VALUES (NEW.id, adef.id, atype, adef.code, value);
447 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
449 IF TG_OP = 'UPDATE' THEN
450 DELETE FROM acq.lineitem_attr
451 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
454 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
460 CREATE TRIGGER cleanup_lineitem_trigger
461 BEFORE UPDATE OR DELETE ON acq.lineitem
462 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
464 CREATE TRIGGER ingest_lineitem_trigger
465 AFTER INSERT OR UPDATE ON acq.lineitem
466 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
468 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
472 IF from_ex = to_ex THEN
476 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
481 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
492 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
493 SELECT $3 * acq.exchange_ratio($1, $2);
496 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
497 SELECT funding_source,
498 SUM(amount) AS amount
499 FROM acq.funding_source_credit
502 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
503 SELECT funding_source,
504 SUM(amount)::NUMERIC(100,2) AS amount
506 SELECT funding_source,
507 SUM(a.amount)::NUMERIC(100,2) AS amount
508 FROM acq.fund_allocation a
509 WHERE a.percent IS NULL
512 SELECT funding_source,
513 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
514 FROM acq.fund_allocation a
515 WHERE a.amount IS NULL
520 CREATE OR REPLACE VIEW acq.funding_source_balance AS
521 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
522 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
523 FROM acq.funding_source_credit_total c
524 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
527 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
529 SUM(amount)::NUMERIC(100,2) AS amount
532 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
533 FROM acq.fund_allocation a
534 JOIN acq.fund f ON (a.fund = f.id)
535 JOIN acq.funding_source s ON (a.funding_source = s.id)
536 WHERE a.percent IS NULL
540 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
541 FROM acq.fund_allocation a
542 JOIN acq.fund f ON (a.fund = f.id)
543 JOIN acq.funding_source s ON (a.funding_source = s.id)
544 WHERE a.amount IS NULL
549 CREATE OR REPLACE VIEW acq.fund_debit_total AS
552 SUM(amount) AS amount
556 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
558 SUM(amount) AS amount
559 FROM acq.fund_debit_total
560 WHERE encumbrance IS TRUE
563 CREATE OR REPLACE VIEW acq.fund_spent_total AS
565 SUM(amount) AS amount
566 FROM acq.fund_debit_total
567 WHERE encumbrance IS FALSE
570 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
572 c.amount - COALESCE(d.amount,0.0) AS amount
573 FROM acq.fund_allocation_total c
574 LEFT JOIN acq.fund_debit_total d USING (fund);
576 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
578 c.amount - COALESCE(d.amount,0.0) AS amount
579 FROM acq.fund_allocation_total c
580 LEFT JOIN acq.fund_spent_total d USING (fund);