Forum Stats

  • 3,817,223 Users
  • 2,259,291 Discussions
  • 7,893,702 Comments

Discussions

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

Edwin van den Bos
Edwin van den Bos Member Posts: 4 Red Ribbon
edited May 12, 2020 9:46AM in SQL & PL/SQL

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

Tagged:

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 12, 2020 9:09AM

    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
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,805 Silver Crown
    edited May 12, 2020 9:11AM

    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
    Paulzip Member Posts: 8,678 Blue Diamond
    edited May 12, 2020 9:16AM

    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
    Edwin van den Bos Member Posts: 4 Red Ribbon
    edited May 12, 2020 9:25AM

    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
    Edwin van den Bos Member Posts: 4 Red Ribbon
    edited May 12, 2020 9:25AM

    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
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 12, 2020 9:46AM

    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