Open-ILS/src/sql/Pg/upgrade/0219.schema.acq.propagate-fund-bal-pct.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0219'); -- Scott McKellar
   4
   5 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit(
   6         old_year INTEGER,
   7         user_id INTEGER,
   8         org_unit_id INTEGER
   9 ) RETURNS VOID AS $$
  10 DECLARE
  11 --
  12 new_id      INT;
  13 old_fund    RECORD;
  14 org_found   BOOLEAN;
  15 --
  16 BEGIN
  17         --
  18         -- Sanity checks
  19         --
  20         IF old_year IS NULL THEN
  21                 RAISE EXCEPTION 'Input year argument is NULL';
  22         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
  23                 RAISE EXCEPTION 'Input year is out of range';
  24         END IF;
  25         --
  26         IF user_id IS NULL THEN
  27                 RAISE EXCEPTION 'Input user id argument is NULL';
  28         END IF;
  29         --
  30         IF org_unit_id IS NULL THEN
  31                 RAISE EXCEPTION 'Org unit id argument is NULL';
  32         ELSE
  33                 SELECT TRUE INTO org_found
  34                 FROM actor.org_unit
  35                 WHERE id = org_unit_id;
  36                 --
  37                 IF org_found IS NULL THEN
  38                         RAISE EXCEPTION 'Org unit id is invalid';
  39                 END IF;
  40         END IF;
  41         --
  42         -- Loop over the applicable funds
  43         --
  44         FOR old_fund in SELECT * FROM acq.fund
  45         WHERE
  46                 year = old_year
  47                 AND propagate
  48                 AND org = org_unit_id
  49         LOOP
  50                 BEGIN
  51                         INSERT INTO acq.fund (
  52                                 org,
  53                                 name,
  54                                 year,
  55                                 currency_type,
  56                                 code,
  57                                 rollover,
  58                                 propagate,
  59                                 balance_warning_percent,
  60                                 balance_stop_percent
  61                         ) VALUES (
  62                                 old_fund.org,
  63                                 old_fund.name,
  64                                 old_year + 1,
  65                                 old_fund.currency_type,
  66                                 old_fund.code,
  67                                 old_fund.rollover,
  68                                 true,
  69                                 old_fund.balance_warning_percent,
  70                                 old_fund.balance_stop_percent
  71                         )
  72                         RETURNING id INTO new_id;
  73                 EXCEPTION
  74                         WHEN unique_violation THEN
  75                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
  76                                 CONTINUE;
  77                 END;
  78                 --RAISE NOTICE 'Propagating fund % to fund %',
  79                 --      old_fund.code, new_id;
  80         END LOOP;
  81 END;
  82 $$ LANGUAGE plpgsql;
  83
  84 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
  85         old_year INTEGER,
  86         user_id INTEGER,
  87         org_unit_id INTEGER
  88 ) RETURNS VOID AS $$
  89 DECLARE
  90 --
  91 new_id      INT;
  92 old_fund    RECORD;
  93 org_found   BOOLEAN;
  94 --
  95 BEGIN
  96         --
  97         -- Sanity checks
  98         --
  99         IF old_year IS NULL THEN
 100                 RAISE EXCEPTION 'Input year argument is NULL';
 101         ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
 102                 RAISE EXCEPTION 'Input year is out of range';
 103         END IF;
 104         --
 105         IF user_id IS NULL THEN
 106                 RAISE EXCEPTION 'Input user id argument is NULL';
 107         END IF;
 108         --
 109         IF org_unit_id IS NULL THEN
 110                 RAISE EXCEPTION 'Org unit id argument is NULL';
 111         ELSE
 112                 SELECT TRUE INTO org_found
 113                 FROM actor.org_unit
 114                 WHERE id = org_unit_id;
 115                 --
 116                 IF org_found IS NULL THEN
 117                         RAISE EXCEPTION 'Org unit id is invalid';
 118                 END IF;
 119         END IF;
 120         --
 121         -- Loop over the applicable funds
 122         --
 123         FOR old_fund in SELECT * FROM acq.fund
 124         WHERE
 125                 year = old_year
 126                 AND propagate
 127                 AND org in (
 128                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
 129                 )
 130         LOOP
 131                 BEGIN
 132                         INSERT INTO acq.fund (
 133                                 org,
 134                                 name,
 135                                 year,
 136                                 currency_type,
 137                                 code,
 138                                 rollover,
 139                                 propagate,
 140                                 balance_warning_percent,
 141                                 balance_stop_percent
 142                         ) VALUES (
 143                                 old_fund.org,
 144                                 old_fund.name,
 145                                 old_year + 1,
 146                                 old_fund.currency_type,
 147                                 old_fund.code,
 148                                 old_fund.rollover,
 149                                 true,
 150                                 old_fund.balance_warning_percent,
 151                                 old_fund.balance_stop_percent
 152                         )
 153                         RETURNING id INTO new_id;
 154                 EXCEPTION
 155                         WHEN unique_violation THEN
 156                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
 157                                 CONTINUE;
 158                 END;
 159                 --RAISE NOTICE 'Propagating fund % to fund %',
 160                 --      old_fund.code, new_id;
 161         END LOOP;
 162 END;
 163 $$ LANGUAGE plpgsql;
 164
 165 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
 166         old_year INTEGER,
 167         user_id INTEGER,
 168         org_unit_id INTEGER
 169 ) RETURNS VOID AS $$
 170 DECLARE
 171 --
 172 new_fund    INT;
 173 new_year    INT := old_year + 1;
 174 org_found   BOOL;
 175 xfer_amount NUMERIC;
 176 roll_fund   RECORD;
 177 deb         RECORD;
 178 detail      RECORD;
 179 --
 180 BEGIN
 181         --
 182         -- Sanity checks
 183         --
 184         IF old_year IS NULL THEN
 185                 RAISE EXCEPTION 'Input year argument is NULL';
 186     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
 187         RAISE EXCEPTION 'Input year is out of range';
 188         END IF;
 189         --
 190         IF user_id IS NULL THEN
 191                 RAISE EXCEPTION 'Input user id argument is NULL';
 192         END IF;
 193         --
 194         IF org_unit_id IS NULL THEN
 195                 RAISE EXCEPTION 'Org unit id argument is NULL';
 196         ELSE
 197                 --
 198                 -- Validate the org unit
 199                 --
 200                 SELECT TRUE
 201                 INTO org_found
 202                 FROM actor.org_unit
 203                 WHERE id = org_unit_id;
 204                 --
 205                 IF org_found IS NULL THEN
 206                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
 207                 END IF;
 208         END IF;
 209         --
 210         -- Loop over the propagable funds to identify the details
 211         -- from the old fund plus the id of the new one, if it exists.
 212         --
 213         FOR roll_fund in
 214         SELECT
 215             oldf.id AS old_fund,
 216             oldf.org,
 217             oldf.name,
 218             oldf.currency_type,
 219             oldf.code,
 220                 oldf.rollover,
 221             newf.id AS new_fund_id
 222         FROM
 223         acq.fund AS oldf
 224         LEFT JOIN acq.fund AS newf
 225                 ON ( oldf.code = newf.code )
 226         WHERE
 227                     oldf.org = org_unit_id
 228                 and oldf.year = old_year
 229                 and oldf.propagate
 230         and newf.year = new_year
 231         LOOP
 232                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
 233                 --
 234                 IF roll_fund.new_fund_id IS NULL THEN
 235                         --
 236                         -- The old fund hasn't been propagated yet.  Propagate it now.
 237                         --
 238                         INSERT INTO acq.fund (
 239                                 org,
 240                                 name,
 241                                 year,
 242                                 currency_type,
 243                                 code,
 244                                 rollover,
 245                                 propagate,
 246                                 balance_warning_percent,
 247                                 balance_stop_percent
 248                         ) VALUES (
 249                                 roll_fund.org,
 250                                 roll_fund.name,
 251                                 new_year,
 252                                 roll_fund.currency_type,
 253                                 roll_fund.code,
 254                                 true,
 255                                 true,
 256                                 roll_fund.balance_warning_percent,
 257                                 roll_fund.balance_stop_percent
 258                         )
 259                         RETURNING id INTO new_fund;
 260                 ELSE
 261                         new_fund = roll_fund.new_fund_id;
 262                 END IF;
 263                 --
 264                 -- Determine the amount to transfer
 265                 --
 266                 SELECT amount
 267                 INTO xfer_amount
 268                 FROM acq.fund_spent_balance
 269                 WHERE fund = roll_fund.old_fund;
 270                 --
 271                 IF xfer_amount <> 0 THEN
 272                         IF roll_fund.rollover THEN
 273                                 --
 274                                 -- Transfer balance from old fund to new
 275                                 --
 276                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
 277                                 --
 278                                 PERFORM acq.transfer_fund(
 279                                         roll_fund.old_fund,
 280                                         xfer_amount,
 281                                         new_fund,
 282                                         xfer_amount,
 283                                         user_id,
 284                                         'Rollover'
 285                                 );
 286                         ELSE
 287                                 --
 288                                 -- Transfer balance from old fund to the void
 289                                 --
 290                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
 291                                 --
 292                                 PERFORM acq.transfer_fund(
 293                                         roll_fund.old_fund,
 294                                         xfer_amount,
 295                                         NULL,
 296                                         NULL,
 297                                         user_id,
 298                                         'Rollover'
 299                                 );
 300                         END IF;
 301                 END IF;
 302                 --
 303                 IF roll_fund.rollover THEN
 304                         --
 305                         -- Move any lineitems from the old fund to the new one
 306                         -- where the associated debit is an encumbrance.
 307                         --
 308                         -- Any other tables tying expenditure details to funds should
 309                         -- receive similar treatment.  At this writing there are none.
 310                         --
 311                         UPDATE acq.lineitem_detail
 312                         SET fund = new_fund
 313                         WHERE
 314                         fund = roll_fund.old_fund -- this condition may be redundant
 315                         AND fund_debit in
 316                         (
 317                                 SELECT id
 318                                 FROM acq.fund_debit
 319                                 WHERE
 320                                 fund = roll_fund.old_fund
 321                                 AND encumbrance
 322                         );
 323                         --
 324                         -- Move encumbrance debits from the old fund to the new fund
 325                         --
 326                         UPDATE acq.fund_debit
 327                         SET fund = new_fund
 328                         wHERE
 329                                 fund = roll_fund.old_fund
 330                                 AND encumbrance;
 331                 END IF;
 332                 --
 333                 -- Mark old fund as inactive, now that we've closed it
 334                 --
 335                 UPDATE acq.fund
 336                 SET active = FALSE
 337                 WHERE id = roll_fund.old_fund;
 338         END LOOP;
 339 END;
 340 $$ LANGUAGE plpgsql;
 341
 342 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
 343         old_year INTEGER,
 344         user_id INTEGER,
 345         org_unit_id INTEGER
 346 ) RETURNS VOID AS $$
 347 DECLARE
 348 --
 349 new_fund    INT;
 350 new_year    INT := old_year + 1;
 351 org_found   BOOL;
 352 xfer_amount NUMERIC;
 353 roll_fund   RECORD;
 354 deb         RECORD;
 355 detail      RECORD;
 356 --
 357 BEGIN
 358         --
 359         -- Sanity checks
 360         --
 361         IF old_year IS NULL THEN
 362                 RAISE EXCEPTION 'Input year argument is NULL';
 363     ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
 364         RAISE EXCEPTION 'Input year is out of range';
 365         END IF;
 366         --
 367         IF user_id IS NULL THEN
 368                 RAISE EXCEPTION 'Input user id argument is NULL';
 369         END IF;
 370         --
 371         IF org_unit_id IS NULL THEN
 372                 RAISE EXCEPTION 'Org unit id argument is NULL';
 373         ELSE
 374                 --
 375                 -- Validate the org unit
 376                 --
 377                 SELECT TRUE
 378                 INTO org_found
 379                 FROM actor.org_unit
 380                 WHERE id = org_unit_id;
 381                 --
 382                 IF org_found IS NULL THEN
 383                         RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
 384                 END IF;
 385         END IF;
 386         --
 387         -- Loop over the propagable funds to identify the details
 388         -- from the old fund plus the id of the new one, if it exists.
 389         --
 390         FOR roll_fund in
 391         SELECT
 392             oldf.id AS old_fund,
 393             oldf.org,
 394             oldf.name,
 395             oldf.currency_type,
 396             oldf.code,
 397                 oldf.rollover,
 398             newf.id AS new_fund_id
 399         FROM
 400         acq.fund AS oldf
 401         LEFT JOIN acq.fund AS newf
 402                 ON ( oldf.code = newf.code )
 403         WHERE
 404                     oldf.year = old_year
 405                 AND oldf.propagate
 406         AND newf.year = new_year
 407                 AND oldf.org in (
 408                         SELECT id FROM actor.org_unit_descendants( org_unit_id )
 409                 )
 410         LOOP
 411                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
 412                 --
 413                 IF roll_fund.new_fund_id IS NULL THEN
 414                         --
 415                         -- The old fund hasn't been propagated yet.  Propagate it now.
 416                         --
 417                         INSERT INTO acq.fund (
 418                                 org,
 419                                 name,
 420                                 year,
 421                                 currency_type,
 422                                 code,
 423                                 rollover,
 424                                 propagate,
 425                                 balance_warning_percent,
 426                                 balance_stop_percent
 427                         ) VALUES (
 428                                 roll_fund.org,
 429                                 roll_fund.name,
 430                                 new_year,
 431                                 roll_fund.currency_type,
 432                                 roll_fund.code,
 433                                 true,
 434                                 true,
 435                                 roll_fund.balance_warning_percent,
 436                                 roll_fund.balance_stop_percent
 437                         )
 438                         RETURNING id INTO new_fund;
 439                 ELSE
 440                         new_fund = roll_fund.new_fund_id;
 441                 END IF;
 442                 --
 443                 -- Determine the amount to transfer
 444                 --
 445                 SELECT amount
 446                 INTO xfer_amount
 447                 FROM acq.fund_spent_balance
 448                 WHERE fund = roll_fund.old_fund;
 449                 --
 450                 IF xfer_amount <> 0 THEN
 451                         IF roll_fund.rollover THEN
 452                                 --
 453                                 -- Transfer balance from old fund to new
 454                                 --
 455                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
 456                                 --
 457                                 PERFORM acq.transfer_fund(
 458                                         roll_fund.old_fund,
 459                                         xfer_amount,
 460                                         new_fund,
 461                                         xfer_amount,
 462                                         user_id,
 463                                         'Rollover'
 464                                 );
 465                         ELSE
 466                                 --
 467                                 -- Transfer balance from old fund to the void
 468                                 --
 469                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
 470                                 --
 471                                 PERFORM acq.transfer_fund(
 472                                         roll_fund.old_fund,
 473                                         xfer_amount,
 474                                         NULL,
 475                                         NULL,
 476                                         user_id,
 477                                         'Rollover'
 478                                 );
 479                         END IF;
 480                 END IF;
 481                 --
 482                 IF roll_fund.rollover THEN
 483                         --
 484                         -- Move any lineitems from the old fund to the new one
 485                         -- where the associated debit is an encumbrance.
 486                         --
 487                         -- Any other tables tying expenditure details to funds should
 488                         -- receive similar treatment.  At this writing there are none.
 489                         --
 490                         UPDATE acq.lineitem_detail
 491                         SET fund = new_fund
 492                         WHERE
 493                         fund = roll_fund.old_fund -- this condition may be redundant
 494                         AND fund_debit in
 495                         (
 496                                 SELECT id
 497                                 FROM acq.fund_debit
 498                                 WHERE
 499                                 fund = roll_fund.old_fund
 500                                 AND encumbrance
 501                         );
 502                         --
 503                         -- Move encumbrance debits from the old fund to the new fund
 504                         --
 505                         UPDATE acq.fund_debit
 506                         SET fund = new_fund
 507                         wHERE
 508                                 fund = roll_fund.old_fund
 509                                 AND encumbrance;
 510                 END IF;
 511                 --
 512                 -- Mark old fund as inactive, now that we've closed it
 513                 --
 514                 UPDATE acq.fund
 515                 SET active = FALSE
 516                 WHERE id = roll_fund.old_fund;
 517         END LOOP;
 518 END;
 519 $$ LANGUAGE plpgsql;
 520
 521 COMMIT;