Open-ILS/src/sql/Pg/upgrade/0148.schema.acq.rollover-fund.sql

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