Open-ILS/src/sql/Pg/upgrade/1113.function.acq.rollover-by-org-tree.sql

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