5 Replies Latest reply: Mar 28, 2014 1:28 PM by 9f3d1e56-3993-4695-8264-7f21563a093d RSS

How to show custom error message in WebADI Excel template?

ens005 Newbie
Currently Being Moderated

Hi,

I've  created a custom Web ADI integrator and associated it with a 'Procedure' based custom interface.

WebADI Interface API Returns is set to  "Error Message".

I'm using  raise_application_error(-20001, "Actual Error Message") for invalid rows,but custom error message from PL/SQL  is not populated on the excel template.

Instead it is showing "SQL exception occurred during PL/SQL upload".


Am I missing anything? How to show custom error message from Pl/SQL procedure to WebADI Excel template?



TIA

Narasimha

  • 1. Re: How to show custom error message in WebADI Excel template?
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated

    Can you find any errors in the BNE.log file? -- How to Create a BNE Log For Web Adi Issues and Errors? (Doc ID 817023.1)

     

    Please see if (How to Define an Importer Returning Error Messages to the Oracle Web Applications Desktop Integrator Document (Doc ID 1475566.1)) helps.

     

    Thanks,

    Hussein

  • 2. Re: How to show custom error message in WebADI Excel template?
    ens005 Newbie
    Currently Being Moderated

    The custom API errors are visible in the BNE log but not on the Excel.

     

    BNE Log=>

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    12/10/13 2:52 PM Web ADI Upload Job 13008 ERROR          BnePLSQLUpload.doUpload: Exception while uploading to PL/SQL API.  Error Code: 20001, Message: ORA-20001: -Please enter CONTAINER_ID -  Enter PO_NO -

    ORA-06512: at "APPS.XXPO_COSTFACTS_WEBADI_PKG", line 264

    ORA-06512: at line 1

     

     

    12/10/13 2:52 PM Web ADI Upload Job 13008 ERROR          BnePLSQLUpload.doUpload: Stack trace: java.sql.SQLException: ORA-20001: -Please enter CONTAINER_ID -  Enter PO_NO -

    ORA-06512: at "APPS.XXPO_COSTFACTS_WEBADI_PKG", line 264

    ORA-06512: at line 1

     

     

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)

      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)

      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)

      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)

      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)

      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)

      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)

      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)

      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)

      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)

      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)

      at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)

      at oracle.apps.bne.integrator.upload.BnePLSQLUpload.doUpload(BnePLSQLUpload.java:284)

      at oracle.apps.bne.integrator.upload.BneSAXUploader.processDeepestLevel(BneSAXUploader.java:2346)

      at oracle.apps.bne.integrator.upload.BneSAXUploader.startElement(BneSAXUploader.java:1182)

      at oracle.xml.parser.v2.XMLContentHandler.startElement(XMLContentHandler.java:181)

      at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1288)

      at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:336)

      at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:303)

      at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:234)

      at oracle.apps.bne.integrator.upload.BneUploader.processUpload(BneUploader.java:301)

      at oracle.apps.bne.integrator.upload.BneAbstractUploader.processUpload(BneAbstractUploader.java:114)

      at oracle.apps.bne.integrator.upload.async.BneAsyncUploadThread.run(BneAsyncUploadThread.java:140)

     

     

    12/10/13 2:52 PM AJPRequestHandler-HTTPThreadGroup-5 WARNING        BneOracleWebAppsContext.getTimeZone CLIENT_TIMEZONE_ID has not been set

    12/10/13 2:52 PM AJPRequestHandler-HTTPThreadGroup-5 ERROR          BneOracleWebAppsContext.getExtraJDBCConnection recieved the same connection as the base connection.  There may be transaction problems.

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

    How to show the same error in the excel template?

     

     

     

    Here is the package:

     

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    CREATE OR REPLACE PACKAGE BODY APPS.XXPO_COSTFACTS_WEBADI_PKG

    AS

       PROCEDURE upload_data (

                              P_CONTAINER_ID IN VARCHAR2

                            , P_SAIL_DATE IN DATE

                            , P_PO_NO IN VARCHAR2                     

                             ) IS

        --declare

        lv_err_msg      VARCHAR2(240);

        lf_err_flag     NUMBER := 0;

        ln_temp         NUMBER;

                                    

        BEGIN

       

        --------------------- checking for mandatory parameters---------------------------

          IF (P_CONTAINER_ID IS NULL) THEN

             lf_err_flag := 1;

             lv_err_msg := lv_err_msg||'-'||'Please enter CONTAINER_ID - ';

           

          END IF;

         

         

          -------------Validation for Sail Date Format----------------------

          IF (P_SAIL_DATE IS NULL) THEN

             lf_err_flag := 1;

             lv_err_msg := lv_err_msg || ' ' || 'Enter Sail Date - ';

          ELSE

             BEGIN

                SELECT 1

                  INTO ln_temp

                  FROM DUAL

                 WHERE P_SAIL_DATE =  TO_DATE (TO_CHAR (P_SAIL_DATE, 'DD-MON-YYYY'), 'DD-MM-YYYY');

             EXCEPTION

                WHEN NO_DATA_FOUND THEN

                   lf_err_flag := 1;

                   lv_err_msg := lv_err_msg || ' Enter Sail date in DD-MON-YYYY Format';

                  

                WHEN OTHERS THEN

                   lf_err_flag := 1;

                   lv_err_msg := lv_err_msg || ' Enter Sail date in DD-MON-YYYY Format'|| SQLERRM;

                  

             END;

          END IF;

               

          -------------Validation for PO_Number----------------------

          IF (P_PO_NO IS NULL) THEN

             lf_err_flag := 1;

             lv_err_msg := lv_err_msg || ' ' || 'Enter PO_NO - ';

          ELSE

             BEGIN

                SELECT count(1)

                  INTO ln_temp

                  FROM PO_HEADERS

                 WHERE Attribute4 =  P_PO_NO;

             EXCEPTION

                WHEN NO_DATA_FOUND THEN

                   lf_err_flag := 1;

                   lv_err_msg := lv_err_msg || ' No Oracle PO for Biceps PO#'||P_PO_NO;

                  

                WHEN OTHERS THEN

                   lf_err_flag := 1;

                   lv_err_msg := lv_err_msg || ' Error getting the Oracle PO for Bicpes PO#'||P_PO_NO||' Error-' || SQLERRM;

                  

             END;

          END IF;

         

         -----------------------Insert Record----------------------------

         IF lv_err_msg is NULL THEN

         BEGIN

             INSERT

              INTO XXP2P_HW_COST_FACTORS_STG

                (

                     CONTAINER_ID

                    ,SAIL_DATE

                    ,PO_NO

                    , ERROR_FLAG

                    , ERROR_MSG

                   ,CREATED_BY

                    ,CREATION_DATE

                    ,LAST_UPDATED_BY

                    ,LAST_UPDATE_DATE

                    ,LAST_UPDATE_LOGIN              

                )

                VALUES

                (

                     P_CONTAINER_ID

                    ,P_SAIL_DATE

                    ,P_PO_NO              

                    ,lf_err_flag

                    ,lv_err_msg

                  ,FND_GLOBAL.USER_ID

                    , trunc (sysdate)

                    ,FND_GLOBAL.USER_ID

                    , trunc (sysdate)

                    ,FND_GLOBAL.LOGIN_ID              

                );

                       

                  --  commit;

                  DBMS_OUTPUT.put_line

                  (

                    '-' || 'After ap_invoices_interface'

                  );

            EXCEPTION

            WHEN OTHERS THEN

              ROLLBACK;

              lf_err_flag := 1;

              lv_err_msg  := lv_err_msg || ' ' || 'error loading CONTAINER_ID-' || P_CONTAINER_ID || SQLERRM;

              raise_application_error(-20001, lv_err_msg);

            END;

        ELSE

              raise_application_error(-20001, lv_err_msg);

        END IF;

       

                   

      END upload_data;                        

     

     

    END XXPO_COSTFACTS_WEBADI_PKG;

    /

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  • 3. Re: How to show custom error message in WebADI Excel template?
    ens005 Newbie
    Currently Being Moderated

    I changed the API Returns to 'FND MEssage Code' instead of 'Error Message' on the web adi interface.

     

    Changed the pl/sql code to:

       --raise_application_error(-20001, lv_err_msg);       

            fnd_message.set_name('XXCMN', 'XXXMN_ERROR');

            fnd_message.set_token('ERROR_MESSAGE', lv_err_msg);

            l_mesg := fnd_message.get();

            --fnd_message.raise_error;         

            raise_application_error(-20001, l_mesg);

     

    Cleared Web ADI cache using BneAdminServlet.

    (How to access BneAdminServlet:

    If Oracle EBiz url :- https://appsinstance.com/ then paste the following on the browser url

    https://appsinstance.com/OA_HTML/BneAdminServlet and press enter.)

     

    Now custom error messages are displayed on the excel template for invalid rows.

  • 4. Re: How to show custom error message in WebADI Excel template?
    1274416 Newbie
    Currently Being Moderated

    HI,

    I am facing same issue and getting error

    • An error has occurred while running an API import. The ERRORED_ROWS step 20003:ER_500004, parameter number 1 must contain the value BIND in attribute 1.

    how you defined importer to send result back to xls?

  • 5. Re: How to show custom error message in WebADI Excel template?
    9f3d1e56-3993-4695-8264-7f21563a093d Newbie
    Currently Being Moderated

    Hi Narasimha,

     

    Set your WebADI Interface API Return to null and use the same raise_application_error(-20001, "Actual Error Message") in your procedure.

    Clear cache from functional administrator and bounce apache server.


    Execute WebADI template again.

     

    Cause : Sometimes when you change/alter package body and execute WebADI after initial run, WebADI framework it throws these kind of error but this does not mean that package.procedure is actually throwing exception unless it is invalid in database.

     

    Solution : In this case just clear cache and bounce apache server as WebADI framework stores package defination/body in cache which needs to be refreshed each time after altering package definition/body.

     

    Thanks,

    Niladri Biswas.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points