problem with a trigger (Oracle 11gR2)

From: Murray Sobol <Murray.Sobol_at_openlink.com>
Date: Wed, 30 Apr 2014 14:33:45 -0400
Message-ID: <D9F6401A9C2CFC4280E2F648ADFCDD7782C77B007A_at_OLFANDEXCH01.andover.olf.com>

I am having a problem accessing a Global Temporary table inside a trigger. Here is my code:
CREATE GLOBAL TEMPORARY TABLE gtt_ai_fin_transaction   (

   transaction_nbr                 number                      null,
   gl_date                         date                        null,
   transaction_amount              number(18,6)                null,
   discount_taken_amount           number(18,6)                null,
   after_balance_amount            number(18,6)                null,
   after_status                    char(1)                     null

  )
  ON COMMIT DELETE ROWS
  tablespace temp
 /

CREATE or replace TRIGGER ai_fin_transaction AFTER INSERT ON fin_transaction FOR EACH ROW

   DECLARE TransactionNumber      number;
           TransactionAmount      number(18,6);
           DiscountTakenAmount    number(18,6);
           AfterBalanceAmount     number(18,6);
           InvoiceNbr             number;

   CURSOR transactions IS
   WITH MaxGLdate (invoice_nbr,gl_date) AS

       (SELECT ft.invoice_nbr,
               MAX(ft.gl_date)
          FROM fin_transaction ft
         WHERE ft.invoice_nbr = :new.invoice_nbr
         GROUP BY ft.invoice_nbr
       )
   SELECT :new.invoice_nbr
     FROM dual
          JOIN MaxGLdate mgld
            ON :new.invoice_nbr = mgld.invoice_nbr

    WHERE :new.gl_date < mgld.gl_date;

   CURSOR recalculate IS
   SELECT transaction_nbr,

          transaction_amount,
          discount_taken_amount
     FROM gtt_ai_fin_transaction
    ORDER BY gl_date,
             transaction_nbr;

BEGIN    IF (:new.invoice_nbr IS NOT NULL) THEN

     UPDATE fin_invoice
        SET last_transaction_date = :new.transaction_date
      WHERE invoice_nbr = :new.invoice_nbr;

   END IF;    IF (:new.payment_nbr IS NOT NULL) THEN

     UPDATE fin_payment
        SET last_transaction_date = :new.transaction_date
      WHERE payment_nbr = :new.payment_nbr;

   END IF;    OPEN transactions;

     LOOP

       FETCH transactions INTO InvoiceNbr;
       EXIT WHEN transactions%NOTFOUND;

       DELETE FROM gtt_ai_fin_transaction;

       INSERT INTO gtt_ai_fin_transaction
       SELECT transaction_nbr,
              gl_date,
              CASE WHEN transaction_type IN ('PAY','CON','CRD','PAD','ADJ','ACC')
                       THEN transaction_amount * -1
                       ELSE transaction_amount
              END,
              CASE WHEN transaction_type IN ('PAY','CON','CRD','PAD','ADJ','ACC')
                       THEN discount_taken_amount * -1
                       ELSE discount_taken_amount
              END,
              0.00,
              NULL
         FROM fin_transaction
        WHERE invoice_nbr = InvoiceNbr
          AND payment_nbr IS NOT NULL
        ORDER BY gl_date,
                 transaction_nbr;

       SELECT after_balance_amount
         INTO AfterBalanceAmount
         FROM fin_transaction
        WHERE invoice_nbr = InvoiceNbr
          AND payment_nbr IS NULL;

        OPEN recalculate;

         LOOP

            FETCH recalculate INTO TransactionNumber,
                                   TransactionAmount,
                                   DiscountTakenAmount;
            EXIT WHEN recalculate%NOTFOUND;

            AfterBalanceAmount := AfterBalanceAmount + TransactionAmount + DiscountTakenAmount;

            UPDATE gtt_ai_fin_transaction
               SET after_balance_amount = AfterBalanceAmount,
                   after_status = CASE WHEN AfterBalanceAmount = 0.00
                                           THEN 'C'
                                           ELSE 'O'
                                  END
             WHERE transaction_nbr = TransactionNumber;

        END LOOP;

        UPDATE fin_transaction
           SET fin_transaction.after_balance_amount = gtt_ai_fin_transaction.after_balance_amount,
               fin_transaction.after_status = gtt_ai_fin_transaction.after_status
         WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr;

     END LOOP;

END; And here is the error:

AI_FIN_TRANSACTION	TRIGGER	1	128	50	PL/SQL: ORA-00904: "GTT_AI_FIN_TRANSACTION"."TRANSACTION_NBR": invalid identifier	ERROR	0
AI_FIN_TRANSACTION	TRIGGER	2	125	9	PL/SQL: SQL Statement ignored	ERROR	0

The error is pointing to this "update" statement but I don't understand why.
        UPDATE fin_transaction
           SET fin_transaction.after_balance_amount = gtt_ai_fin_transaction.after_balance_amount,
               fin_transaction.after_status = gtt_ai_fin_transaction.after_status
         WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr;

Thanks
Murray Sobol
Murray.sobol_at_openlink.com
murrays_at_dbcsmartsoftware.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 30 2014 - 20:33:45 CEST