Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_fund_transfer.sql

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