-
DB Trigger
Hi,
I am trying to create a DB trigger on AR_RECEIVABLE_APPLICATIONS_ALL table to update the custom table when the above table column attribute1 is updated through the form DFF. But I am getting the following error when I save the changes.
FRM-40735: ON-UPDATE trigger raised unhandled exception ORA-06502.
--------------------------------------------------------------
ORA-01403: no data found
I tested the sql query I have in query and it is giving the value.
The trigger code is
CREATE OR REPLACE TRIGGER XXUL_AR_TRE_REFUND_DATE_UPD
BEFORE UPDATE OF attribute1 ON AR_RECEIVABLE_APPLICATIONS_ALL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/*********************************************************************************
* TYPE : TRIGGER *
* NAME : XXUL_AR_TRE_REFUND_DATE_UPD *
* INPUT PARAMETERS : *
* PURPOSE : This trigger to update the treasury refund date in ULCUS.ULAR_REFUND_PSAP_STAGING
from dff (attribute1 of AR_RECEIVABLE_APPLICATIONS_ALL)
DECLARE
v_tr_refund_date varchar2(150);
v_receipt_number varchar2(30);
v_org_id number;
BEGIN
begin
update XX_REFUND_PSAP_STAGING
set treasury_refunded_date = to_date(:NEW.attribute1,'dd-mon-yyyy')
where refund_number =
(select b.RECEIPT_NUMBER
from AR.AR_RECEIVABLES_TRX_ALL a,
AR.AR_CASH_RECEIPTS_ALL b,
AR.AR_RECEIVABLE_APPLICATIONS_ALL c
where a.org_id = c.org_id and
a.RECEIVABLES_TRX_ID = c.RECEIVABLES_TRX_ID and
b.org_id = c.org_id and
b.cash_receipt_id = c.CASH_RECEIPT_ID and
c.receivable_application_id = :NEW.receivable_application_id
AND ROWNUM = 1);
EXCEPTION
WHEN OTHERS THEN
-- FND_MESSAGE.SET_NAME('FND','ERROR:' || substr(sqlerrm,1,200));
-- FND_MESSAGE.RETRIEVE;
-- raise_application_error(-20020, 'Its Strange :' || sqlerrm);
NULL;
END;
Any help would be greatly appreciated.
Thanks in advance.
-
The select that is a part of the update is not returning any rows. You need to find out if you wrote the query correctly, and if the data is there when you run this. i.e. are you doing things out of order? Updating data before it gets created? Try to avoid doing "WHEN OTHERS THEN NULL;" And document cases where you intentionally ignore errors.
Code:
CREATE OR REPLACE TRIGGER xxul_ar_tre_refund_date_upd
BEFORE UPDATE OF attribute1
ON ar_receivable_applications_all
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/******************************************************************************
* TYPE : TRIGGER *
* NAME : XXUL_AR_TRE_REFUND_DATE_UPD *
* INPUT PARAMETERS : *
* PURPOSE : This trigger to update the treasury refund date in ULCUS.ULAR_REFUND_PSAP_STAGING
* from dff (attribute1 of AR_RECEIVABLE_APPLICATIONS_ALL)
*/
DECLARE
v_tr_refund_date VARCHAR2 ( 150 );
v_receipt_number VARCHAR2 ( 30 );
v_org_id NUMBER;
BEGIN
UPDATE xx_refund_psap_staging
SET treasury_refunded_date = TO_DATE ( :NEW.attribute1, 'dd-mon-yyyy' )
WHERE refund_number =
( SELECT b.receipt_number
FROM ar.ar_receivables_trx_all a,
ar.ar_cash_receipts_all b,
ar.ar_receivable_applications_all c
WHERE a.org_id = c.org_id
AND a.receivables_trx_id = c.receivables_trx_id
AND b.org_id = c.org_id
AND b.cash_receipt_id = c.cash_receipt_id
AND c.receivable_application_id = :NEW.receivable_application_id
AND ROWNUM = 1 );
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- There were no rows to update
NULL;
END;
/
-
I am trying to update the data which is already there. I have attribute1 as '12/12/05' and trying to update it to '12/14/05' throught the form DFF (which is attribute1). If I don't raise the exception, it is saving the data but not updating in Cutom table. Just to test the code I tried all the options with WHEN OTHERS THEN NULL, WHEN OTHERS THEN raise the application error. I can document the errors once it is working ...
Please help.
Originally Posted by gandolf989
The select that is a part of the update is not returning any rows. You need to find out if you wrote the query correctly, and if the data is there when you run this. i.e. are you doing things out of order? Updating data before it gets created? Try to avoid doing "WHEN OTHERS THEN NULL;" And document cases where you intentionally ignore errors.
Code:
CREATE OR REPLACE TRIGGER xxul_ar_tre_refund_date_upd
BEFORE UPDATE OF attribute1
ON ar_receivable_applications_all
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/******************************************************************************
* TYPE : TRIGGER *
* NAME : XXUL_AR_TRE_REFUND_DATE_UPD *
* INPUT PARAMETERS : *
* PURPOSE : This trigger to update the treasury refund date in ULCUS.ULAR_REFUND_PSAP_STAGING
* from dff (attribute1 of AR_RECEIVABLE_APPLICATIONS_ALL)
*/
DECLARE
v_tr_refund_date VARCHAR2 ( 150 );
v_receipt_number VARCHAR2 ( 30 );
v_org_id NUMBER;
BEGIN
UPDATE xx_refund_psap_staging
SET treasury_refunded_date = TO_DATE ( :NEW.attribute1, 'dd-mon-yyyy' )
WHERE refund_number =
( SELECT b.receipt_number
FROM ar.ar_receivables_trx_all a,
ar.ar_cash_receipts_all b,
ar.ar_receivable_applications_all c
WHERE a.org_id = c.org_id
AND a.receivables_trx_id = c.receivables_trx_id
AND b.org_id = c.org_id
AND b.cash_receipt_id = c.cash_receipt_id
AND c.receivable_application_id = :NEW.receivable_application_id
AND ROWNUM = 1 );
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- There were no rows to update
NULL;
END;
/
-
Is the data in xx_refund_psap_staging being inserted in the same transaction?
What if you made this an after trigger?
-
yes it is there in xx_refund_staging table. If I run the query outside with the receivable application id hardcoded, it is working fine and also I tried to hardcode the all the values including attribute1 new value and receivablie_application_id in the trigger, still the same error message. So it not the select or update issue.
I tried the after trigger too ....no luck.
I found there are already 5 triggers on this table. that could be the issue ?
Thanks
-
You provided the code of DATABASE trigger XXUL_AR_TRE_REFUND_DATE_UPD, but that trigger can never ever raise an "ORA-01403: no data found" error. Not a chance, not the way it is coded - the update statement can never ever raise "no data found", not even when it contains a select statement.
Your problem is not the database trigger, but your FORMS trigger. You obviously have ON-UPDATATE trigger on that form/block/field (hence the "FRM-40735: ON-UPDATE trigger raised unhandled exception ORA-06502" error message), and that is the trigger that is raising your "no data found" - you must have SELECT INTO clause in that trigger that is not returning any row. That is what you have to sort out.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
That make sense, however, I did not touch the form trigger. Which is Oracle seeded form in AR module. So all I am trying write to DB trigger on that table.
-
If I update the table outside Oracle Apps, it is giving the following error.
"ORA-04091: table AR.AR_RECEIVABLE_APPLICATIONS_ALL is mutating, trigger/function may not see it"
Looks like table is mutating ......
-
OK, but "no data found" and "mutating table" are two wery different things! The "mutating table" error is of course caused by the trigger you provided - in the SELECT inside UPDATE statement you are querrying the table that hosts the trigger (AR.AR_RECEIVABLE_APPLICATIONS_ALL), which is not allowed for row level triggers. So that trigger is not causing your "no data found" (for that you'll have to check other triggers on that table), but it surely does cause "mutating table" error.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi Jurij,
Thanks for the valuable suggestions. I changed the sql statement not use the AR.AR_RECEIVABLE_APPLICATIONS_ALL inside the trigger and it worked fine.
This issue resolved. thanks once again.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|