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,
123 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
126 CREATE TABLE acq.fund_allocation (
127 id SERIAL PRIMARY KEY,
128 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
129 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
131 percent NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
132 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
134 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
135 CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
139 CREATE TABLE acq.picklist (
140 id SERIAL PRIMARY KEY,
141 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
142 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
143 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
144 org_unit 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 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
151 CREATE TABLE acq.purchase_order (
152 id SERIAL PRIMARY KEY,
153 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
154 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
155 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
156 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
157 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
158 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
159 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
160 state TEXT NOT NULL DEFAULT 'new'
162 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
163 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
164 CREATE INDEX po_state_idx ON acq.purchase_order (state);
166 CREATE TABLE acq.po_note (
167 id SERIAL PRIMARY KEY,
168 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
169 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
170 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
171 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
172 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
175 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
177 CREATE TABLE acq.lineitem (
178 id BIGSERIAL PRIMARY KEY,
179 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
180 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
181 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
182 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
183 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
184 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
185 expected_recv_time TIMESTAMP WITH TIME ZONE,
186 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
187 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
189 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
191 item_count INT NOT NULL DEFAULT 0,
192 state TEXT NOT NULL DEFAULT 'new',
193 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
195 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
196 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
198 CREATE TABLE acq.lineitem_note (
199 id SERIAL PRIMARY KEY,
200 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
201 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
202 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
203 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
204 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
207 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
209 CREATE TABLE acq.lineitem_detail (
210 id BIGSERIAL PRIMARY KEY,
211 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
212 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
213 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
214 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
218 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
219 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
220 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
221 recv_time TIMESTAMP WITH TIME ZONE
224 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
226 CREATE TABLE acq.lineitem_attr_definition (
227 id BIGSERIAL PRIMARY KEY,
229 description TEXT NOT NULL,
230 remove TEXT NOT NULL DEFAULT '',
231 ident BOOL NOT NULL DEFAULT FALSE
234 CREATE TABLE acq.lineitem_marc_attr_definition (
235 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
237 ) INHERITS (acq.lineitem_attr_definition);
239 CREATE TABLE acq.lineitem_provider_attr_definition (
240 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
242 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
243 ) INHERITS (acq.lineitem_attr_definition);
245 CREATE TABLE acq.lineitem_generated_attr_definition (
246 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
248 ) INHERITS (acq.lineitem_attr_definition);
250 CREATE TABLE acq.lineitem_usr_attr_definition (
251 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
252 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
253 ) INHERITS (acq.lineitem_attr_definition);
255 CREATE TABLE acq.lineitem_local_attr_definition (
256 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
257 ) INHERITS (acq.lineitem_attr_definition);
259 CREATE TABLE acq.lineitem_attr (
260 id BIGSERIAL PRIMARY KEY,
261 definition BIGINT NOT NULL,
262 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
263 attr_type TEXT NOT NULL,
264 attr_name TEXT NOT NULL,
265 attr_value TEXT NOT NULL
268 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
269 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
270 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
276 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
277 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)]');
278 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
279 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
280 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
281 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
282 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
283 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
284 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
285 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
286 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
289 CREATE TABLE acq.distribution_formula (
290 id SERIAL PRIMARY KEY,
292 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
294 skip_count INT NOT NULL DEFAULT 0,
295 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
298 CREATE TABLE acq.distribution_formula_entry (
299 id SERIAL PRIMARY KEY,
300 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
302 DEFERRABLE INITIALLY DEFERRED,
303 position INTEGER NOT NULL,
304 item_count INTEGER NOT NULL,
305 owning_lib INTEGER REFERENCES actor.org_unit(id)
306 DEFERRABLE INITIALLY DEFERRED,
307 location INTEGER REFERENCES asset.copy_location(id),
308 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
309 CONSTRAINT acqdfe_must_be_somewhere
310 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
313 CREATE TABLE acq.fund_tag (
314 id SERIAL PRIMARY KEY,
316 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
318 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
321 CREATE TABLE acq.fund_tag_map (
322 id SERIAL PRIMARY KEY,
323 fund INTEGER NOT NULL REFERENCES acq.fund(id)
324 DEFERRABLE INITIALLY DEFERRED,
325 tag INTEGER REFERENCES acq.fund_tag(id)
327 DEFERRABLE INITIALLY DEFERRED,
328 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
333 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
334 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
337 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
340 SELECT COUNT(*) INTO counter
345 '//*[@tag="' || tag || '"]',
347 ) as t(i int,c text);
349 FOR i IN 1 .. counter LOOP
352 FROM ( SELECT id,i,t,v
357 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
358 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
360 ) as t(id int,t text,v text)
371 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
372 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
376 lida acq.flat_lineitem_detail%ROWTYPE;
378 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
379 IF NOT FOUND THEN RETURN; END IF;
381 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
382 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
389 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
390 JOIN acq.provider_holding_subfield_map a USING (subfield)
391 WHERE a.provider = prov_i
400 -- select * from acq.extract_provider_holding_data(699);
402 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
403 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
407 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
408 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
411 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
412 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
416 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
419 Suggested vendor fields:
427 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
435 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
437 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
439 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
440 IF (atype = 'lineitem_provider_attr_definition') THEN
441 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
442 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
445 IF (atype = 'lineitem_provider_attr_definition') THEN
446 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
447 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
448 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
449 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
450 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
453 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
455 IF (value IS NOT NULL AND value <> '') THEN
456 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
457 VALUES (NEW.id, adef.id, atype, adef.code, value);
468 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
470 IF TG_OP = 'UPDATE' THEN
471 DELETE FROM acq.lineitem_attr
472 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
475 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
481 CREATE TRIGGER cleanup_lineitem_trigger
482 BEFORE UPDATE OR DELETE ON acq.lineitem
483 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
485 CREATE TRIGGER ingest_lineitem_trigger
486 AFTER INSERT OR UPDATE ON acq.lineitem
487 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
489 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
493 IF from_ex = to_ex THEN
497 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
502 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
513 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
514 SELECT $3 * acq.exchange_ratio($1, $2);
517 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
518 SELECT funding_source,
519 SUM(amount) AS amount
520 FROM acq.funding_source_credit
523 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
524 SELECT funding_source,
525 SUM(amount)::NUMERIC(100,2) AS amount
527 SELECT funding_source,
528 SUM(a.amount)::NUMERIC(100,2) AS amount
529 FROM acq.fund_allocation a
530 WHERE a.percent IS NULL
533 SELECT funding_source,
534 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
535 FROM acq.fund_allocation a
536 WHERE a.amount IS NULL
541 CREATE OR REPLACE VIEW acq.funding_source_balance AS
542 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
543 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
544 FROM acq.funding_source_credit_total c
545 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
548 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
550 SUM(amount)::NUMERIC(100,2) AS amount
553 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
554 FROM acq.fund_allocation a
555 JOIN acq.fund f ON (a.fund = f.id)
556 JOIN acq.funding_source s ON (a.funding_source = s.id)
557 WHERE a.percent IS NULL
561 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
562 FROM acq.fund_allocation a
563 JOIN acq.fund f ON (a.fund = f.id)
564 JOIN acq.funding_source s ON (a.funding_source = s.id)
565 WHERE a.amount IS NULL
570 CREATE OR REPLACE VIEW acq.fund_debit_total AS
573 SUM(amount) AS amount
577 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
579 SUM(amount) AS amount
580 FROM acq.fund_debit_total
581 WHERE encumbrance IS TRUE
584 CREATE OR REPLACE VIEW acq.fund_spent_total AS
586 SUM(amount) AS amount
587 FROM acq.fund_debit_total
588 WHERE encumbrance IS FALSE
591 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
593 c.amount - COALESCE(d.amount,0.0) AS amount
594 FROM acq.fund_allocation_total c
595 LEFT JOIN acq.fund_debit_total d USING (fund);
597 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
599 c.amount - COALESCE(d.amount,0.0) AS amount
600 FROM acq.fund_allocation_total c
601 LEFT JOIN acq.fund_spent_total d USING (fund);