I create a trigger which is now giving a mutating error.
Here is the trigger:
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
REFERENCING NEW AS NEW
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(COALESCE(ft.gl_date,ft.transaction_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,
COALESCE(gl_date,transaction_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 COALESCE(gl_date,transaction_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 =
(SELECT after_balance_amount
FROM gtt_ai_fin_transaction
WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr
),
fin_transaction.after_status =
(SELECT after_status
FROM gtt_ai_fin_transaction
WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr
)
WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr;

END LOOP;

END;

The trigger compiles ok with no errors;
When I try to test it using this data:
insert into fin_transaction (TRANSACTION_NBR,invoice_nbr,transaction_amount,transaction_type,transaction_date,after_balance_amou nt,after_status,financial_source)
values (319,999,10000.00,'INV',SysDate,10000.00,'O','A/R')
;
I get this error:
Error starting at line : 1 in command -
insert into fin_transaction (TRANSACTION_NBR,invoice_nbr,transaction_amount,transaction_type,transaction_date,after_balance_amou nt,after_status,financial_source)
values (319,999,10000.00,'INV',SysDate,10000.00,'O','A/R')
Error report -
SQL Error: ORA-04091: table GRAIND16.FIN_TRANSACTION is mutating, trigger/function may not see it
ORA-06512: at "GRAIND16.AI_FIN_TRANSACTION", line 8
ORA-06512: at "GRAIND16.AI_FIN_TRANSACTION", line 47
ORA-04088: error during execution of trigger 'GRAIND16.AI_FIN_TRANSACTION'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

I can run the SQL statements individually and do not have any problems.
I have examined the trigger and, for the life of me, I don't see where the problem is.
Can anyone assist me???

Thanks
Murray