Open-ILS/src/sql/Pg/upgrade/0147.schema.acq.transfer-fund.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0147'); -- Scott McKellar
   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 BEGIN
  73         --
  74         -- Sanity checks
  75         --
  76         IF old_fund IS NULL THEN
  77                 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
  78         END IF;
  79         --
  80         IF old_amount IS NULL THEN
  81                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
  82         END IF;
  83         --
  84         -- The new fund and its amount must be both NULL or both not NULL.
  85         --
  86         IF new_fund IS NOT NULL AND new_amount IS NULL THEN
  87                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
  88         END IF;
  89         --
  90         IF new_fund IS NULL AND new_amount IS NOT NULL THEN
  91                 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
  92         END IF;
  93         --
  94         IF user_id IS NULL THEN
  95                 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
  96         END IF;
  97         --
  98         -- Initialize the amounts to be transferred, each denominated
  99         -- in the currency of its respective fund.  They will be
 100         -- reduced on each iteration of the loop.
 101         --
 102         old_remaining := old_amount;
 103         new_remaining := new_amount;
 104         --
 105         -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
 106         --      old_amount, old_fund, new_amount, new_fund;
 107         --
 108         -- Get the currency types of the old and new funds.
 109         --
 110         SELECT
 111                 currency_type
 112         INTO
 113                 old_fund_currency
 114         FROM
 115                 acq.fund
 116         WHERE
 117                 id = old_fund;
 118         --
 119         IF old_fund_currency IS NULL THEN
 120                 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
 121         END IF;
 122         --
 123         IF new_fund IS NOT NULL THEN
 124                 SELECT
 125                         currency_type,
 126                         active
 127                 INTO
 128                         new_fund_currency,
 129                         new_fund_active
 130                 FROM
 131                         acq.fund
 132                 WHERE
 133                         id = new_fund;
 134                 --
 135                 IF new_fund_currency IS NULL THEN
 136                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
 137                 ELSIF NOT new_fund_active THEN
 138                         --
 139                         -- No point in putting money into a fund from whence you can't spend it
 140                         --
 141                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
 142                 END IF;
 143                 --
 144                 IF new_amount = old_amount THEN
 145                         same_currency := true;
 146                         currency_ratio := 1;
 147                 ELSE
 148                         --
 149                         -- We'll have to translate currency between funds.  We presume that
 150                         -- the calling code has already applied an appropriate exchange rate,
 151                         -- so we'll apply the same conversion to each sub-transfer.
 152                         --
 153                         same_currency := false;
 154                         currency_ratio := new_amount / old_amount;
 155                 END IF;
 156         END IF;
 157         --
 158         -- Identify the funding source(s) from which we want to transfer the money.
 159         -- The principle is that we want to transfer the newest money first, because
 160         -- we spend the oldest money first.  The priority for spending is defined
 161         -- by a sort of the view acq.ordered_funding_source_credit.
 162         --
 163         FOR source in
 164                 SELECT
 165                         ofsc.id,
 166                         ofsc.funding_source,
 167                         ofsc.amount,
 168                         ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
 169                                 AS converted_amt,
 170                         fs.currency_type
 171                 FROM
 172                         acq.ordered_funding_source_credit AS ofsc,
 173                         acq.funding_source fs
 174                 WHERE
 175                         ofsc.funding_source = fs.id
 176                         and ofsc.funding_source IN
 177                         (
 178                                 SELECT funding_source
 179                                 FROM acq.fund_allocation
 180                                 WHERE fund = old_fund
 181                         )
 182                         -- and
 183                         -- (
 184                         --      ofsc.funding_source = funding_source_in
 185                         --      OR funding_source_in IS NULL
 186                         -- )
 187                 ORDER BY
 188                         ofsc.sort_priority desc,
 189                         ofsc.sort_date desc,
 190                         ofsc.id desc
 191         LOOP
 192                 --
 193                 -- Determine how much money the old fund got from this funding source,
 194                 -- denominated in the currency types of the source and of the fund.
 195                 -- This result may reflect transfers from previous iterations.
 196                 --
 197                 SELECT
 198                         COALESCE( sum( amount ), 0 ),
 199                         COALESCE( sum( amount )
 200                                 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
 201                 INTO
 202                         orig_allocated_amt,     -- in currency of the source
 203                         allocated_amt           -- in currency of the old fund
 204                 FROM
 205                         acq.fund_allocation
 206                 WHERE
 207                         fund = old_fund
 208                         and funding_source = source.funding_source;
 209                 --
 210                 -- Determine how much to transfer from this credit, in the currency
 211                 -- of the fund.   Begin with the amount remaining to be attributed:
 212                 --
 213                 curr_old_amt := old_remaining;
 214                 --
 215                 -- Can't attribute more than was allocated from the fund:
 216                 --
 217                 IF curr_old_amt > allocated_amt THEN
 218                         curr_old_amt := allocated_amt;
 219                 END IF;
 220                 --
 221                 -- Can't attribute more than the amount of the current credit:
 222                 --
 223                 IF curr_old_amt > source.converted_amt THEN
 224                         curr_old_amt := source.converted_amt;
 225                 END IF;
 226                 --
 227                 curr_old_amt := trunc( curr_old_amt, 2 );
 228                 --
 229                 old_remaining := old_remaining - curr_old_amt;
 230                 --
 231                 -- Determine the amount to be deducted, if any,
 232                 -- from the old allocation.
 233                 --
 234                 IF old_remaining > 0 THEN
 235                         --
 236                         -- In this case we're using the whole allocation, so use that
 237                         -- amount directly instead of applying a currency translation
 238                         -- and thereby inviting round-off errors.
 239                         --
 240                         source_deduction := - orig_allocated_amt;
 241                 ELSE
 242                         source_deduction := trunc(
 243                                 ( - curr_old_amt ) *
 244                                         acq.exchange_ratio( old_fund_currency, source.currency_type ),
 245                                 2 );
 246                 END IF;
 247                 --
 248                 IF source_deduction <> 0 THEN
 249                         --
 250                         -- Insert negative allocation for old fund in fund_allocation,
 251                         -- converted into the currency of the funding source
 252                         --
 253                         INSERT INTO acq.fund_allocation (
 254                                 funding_source,
 255                                 fund,
 256                                 amount,
 257                                 allocator,
 258                                 note
 259                         ) VALUES (
 260                                 source.funding_source,
 261                                 old_fund,
 262                                 source_deduction,
 263                                 user_id,
 264                                 'Transfer to fund ' || new_fund
 265                         );
 266                 END IF;
 267                 --
 268                 IF new_fund IS NOT NULL THEN
 269                         --
 270                         -- Determine how much to add to the new fund, in
 271                         -- its currency, and how much remains to be added:
 272                         --
 273                         IF same_currency THEN
 274                                 curr_new_amt := curr_old_amt;
 275                         ELSE
 276                                 IF old_remaining = 0 THEN
 277                                         --
 278                                         -- This is the last iteration, so nothing should be left
 279                                         --
 280                                         curr_new_amt := new_remaining;
 281                                         new_remaining := 0;
 282                                 ELSE
 283                                         curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
 284                                         new_remaining := new_remaining - curr_new_amt;
 285                                 END IF;
 286                         END IF;
 287                         --
 288                         -- Determine how much to add, if any,
 289                         -- to the new fund's allocation.
 290                         --
 291                         IF old_remaining > 0 THEN
 292                                 --
 293                                 -- In this case we're using the whole allocation, so use that amount
 294                                 -- amount directly instead of applying a currency translation and
 295                                 -- thereby inviting round-off errors.
 296                                 --
 297                                 source_addition := orig_allocated_amt;
 298                         ELSIF source.currency_type = old_fund_currency THEN
 299                                 --
 300                                 -- In this case we don't need a round trip currency translation,
 301                                 -- thereby inviting round-off errors:
 302                                 --
 303                                 source_addition := curr_old_amt;
 304                         ELSE
 305                                 source_addition := trunc(
 306                                         curr_new_amt *
 307                                                 acq.exchange_ratio( new_fund_currency, source.currency_type ),
 308                                         2 );
 309                         END IF;
 310                         --
 311                         IF source_addition <> 0 THEN
 312                                 --
 313                                 -- Insert positive allocation for new fund in fund_allocation,
 314                                 -- converted to the currency of the founding source
 315                                 --
 316                                 INSERT INTO acq.fund_allocation (
 317                                         funding_source,
 318                                         fund,
 319                                         amount,
 320                                         allocator,
 321                                         note
 322                                 ) VALUES (
 323                                         source.funding_source,
 324                                         new_fund,
 325                                         source_addition,
 326                                         user_id,
 327                                         'Transfer from fund ' || old_fund
 328                                 );
 329                         END IF;
 330                 END IF;
 331                 --
 332                 IF trunc( curr_old_amt, 2 ) <> 0
 333                 OR trunc( curr_new_amt, 2 ) <> 0 THEN
 334                         --
 335                         -- Insert row in fund_transfer, using amounts in the currency of the funds
 336                         --
 337                         INSERT INTO acq.fund_transfer (
 338                                 src_fund,
 339                                 src_amount,
 340                                 dest_fund,
 341                                 dest_amount,
 342                                 transfer_user,
 343                                 note,
 344                                 funding_source_credit
 345                         ) VALUES (
 346                                 old_fund,
 347                                 trunc( curr_old_amt, 2 ),
 348                                 new_fund,
 349                                 trunc( curr_new_amt, 2 ),
 350                                 user_id,
 351                                 xfer_note,
 352                                 source.id
 353                         );
 354                 END IF;
 355                 --
 356                 if old_remaining <= 0 THEN
 357                         EXIT;                   -- Nothing more to be transferred
 358                 END IF;
 359         END LOOP;
 360 END;
 361 $$ LANGUAGE plpgsql;
 362
 363 COMMIT;