Open-ILS/src/sql/Pg/upgrade/1275.schema.update_fund_xfr_notes.sql

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