Open-ILS/src/sql/Pg/upgrade/0143.schema.debit_attribution.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0143'); -- Scott McKellar
   4
   5 CREATE TABLE acq.debit_attribution (
   6         id                     INT         NOT NULL PRIMARY KEY,
   7         fund_debit             INT         NOT NULL
   8                                            REFERENCES acq.fund_debit
   9                                            DEFERRABLE INITIALLY DEFERRED,
  10     debit_amount           NUMERIC     NOT NULL,
  11         funding_source_credit  INT         REFERENCES acq.funding_source_credit
  12                                            DEFERRABLE INITIALLY DEFERRED,
  13     credit_amount          NUMERIC
  14 );
  15
  16 CREATE INDEX acq_attribution_debit_idx
  17         ON acq.debit_attribution( fund_debit );
  18
  19 CREATE INDEX acq_attribution_credit_idx
  20         ON acq.debit_attribution( funding_source_credit );
  21
  22 -- The following three types are intended for internal use
  23 -- by the acq.attribute_debits() function.
  24
  25 -- For a combination of fund and funding_source: How much that source
  26 -- allocated to that fund, and how much is left.
  27 CREATE TYPE acq.fund_source_balance AS
  28 (
  29         fund       INT,        -- fund id
  30         source     INT,        -- funding source id
  31         amount     NUMERIC,    -- original total allocation
  32         balance    NUMERIC     -- what's left
  33 );
  34
  35 -- For a fund: a list of funding_source_credits to which
  36 -- the fund's debits can be attributed.
  37 CREATE TYPE acq.fund_credits AS
  38 (
  39         fund       INT,        -- fund id
  40         credit_count INT,      -- number of entries in the following array
  41         credit     INT []      -- funding source credits from which a fund may draw
  42 );
  43
  44 -- For a funding source credit: the funding source, the currency type
  45 -- of the funding source, and the current balance.
  46 CREATE TYPE acq.funding_source_credit_balance AS
  47 (
  48         credit_id       INT,        -- if for funding source credit
  49         funding_source  INT,        -- id of funding source
  50         currency_type   TEXT,       -- currency type of funding source
  51         amount          NUMERIC,    -- original amount of credit
  52         balance         NUMERIC     -- how much is left
  53 );
  54
  55 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
  56 /*
  57         Function to attribute expenditures and encumbrances to funding source credits,
  58         and thereby to funding sources.
  59
  60         Read the debits in chonological order, attributing each one to one or
  61         more funding source credits.  Constraints:
  62
  63         1. Don't attribute more to a credit than the amount of the credit.
  64
  65         2. For a given fund, don't attribute more to a funding source than the
  66         source has allocated to that fund.
  67
  68         3. Attribute debits to credits with deadlines before attributing them to
  69         credits without deadlines.  Otherwise attribute to the earliest credits
  70         first, based on the deadline date when present, or on the effective date
  71         when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
  72         This ordering is defined by an ORDER BY clause on the view
  73         acq.ordered_funding_source_credit.
  74
  75         Start by truncating the table acq.debit_attribution.  Then insert a row
  76         into that table for each attribution.  If a debit cannot be fully
  77         attributed, insert a row for the unattributable balance, with the
  78         funding_source_credit and credit_amount columns NULL.
  79 */
  80 DECLARE
  81         curr_fund_src_bal   acq.fund_source_balance;
  82         fund_source_balance acq.fund_source_balance [];
  83         curr_fund_cr_list   acq.fund_credits;
  84         fund_credit_list    acq.fund_credits [];
  85         curr_cr_bal         acq.funding_source_credit_balance;
  86         cr_bal              acq.funding_source_credit_balance[];
  87         crl_max             INT;     -- Number of entries in fund_credits[]
  88         fcr_max             INT;     -- Number of entries in a credit list
  89         fsa_max             INT;     -- Number of entries in fund_source_balance[]
  90         fscr_max            INT;     -- Number of entries in cr_bal[]
  91         fsa                 RECORD;
  92         fc                  RECORD;
  93         sc                  RECORD;
  94         cr                  RECORD;
  95         --
  96         -- Used exclusively in the main loop:
  97         --
  98         deb                 RECORD;
  99         debit_balance       NUMERIC;  -- amount left to attribute for current debit
 100         conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
 101         attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
 102         conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
 103         conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
 104         conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
 105         fund_found          BOOL;
 106         credit_found        BOOL;
 107         alloc_found         BOOL;
 108         curr_cred_x         INT;   -- index of current credit in cr_bal[]
 109         curr_fund_src_x     INT;   -- index of current credit in fund_source_balance[]
 110         attrib_count        INT;   -- populates id of acq.debit_attribution
 111 BEGIN
 112         --
 113         -- Load an array.  For each combination of fund and funding source, load an
 114         -- entry with the total amount allocated to that fund by that source.  This
 115         -- sum may reflect transfers as well as original allocations.  The balance
 116         -- is initially equal to the original amount.
 117         --
 118         fsa_max := 0;
 119         FOR fsa IN
 120                 SELECT
 121                         fund AS fund,
 122                         funding_source AS source,
 123                         sum( amount ) AS amount
 124                 FROM
 125                         acq.fund_allocation
 126                 GROUP BY
 127                         fund,
 128                         funding_source
 129                 HAVING
 130                         sum( amount ) <> 0
 131                 ORDER BY
 132                         fund,
 133                         funding_source
 134         LOOP
 135                 IF fsa.amount > 0 THEN
 136                         --
 137                         -- Add this fund/source combination to the list
 138                         --
 139                         curr_fund_src_bal.fund    := fsa.fund;
 140                         curr_fund_src_bal.source  := fsa.source;
 141                         curr_fund_src_bal.amount  := fsa.amount;
 142                         curr_fund_src_bal.balance := fsa.amount;
 143                         --
 144                         fsa_max := fsa_max + 1;
 145                         fund_source_balance[ fsa_max ] := curr_fund_src_bal;
 146                 END IF;
 147                 --
 148         END LOOP;
 149         -------------------------------------------------------------------------------
 150         --
 151         -- Load another array.  For each fund, load a list of funding
 152         -- source credits from which that fund can get money.
 153         --
 154         crl_max := 0;
 155         FOR fc IN
 156                 SELECT DISTINCT fund
 157                 FROM acq.fund_allocation
 158                 ORDER BY fund
 159         LOOP                  -- Loop over the funds
 160                 --
 161                 -- Initialize the array entry
 162                 --
 163                 curr_fund_cr_list.fund := fc.fund;
 164                 fcr_max := 0;
 165                 curr_fund_cr_list.credit := NULL;
 166                 --
 167                 -- Make a list of the funding source credits
 168                 -- applicable to this fund
 169                 --
 170                 FOR sc IN
 171                         SELECT
 172                                 ofsc.id
 173                         FROM
 174                                 acq.ordered_funding_source_credit AS ofsc
 175                         WHERE
 176                                 ofsc.funding_source IN
 177                                 (
 178                                         SELECT funding_source
 179                                         FROM acq.fund_allocation
 180                                         WHERE fund = fc.fund
 181                                 )
 182                 ORDER BY
 183                     ofsc.sort_priority,
 184                     ofsc.sort_date,
 185                     ofsc.id
 186                 LOOP                        -- Add each credit to the list
 187                         fcr_max := fcr_max + 1;
 188                         curr_fund_cr_list.credit[ fcr_max ] := sc.id;
 189                         --
 190                 END LOOP;
 191                 --
 192                 -- If there are any credits applicable to this fund,
 193                 -- add the credit list to the list of credit lists.
 194                 --
 195                 IF fcr_max > 0 THEN
 196                         curr_fund_cr_list.credit_count := fcr_max;
 197                         crl_max := crl_max + 1;
 198                         fund_credit_list[ crl_max ] := curr_fund_cr_list;
 199                 END IF;
 200                 --
 201         END LOOP;
 202         -------------------------------------------------------------------------------
 203         --
 204         -- Load yet another array.  This one is a list of funding source credits, with
 205         -- their balances.
 206         --
 207         fscr_max := 0;
 208     FOR cr in
 209         SELECT
 210             ofsc.id,
 211             ofsc.funding_source,
 212             ofsc.amount,
 213             fs.currency_type
 214         FROM
 215             acq.ordered_funding_source_credit AS ofsc,
 216             acq.funding_source fs
 217         WHERE
 218             ofsc.funding_source = fs.id
 219        ORDER BY
 220             ofsc.sort_priority,
 221             ofsc.sort_date,
 222             ofsc.id
 223         LOOP
 224                 --
 225                 curr_cr_bal.credit_id      := cr.id;
 226                 curr_cr_bal.funding_source := cr.funding_source;
 227                 curr_cr_bal.amount         := cr.amount;
 228                 curr_cr_bal.balance        := cr.amount;
 229                 curr_cr_bal.currency_type  := cr.currency_type;
 230                 --
 231                 fscr_max := fscr_max + 1;
 232                 cr_bal[ fscr_max ] := curr_cr_bal;
 233         END LOOP;
 234         --
 235         -------------------------------------------------------------------------------
 236         --
 237         -- Now that we have loaded the lookup tables: loop through the debits,
 238         -- attributing each one to one or more funding source credits.
 239         --
 240         truncate table acq.debit_attribution;
 241         --
 242         attrib_count := 0;
 243         FOR deb in
 244                 SELECT
 245                         fd.id,
 246                         fd.fund,
 247                         fd.amount,
 248                         f.currency_type,
 249                         fd.encumbrance
 250                 FROM
 251                         acq.fund_debit fd,
 252                         acq.fund f
 253                 WHERE
 254                         fd.fund = f.id
 255                 ORDER BY
 256                         id
 257         LOOP
 258                 debit_balance := deb.amount;
 259                 --
 260                 -- Find the list of credits applicable to this fund
 261                 --
 262                 fund_found := false;
 263                 FOR i in 1 .. crl_max LOOP
 264                         IF fund_credit_list[ i ].fund = deb.fund THEN
 265                                 curr_fund_cr_list := fund_credit_list[ i ];
 266                                 fund_found := true;
 267                                 exit;
 268                         END IF;
 269                 END LOOP;
 270                 --
 271                 -- If we didn't find an entry for this fund, then there are no applicable
 272                 -- funding sources for this fund, and the debit is hence unattributable.
 273                 --
 274                 -- If we did find an entry for this fund, then we have a list of funding source
 275                 -- credits that we can apply to it.  Go through that list and attribute the
 276                 -- debit accordingly.
 277                 --
 278                 IF fund_found THEN
 279                         --
 280                         -- For each applicable credit
 281                         --
 282                         FOR i in 1 .. curr_fund_cr_list.credit_count LOOP
 283                                 --
 284                                 -- Find the entry in the credit list for this credit.  If you find it but
 285                                 -- it has a zero balance, it's not useful, so treat it as if you didn't
 286                                 -- find it.
 287                                 --
 288                                 credit_found := false;
 289                                 FOR j in 1 .. fscr_max LOOP
 290                                         IF cr_bal[ j ].credit_id = curr_fund_cr_list.credit[i] THEN
 291                                                 curr_cr_bal  := cr_bal[ j ];
 292                                                 IF curr_cr_bal.balance <> 0 THEN
 293                                                         curr_cred_x  := j;
 294                                                         credit_found := true;
 295                                                 END IF;
 296                                                 EXIT;
 297                                         END IF;
 298                                 END LOOP;
 299                                 --
 300                                 IF NOT credit_found THEN
 301                                         --
 302                                         -- This credit is not usable; try the next one.
 303                                         --
 304                                         CONTINUE;
 305                                 END IF;
 306                                 --
 307                                 -- At this point we have an applicable credit with some money left.
 308                                 -- Now see if the relevant funding_source has any money left.
 309                                 --
 310                                 -- Search the fund/source list for an entry with this combination
 311                                 -- of fund and source.  If you find such an entry, but it has a zero
 312                                 -- balance, then it's not useful, so treat it as unfound.
 313                                 --
 314                                 alloc_found := false;
 315                                 FOR j in 1 .. fsa_max LOOP
 316                                         IF fund_source_balance[ j ].fund = deb.fund
 317                                         AND fund_source_balance[ j ].source = curr_cr_bal.funding_source THEN
 318                                                 curr_fund_src_bal := fund_source_balance[ j ];
 319                                                 IF curr_fund_src_bal.balance <> 0 THEN
 320                                                         curr_fund_src_x := j;
 321                                                         alloc_found := true;
 322                                                 END IF;
 323                                                 EXIT;
 324                                         END IF;
 325                                 END LOOP;
 326                                 --
 327                                 IF NOT alloc_found THEN
 328                                         --
 329                                         -- This fund/source doesn't exist is already exhausted,
 330                                         -- so we can't use this credit.  Go on to the next on.
 331                                         --
 332                                         CONTINUE;
 333                                 END IF;
 334                                 --
 335                                 -- Convert the available balances to the currency of the fund
 336                                 --
 337                                 conv_alloc_balance := curr_fund_src_bal.balance * acq.exchange_ratio(
 338                                         curr_cr_bal.currency_type, deb.currency_type );
 339                                 conv_cred_balance := curr_cr_bal.balance * acq.exchange_ratio(
 340                                         curr_cr_bal.currency_type, deb.currency_type );
 341                                 --
 342                                 -- Determine how much we can attribute to this credit: the minimum
 343                                 -- of the debit amount, the fund/source balance, and the
 344                                 -- credit balance
 345                                 --
 346                                 attr_amount := debit_balance;
 347                                 IF attr_amount > conv_alloc_balance THEN
 348                                         attr_amount := conv_alloc_balance;
 349                                 END IF;
 350                                 IF attr_amount > conv_cred_balance THEN
 351                                         attr_amount := conv_cred_balance;
 352                                 END IF;
 353                                 --
 354                                 -- Convert the amount of the attribution to the
 355                                 -- currency of the funding source.
 356                                 --
 357                                 conv_attr_amount := attr_amount * acq.exchange_ratio(
 358                                         deb.currency_type, curr_cr_bal.currency_type );
 359                                 --
 360                                 -- Insert a row to record the attribution
 361                                 --
 362                                 attrib_count := attrib_count + 1;
 363                                 INSERT INTO acq.debit_attribution (
 364                                         id,
 365                                         fund_debit,
 366                                         debit_amount,
 367                                         funding_source_credit,
 368                                         credit_amount
 369                                 ) VALUES (
 370                                         attrib_count,
 371                                         deb.id,
 372                                         attr_amount,
 373                                         curr_cr_bal.credit_id,
 374                                         conv_attr_amount
 375                                 );
 376                                 --
 377                                 -- Subtract the attributed amount from the various balances
 378                                 --
 379                                 debit_balance := debit_balance - attr_amount;
 380                                 --
 381                                 curr_fund_src_bal.balance := curr_fund_src_bal.balance - conv_attr_amount;
 382                                 fund_source_balance[ curr_fund_src_x ] := curr_fund_src_bal;
 383                                 IF curr_fund_src_bal.balance <= 0 THEN
 384                                         --
 385                                         -- This allocation is exhausted.  Take it out of the list
 386                                         -- so that we don't waste time looking at it again.
 387                                         --
 388                                         FOR i IN curr_fund_src_x .. fsa_max - 1 LOOP
 389                                                 fund_source_balance[ i ] := fund_source_balance[ i + 1 ];
 390                                         END LOOP;
 391                                         fund_source_balance[ fsa_max ] := NULL;
 392                                         fsa_max := fsa_max - 1;
 393                                 END IF;
 394                                 --
 395                                 curr_cr_bal.balance   := curr_cr_bal.balance - conv_attr_amount;
 396                                 cr_bal[ curr_cred_x ] := curr_cr_bal;
 397                                 IF curr_cr_bal.balance <= 0 THEN
 398                                         --
 399                                         -- This funding source credit is exhausted.  Take it out of
 400                                         -- the list so that we don't waste time looking at it again.
 401                                         --
 402                                         FOR i IN curr_cred_x .. fscr_max - 1 LOOP
 403                                                 cr_bal[ i ] := cr_bal[ i + 1 ];
 404                                         END LOOP;
 405                                         cr_bal[ fscr_max ] := NULL;
 406                                         fscr_max := fscr_max - 1;
 407                                 END IF;
 408                                 --
 409                                 -- Are we done with this debit yet?
 410                                 --
 411                                 IF debit_balance <= 0 THEN
 412                                         EXIT;       -- We've fully attributed this debit; stop looking at credits.
 413                                 END IF;
 414                         END LOOP;           -- End of loop over applicable credits
 415                 END IF;
 416                 --
 417                 IF debit_balance <> 0 THEN
 418                         --
 419                         -- We weren't able to attribute this debit, or at least not
 420                         -- all of it.  Insert a row for the unattributed balance.
 421                         --
 422                         attrib_count := attrib_count + 1;
 423                         INSERT INTO acq.debit_attribution (
 424                                 id,
 425                                 fund_debit,
 426                                 debit_amount,
 427                                 funding_source_credit,
 428                                 credit_amount
 429                         ) VALUES (
 430                                 attrib_count,
 431                                 deb.id,
 432                                 debit_balance,
 433                                 NULL,
 434                                 NULL
 435                         );
 436                 END IF;
 437         END LOOP;   -- End of loop over debits
 438 END;
 439 $$ LANGUAGE 'plpgsql';
 440
 441 COMMIT;