Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   4
   5
   6 CREATE TABLE money.materialized_payment_by_billing_type (
   7     id              BIGSERIAL       PRIMARY KEY,
   8     xact            BIGINT          NOT NULL,
   9     payment         BIGINT          NOT NULL,
  10     billing         BIGINT          NOT NULL,
  11     payment_ts      TIMESTAMPTZ     NOT NULL,
  12     billing_ts      TIMESTAMPTZ     NOT NULL,
  13     amount          NUMERIC(8,2)    NOT NULL,
  14     payment_type    TEXT,
  15     billing_type    TEXT,
  16     payment_ou      INT,
  17     billing_ou      INT,
  18     CONSTRAINT x_p_b_once UNIQUE (xact,payment,billing)
  19 );
  20
  21 CREATE INDEX p_by_b_payment_ts_idx
  22     ON money.materialized_payment_by_billing_type (payment_ts);
  23
  24 CREATE OR REPLACE FUNCTION money.payment_by_billing_type (
  25     p_xact BIGINT
  26 ) RETURNS SETOF money.materialized_payment_by_billing_type AS $$
  27 DECLARE
  28     current_result      money.materialized_payment_by_billing_type%ROWTYPE;
  29     current_payment     money.payment_view%ROWTYPE;
  30     current_billing     money.billing%ROWTYPE;
  31     payment_remainder   NUMERIC(8,2) := 0.0;
  32     billing_remainder   NUMERIC(8,2) := 0.0;
  33     payment_offset      INT := 0;
  34     billing_offset      INT := 0;
  35     billing_ou          INT := 0;
  36     payment_ou          INT := 0;
  37     fast_forward        BOOLEAN := FALSE;
  38     maintain_billing_remainder    BOOLEAN := FALSE;
  39     billing_loop        INT := -1;
  40     billing_row_count    INT := 0;
  41     current_billing_id    BIGINT := 0;
  42     billing_id_used     BIGINT ARRAY;
  43     billing_l        INT := 0;
  44     continuing_payment    BOOLEAN := FALSE;
  45     continuing_payment_last_row    BOOLEAN := FALSE;
  46 BEGIN
  47
  48     /*  We take a transaction id and fetch its payments in chronological order.
  49      *  We apply the payment amount, or a portion thereof, to each billing on
  50      *  the transaction, also in chronological order, until we run out of money
  51      *  from that payment.  For each billing we encounter while we have money
  52      *  left from a payment we emmit a row of output containing the information
  53      *  about the billing and payment, and the amount of the current payment that
  54      *  was applied to the current billing.
  55      */
  56
  57     -- First we'll go get the xact location.  That will be the fallback location.
  58
  59     SELECT billing_location INTO billing_ou FROM money.grocery WHERE id = p_xact;
  60     IF NOT FOUND THEN
  61         SELECT circ_lib INTO billing_ou FROM action.circulation WHERE id = p_xact;
  62     END IF;
  63
  64     SELECT count(id) INTO billing_row_count FROM money.billing WHERE xact = p_xact;
  65
  66     -- Loop through the positive payments
  67     FOR current_payment IN
  68         SELECT  *
  69           FROM  money.payment_view
  70           WHERE xact = p_xact
  71                 AND NOT voided
  72                 AND amount > 0.0
  73           ORDER BY payment_ts
  74     LOOP
  75
  76     payment_remainder = current_payment.amount;
  77         -- With every new payment row, we need to fast forward
  78         -- the billing lines up to the last paid billing row
  79         fast_forward := TRUE;
  80
  81         SELECT  ws.owning_lib INTO payment_ou
  82             FROM  money.bnm_desk_payment p
  83                 JOIN actor.workstation ws ON (p.cash_drawer = ws.id)
  84             WHERE p.id = current_payment.id;
  85         -- If we don't do this then OPAC CC payments have no payment_ou
  86         IF NOT FOUND THEN
  87             SELECT home_ou INTO payment_ou FROM actor.usr WHERE id = (SELECT accepting_usr FROM money.bnm_payment WHERE id = current_payment.id);
  88         END IF;
  89
  90         -- Were we looking at a billing from a previous step in the loop?
  91         IF billing_remainder > 0.0 THEN
  92             current_result.xact = p_xact;
  93             current_result.payment = current_payment.id;
  94             current_result.billing = current_billing.id;
  95             current_result.payment_ts = current_payment.payment_ts;
  96             current_result.billing_ts = current_billing.billing_ts;
  97             current_result.payment_type = current_payment.payment_type;
  98             current_result.billing_type = current_billing.billing_type;
  99             current_result.payment_ou = payment_ou;
 100             current_result.billing_ou = billing_ou;
 101
 102             IF billing_remainder >= payment_remainder THEN
 103                 current_result.amount = payment_remainder;
 104                 billing_remainder = billing_remainder - payment_remainder;
 105                 payment_remainder = 0.0;
 106                 payment_offset = payment_offset + 1;
 107                 -- If it is equal then we need to close up the billing line and move to the next
 108                 -- This prevents 0 amounts applied to billing lines
 109                 IF billing_remainder = payment_remainder THEN
 110                     billing_remainder = 0.0;
 111                     billing_offset = billing_offset + 1;
 112                     billing_id_used = array_append( billing_id_used, current_billing_id );
 113                 ELSE
 114                     maintain_billing_remainder := TRUE;
 115                 END IF;
 116
 117             ELSE
 118                 current_result.amount = billing_remainder;
 119                 payment_remainder = payment_remainder - billing_remainder;
 120                 billing_remainder = 0.0;
 121                 billing_offset = billing_offset + 1;
 122                 billing_id_used = array_append( billing_id_used, current_billing_id );
 123                 continuing_payment := TRUE;
 124                 maintain_billing_remainder := FALSE;
 125             END IF;
 126
 127             RETURN NEXT current_result;
 128             -- Done paying the billing rows when we run out of rows to pay (out of bounds)
 129             EXIT WHEN array_length(billing_id_used, 1) = billing_row_count;
 130         END IF;
 131
 132         CONTINUE WHEN payment_remainder = 0.0;
 133
 134         -- next billing, please
 135         billing_loop := -1;
 136
 137         FOR current_billing IN
 138             SELECT  *
 139               FROM  money.billing
 140               WHERE xact = p_xact
 141                -- Gotta put the voided billing rows at the bottom (last)
 142               ORDER BY voided,billing_ts
 143         LOOP
 144             billing_loop = billing_loop + 1;
 145
 146             -- Skip billing rows that we have already paid
 147             IF billing_id_used @> ARRAY[current_billing.id]    THEN CONTINUE;
 148             END IF;
 149
 150             IF maintain_billing_remainder THEN
 151                 CONTINUE WHEN current_billing.id <> current_billing_id;
 152                 -- Account adjustment - we expect to pay billing rows that are identical amounts
 153                 ELSE IF current_payment.payment_type = 'account_adjustment' THEN
 154                     -- Go ahead and allow the row through when it's the last row and we still haven't found one with equal payment amount
 155                     CONTINUE WHEN ( ( current_billing.amount <> current_payment.amount ) AND ( billing_loop + 1 <> billing_row_count ) );
 156                 END IF;
 157             END IF;
 158
 159             -- Keep the old remainder if we were in the middle of a billing row
 160             IF NOT maintain_billing_remainder THEN
 161                 billing_remainder = current_billing.amount;
 162             END IF;
 163
 164             maintain_billing_remainder := FALSE;
 165             fast_forward := FALSE;
 166             current_billing_id := current_billing.id;
 167             continuing_payment := FALSE;
 168
 169             current_result.xact = p_xact;
 170             current_result.payment = current_payment.id;
 171             current_result.billing = current_billing.id;
 172             current_result.payment_ts = current_payment.payment_ts;
 173             current_result.billing_ts = current_billing.billing_ts;
 174             current_result.payment_type = current_payment.payment_type;
 175             current_result.billing_type = current_billing.billing_type;
 176             current_result.payment_ou = payment_ou;
 177             current_result.billing_ou = billing_ou;
 178
 179             IF billing_remainder >= payment_remainder THEN
 180                 current_result.amount = payment_remainder;
 181                 billing_remainder = billing_remainder - payment_remainder;
 182                 payment_remainder = 0.0;
 183                 -- If it is equal then we need to close up the billing line and move to the next
 184                 -- This prevents 0 amounts applied to billing lines
 185                 IF billing_remainder = payment_remainder THEN
 186                     billing_remainder = 0.0;
 187                     billing_offset = billing_offset + 1;
 188                     billing_id_used = array_append( billing_id_used, current_billing_id );
 189                 END IF;
 190             ELSE
 191                 current_result.amount = billing_remainder;
 192                 payment_remainder = payment_remainder - billing_remainder;
 193                 continuing_payment := TRUE;
 194                 IF billing_loop + 1 = billing_row_count THEN
 195                 -- We have a situation where we are on the last billing row and we are in the middle of a payment row
 196                 -- We need to start back at the beginning of the billing rows and pay
 197                     continuing_payment_last_row := TRUE;
 198                 END IF;
 199                 billing_remainder = 0.0;
 200                 billing_offset = billing_offset + 1;
 201                 billing_id_used = array_append( billing_id_used, current_billing_id );
 202             END IF;
 203
 204             RETURN NEXT current_result;
 205             IF continuing_payment_last_row THEN
 206                 -- This should only occur when the account_adjustment's do not line up exactly with the billing
 207                 -- So we are going to pay some other type of billing row with this odd account_adjustment
 208                 -- And we need to stay in the current_payment row while doing so
 209                 billing_loop := -1;
 210                 FOR current_billing IN
 211                     SELECT  *
 212                       FROM  money.billing
 213                       WHERE xact = p_xact
 214                       ORDER BY voided,billing_ts
 215                 LOOP
 216                     billing_loop = billing_loop + 1;
 217                     -- Skip billing rows that we have already paid
 218                     IF billing_id_used @> ARRAY[current_billing.id]    THEN CONTINUE; END IF;
 219
 220                     billing_remainder = current_billing.amount;
 221                     current_billing_id := current_billing.id;
 222                     continuing_payment := FALSE;
 223
 224                     current_result.xact = p_xact;
 225                     current_result.payment = current_payment.id;
 226                     current_result.billing = current_billing.id;
 227                     current_result.payment_ts = current_payment.payment_ts;
 228                     current_result.billing_ts = current_billing.billing_ts;
 229                     current_result.payment_type = current_payment.payment_type;
 230                     current_result.billing_type = current_billing.billing_type;
 231                     current_result.payment_ou = payment_ou;
 232                     current_result.billing_ou = billing_ou;
 233
 234                     IF billing_remainder >= payment_remainder THEN
 235                         current_result.amount = payment_remainder;
 236                         billing_remainder = billing_remainder - payment_remainder;
 237                         payment_remainder = 0.0;
 238                         -- If it is equal then we need to close up the billing line and move to the next
 239                         -- This prevents 0 amounts applied to billing lines
 240                         IF billing_remainder = payment_remainder THEN
 241                             billing_remainder = 0.0;
 242                             billing_offset = billing_offset + 1;
 243                             billing_id_used = array_append( billing_id_used, current_billing_id );
 244                         END IF;
 245                     ELSE
 246                         current_result.amount = billing_remainder;
 247                         payment_remainder = payment_remainder - billing_remainder;
 248                         billing_remainder = 0.0;
 249                         billing_offset = billing_offset + 1;
 250                         billing_id_used = array_append( billing_id_used, current_billing_id );
 251                     END IF;
 252
 253                     RETURN NEXT current_result;
 254                     EXIT WHEN payment_remainder = 0.0;
 255                 END LOOP;
 256
 257             END IF;
 258             EXIT WHEN payment_remainder = 0.0;
 259         END LOOP;
 260
 261         payment_offset = payment_offset + 1;
 262         -- Done paying the billing rows when we run out of rows to pay (out of bounds)
 263         EXIT WHEN array_length(billing_id_used, 1) = billing_row_count;
 264
 265     END LOOP;
 266
 267     payment_remainder   := 0.0;
 268     billing_remainder   := 0.0;
 269     payment_offset      := 0;
 270     billing_offset      := 0;
 271     billing_row_count   := 0;
 272     billing_loop        := -1;
 273
 274     -- figure out how many voided billing rows there are
 275     SELECT count(id) INTO billing_row_count FROM money.billing WHERE xact = p_xact AND voided;
 276
 277     -- Loop through the negative payments, these are refunds on voided billings
 278     FOR current_payment IN
 279         SELECT  *
 280           FROM  money.payment_view
 281           WHERE xact = p_xact
 282                 AND NOT voided
 283                 AND amount < 0.0
 284           ORDER BY payment_ts
 285     LOOP
 286
 287         SELECT  ws.owning_lib INTO payment_ou
 288             FROM  money.bnm_desk_payment p
 289                 JOIN actor.workstation ws ON (p.cash_drawer = ws.id)
 290             WHERE p.id = current_payment.id;
 291
 292         IF NOT FOUND THEN
 293             SELECT home_ou INTO payment_ou FROM actor.usr WHERE id = (SELECT accepting_usr FROM money.bnm_payment WHERE id = current_payment.id);
 294         END IF;
 295
 296         payment_remainder = -current_payment.amount; -- invert
 297         -- With every new payment row, we need to fast forward
 298         -- the billing lines up to the last paid billing row
 299         fast_forward := TRUE;
 300
 301         -- Were we looking at a billing from a previous step in the loop?
 302         IF billing_remainder > 0.0 THEN
 303
 304             current_result.xact = p_xact;
 305             current_result.payment = current_payment.id;
 306             current_result.billing = current_billing.id;
 307             current_result.payment_ts = current_payment.payment_ts;
 308             current_result.billing_ts = current_billing.billing_ts;
 309             current_result.payment_type = 'REFUND';
 310             current_result.billing_type = current_billing.billing_type;
 311             current_result.payment_ou = payment_ou;
 312             current_result.billing_ou = billing_ou;
 313
 314             IF billing_remainder >= payment_remainder THEN
 315                 current_result.amount = payment_remainder;
 316                 billing_remainder = billing_remainder - payment_remainder;
 317                 payment_remainder = 0.0;
 318                 payment_offset = payment_offset + 1;
 319                 -- If it is equal then we need to close up the billing line and move to the next
 320                 -- This prevents 0 amounts applied to billing lines
 321                 IF billing_remainder = payment_remainder THEN
 322                     billing_remainder = 0.0;
 323                     billing_offset = billing_offset + 1;
 324                 ELSE
 325                     maintain_billing_remainder := TRUE;
 326                 END IF;
 327             ELSE
 328                 current_result.amount = billing_remainder;
 329                 payment_remainder = payment_remainder - billing_remainder;
 330                 billing_remainder = 0.0;
 331                 billing_offset = billing_offset + 1;
 332             END IF;
 333
 334             current_result.amount = -current_result.amount;
 335             RETURN NEXT current_result;
 336             -- Done paying the billing rows when we run out of rows to pay (out of bounds)
 337             EXIT WHEN billing_offset = billing_row_count + 1;
 338         END IF;
 339
 340         CONTINUE WHEN payment_remainder = 0.0;
 341
 342         -- next billing, please
 343         billing_loop := -1;
 344         FOR current_billing IN
 345             SELECT  *
 346               FROM  money.billing
 347               WHERE xact = p_xact
 348                     AND voided
 349               ORDER BY billing_ts
 350         LOOP
 351             billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1
 352             -- Fast forward through the rows until we get to the billing row
 353             -- where we left off
 354             IF fast_forward THEN
 355                 CONTINUE WHEN billing_loop <> billing_offset;
 356             END IF;
 357
 358             -- Keep the old remainder if we were in the middle of a billing row
 359             IF NOT maintain_billing_remainder THEN
 360                 billing_remainder = current_billing.amount;
 361             END IF;
 362
 363             maintain_billing_remainder := FALSE;
 364             fast_forward := FALSE;
 365
 366             current_result.xact = p_xact;
 367             current_result.payment = current_payment.id;
 368             current_result.billing = current_billing.id;
 369             current_result.payment_ts = current_payment.payment_ts;
 370             current_result.billing_ts = current_billing.billing_ts;
 371             current_result.payment_type = 'REFUND';
 372             current_result.billing_type = current_billing.billing_type;
 373             current_result.payment_ou = payment_ou;
 374             current_result.billing_ou = billing_ou;
 375
 376             IF billing_remainder >= payment_remainder THEN
 377                 current_result.amount = payment_remainder;
 378                 billing_remainder = billing_remainder - payment_remainder;
 379                 payment_remainder = 0.0;
 380                 -- If it is equal then we need to close up the billing line and move to the next
 381                 -- This prevents 0 amounts applied to billing lines
 382                 IF billing_remainder = payment_remainder THEN
 383                     billing_remainder = 0.0;
 384                     billing_offset = billing_offset + 1;
 385                 END IF;
 386             ELSE
 387                 current_result.amount = billing_remainder;
 388                 payment_remainder = payment_remainder - billing_remainder;
 389                 billing_remainder = 0.0;
 390                 billing_offset = billing_offset + 1;
 391             END IF;
 392
 393             current_result.amount = -current_result.amount;
 394             RETURN NEXT current_result;
 395
 396             EXIT WHEN payment_remainder = 0.0;
 397
 398         END LOOP;
 399
 400         payment_offset = payment_offset + 1;
 401         -- Done paying the billing rows when we run out of rows to pay (out of bounds)
 402         EXIT WHEN billing_offset = billing_row_count + 1;
 403
 404     END LOOP;
 405
 406 END;
 407
 408 $$ LANGUAGE PLPGSQL;
 409
 410
 411
 412 CREATE OR REPLACE FUNCTION money.payment_by_billing_type (
 413     range_start TIMESTAMPTZ,
 414     range_end TIMESTAMPTZ,
 415     location INT
 416 ) RETURNS SETOF money.materialized_payment_by_billing_type AS $$
 417
 418 DECLARE
 419     current_transaction RECORD;
 420     current_result      money.materialized_payment_by_billing_type%ROWTYPE;
 421 BEGIN
 422
 423     -- first, we find transactions at specified locations involving
 424     -- positve, unvoided payments within the specified range
 425     FOR current_transaction IN
 426         SELECT  DISTINCT x.id
 427           FROM  action.circulation x
 428                 JOIN money.payment p ON (x.id = p.xact)
 429                 JOIN actor.org_unit_descendants(location) d
 430                     ON (d.id = x.circ_lib)
 431           WHERE p.payment_ts BETWEEN range_start AND range_end
 432                 AND NOT p.voided
 433                 AND p.amount > 0.0
 434             UNION ALL
 435         SELECT  DISTINCT x.id
 436           FROM  money.grocery x
 437                 JOIN money.payment p ON (x.id = p.xact)
 438                 JOIN actor.org_unit_descendants(location) d
 439                     ON (d.id = x.billing_location)
 440           WHERE p.payment_ts BETWEEN range_start AND range_end
 441                 AND NOT p.voided
 442                 AND p.amount > 0.0
 443     LOOP
 444
 445         -- then, we send each transaction to the payment-by-billing-type
 446         -- calculator, and return rows for payments we're interested in
 447         FOR current_result IN
 448             SELECT * FROM money.payment_by_billing_type( current_transaction.id )
 449         LOOP
 450             IF current_result.payment_ts BETWEEN range_start AND range_end THEN
 451                 RETURN NEXT current_result;
 452             END IF;
 453         END LOOP;
 454
 455     END LOOP;
 456
 457 END;
 458
 459 $$ LANGUAGE PLPGSQL;
 460
 461 CREATE OR REPLACE FUNCTION money.payment_by_billing_type_trigger ()
 462 RETURNS TRIGGER AS $$
 463
 464 BEGIN
 465
 466     IF TG_OP = 'INSERT' THEN
 467         DELETE FROM money.materialized_payment_by_billing_type
 468             WHERE xact = NEW.xact;
 469
 470         INSERT INTO money.materialized_payment_by_billing_type (
 471             xact, payment, billing, payment_ts, billing_ts,
 472             payment_type, billing_type, amount, billing_ou, payment_ou
 473         ) SELECT    xact, payment, billing, payment_ts, billing_ts,
 474                     payment_type, billing_type, amount, billing_ou, payment_ou
 475           FROM  money.payment_by_billing_type( NEW.xact );
 476
 477     ELSIF TG_OP = 'UPDATE' THEN
 478         DELETE FROM money.materialized_payment_by_billing_type
 479             WHERE xact IN (OLD.xact,NEW.xact);
 480
 481         INSERT INTO money.materialized_payment_by_billing_type (
 482             xact, payment, billing, payment_ts, billing_ts,
 483             payment_type, billing_type, amount, billing_ou, payment_ou
 484         ) SELECT    xact, payment, billing, payment_ts, billing_ts,
 485                     payment_type, billing_type, amount, billing_ou, payment_ou
 486           FROM money.payment_by_billing_type( NEW.xact );
 487
 488         IF NEW.xact <> OLD.xact THEN
 489             INSERT INTO money.materialized_payment_by_billing_type (
 490                 xact, payment, billing, payment_ts, billing_ts,
 491                 payment_type, billing_type, amount, billing_ou, payment_ou
 492             ) SELECT    xact, payment, billing, payment_ts, billing_ts,
 493                         payment_type, billing_type, amount, billing_ou, payment_ou
 494               FROM money.payment_by_billing_type( OLD.xact );
 495         END IF;
 496
 497     ELSE
 498         DELETE FROM money.materialized_payment_by_billing_type
 499             WHERE xact = OLD.xact;
 500
 501         INSERT INTO money.materialized_payment_by_billing_type (
 502             xact, payment, billing, payment_ts, billing_ts,
 503             payment_type, billing_type, amount, billing_ou, payment_ou
 504         ) SELECT    xact, payment, billing, payment_ts, billing_ts,
 505                     payment_type, billing_type, amount, billing_ou, payment_ou
 506           FROM money.payment_by_billing_type( OLD.xact );
 507
 508         RETURN OLD;
 509     END IF;
 510
 511     RETURN NEW;
 512
 513 END;
 514
 515 $$ LANGUAGE PLPGSQL;
 516
 517
 518 CREATE TRIGGER calculate_payment_by_btype_tgr
 519     AFTER INSERT OR UPDATE OR DELETE ON money.billing
 520     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 521
 522 CREATE TRIGGER calculate_payment_by_btype_tgr
 523     AFTER INSERT OR UPDATE OR DELETE ON money.payment
 524     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 525
 526 CREATE TRIGGER calculate_payment_by_btype_tgr
 527     AFTER INSERT OR UPDATE OR DELETE ON money.bnm_payment
 528     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 529
 530 CREATE TRIGGER calculate_payment_by_btype_tgr
 531     AFTER INSERT OR UPDATE OR DELETE ON money.forgive_payment
 532     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 533
 534 CREATE TRIGGER calculate_payment_by_btype_tgr
 535     AFTER INSERT OR UPDATE OR DELETE ON money.work_payment
 536     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 537
 538 CREATE TRIGGER calculate_payment_by_btype_tgr
 539     AFTER INSERT OR UPDATE OR DELETE ON money.credit_payment
 540     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 541
 542 CREATE TRIGGER calculate_payment_by_btype_tgr
 543     AFTER INSERT OR UPDATE OR DELETE ON money.goods_payment
 544     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 545
 546 CREATE TRIGGER calculate_payment_by_btype_tgr
 547     AFTER INSERT OR UPDATE OR DELETE ON money.bnm_desk_payment
 548     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 549
 550 CREATE TRIGGER calculate_payment_by_btype_tgr
 551     AFTER INSERT OR UPDATE OR DELETE ON money.cash_payment
 552     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 553
 554 CREATE TRIGGER calculate_payment_by_btype_tgr
 555     AFTER INSERT OR UPDATE OR DELETE ON money.check_payment
 556     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 557
 558 CREATE TRIGGER calculate_payment_by_btype_tgr
 559     AFTER INSERT OR UPDATE OR DELETE ON money.credit_card_payment
 560     FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger();
 561
 562
 563 COMMIT;
 564
 565
 566 -- Now Populate the materialized table
 567
 568 BEGIN;
 569
 570 CREATE OR REPLACE FUNCTION tmp_populate_p_b_bt () RETURNS BOOL AS $$
 571 DECLARE
 572     p   RECORD;
 573 BEGIN
 574     FOR p IN
 575         SELECT  DISTINCT xact
 576           FROM  money.payment
 577     LOOP
 578
 579         INSERT INTO money.materialized_payment_by_billing_type (
 580             xact, payment, billing, payment_ts, billing_ts,
 581             payment_type, billing_type, amount, billing_ou, payment_ou
 582         ) SELECT    xact, payment, billing, payment_ts, billing_ts,
 583                     payment_type, billing_type, amount, billing_ou, payment_ou
 584           FROM money.payment_by_billing_type( p.xact );
 585
 586     END LOOP;
 587
 588     RETURN TRUE;
 589 END;
 590 $$ LANGUAGE PLPGSQL;
 591
 592 SELECT tmp_populate_p_b_bt();
 593
 594 DROP FUNCTION tmp_populate_p_b_bt ();
 595
 596 COMMIT;