Open-ILS/src/sql/Pg/version-upgrade/3.0.9-3.0.10-upgrade-db.sql

   1 --Upgrade Script for 3.0.9 to 3.0.10
   2 \set eg_version '''3.0.10'''
   3 BEGIN;
   4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.0.10', :eg_version);
   5
   6 SELECT evergreen.upgrade_deps_block_check('1113', :eg_version);
   7
   8 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
   9         old_year INTEGER,
  10         user_id INTEGER,
  11         org_unit_id INTEGER,
  12     encumb_only BOOL DEFAULT FALSE,
  13     include_desc BOOL DEFAULT TRUE
  14 ) RETURNS VOID AS $$
  15 DECLARE
  16 --
  17 new_fund    INT;
  18 new_year    INT := old_year + 1;
  19 org_found   BOOL;
  20 perm_ous    BOOL;
  21 xfer_amount NUMERIC := 0;
  22 roll_fund   RECORD;
  23 deb         RECORD;
  24 detail      RECORD;
  25 roll_distrib_forms BOOL;
  26 --
  27 BEGIN
  28         --
  29         -- Sanity checks
  30         --
  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';
  35         END IF;
  36         --
  37         IF user_id IS NULL THEN
  38                 RAISE EXCEPTION 'Input user id argument is NULL';
  39         END IF;
  40         --
  41         IF org_unit_id IS NULL THEN
  42                 RAISE EXCEPTION 'Org unit id argument is NULL';
  43         ELSE
  44                 --
  45                 -- Validate the org unit
  46                 --
  47                 SELECT TRUE
  48                 INTO org_found
  49                 FROM actor.org_unit
  50                 WHERE id = org_unit_id;
  51                 --
  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
  58                         );
  59                         IF NOT FOUND OR NOT perm_ous THEN
  60                                 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
  61                         END IF;
  62                 END IF;
  63         END IF;
  64         --
  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.
  67         --
  68         FOR roll_fund in
  69         SELECT
  70             oldf.id AS old_fund,
  71             oldf.org,
  72             oldf.name,
  73             oldf.currency_type,
  74             oldf.code,
  75                 oldf.rollover,
  76             newf.id AS new_fund_id
  77         FROM
  78         acq.fund AS oldf
  79         LEFT JOIN acq.fund AS newf
  80                 ON ( oldf.code = newf.code AND oldf.org = newf.org )
  81         WHERE
  82                     oldf.year = old_year
  83                 AND oldf.propagate
  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 ) )
  87         LOOP
  88                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
  89                 --
  90                 IF roll_fund.new_fund_id IS NULL THEN
  91                         --
  92                         -- The old fund hasn't been propagated yet.  Propagate it now.
  93                         --
  94                         INSERT INTO acq.fund (
  95                                 org,
  96                                 name,
  97                                 year,
  98                                 currency_type,
  99                                 code,
 100                                 rollover,
 101                                 propagate,
 102                                 balance_warning_percent,
 103                                 balance_stop_percent
 104                         ) VALUES (
 105                                 roll_fund.org,
 106                                 roll_fund.name,
 107                                 new_year,
 108                                 roll_fund.currency_type,
 109                                 roll_fund.code,
 110                                 true,
 111                                 true,
 112                                 roll_fund.balance_warning_percent,
 113                                 roll_fund.balance_stop_percent
 114                         )
 115                         RETURNING id INTO new_fund;
 116
 117                         PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
 118
 119                 ELSE
 120                         new_fund = roll_fund.new_fund_id;
 121                 END IF;
 122                 --
 123                 -- Determine the amount to transfer
 124                 --
 125                 SELECT amount
 126                 INTO xfer_amount
 127                 FROM acq.fund_spent_balance
 128                 WHERE fund = roll_fund.old_fund;
 129                 --
 130                 IF xfer_amount <> 0 THEN
 131                         IF NOT encumb_only AND roll_fund.rollover THEN
 132                                 --
 133                                 -- Transfer balance from old fund to new
 134                                 --
 135                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
 136                                 --
 137                                 PERFORM acq.transfer_fund(
 138                                         roll_fund.old_fund,
 139                                         xfer_amount,
 140                                         new_fund,
 141                                         xfer_amount,
 142                                         user_id,
 143                                         'Rollover'
 144                                 );
 145                         ELSE
 146                                 --
 147                                 -- Transfer balance from old fund to the void
 148                                 --
 149                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
 150                                 --
 151                                 PERFORM acq.transfer_fund(
 152                                         roll_fund.old_fund,
 153                                         xfer_amount,
 154                                         NULL,
 155                                         NULL,
 156                                         user_id,
 157                                         'Rollover into the void'
 158                                 );
 159                         END IF;
 160                 END IF;
 161                 --
 162                 IF roll_fund.rollover THEN
 163                         --
 164                         -- Move any lineitems from the old fund to the new one
 165                         -- where the associated debit is an encumbrance.
 166                         --
 167                         -- Any other tables tying expenditure details to funds should
 168                         -- receive similar treatment.  At this writing there are none.
 169                         --
 170                         UPDATE acq.lineitem_detail
 171                         SET fund = new_fund
 172                         WHERE
 173                         fund = roll_fund.old_fund -- this condition may be redundant
 174                         AND fund_debit in
 175                         (
 176                                 SELECT id
 177                                 FROM acq.fund_debit
 178                                 WHERE
 179                                 fund = roll_fund.old_fund
 180                                 AND encumbrance
 181                         );
 182                         --
 183                         -- Move encumbrance debits from the old fund to the new fund
 184                         --
 185                         UPDATE acq.fund_debit
 186                         SET fund = new_fund
 187                         wHERE
 188                                 fund = roll_fund.old_fund
 189                                 AND encumbrance;
 190                 END IF;
 191
 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
 196                         );
 197
 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;
 202                 END IF;
 203
 204                 --
 205                 -- Mark old fund as inactive, now that we've closed it
 206                 --
 207                 UPDATE acq.fund
 208                 SET active = FALSE
 209                 WHERE id = roll_fund.old_fund;
 210         END LOOP;
 211 END;
 212 $$ LANGUAGE plpgsql;
 213
 214 COMMIT;