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),
24 to_currency TEXT NOT NULL REFERENCES acq.currency_type (code),
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),
36 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
38 CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner)
41 CREATE TABLE acq.funding_source (
42 id SERIAL PRIMARY KEY,
44 owner INT NOT NULL REFERENCES actor.org_unit (id),
45 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
47 CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
50 CREATE TABLE acq.funding_source_credit (
51 id SERIAL PRIMARY KEY,
52 funding_source INT NOT NULL REFERENCES acq.funding_source (id),
53 amount NUMERIC NOT NULL,
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,
61 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
62 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
64 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year)
67 CREATE TABLE acq.fund_debit (
68 id SERIAL PRIMARY KEY,
69 fund INT NOT NULL REFERENCES acq.fund (id),
70 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
71 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
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)
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,
81 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE,
83 percent NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
84 allocator INT NOT NULL REFERENCES actor.usr (id),
86 CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
90 CREATE TABLE acq.picklist (
91 id SERIAL PRIMARY KEY,
92 owner INT NOT NULL REFERENCES actor.usr (id),
93 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
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)
100 CREATE TABLE acq.purchase_order (
101 id SERIAL PRIMARY KEY,
102 owner INT NOT NULL REFERENCES actor.usr (id),
103 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id),
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),
107 state TEXT NOT NULL DEFAULT 'new'
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);
113 CREATE TABLE acq.po_note (
114 id SERIAL PRIMARY KEY,
115 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id),
116 creator INT NOT NULL REFERENCES actor.usr (id),
117 editor INT NOT NULL REFERENCES actor.usr (id),
118 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
119 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
122 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
124 CREATE TABLE acq.lineitem (
125 id BIGSERIAL PRIMARY KEY,
126 selector INT NOT NULL REFERENCES actor.org_unit (id),
127 provider INT REFERENCES acq.provider (id),
128 purchase_order INT REFERENCES acq.purchase_order (id),
129 picklist INT REFERENCES acq.picklist (id),
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(),
134 eg_bib_id INT REFERENCES biblio.record_entry (id),
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)
140 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
141 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
143 CREATE TABLE acq.lineitem_note (
144 id SERIAL PRIMARY KEY,
145 lineitem INT NOT NULL REFERENCES acq.lineitem (id),
146 creator INT NOT NULL REFERENCES actor.usr (id),
147 editor INT NOT NULL REFERENCES actor.usr (id),
148 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
149 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
152 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
154 CREATE TABLE acq.lineitem_detail (
155 id BIGSERIAL PRIMARY KEY,
156 lineitem INT NOT NULL REFERENCES acq.lineitem (id),
157 fund INT REFERENCES acq.fund (id),
158 fund_debit INT REFERENCES acq.fund_debit (id),
159 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
162 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL,
163 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL,
164 recv_time TIMESTAMP WITH TIME ZONE
167 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
169 CREATE TABLE acq.lineitem_attr_definition (
170 id BIGSERIAL PRIMARY KEY,
172 description TEXT NOT NULL,
173 remove TEXT NOT NULL DEFAULT '',
174 ident BOOL NOT NULL DEFAULT FALSE
177 CREATE TABLE acq.lineitem_marc_attr_definition (
178 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
180 ) INHERITS (acq.lineitem_attr_definition);
182 CREATE TABLE acq.lineitem_provider_attr_definition (
183 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
185 provider INT NOT NULL REFERENCES acq.provider (id)
186 ) INHERITS (acq.lineitem_attr_definition);
188 CREATE TABLE acq.lineitem_generated_attr_definition (
189 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
191 ) INHERITS (acq.lineitem_attr_definition);
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)
196 ) INHERITS (acq.lineitem_attr_definition);
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);
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),
206 attr_type TEXT NOT NULL,
207 attr_name TEXT NOT NULL,
208 attr_value TEXT NOT NULL
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);
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]');
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);
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);
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);
249 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
252 Suggested vendor fields:
260 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
268 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
270 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
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;
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;
286 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
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);
301 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
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');
308 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
314 CREATE TRIGGER cleanup_lineitem_trigger
315 BEFORE UPDATE OR DELETE ON acq.lineitem
316 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
318 CREATE TRIGGER ingest_lineitem_trigger
319 AFTER INSERT OR UPDATE ON acq.lineitem
320 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
322 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
326 IF from_ex = to_ex THEN
330 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
335 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
346 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
347 SELECT $3 * acq.exchange_ratio($1, $2);
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
356 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
357 SELECT funding_source,
358 SUM(amount)::NUMERIC(100,2) AS amount
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
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
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)
381 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
383 SUM(amount)::NUMERIC(100,2) AS amount
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
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
403 CREATE OR REPLACE VIEW acq.fund_debit_total AS
406 SUM(amount) AS amount
410 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
412 SUM(amount) AS amount
413 FROM acq.fund_debit_total
414 WHERE encumbrance IS TRUE
417 CREATE OR REPLACE VIEW acq.fund_spent_total AS
419 SUM(amount) AS amount
420 FROM acq.fund_debit_total
421 WHERE encumbrance IS FALSE
424 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
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);
430 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
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);