Custom trigger on IBY_PMT_INSTR_USES_ALL column BEFORE UPDATE OF END_DATE raise userfriendly excepti

Edwin van den Bos

Hi,

ORACLE E-business suite
RDBMS_VERSION = 11.2.0.3.0
PL/SQL Release 11.2.0.3.0 - Production
ORacle release 12.2.4

ARU_RELEASE_NAME =R12

I would like to raise an exception when the user is changing the end_date of a bank account.

an exception is raised by oracle but it isn't the exception I have specified

I have placed a custom trigger on the table IBY_PMT_INSTR_USES_ALL and I want to raise an user-friendly exception when the user changes the end_date of the bank.

but whatever I'm doing Oracle is always showing the error down below in the web form.

Error Page

You have encountered an unexpected error. Please contact the System Administrator for assistance.

My question is.

is it possible to raise a user-friendly exception using a trigger which is defined on the table IBY_PMT_INSTR_USES_ALL column BEFORE UPDATE OF END_DATE ?

create or replace TRIGGER APPS.XXTEST_IPIUA_BRU BEFORE UPDATE OF END_DATE ON IBY.IBY_PMT_INSTR_USES_ALL#

If so, how should this raise exception look like?

I already have tried.

- RAISE_APPLICATION_ERROR(-20001, '----------test----------'); at the begin of the trigger

- already defined a custom Exception using the pragma Exception_init

- Exception handling at the end of the trigger

- fnd_message.set_name ('FND', 'test');

app_exception.raise_exception;

But all these options will end in the same errormessage

Error Page You have encountered an unexpected error. Please contact the System Administrator for assistance.

steps to reproduce

1. add a bank account on customer site level in the responsibility Receivables
2. make sure a bank account is added on site level
3. end_date the bank account, so that the trigger will be triggered
4. make sure that the exception is added at the begin of the trigger
5. you will face the errormessage :You have encountered an unexpected error. Please contact the System Administrator for assistance.

hopefully you can help me with this.

I already did some research on the web but without any success.

The same approach is working at another database table and column.

thanks for your help.

Edwin

John Thorton

We can't debug code we can not see

Please click on URL below & provide details as stated in #5 - #9 inclusive

How do I ask a question on the forums?

L. Fernigrini

The trigger runs on the DB and does not show any message, exceptions are passed to the UI

What application are you talking about? It is one that Oracle sells like EBS or a home made app? If it is an Oracle app, you may want to ask on the space of that app.

Otherwise you need to talk to the team that develops the App and ask them to handle the exception the way you want.

Paulzip

We don't have access to your application / web pages.

We don't have your tables, data or code.

So your "steps to reproduce" are meaningless to us, we can't mind-read what your code does.

However, exceptions propagate from a trigger the same as from any pl/sql block  (anonymous block / stored proc / package etc. etc.).  So it seems like your application layer is gobbling them up and returning a generic "You have encountered an unexpected error. Please contact the System Administrator for assistance.", which in my eyes is poor programming.

Edwin van den Bos

Hi John,

Please find below the code what will raise an errormessage.
it's the most simple code yet, but this will reproduce it my scenario

create or replace TRIGGER APPS.XXTEST_IPIUA_BRU BEFORE UPDATE OF END_DATE ON IBY.IBY_PMT_INSTR_USES_ALL#

FOR EACH ROW
DECLARE
BEGIN
IF (:NEW.payment_function = 'CUSTOMER_PAYMENT') THEN
    fnd_message.set_name ('FND', 'Please do not remove end_date') ;
    app_exception.raise_exception;
   -- RAISE_APPLICATION_ERROR(-20001, '----------test----------');
   END IF;

END XXTEXT_IPIUA_BRU;

thanks
Edwin

Edwin van den Bos

Hi John,

Please find below the code what will raise an errormessage.
it's the most simple code yet, but this will reproduce it my scenario

create or replace TRIGGER APPS.XXTEST_IPIUA_BRU BEFORE UPDATE OF END_DATE ON IBY.IBY_PMT_INSTR_USES_ALL#

FOR EACH ROW
DECLARE
BEGIN
IF (:NEW.payment_function = 'CUSTOMER_PAYMENT') THEN
    fnd_message.set_name ('FND', 'Please do not remove end_date') ;
    app_exception.raise_exception;
   -- RAISE_APPLICATION_ERROR(-20001, '----------test----------');
   END IF;

END XXTEXT_IPIUA_BRU;

thanks
Edwin

John Thorton

You have a mystery & gives us NO clues.

Posted "error message" is not a standard Oracle error message & is NOT coming from posted code.

So we are clueless from where or why it appears.

We  have no evidence that posted TRIGGER is actually getting invoked.

When below is FALSE, then TRIGGER code exits silently & does nothing

>IF (:NEW.payment_function = 'CUSTOMER_PAYMENT') THEN