Open-ILS/src/sql/Pg/version-upgrade/2.5.6-2.5.7-upgrade-db.sql

   1 --Upgrade Script for 2.5.6 to 2.5.7
   2 \set eg_version '''2.5.7'''
   3 BEGIN;
   4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.5.7', :eg_version);
   5
   6 SELECT evergreen.upgrade_deps_block_check('0890', :eg_version);
   7
   8 CREATE OR REPLACE FUNCTION acq.transfer_fund(
   9         old_fund   IN INT,
  10         old_amount IN NUMERIC,     -- in currency of old fund
  11         new_fund   IN INT,
  12         new_amount IN NUMERIC,     -- in currency of new fund
  13         user_id    IN INT,
  14         xfer_note  IN TEXT         -- to be recorded in acq.fund_transfer
  15         -- ,funding_source_in IN INT  -- if user wants to specify a funding source (see notes)
  16 ) RETURNS VOID AS $$
  17 /* -------------------------------------------------------------------------------
  18
  19 Function to transfer money from one fund to another.
  20
  21 A transfer is represented as a pair of entries in acq.fund_allocation, with a
  22 negative amount for the old (losing) fund and a positive amount for the new
  23 (gaining) fund.  In some cases there may be more than one such pair of entries
  24 in order to pull the money from different funding sources, or more specifically
  25 from different funding source credits.  For each such pair there is also an
  26 entry in acq.fund_transfer.
  27
  28 Since funding_source is a non-nullable column in acq.fund_allocation, we must
  29 choose a funding source for the transferred money to come from.  This choice
  30 must meet two constraints, so far as possible:
  31
  32 1. The amount transferred from a given funding source must not exceed the
  33 amount allocated to the old fund by the funding source.  To that end we
  34 compare the amount being transferred to the amount allocated.
  35
  36 2. We shouldn't transfer money that has already been spent or encumbered, as
  37 defined by the funding attribution process.  We attribute expenses to the
  38 oldest funding source credits first.  In order to avoid transferring that
  39 attributed money, we reverse the priority, transferring from the newest funding
  40 source credits first.  There can be no guarantee that this approach will
  41 avoid overcommitting a fund, but no other approach can do any better.
  42
  43 In this context the age of a funding source credit is defined by the
  44 deadline_date for credits with deadline_dates, and by the effective_date for
  45 credits without deadline_dates, with the proviso that credits with deadline_dates
  46 are all considered "older" than those without.
  47
  48 ----------
  49
  50 In the signature for this function, there is one last parameter commented out,
  51 named "funding_source_in".  Correspondingly, the WHERE clause for the query
  52 driving the main loop has an OR clause commented out, which references the
  53 funding_source_in parameter.
  54
  55 If these lines are uncommented, this function will allow the user optionally to
  56 restrict a fund transfer to a specified funding source.  If the source
  57 parameter is left NULL, then there will be no such restriction.
  58
  59 ------------------------------------------------------------------------------- */
  60 DECLARE
  61         same_currency      BOOLEAN;
  62         currency_ratio     NUMERIC;
  63         old_fund_currency  TEXT;
  64         old_remaining      NUMERIC;  -- in currency of old fund
  65         new_fund_currency  TEXT;
  66         new_fund_active    BOOLEAN;
  67         new_remaining      NUMERIC;  -- in currency of new fund
  68         curr_old_amt       NUMERIC;  -- in currency of old fund
  69         curr_new_amt       NUMERIC;  -- in currency of new fund
  70         source_addition    NUMERIC;  -- in currency of funding source
  71         source_deduction   NUMERIC;  -- in currency of funding source
  72         orig_allocated_amt NUMERIC;  -- in currency of funding source
  73         allocated_amt      NUMERIC;  -- in currency of fund
  74         source             RECORD;
  75 BEGIN
  76         --
  77         -- Sanity checks
  78         --
  79         IF old_fund IS NULL THEN
  80                 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
  81         END IF;
  82         --
  83         IF old_amount IS NULL THEN
  84                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
  85         END IF;
  86         --
  87         -- The new fund and its amount must be both NULL or both not NULL.
  88         --
  89         IF new_fund IS NOT NULL AND new_amount IS NULL THEN
  90                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
  91         END IF;
  92         --
  93         IF new_fund IS NULL AND new_amount IS NOT NULL THEN
  94                 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
  95         END IF;
  96         --
  97         IF user_id IS NULL THEN
  98                 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
  99         END IF;
 100         --
 101         -- Initialize the amounts to be transferred, each denominated
 102         -- in the currency of its respective fund.  They will be
 103         -- reduced on each iteration of the loop.
 104         --
 105         old_remaining := old_amount;
 106         new_remaining := new_amount;
 107         --
 108         -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
 109         --      old_amount, old_fund, new_amount, new_fund;
 110         --
 111         -- Get the currency types of the old and new funds.
 112         --
 113         SELECT
 114                 currency_type
 115         INTO
 116                 old_fund_currency
 117         FROM
 118                 acq.fund
 119         WHERE
 120                 id = old_fund;
 121         --
 122         IF old_fund_currency IS NULL THEN
 123                 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
 124         END IF;
 125         --
 126         IF new_fund IS NOT NULL THEN
 127                 SELECT
 128                         currency_type,
 129                         active
 130                 INTO
 131                         new_fund_currency,
 132                         new_fund_active
 133                 FROM
 134                         acq.fund
 135                 WHERE
 136                         id = new_fund;
 137                 --
 138                 IF new_fund_currency IS NULL THEN
 139                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
 140                 ELSIF NOT new_fund_active THEN
 141                         --
 142                         -- No point in putting money into a fund from whence you can't spend it
 143                         --
 144                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
 145                 END IF;
 146                 --
 147                 IF new_amount = old_amount THEN
 148                         same_currency := true;
 149                         currency_ratio := 1;
 150                 ELSE
 151                         --
 152                         -- We'll have to translate currency between funds.  We presume that
 153                         -- the calling code has already applied an appropriate exchange rate,
 154                         -- so we'll apply the same conversion to each sub-transfer.
 155                         --
 156                         same_currency := false;
 157                         currency_ratio := new_amount / old_amount;
 158                 END IF;
 159         END IF;
 160         --
 161         -- Identify the funding source(s) from which we want to transfer the money.
 162         -- The principle is that we want to transfer the newest money first, because
 163         -- we spend the oldest money first.  The priority for spending is defined
 164         -- by a sort of the view acq.ordered_funding_source_credit.
 165         --
 166         FOR source in
 167                 SELECT
 168                         ofsc.id,
 169                         ofsc.funding_source,
 170                         ofsc.amount,
 171                         ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
 172                                 AS converted_amt,
 173                         fs.currency_type
 174                 FROM
 175                         acq.ordered_funding_source_credit AS ofsc,
 176                         acq.funding_source fs
 177                 WHERE
 178                         ofsc.funding_source = fs.id
 179                         and ofsc.funding_source IN
 180                         (
 181                                 SELECT funding_source
 182                                 FROM acq.fund_allocation
 183                                 WHERE fund = old_fund
 184                         )
 185                         -- and
 186                         -- (
 187                         --      ofsc.funding_source = funding_source_in
 188                         --      OR funding_source_in IS NULL
 189                         -- )
 190                 ORDER BY
 191                         ofsc.sort_priority desc,
 192                         ofsc.sort_date desc,
 193                         ofsc.id desc
 194         LOOP
 195                 --
 196                 -- Determine how much money the old fund got from this funding source,
 197                 -- denominated in the currency types of the source and of the fund.
 198                 -- This result may reflect transfers from previous iterations.
 199                 --
 200                 SELECT
 201                         COALESCE( sum( amount ), 0 ),
 202                         COALESCE( sum( amount )
 203                                 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
 204                 INTO
 205                         orig_allocated_amt,     -- in currency of the source
 206                         allocated_amt           -- in currency of the old fund
 207                 FROM
 208                         acq.fund_allocation
 209                 WHERE
 210                         fund = old_fund
 211                         and funding_source = source.funding_source;
 212                 --
 213                 -- Determine how much to transfer from this credit, in the currency
 214                 -- of the fund.   Begin with the amount remaining to be attributed:
 215                 --
 216                 curr_old_amt := old_remaining;
 217                 --
 218                 -- Can't attribute more than was allocated from the fund:
 219                 --
 220                 IF curr_old_amt > allocated_amt THEN
 221                         curr_old_amt := allocated_amt;
 222                 END IF;
 223                 --
 224                 -- Can't attribute more than the amount of the current credit:
 225                 --
 226                 IF curr_old_amt > source.converted_amt THEN
 227                         curr_old_amt := source.converted_amt;
 228                 END IF;
 229                 --
 230                 curr_old_amt := trunc( curr_old_amt, 2 );
 231                 --
 232                 old_remaining := old_remaining - curr_old_amt;
 233                 --
 234                 -- Determine the amount to be deducted, if any,
 235                 -- from the old allocation.
 236                 --
 237                 IF old_remaining > 0 THEN
 238                         --
 239                         -- In this case we're using the whole allocation, so use that
 240                         -- amount directly instead of applying a currency translation
 241                         -- and thereby inviting round-off errors.
 242                         --
 243                         source_deduction := - curr_old_amt;
 244                 ELSE
 245                         source_deduction := trunc(
 246                                 ( - curr_old_amt ) *
 247                                         acq.exchange_ratio( old_fund_currency, source.currency_type ),
 248                                 2 );
 249                 END IF;
 250                 --
 251                 IF source_deduction <> 0 THEN
 252                         --
 253                         -- Insert negative allocation for old fund in fund_allocation,
 254                         -- converted into the currency of the funding source
 255                         --
 256                         INSERT INTO acq.fund_allocation (
 257                                 funding_source,
 258                                 fund,
 259                                 amount,
 260                                 allocator,
 261                                 note
 262                         ) VALUES (
 263                                 source.funding_source,
 264                                 old_fund,
 265                                 source_deduction,
 266                                 user_id,
 267                                 'Transfer to fund ' || new_fund
 268                         );
 269                 END IF;
 270                 --
 271                 IF new_fund IS NOT NULL THEN
 272                         --
 273                         -- Determine how much to add to the new fund, in
 274                         -- its currency, and how much remains to be added:
 275                         --
 276                         IF same_currency THEN
 277                                 curr_new_amt := curr_old_amt;
 278                         ELSE
 279                                 IF old_remaining = 0 THEN
 280                                         --
 281                                         -- This is the last iteration, so nothing should be left
 282                                         --
 283                                         curr_new_amt := new_remaining;
 284                                         new_remaining := 0;
 285                                 ELSE
 286                                         curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
 287                                         new_remaining := new_remaining - curr_new_amt;
 288                                 END IF;
 289                         END IF;
 290                         --
 291                         -- Determine how much to add, if any,
 292                         -- to the new fund's allocation.
 293                         --
 294                         IF old_remaining > 0 THEN
 295                                 --
 296                                 -- In this case we're using the whole allocation, so use that amount
 297                                 -- amount directly instead of applying a currency translation and
 298                                 -- thereby inviting round-off errors.
 299                                 --
 300                                 source_addition := curr_new_amt;
 301                         ELSIF source.currency_type = old_fund_currency THEN
 302                                 --
 303                                 -- In this case we don't need a round trip currency translation,
 304                                 -- thereby inviting round-off errors:
 305                                 --
 306                                 source_addition := curr_old_amt;
 307                         ELSE
 308                                 source_addition := trunc(
 309                                         curr_new_amt *
 310                                                 acq.exchange_ratio( new_fund_currency, source.currency_type ),
 311                                         2 );
 312                         END IF;
 313                         --
 314                         IF source_addition <> 0 THEN
 315                                 --
 316                                 -- Insert positive allocation for new fund in fund_allocation,
 317                                 -- converted to the currency of the founding source
 318                                 --
 319                                 INSERT INTO acq.fund_allocation (
 320                                         funding_source,
 321                                         fund,
 322                                         amount,
 323                                         allocator,
 324                                         note
 325                                 ) VALUES (
 326                                         source.funding_source,
 327                                         new_fund,
 328                                         source_addition,
 329                                         user_id,
 330                                         'Transfer from fund ' || old_fund
 331                                 );
 332                         END IF;
 333                 END IF;
 334                 --
 335                 IF trunc( curr_old_amt, 2 ) <> 0
 336                 OR trunc( curr_new_amt, 2 ) <> 0 THEN
 337                         --
 338                         -- Insert row in fund_transfer, using amounts in the currency of the funds
 339                         --
 340                         INSERT INTO acq.fund_transfer (
 341                                 src_fund,
 342                                 src_amount,
 343                                 dest_fund,
 344                                 dest_amount,
 345                                 transfer_user,
 346                                 note,
 347                                 funding_source_credit
 348                         ) VALUES (
 349                                 old_fund,
 350                                 trunc( curr_old_amt, 2 ),
 351                                 new_fund,
 352                                 trunc( curr_new_amt, 2 ),
 353                                 user_id,
 354                                 xfer_note,
 355                                 source.id
 356                         );
 357                 END IF;
 358                 --
 359                 if old_remaining <= 0 THEN
 360                         EXIT;                   -- Nothing more to be transferred
 361                 END IF;
 362         END LOOP;
 363 END;
 364 $$ LANGUAGE plpgsql;
 365
 366
 367 SELECT evergreen.upgrade_deps_block_check('0891', :eg_version);
 368
 369 UPDATE permission.perm_list
 370 SET description = 'Allows a user to process and verify URLs'
 371 WHERE code = 'URL_VERIFY';
 372
 373 COMMIT;