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

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