Open-ILS/src/sql/Pg/upgrade/0926.fund_tags_persist.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0926', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION acq.copy_fund_tags(
   6         old_fund_id INTEGER,
   7         new_fund_id INTEGER
   8 ) RETURNS VOID AS $$
   9 DECLARE
  10 fund_tag_rec    RECORD;
  11 BEGIN
  12
  13         FOR fund_tag_rec IN SELECT * FROM acq.fund_tag_map WHERE fund=old_fund_id LOOP
  14                 BEGIN
  15                      INSERT INTO acq.fund_tag_map(fund, tag) VALUES(new_fund_id, fund_tag_rec.tag);
  16                 EXCEPTION
  17                         WHEN unique_violation THEN
  18                         --    RAISE NOTICE 'Fund tag already propagated', old_fund.id;
  19                         CONTINUE;
  20                 END;
  21         END LOOP;
  22         RETURN;
  23 END;
  24 $$ LANGUAGE plpgsql;
  25
  26 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
  27         old_year INTEGER,
  28         user_id INTEGER,
  29         org_unit_id INTEGER,
  30     encumb_only BOOL DEFAULT FALSE,
  31     include_desc BOOL DEFAULT TRUE
  32 ) RETURNS VOID AS $$
  33 DECLARE
  34 --
  35 new_fund    INT;
  36 new_year    INT := old_year + 1;
  37 org_found   BOOL;
  38 perm_ous    BOOL;
  39 xfer_amount NUMERIC := 0;
  40 roll_fund   RECORD;
  41 deb         RECORD;
  42 detail      RECORD;
  43 roll_distrib_forms BOOL;
  44 --
  45 BEGIN
  46         --
  47         -- Sanity checks
  48         --
  49         IF old_year IS NULL THEN
  50                 RAISE EXCEPTION 'Input year argument is NULL';
  51     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
  52         RAISE EXCEPTION 'Input year is out of range';
  53         END IF;
  54         --
  55         IF user_id IS NULL THEN
  56                 RAISE EXCEPTION 'Input user id argument is NULL';
  57         END IF;
  58         --
  59         IF org_unit_id IS NULL THEN
  60                 RAISE EXCEPTION 'Org unit id argument is NULL';
  61         ELSE
  62                 --
  63                 -- Validate the org unit
  64                 --
  65                 SELECT TRUE
  66                 INTO org_found
  67                 FROM actor.org_unit
  68                 WHERE id = org_unit_id;
  69                 --
  70                 IF org_found IS NULL THEN
  71                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
  72                 ELSIF encumb_only THEN
  73                         SELECT INTO perm_ous value::BOOL FROM
  74                         actor.org_unit_ancestor_setting(
  75                                 'acq.fund.allow_rollover_without_money', org_unit_id
  76                         );
  77                         IF NOT FOUND OR NOT perm_ous THEN
  78                                 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
  79                         END IF;
  80                 END IF;
  81         END IF;
  82         --
  83         -- Loop over the propagable funds to identify the details
  84         -- from the old fund plus the id of the new one, if it exists.
  85         --
  86         FOR roll_fund in
  87         SELECT
  88             oldf.id AS old_fund,
  89             oldf.org,
  90             oldf.name,
  91             oldf.currency_type,
  92             oldf.code,
  93                 oldf.rollover,
  94             newf.id AS new_fund_id
  95         FROM
  96         acq.fund AS oldf
  97         LEFT JOIN acq.fund AS newf
  98                 ON ( oldf.code = newf.code )
  99         WHERE
 100                     oldf.year = old_year
 101                 AND oldf.propagate
 102         AND newf.year = new_year
 103                 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
 104                 OR (NOT include_desc AND oldf.org = org_unit_id ) )
 105         LOOP
 106                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
 107                 --
 108                 IF roll_fund.new_fund_id IS NULL THEN
 109                         --
 110                         -- The old fund hasn't been propagated yet.  Propagate it now.
 111                         --
 112                         INSERT INTO acq.fund (
 113                                 org,
 114                                 name,
 115                                 year,
 116                                 currency_type,
 117                                 code,
 118                                 rollover,
 119                                 propagate,
 120                                 balance_warning_percent,
 121                                 balance_stop_percent
 122                         ) VALUES (
 123                                 roll_fund.org,
 124                                 roll_fund.name,
 125                                 new_year,
 126                                 roll_fund.currency_type,
 127                                 roll_fund.code,
 128                                 true,
 129                                 true,
 130                                 roll_fund.balance_warning_percent,
 131                                 roll_fund.balance_stop_percent
 132                         )
 133                         RETURNING id INTO new_fund;
 134
 135                         PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
 136
 137                 ELSE
 138                         new_fund = roll_fund.new_fund_id;
 139                 END IF;
 140                 --
 141                 -- Determine the amount to transfer
 142                 --
 143                 SELECT amount
 144                 INTO xfer_amount
 145                 FROM acq.fund_spent_balance
 146                 WHERE fund = roll_fund.old_fund;
 147                 --
 148                 IF xfer_amount <> 0 THEN
 149                         IF NOT encumb_only AND roll_fund.rollover THEN
 150                                 --
 151                                 -- Transfer balance from old fund to new
 152                                 --
 153                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
 154                                 --
 155                                 PERFORM acq.transfer_fund(
 156                                         roll_fund.old_fund,
 157                                         xfer_amount,
 158                                         new_fund,
 159                                         xfer_amount,
 160                                         user_id,
 161                                         'Rollover'
 162                                 );
 163                         ELSE
 164                                 --
 165                                 -- Transfer balance from old fund to the void
 166                                 --
 167                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
 168                                 --
 169                                 PERFORM acq.transfer_fund(
 170                                         roll_fund.old_fund,
 171                                         xfer_amount,
 172                                         NULL,
 173                                         NULL,
 174                                         user_id,
 175                                         'Rollover into the void'
 176                                 );
 177                         END IF;
 178                 END IF;
 179                 --
 180                 IF roll_fund.rollover THEN
 181                         --
 182                         -- Move any lineitems from the old fund to the new one
 183                         -- where the associated debit is an encumbrance.
 184                         --
 185                         -- Any other tables tying expenditure details to funds should
 186                         -- receive similar treatment.  At this writing there are none.
 187                         --
 188                         UPDATE acq.lineitem_detail
 189                         SET fund = new_fund
 190                         WHERE
 191                         fund = roll_fund.old_fund -- this condition may be redundant
 192                         AND fund_debit in
 193                         (
 194                                 SELECT id
 195                                 FROM acq.fund_debit
 196                                 WHERE
 197                                 fund = roll_fund.old_fund
 198                                 AND encumbrance
 199                         );
 200                         --
 201                         -- Move encumbrance debits from the old fund to the new fund
 202                         --
 203                         UPDATE acq.fund_debit
 204                         SET fund = new_fund
 205                         wHERE
 206                                 fund = roll_fund.old_fund
 207                                 AND encumbrance;
 208                 END IF;
 209
 210                 -- Rollover distribution formulae funds
 211                 SELECT INTO roll_distrib_forms value::BOOL FROM
 212                         actor.org_unit_ancestor_setting(
 213                                 'acq.fund.rollover_distrib_forms', org_unit_id
 214                         );
 215
 216                 IF roll_distrib_forms THEN
 217                         UPDATE acq.distribution_formula_entry
 218                                 SET fund = roll_fund.new_fund_id
 219                                 WHERE fund = roll_fund.old_fund;
 220                 END IF;
 221
 222                 --
 223                 -- Mark old fund as inactive, now that we've closed it
 224                 --
 225                 UPDATE acq.fund
 226                 SET active = FALSE
 227                 WHERE id = roll_fund.old_fund;
 228         END LOOP;
 229 END;
 230 $$ LANGUAGE plpgsql;
 231
 232 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
 233         old_year INTEGER,
 234         user_id INTEGER,
 235         org_unit_id INTEGER,
 236     include_desc BOOL DEFAULT TRUE
 237 ) RETURNS VOID AS $$
 238 DECLARE
 239 --
 240 new_id      INT;
 241 old_fund    RECORD;
 242 org_found   BOOLEAN;
 243 --
 244 BEGIN
 245         --
 246         -- Sanity checks
 247         --
 248         IF old_year IS NULL THEN
 249                 RAISE EXCEPTION 'Input year argument is NULL';
 250         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
 251                 RAISE EXCEPTION 'Input year is out of range';
 252         END IF;
 253         --
 254         IF user_id IS NULL THEN
 255                 RAISE EXCEPTION 'Input user id argument is NULL';
 256         END IF;
 257         --
 258         IF org_unit_id IS NULL THEN
 259                 RAISE EXCEPTION 'Org unit id argument is NULL';
 260         ELSE
 261                 SELECT TRUE INTO org_found
 262                 FROM actor.org_unit
 263                 WHERE id = org_unit_id;
 264                 --
 265                 IF org_found IS NULL THEN
 266                         RAISE EXCEPTION 'Org unit id is invalid';
 267                 END IF;
 268         END IF;
 269         --
 270         -- Loop over the applicable funds
 271         --
 272         FOR old_fund in SELECT * FROM acq.fund
 273         WHERE
 274                 year = old_year
 275                 AND propagate
 276                 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
 277                 OR (NOT include_desc AND org = org_unit_id ) )
 278
 279         LOOP
 280                 BEGIN
 281                         INSERT INTO acq.fund (
 282                                 org,
 283                                 name,
 284                                 year,
 285                                 currency_type,
 286                                 code,
 287                                 rollover,
 288                                 propagate,
 289                                 balance_warning_percent,
 290                                 balance_stop_percent
 291                         ) VALUES (
 292                                 old_fund.org,
 293                                 old_fund.name,
 294                                 old_year + 1,
 295                                 old_fund.currency_type,
 296                                 old_fund.code,
 297                                 old_fund.rollover,
 298                                 true,
 299                                 old_fund.balance_warning_percent,
 300                                 old_fund.balance_stop_percent
 301                         )
 302                         RETURNING id INTO new_id;
 303                 EXCEPTION
 304                         WHEN unique_violation THEN
 305                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
 306                                 CONTINUE;
 307                 END;
 308
 309                 PERFORM acq.copy_fund_tags(old_fund.id,new_id);
 310
 311                 --RAISE NOTICE 'Propagating fund % to fund %',
 312                 --      old_fund.code, new_id;
 313         END LOOP;
 314 END;
 315 $$ LANGUAGE plpgsql;
 316
 317 COMMIT;
 318
 319