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 collection_code TEXT,
219 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
220 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
221 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
222 recv_time TIMESTAMP WITH TIME ZONE
225 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
227 CREATE TABLE acq.lineitem_attr_definition (
228 id BIGSERIAL PRIMARY KEY,
230 description TEXT NOT NULL,
231 remove TEXT NOT NULL DEFAULT '',
232 ident BOOL NOT NULL DEFAULT FALSE
235 CREATE TABLE acq.lineitem_marc_attr_definition (
236 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
238 ) INHERITS (acq.lineitem_attr_definition);
240 CREATE TABLE acq.lineitem_provider_attr_definition (
241 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
243 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
244 ) INHERITS (acq.lineitem_attr_definition);
246 CREATE TABLE acq.lineitem_generated_attr_definition (
247 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
249 ) INHERITS (acq.lineitem_attr_definition);
251 CREATE TABLE acq.lineitem_usr_attr_definition (
252 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
253 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
254 ) INHERITS (acq.lineitem_attr_definition);
256 CREATE TABLE acq.lineitem_local_attr_definition (
257 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
258 ) INHERITS (acq.lineitem_attr_definition);
260 CREATE TABLE acq.lineitem_attr (
261 id BIGSERIAL PRIMARY KEY,
262 definition BIGINT NOT NULL,
263 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
264 attr_type TEXT NOT NULL,
265 attr_name TEXT NOT NULL,
266 attr_value TEXT NOT NULL
269 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
270 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
271 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
277 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
278 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)]');
279 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
280 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
281 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
282 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
283 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
284 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
285 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
286 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
287 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
289 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
292 CREATE TABLE acq.distribution_formula (
293 id SERIAL PRIMARY KEY,
295 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
297 skip_count INT NOT NULL DEFAULT 0,
298 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
301 CREATE TABLE acq.distribution_formula_entry (
302 id SERIAL PRIMARY KEY,
303 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
305 DEFERRABLE INITIALLY DEFERRED,
306 position INTEGER NOT NULL,
307 item_count INTEGER NOT NULL,
308 owning_lib INTEGER REFERENCES actor.org_unit(id)
309 DEFERRABLE INITIALLY DEFERRED,
310 location INTEGER REFERENCES asset.copy_location(id),
311 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
312 CONSTRAINT acqdfe_must_be_somewhere
313 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
316 CREATE TABLE acq.fund_tag (
317 id SERIAL PRIMARY KEY,
319 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
321 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
324 CREATE TABLE acq.fund_tag_map (
325 id SERIAL PRIMARY KEY,
326 fund INTEGER NOT NULL REFERENCES acq.fund(id)
327 DEFERRABLE INITIALLY DEFERRED,
328 tag INTEGER REFERENCES acq.fund_tag(id)
330 DEFERRABLE INITIALLY DEFERRED,
331 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
336 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
337 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
340 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
343 SELECT COUNT(*) INTO counter
348 '//*[@tag="' || tag || '"]',
350 ) as t(i int,c text);
352 FOR i IN 1 .. counter LOOP
355 FROM ( SELECT id,i,t,v
360 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
361 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
363 ) as t(id int,t text,v text)
374 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
375 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
379 lida acq.flat_lineitem_detail%ROWTYPE;
381 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
382 IF NOT FOUND THEN RETURN; END IF;
384 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
385 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
392 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
393 JOIN acq.provider_holding_subfield_map a USING (subfield)
394 WHERE a.provider = prov_i
403 -- select * from acq.extract_provider_holding_data(699);
405 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
406 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
410 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
411 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
414 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
415 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
419 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
422 Suggested vendor fields:
430 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
438 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
440 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
442 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
443 IF (atype = 'lineitem_provider_attr_definition') THEN
444 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
445 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
448 IF (atype = 'lineitem_provider_attr_definition') THEN
449 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
450 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
451 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
452 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
453 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
456 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
458 IF (value IS NOT NULL AND value <> '') THEN
459 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
460 VALUES (NEW.id, adef.id, atype, adef.code, value);
471 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
473 IF TG_OP = 'UPDATE' THEN
474 DELETE FROM acq.lineitem_attr
475 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
478 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
484 CREATE TRIGGER cleanup_lineitem_trigger
485 BEFORE UPDATE OR DELETE ON acq.lineitem
486 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
488 CREATE TRIGGER ingest_lineitem_trigger
489 AFTER INSERT OR UPDATE ON acq.lineitem
490 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
492 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
496 IF from_ex = to_ex THEN
500 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
505 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
516 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
517 SELECT $3 * acq.exchange_ratio($1, $2);
520 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
521 SELECT funding_source,
522 SUM(amount) AS amount
523 FROM acq.funding_source_credit
526 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
527 SELECT funding_source,
528 SUM(amount)::NUMERIC(100,2) AS amount
530 SELECT funding_source,
531 SUM(a.amount)::NUMERIC(100,2) AS amount
532 FROM acq.fund_allocation a
533 WHERE a.percent IS NULL
536 SELECT funding_source,
537 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
538 FROM acq.fund_allocation a
539 WHERE a.amount IS NULL
544 CREATE OR REPLACE VIEW acq.funding_source_balance AS
545 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
546 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
547 FROM acq.funding_source_credit_total c
548 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
551 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
553 SUM(amount)::NUMERIC(100,2) AS amount
556 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
557 FROM acq.fund_allocation a
558 JOIN acq.fund f ON (a.fund = f.id)
559 JOIN acq.funding_source s ON (a.funding_source = s.id)
560 WHERE a.percent IS NULL
564 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
565 FROM acq.fund_allocation a
566 JOIN acq.fund f ON (a.fund = f.id)
567 JOIN acq.funding_source s ON (a.funding_source = s.id)
568 WHERE a.amount IS NULL
573 CREATE OR REPLACE VIEW acq.fund_debit_total AS
576 SUM(amount) AS amount
580 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
582 SUM(amount) AS amount
583 FROM acq.fund_debit_total
584 WHERE encumbrance IS TRUE
587 CREATE OR REPLACE VIEW acq.fund_spent_total AS
589 SUM(amount) AS amount
590 FROM acq.fund_debit_total
591 WHERE encumbrance IS FALSE
594 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
596 c.amount - COALESCE(d.amount,0.0) AS amount
597 FROM acq.fund_allocation_total c
598 LEFT JOIN acq.fund_debit_total d USING (fund);
600 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
602 c.amount - COALESCE(d.amount,0.0) AS amount
603 FROM acq.fund_allocation_total c
604 LEFT JOIN acq.fund_spent_total d USING (fund);