1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Web ADI - How to return an error with a custom integrator

Discussion in 'Oracle Apps Technical' started by uday_3393, Apr 27, 2010.

  1. uday_3393

    uday_3393 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hey guys Iam facing problem when returning pkg exceptions to the web adi

    Scenario:
    We have temporary table where we upload the excel sheet data.
    After rows get uploaded through web ADI excel sheet, we call the pkg to process the data. Even if error or exception ocurred in pkg , the web adi after completion shows rows successfully processed.

    I have added the code like-

    Code (SQL):
    BEGIN
       SELECT   action_type, quote_header_id
         INTO   v_action_type, v_quote_header_id
         FROM   quotes_upd_intf
        WHERE   batch_id = p_batch_id AND ROWNUM < 2;

       SELECT   max_version_flag
         INTO   v_max_version_flag
         FROM   aso_quote_headers_all
        WHERE   quote_header_id = v_quote_header_id;

       IF v_max_version_flag = 'Y'
       THEN
          IF v_action_type = 'U'
          THEN
             update_quote (p_batch_id);
          ELSIF v_action_type = 'C'
          THEN
             create_quote_version (p_batch_id);
          END IF;
       ELSE
          FND_MESSAGE.CLEAR;
          FND_MESSAGE.SET_NAME ('BNE', 'WEBADI_ERROR');
          FND_MESSAGE.SET_TOKEN ('MSG', 'Error');
       END IF;
    END;
    This is my setup:
    Name: WEBADI_ERROR
    Language: US
    Application: Web Applications Desktop Integrator
    Type: 30% Expansion Prompt
    Current Message Text: &MSG


    But this code is not working. It not returns any error msg to excel webadi sheet.
    what would be the problem
    Please help me. its very urgent

    Thanks
    Uday
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    You need to use exceptions in your ADI procedure code and use raise raise_application_error to send the error back to the ADI. Post your ADI package's procedure code that you are calling on running the ADI and I might be able to help you out.
     
  3. uday_3393

    uday_3393 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    PROCEDURE process_quote (p_batch_id IN NUMBER
    )
    IS
    ---------------------------
    v_action_type varchar2(1);
    begin
    SELECT action_type, quote_header_id
    INTO v_action_type, v_quote_header_id
    FROM quotes_upd_intf
    WHERE batch_id = p_batch_id AND ROWNUM < 2;

    SELECT max_version_flag
    INTO v_max_version_flag
    FROM aso_quote_headers_all
    WHERE quote_header_id = v_quote_header_id;

    IF v_max_version_flag = 'Y'
    THEN
    IF v_action_type = 'U'
    THEN
    update_quote (p_batch_id);
    ELSIF v_action_type = 'C'
    THEN
    create_quote_version (p_batch_id);
    END IF;
    ELSE
    FND_MESSAGE.CLEAR;
    FND_MESSAGE.SET_NAME ('BNE', 'WEBADI_ERROR');
    FND_MESSAGE.SET_TOKEN ('MSG', 'Error');
    END IF;
    END;
     
  4. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    PLease use the SQL button to highlight your code.

    Try this (not tested):

    Code (SQL):

    PROCEDURE process_quote (p_batch_id IN NUMBER)
    IS
       ---------------------------
       v_action_type   VARCHAR2 (1);
       l_error         VARCHAR2 (500);
       v_exception EXCEPTION;
    BEGIN
       SELECT   action_type, quote_header_id
         INTO   v_action_type, v_quote_header_id
         FROM   quotes_upd_intf
        WHERE   batch_id = p_batch_id AND ROWNUM < 2;

       SELECT   max_version_flag
         INTO   v_max_version_flag
         FROM   aso_quote_headers_all
        WHERE   quote_header_id = v_quote_header_id;

       IF v_max_version_flag = 'Y'
       THEN
          IF v_action_type = 'U'
          THEN
             update_quote (p_batch_id);
          ELSIF v_action_type = 'C'
          THEN
             create_quote_version (p_batch_id);
          END IF;
       ELSE
          RAISE v_exception;
       END IF;
    EXCEPTION
       WHEN v_exception
       THEN
          l_error := 'Error encountered is : ' || SQLERRM;
          raise_application_error (-20001, l_error);
       WHEN OTHERS
       THEN
          l_error := 'Error : ' || SQLERRM;
          raise_application_error (-20001, l_error);
    END process_quote;
     
    Basically this is the way to get error from the sql to show inside the excel file in ADI
     
  5. uday_3393

    uday_3393 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    hey,
    It is not working. In Custom Webadi iam uploading the data in interface table. After upload complete it fires plsql code to insert the data in base table. I think your solution works if i create interface for api.


    thanks,
    Uday
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    There is no reason why the code posted by apps_expert won't work. It's pretty straightforward exception handling. What do you mean when you say, "It's not working"? Is the error not getting shown in the excel? What you should do is create a very basic procedure just to insert a few columns in a custom table without all these functions calls etc.. and see how exception handling can be done. Once you are confident of the methodology you can expand your procedure to do all that you want.
     
  7. uday_3393

    uday_3393 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Sadik,
    thanks for the reply. I added the exception handling in my code. I agree with you that this code is correct. but the problem is that this exception is not getting passed to the webadi excel sheet. After exceptions the web adi still shows that successfully completed.


    thanks,
    Uday
     
  8. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Did you try the suggestion I gave, namely, create a new adi in test instance and create a very basic procedure for it just to insert a few columns in a custom table without all these functions calls etc.. and see how exception handling can be done. Once you are confident of the methodology you can expand your procedure to do all that you want.