Open-ILS/src/sql/Pg/upgrade/0730.schema.acq-function-dedup.sql

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