1 --Upgrade Script for 2.6.2 to 2.6.3
2 \set eg_version '''2.6.3'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.6.3', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0887', :eg_version);
8 DROP FUNCTION IF EXISTS vandelay.marc21_extract_fixed_field_list( text, text );
10 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT, use_default BOOL DEFAULT FALSE ) RETURNS TEXT[] AS $func$
16 collection TEXT[] := '{}'::TEXT[];
18 rtype := (vandelay.marc21_record_type( marc )).code;
19 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
20 IF ff_pos.tag = 'ldr' THEN
21 val := oils_xpath_string('//*[local-name()="leader"]', marc);
22 IF val IS NOT NULL THEN
23 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
24 collection := collection || val;
27 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
28 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
29 collection := collection || val;
32 CONTINUE WHEN NOT use_default;
33 CONTINUE WHEN ARRAY_UPPER(collection, 1) > 0;
34 val := REPEAT( ff_pos.default_val, ff_pos.length );
35 collection := collection || val;
40 $func$ LANGUAGE PLPGSQL;
42 DROP FUNCTION IF EXISTS vandelay.marc21_extract_fixed_field( text, text );
44 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT, use_default BOOL DEFAULT FALSE ) RETURNS TEXT AS $func$
51 rtype := (vandelay.marc21_record_type( marc )).code;
52 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
53 IF ff_pos.tag = 'ldr' THEN
54 val := oils_xpath_string('//*[local-name()="leader"]', marc);
55 IF val IS NOT NULL THEN
56 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
60 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
61 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
65 CONTINUE WHEN NOT use_default;
66 val := REPEAT( ff_pos.default_val, ff_pos.length );
72 $func$ LANGUAGE PLPGSQL;
74 DROP FUNCTION IF EXISTS vandelay.marc21_extract_all_fixed_fields( text );
76 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT, use_default BOOL DEFAULT FALSE ) RETURNS SETOF biblio.record_ff_map AS $func$
81 output biblio.record_ff_map%ROWTYPE;
83 rtype := (vandelay.marc21_record_type( marc )).code;
85 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
86 output.ff_name := ff_pos.fixed_field;
87 output.ff_value := NULL;
89 IF ff_pos.tag = 'ldr' THEN
90 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
91 IF output.ff_value IS NOT NULL THEN
92 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
94 output.ff_value := NULL;
97 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
98 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
99 CONTINUE WHEN output.ff_value IS NULL AND NOT use_default;
100 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
102 output.ff_value := NULL;
110 $func$ LANGUAGE PLPGSQL;
112 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
113 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
116 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$
117 SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
120 CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$
121 SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1), TRUE );
125 SELECT evergreen.upgrade_deps_block_check('0890', :eg_version);
127 CREATE OR REPLACE FUNCTION acq.transfer_fund(
129 old_amount IN NUMERIC, -- in currency of old fund
131 new_amount IN NUMERIC, -- in currency of new fund
133 xfer_note IN TEXT -- to be recorded in acq.fund_transfer
134 -- ,funding_source_in IN INT -- if user wants to specify a funding source (see notes)
136 /* -------------------------------------------------------------------------------
138 Function to transfer money from one fund to another.
140 A transfer is represented as a pair of entries in acq.fund_allocation, with a
141 negative amount for the old (losing) fund and a positive amount for the new
142 (gaining) fund. In some cases there may be more than one such pair of entries
143 in order to pull the money from different funding sources, or more specifically
144 from different funding source credits. For each such pair there is also an
145 entry in acq.fund_transfer.
147 Since funding_source is a non-nullable column in acq.fund_allocation, we must
148 choose a funding source for the transferred money to come from. This choice
149 must meet two constraints, so far as possible:
151 1. The amount transferred from a given funding source must not exceed the
152 amount allocated to the old fund by the funding source. To that end we
153 compare the amount being transferred to the amount allocated.
155 2. We shouldn't transfer money that has already been spent or encumbered, as
156 defined by the funding attribution process. We attribute expenses to the
157 oldest funding source credits first. In order to avoid transferring that
158 attributed money, we reverse the priority, transferring from the newest funding
159 source credits first. There can be no guarantee that this approach will
160 avoid overcommitting a fund, but no other approach can do any better.
162 In this context the age of a funding source credit is defined by the
163 deadline_date for credits with deadline_dates, and by the effective_date for
164 credits without deadline_dates, with the proviso that credits with deadline_dates
165 are all considered "older" than those without.
169 In the signature for this function, there is one last parameter commented out,
170 named "funding_source_in". Correspondingly, the WHERE clause for the query
171 driving the main loop has an OR clause commented out, which references the
172 funding_source_in parameter.
174 If these lines are uncommented, this function will allow the user optionally to
175 restrict a fund transfer to a specified funding source. If the source
176 parameter is left NULL, then there will be no such restriction.
178 ------------------------------------------------------------------------------- */
180 same_currency BOOLEAN;
181 currency_ratio NUMERIC;
182 old_fund_currency TEXT;
183 old_remaining NUMERIC; -- in currency of old fund
184 new_fund_currency TEXT;
185 new_fund_active BOOLEAN;
186 new_remaining NUMERIC; -- in currency of new fund
187 curr_old_amt NUMERIC; -- in currency of old fund
188 curr_new_amt NUMERIC; -- in currency of new fund
189 source_addition NUMERIC; -- in currency of funding source
190 source_deduction NUMERIC; -- in currency of funding source
191 orig_allocated_amt NUMERIC; -- in currency of funding source
192 allocated_amt NUMERIC; -- in currency of fund
198 IF old_fund IS NULL THEN
199 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
202 IF old_amount IS NULL THEN
203 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
206 -- The new fund and its amount must be both NULL or both not NULL.
208 IF new_fund IS NOT NULL AND new_amount IS NULL THEN
209 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
212 IF new_fund IS NULL AND new_amount IS NOT NULL THEN
213 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
216 IF user_id IS NULL THEN
217 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
220 -- Initialize the amounts to be transferred, each denominated
221 -- in the currency of its respective fund. They will be
222 -- reduced on each iteration of the loop.
224 old_remaining := old_amount;
225 new_remaining := new_amount;
227 -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
228 -- old_amount, old_fund, new_amount, new_fund;
230 -- Get the currency types of the old and new funds.
241 IF old_fund_currency IS NULL THEN
242 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
245 IF new_fund IS NOT NULL THEN
257 IF new_fund_currency IS NULL THEN
258 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
259 ELSIF NOT new_fund_active THEN
261 -- No point in putting money into a fund from whence you can't spend it
263 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
266 IF new_amount = old_amount THEN
267 same_currency := true;
271 -- We'll have to translate currency between funds. We presume that
272 -- the calling code has already applied an appropriate exchange rate,
273 -- so we'll apply the same conversion to each sub-transfer.
275 same_currency := false;
276 currency_ratio := new_amount / old_amount;
280 -- Identify the funding source(s) from which we want to transfer the money.
281 -- The principle is that we want to transfer the newest money first, because
282 -- we spend the oldest money first. The priority for spending is defined
283 -- by a sort of the view acq.ordered_funding_source_credit.
290 ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
294 acq.ordered_funding_source_credit AS ofsc,
295 acq.funding_source fs
297 ofsc.funding_source = fs.id
298 and ofsc.funding_source IN
300 SELECT funding_source
301 FROM acq.fund_allocation
302 WHERE fund = old_fund
306 -- ofsc.funding_source = funding_source_in
307 -- OR funding_source_in IS NULL
310 ofsc.sort_priority desc,
315 -- Determine how much money the old fund got from this funding source,
316 -- denominated in the currency types of the source and of the fund.
317 -- This result may reflect transfers from previous iterations.
320 COALESCE( sum( amount ), 0 ),
321 COALESCE( sum( amount )
322 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
324 orig_allocated_amt, -- in currency of the source
325 allocated_amt -- in currency of the old fund
330 and funding_source = source.funding_source;
332 -- Determine how much to transfer from this credit, in the currency
333 -- of the fund. Begin with the amount remaining to be attributed:
335 curr_old_amt := old_remaining;
337 -- Can't attribute more than was allocated from the fund:
339 IF curr_old_amt > allocated_amt THEN
340 curr_old_amt := allocated_amt;
343 -- Can't attribute more than the amount of the current credit:
345 IF curr_old_amt > source.converted_amt THEN
346 curr_old_amt := source.converted_amt;
349 curr_old_amt := trunc( curr_old_amt, 2 );
351 old_remaining := old_remaining - curr_old_amt;
353 -- Determine the amount to be deducted, if any,
354 -- from the old allocation.
356 IF old_remaining > 0 THEN
358 -- In this case we're using the whole allocation, so use that
359 -- amount directly instead of applying a currency translation
360 -- and thereby inviting round-off errors.
362 source_deduction := - curr_old_amt;
364 source_deduction := trunc(
366 acq.exchange_ratio( old_fund_currency, source.currency_type ),
370 IF source_deduction <> 0 THEN
372 -- Insert negative allocation for old fund in fund_allocation,
373 -- converted into the currency of the funding source
375 INSERT INTO acq.fund_allocation (
382 source.funding_source,
386 'Transfer to fund ' || new_fund
390 IF new_fund IS NOT NULL THEN
392 -- Determine how much to add to the new fund, in
393 -- its currency, and how much remains to be added:
395 IF same_currency THEN
396 curr_new_amt := curr_old_amt;
398 IF old_remaining = 0 THEN
400 -- This is the last iteration, so nothing should be left
402 curr_new_amt := new_remaining;
405 curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
406 new_remaining := new_remaining - curr_new_amt;
410 -- Determine how much to add, if any,
411 -- to the new fund's allocation.
413 IF old_remaining > 0 THEN
415 -- In this case we're using the whole allocation, so use that amount
416 -- amount directly instead of applying a currency translation and
417 -- thereby inviting round-off errors.
419 source_addition := curr_new_amt;
420 ELSIF source.currency_type = old_fund_currency THEN
422 -- In this case we don't need a round trip currency translation,
423 -- thereby inviting round-off errors:
425 source_addition := curr_old_amt;
427 source_addition := trunc(
429 acq.exchange_ratio( new_fund_currency, source.currency_type ),
433 IF source_addition <> 0 THEN
435 -- Insert positive allocation for new fund in fund_allocation,
436 -- converted to the currency of the founding source
438 INSERT INTO acq.fund_allocation (
445 source.funding_source,
449 'Transfer from fund ' || old_fund
454 IF trunc( curr_old_amt, 2 ) <> 0
455 OR trunc( curr_new_amt, 2 ) <> 0 THEN
457 -- Insert row in fund_transfer, using amounts in the currency of the funds
459 INSERT INTO acq.fund_transfer (
466 funding_source_credit
469 trunc( curr_old_amt, 2 ),
471 trunc( curr_new_amt, 2 ),
478 if old_remaining <= 0 THEN
479 EXIT; -- Nothing more to be transferred
486 SELECT evergreen.upgrade_deps_block_check('0891', :eg_version);
488 UPDATE permission.perm_list
489 SET description = 'Allows a user to process and verify URLs'
490 WHERE code = 'URL_VERIFY';