1 --Upgrade Script for 3.1.3 to 3.1.4
2 \set eg_version '''3.1.4'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.1.4', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1113', :eg_version);
8 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
12 encumb_only BOOL DEFAULT FALSE,
13 include_desc BOOL DEFAULT TRUE
18 new_year INT := old_year + 1;
21 xfer_amount NUMERIC := 0;
25 roll_distrib_forms BOOL;
31 IF old_year IS NULL THEN
32 RAISE EXCEPTION 'Input year argument is NULL';
33 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
34 RAISE EXCEPTION 'Input year is out of range';
37 IF user_id IS NULL THEN
38 RAISE EXCEPTION 'Input user id argument is NULL';
41 IF org_unit_id IS NULL THEN
42 RAISE EXCEPTION 'Org unit id argument is NULL';
45 -- Validate the org unit
50 WHERE id = org_unit_id;
52 IF org_found IS NULL THEN
53 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
54 ELSIF encumb_only THEN
55 SELECT INTO perm_ous value::BOOL FROM
56 actor.org_unit_ancestor_setting(
57 'acq.fund.allow_rollover_without_money', org_unit_id
59 IF NOT FOUND OR NOT perm_ous THEN
60 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
65 -- Loop over the propagable funds to identify the details
66 -- from the old fund plus the id of the new one, if it exists.
76 newf.id AS new_fund_id
79 LEFT JOIN acq.fund AS newf
80 ON ( oldf.code = newf.code AND oldf.org = newf.org )
84 AND newf.year = new_year
85 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
86 OR (NOT include_desc AND oldf.org = org_unit_id ) )
88 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
90 IF roll_fund.new_fund_id IS NULL THEN
92 -- The old fund hasn't been propagated yet. Propagate it now.
94 INSERT INTO acq.fund (
102 balance_warning_percent,
108 roll_fund.currency_type,
112 roll_fund.balance_warning_percent,
113 roll_fund.balance_stop_percent
115 RETURNING id INTO new_fund;
117 PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
120 new_fund = roll_fund.new_fund_id;
123 -- Determine the amount to transfer
127 FROM acq.fund_spent_balance
128 WHERE fund = roll_fund.old_fund;
130 IF xfer_amount <> 0 THEN
131 IF NOT encumb_only AND roll_fund.rollover THEN
133 -- Transfer balance from old fund to new
135 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
137 PERFORM acq.transfer_fund(
147 -- Transfer balance from old fund to the void
149 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
151 PERFORM acq.transfer_fund(
157 'Rollover into the void'
162 IF roll_fund.rollover THEN
164 -- Move any lineitems from the old fund to the new one
165 -- where the associated debit is an encumbrance.
167 -- Any other tables tying expenditure details to funds should
168 -- receive similar treatment. At this writing there are none.
170 UPDATE acq.lineitem_detail
173 fund = roll_fund.old_fund -- this condition may be redundant
179 fund = roll_fund.old_fund
183 -- Move encumbrance debits from the old fund to the new fund
185 UPDATE acq.fund_debit
188 fund = roll_fund.old_fund
192 -- Rollover distribution formulae funds
193 SELECT INTO roll_distrib_forms value::BOOL FROM
194 actor.org_unit_ancestor_setting(
195 'acq.fund.rollover_distrib_forms', org_unit_id
198 IF roll_distrib_forms THEN
199 UPDATE acq.distribution_formula_entry
200 SET fund = roll_fund.new_fund_id
201 WHERE fund = roll_fund.old_fund;
205 -- Mark old fund as inactive, now that we've closed it
209 WHERE id = roll_fund.old_fund;
216 SELECT evergreen.upgrade_deps_block_check('1114', :eg_version);
218 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
221 the_copy asset.copy%ROWTYPE;
224 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
225 IF NOT FOUND THEN RETURN NULL; END IF;
227 IF the_copy.status = 3 THEN -- Lost
229 ELSIF the_copy.status = 4 THEN -- Missing
231 ELSIF the_copy.status = 14 THEN -- Damaged
233 ELSIF the_copy.status = 17 THEN -- Lost and paid
234 RETURN 'LOST_AND_PAID';
237 SELECT stop_fines INTO last_circ_stop
238 FROM action.circulation
239 WHERE target_copy = cid AND checkin_time IS NULL
240 ORDER BY xact_start DESC LIMIT 1;
243 IF last_circ_stop IN (
244 'CLAIMSNEVERCHECKEDOUT',
248 RETURN last_circ_stop;