Open-ILS/src/sql/Pg/upgrade/0780.schema.acq.distribution_formula.expansion.sql

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