Open-ILS/src/sql/Pg/upgrade/0149.schema.acq.attribute-debits.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0149'); -- Scott McKellar
   4
   5 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
   6 /*
   7 Function to attribute expenditures and encumbrances to funding source credits,
   8 and thereby to funding sources.
   9
  10 Read the debits in chonological order, attributing each one to one or
  11 more funding source credits.  Constraints:
  12
  13 1. Don't attribute more to a credit than the amount of the credit.
  14
  15 2. For a given fund, don't attribute more to a funding source than the
  16 source has allocated to that fund.
  17
  18 3. Attribute debits to credits with deadlines before attributing them to
  19 credits without deadlines.  Otherwise attribute to the earliest credits
  20 first, based on the deadline date when present, or on the effective date
  21 when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
  22 This ordering is defined by an ORDER BY clause on the view
  23 acq.ordered_funding_source_credit.
  24
  25 Start by truncating the table acq.debit_attribution.  Then insert a row
  26 into that table for each attribution.  If a debit cannot be fully
  27 attributed, insert a row for the unattributable balance, with the
  28 funding_source_credit and credit_amount columns NULL.
  29 */
  30 DECLARE
  31         curr_fund_source_bal RECORD;
  32         seqno                INT;     -- sequence num for credits applicable to a fund
  33         fund_credit          RECORD;  -- current row in temp t_fund_credit table
  34         fc                   RECORD;  -- used for loading t_fund_credit table
  35         sc                   RECORD;  -- used for loading t_fund_credit table
  36         --
  37         -- Used exclusively in the main loop:
  38         --
  39         deb                 RECORD;   -- current row from acq.fund_debit table
  40         curr_credit_bal     RECORD;   -- current row from temp t_credit table
  41         debit_balance       NUMERIC;  -- amount left to attribute for current debit
  42         conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
  43         attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
  44         conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
  45         conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
  46         conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
  47         attrib_count        INT;      -- populates id of acq.debit_attribution
  48 BEGIN
  49         --
  50         -- Load a temporary table.  For each combination of fund and funding source,
  51         -- load an entry with the total amount allocated to that fund by that source.
  52         -- This sum may reflect transfers as well as original allocations.  We will
  53         -- reduce this balance whenever we attribute debits to it.
  54         --
  55         CREATE TEMP TABLE t_fund_source_bal
  56         ON COMMIT DROP AS
  57                 SELECT
  58                         fund AS fund,
  59                         funding_source AS source,
  60                         sum( amount ) AS balance
  61                 FROM
  62                         acq.fund_allocation
  63                 GROUP BY
  64                         fund,
  65                         funding_source
  66                 HAVING
  67                         sum( amount ) > 0;
  68         --
  69         CREATE INDEX t_fund_source_bal_idx
  70                 ON t_fund_source_bal( fund, source );
  71         -------------------------------------------------------------------------------
  72         --
  73         -- Load another temporary table.  For each fund, load zero or more
  74         -- funding source credits from which that fund can get money.
  75         --
  76         CREATE TEMP TABLE t_fund_credit (
  77                 fund        INT,
  78                 seq         INT,
  79                 credit      INT
  80         ) ON COMMIT DROP;
  81         --
  82         FOR fc IN
  83                 SELECT DISTINCT fund
  84                 FROM acq.fund_allocation
  85                 ORDER BY fund
  86         LOOP                  -- Loop over the funds
  87                 seqno := 1;
  88                 FOR sc IN
  89                         SELECT
  90                                 ofsc.id
  91                         FROM
  92                                 acq.ordered_funding_source_credit AS ofsc
  93                         WHERE
  94                                 ofsc.funding_source IN
  95                                 (
  96                                         SELECT funding_source
  97                                         FROM acq.fund_allocation
  98                                         WHERE fund = fc.fund
  99                                 )
 100                 ORDER BY
 101                     ofsc.sort_priority,
 102                     ofsc.sort_date,
 103                     ofsc.id
 104                 LOOP                        -- Add each credit to the list
 105                         INSERT INTO t_fund_credit (
 106                                 fund,
 107                                 seq,
 108                                 credit
 109                         ) VALUES (
 110                                 fc.fund,
 111                                 seqno,
 112                                 sc.id
 113                         );
 114                         --RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
 115                         seqno := seqno + 1;
 116                 END LOOP;     -- Loop over credits for a given fund
 117         END LOOP;         -- Loop over funds
 118         --
 119         CREATE INDEX t_fund_credit_idx
 120                 ON t_fund_credit( fund, seq );
 121         -------------------------------------------------------------------------------
 122         --
 123         -- Load yet another temporary table.  This one is a list of funding source
 124         -- credits, with their balances.  We shall reduce those balances as we
 125         -- attribute debits to them.
 126         --
 127         CREATE TEMP TABLE t_credit
 128         ON COMMIT DROP AS
 129         SELECT
 130             fsc.id AS credit,
 131             fsc.funding_source AS source,
 132             fsc.amount AS balance,
 133             fs.currency_type AS currency_type
 134         FROM
 135             acq.funding_source_credit AS fsc,
 136             acq.funding_source fs
 137         WHERE
 138             fsc.funding_source = fs.id
 139                         AND fsc.amount > 0;
 140         --
 141         CREATE INDEX t_credit_idx
 142                 ON t_credit( credit );
 143         --
 144         -------------------------------------------------------------------------------
 145         --
 146         -- Now that we have loaded the lookup tables: loop through the debits,
 147         -- attributing each one to one or more funding source credits.
 148         --
 149         truncate table acq.debit_attribution;
 150         --
 151         attrib_count := 0;
 152         FOR deb in
 153                 SELECT
 154                         fd.id,
 155                         fd.fund,
 156                         fd.amount,
 157                         f.currency_type,
 158                         fd.encumbrance
 159                 FROM
 160                         acq.fund_debit fd,
 161                         acq.fund f
 162                 WHERE
 163                         fd.fund = f.id
 164                 ORDER BY
 165                         fd.id
 166         LOOP
 167                 --RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
 168                 --
 169                 debit_balance := deb.amount;
 170                 --
 171                 -- Loop over the funding source credits that are eligible
 172                 -- to pay for this debit
 173                 --
 174                 FOR fund_credit IN
 175                         SELECT
 176                                 credit
 177                         FROM
 178                                 t_fund_credit
 179                         WHERE
 180                                 fund = deb.fund
 181                         ORDER BY
 182                                 seq
 183                 LOOP
 184                         --RAISE NOTICE '   Examining credit %', fund_credit.credit;
 185                         --
 186                         -- Look up the balance for this credit.  If it's zero, then
 187                         -- it's not useful, so treat it as if you didn't find it.
 188                         -- (Actually there shouldn't be any zero balances in the table,
 189                         -- but we check just to make sure.)
 190                         --
 191                         SELECT *
 192                         INTO curr_credit_bal
 193                         FROM t_credit
 194                         WHERE
 195                                 credit = fund_credit.credit
 196                                 AND balance > 0;
 197                         --
 198                         IF curr_credit_bal IS NULL THEN
 199                                 --
 200                                 -- This credit is exhausted; try the next one.
 201                                 --
 202                                 CONTINUE;
 203                         END IF;
 204                         --
 205                         --
 206                         -- At this point we have an applicable credit with some money left.
 207                         -- Now see if the relevant funding_source has any money left.
 208                         --
 209                         -- Look up the balance of the allocation for this combination of
 210                         -- fund and source.  If you find such an entry, but it has a zero
 211                         -- balance, then it's not useful, so treat it as unfound.
 212                         -- (Actually there shouldn't be any zero balances in the table,
 213                         -- but we check just to make sure.)
 214                         --
 215                         SELECT *
 216                         INTO curr_fund_source_bal
 217                         FROM t_fund_source_bal
 218                         WHERE
 219                                 fund = deb.fund
 220                                 AND source = curr_credit_bal.source
 221                                 AND balance > 0;
 222                         --
 223                         IF curr_fund_source_bal IS NULL THEN
 224                                 --
 225                                 -- This fund/source doesn't exist or is already exhausted,
 226                                 -- so we can't use this credit.  Go on to the next one.
 227                                 --
 228                                 CONTINUE;
 229                         END IF;
 230                         --
 231                         -- Convert the available balances to the currency of the fund
 232                         --
 233                         conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
 234                                 curr_credit_bal.currency_type, deb.currency_type );
 235                         conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
 236                                 curr_credit_bal.currency_type, deb.currency_type );
 237                         --
 238                         -- Determine how much we can attribute to this credit: the minimum
 239                         -- of the debit amount, the fund/source balance, and the
 240                         -- credit balance
 241                         --
 242                         --RAISE NOTICE '   deb bal %', debit_balance;
 243                         --RAISE NOTICE '      source % balance %', curr_credit_bal.source, conv_alloc_balance;
 244                         --RAISE NOTICE '      credit % balance %', curr_credit_bal.credit, conv_cred_balance;
 245                         --
 246                         conv_attr_amount := NULL;
 247                         attr_amount := debit_balance;
 248                         --
 249                         IF attr_amount > conv_alloc_balance THEN
 250                                 attr_amount := conv_alloc_balance;
 251                                 conv_attr_amount := curr_fund_source_bal.balance;
 252                         END IF;
 253                         IF attr_amount > conv_cred_balance THEN
 254                                 attr_amount := conv_cred_balance;
 255                                 conv_attr_amount := curr_credit_bal.balance;
 256                         END IF;
 257                         --
 258                         -- If we're attributing all of one of the balances, then that's how
 259                         -- much we will deduct from the balances, and we already captured
 260                         -- that amount above.  Otherwise we must convert the amount of the
 261                         -- attribution from the currency of the fund back to the currency of
 262                         -- the funding source.
 263                         --
 264                         IF conv_attr_amount IS NULL THEN
 265                                 conv_attr_amount := attr_amount * acq.exchange_ratio(
 266                                         deb.currency_type, curr_credit_bal.currency_type );
 267                         END IF;
 268                         --
 269                         -- Insert a row to record the attribution
 270                         --
 271                         attrib_count := attrib_count + 1;
 272                         INSERT INTO acq.debit_attribution (
 273                                 id,
 274                                 fund_debit,
 275                                 debit_amount,
 276                                 funding_source_credit,
 277                                 credit_amount
 278                         ) VALUES (
 279                                 attrib_count,
 280                                 deb.id,
 281                                 attr_amount,
 282                                 curr_credit_bal.credit,
 283                                 conv_attr_amount
 284                         );
 285                         --
 286                         -- Subtract the attributed amount from the various balances
 287                         --
 288                         debit_balance := debit_balance - attr_amount;
 289                         curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
 290                         --
 291                         IF curr_fund_source_bal.balance <= 0 THEN
 292                                 --
 293                                 -- This allocation is exhausted.  Delete it so
 294                                 -- that we don't waste time looking at it again.
 295                                 --
 296                                 DELETE FROM t_fund_source_bal
 297                                 WHERE
 298                                         fund = curr_fund_source_bal.fund
 299                                         AND source = curr_fund_source_bal.source;
 300                         ELSE
 301                                 UPDATE t_fund_source_bal
 302                                 SET balance = balance - conv_attr_amount
 303                                 WHERE
 304                                         fund = curr_fund_source_bal.fund
 305                                         AND source = curr_fund_source_bal.source;
 306                         END IF;
 307                         --
 308                         IF curr_credit_bal.balance <= 0 THEN
 309                                 --
 310                                 -- This funding source credit is exhausted.  Delete it
 311                                 -- so that we don't waste time looking at it again.
 312                                 --
 313                                 --DELETE FROM t_credit
 314                                 --WHERE
 315                                 --      credit = curr_credit_bal.credit;
 316                                 --
 317                                 DELETE FROM t_fund_credit
 318                                 WHERE
 319                                         credit = curr_credit_bal.credit;
 320                         ELSE
 321                                 UPDATE t_credit
 322                                 SET balance = curr_credit_bal.balance
 323                                 WHERE
 324                                         credit = curr_credit_bal.credit;
 325                         END IF;
 326                         --
 327                         -- Are we done with this debit yet?
 328                         --
 329                         IF debit_balance <= 0 THEN
 330                                 EXIT;       -- We've fully attributed this debit; stop looking at credits.
 331                         END IF;
 332                 END LOOP;       -- End loop over credits
 333                 --
 334                 IF debit_balance <> 0 THEN
 335                         --
 336                         -- We weren't able to attribute this debit, or at least not
 337                         -- all of it.  Insert a row for the unattributed balance.
 338                         --
 339                         attrib_count := attrib_count + 1;
 340                         INSERT INTO acq.debit_attribution (
 341                                 id,
 342                                 fund_debit,
 343                                 debit_amount,
 344                                 funding_source_credit,
 345                                 credit_amount
 346                         ) VALUES (
 347                                 attrib_count,
 348                                 deb.id,
 349                                 debit_balance,
 350                                 NULL,
 351                                 NULL
 352                         );
 353                 END IF;
 354         END LOOP;   -- End of loop over debits
 355 END;
 356 $$ LANGUAGE 'plpgsql';
 357
 358 COMMIT;