4 Replies Latest reply on Mar 22, 2007 8:21 AM by 546074

    Lov Crashes even b4 reaching to PR

    533926
      Hi all my view is based on the following query.

      Statement: SELECT FLVV.lookup_code
      ,FLVV.description , xxxpa_util.Get_rate(:1, FLVV.lookup_type ,FLVV.lookup_code,sysdate ) rate
      ,FLVV.meaning , null project_id , null startDate
      FROM fnd_lookup_values_vl FLVV
      ,fnd_lookup_values_dfv FLVD
      WHERE FLVV.row_id = FLVD.row_id
      AND FLVV.lookup_type = 'SERVICE TYPE' AND enabled_flag = 'Y'

      I have One Lov Region as XXPARN This Region has application module as XXPAAM.This Lov Region has one Table based on the above query.one controlor has been associated with this table .


      i have used this region in one of my page.This page has application mmodule as XXPGAM.

      When we type % in the messageLovInput field and presses TAB .the Lov crashes with the following exception.

      I am binding the variable in PR of the controlor but even before reaching to this point the lov crashes.

      Exception is :




      SELECT * FROM (SELECT * FROM (SELECT FLVV.lookup_code
           ,FLVV.description , xxpa_pa_util.Get_rate(:1, FLVV.lookup_type ,FLVV.lookup_code,sysdate ) rate
      ,FLVV.meaning , null project_id , null startDate
      FROM fnd_lookup_values_vl           FLVV
           ,fnd_lookup_values_dfv           FLVD
      WHERE FLVV.row_id                          = FLVD.row_id
      AND FLVV.lookup_type                = 'SERVICE TYPE' AND enabled_flag = 'Y')) QRSLT WHERE (( UPPER(MEANING) like :1 ))
           at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:888)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1064)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1294)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormData(OAPageBean.java:2308)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1502)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:463)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:384)
           at OA.jspService(OA.jsp:40)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
           at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
           at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
           at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
           at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
           at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
           at java.lang.Thread.run(Thread.java:534)
      ## Detail 0 ##
      java.sql.SQLException: ORA-01722: invalid number

           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
           at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
           at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:583)
           at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
           at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
           at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2548)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2933)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:650)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:578)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:627)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:515)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3289)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:1207)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4146)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:567)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:537)
           at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java:614)
           at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java:3253)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3240)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:411)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:665)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2185)
           at oracle.apps.fnd.framework.webui.OAMessageLovInputHelper.handleValidateEventPrivate(OAMessageLovInputHelper.java:1111)
           at oracle.apps.fnd.framework.webui.OAMessageLovInputHelper.handleValidateEvent(OAMessageLovInputHelper.java:2391)
           at oracle.apps.fnd.framework.webui.LovUtils.handleLovValidate(LovUtils.java:251)
           at oracle.apps.fnd.framework.webui.LovUtils.handleLovEventInPFD(LovUtils.java:170)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormData(OAPageBean.java:2284)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1502)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:463)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:384)



      any clues??
      Naveen
        • 1. Re: Lov Crashes even b4 reaching to PR
          518974
          Naveen,
          I think the problem is with
          xxpa_pa_util.Get_rate(:1, FLVV.lookup_type ,FLVV.lookup_code,sysdate ) 
          Run this query in sql plus
          SELECT FLVV.lookup_code 
          ,FLVV.description , xxxpa_util.Get_rate(:1, FLVV.lookup_type ,FLVV.lookup_code,sysdate ) rate
          ,FLVV.meaning , null project_id , null startDate
          FROM fnd_lookup_values_vl FLVV
          ,fnd_lookup_values_dfv FLVD
          WHERE FLVV.row_id = FLVD.row_id
          AND FLVV.lookup_type = 'SERVICE TYPE' AND enabled_flag = 'Y'
          and
          SELECT FLVV.lookup_code 
          ,FLVV.description ,FLVV.meaning , null project_id , null startDate
          FROM fnd_lookup_values_vl FLVV
          ,fnd_lookup_values_dfv FLVD
          WHERE FLVV.row_id = FLVD.row_id
          AND FLVV.lookup_type = 'SERVICE TYPE' AND enabled_flag = 'Y'
          • 2. Re: Lov Crashes even b4 reaching to PR
            533926
            Yes the error is because of that only..

            But how can i handle this kind of scenario??
            pls help

            Naveen
            • 3. Re: Lov Crashes even b4 reaching to PR
              Sumit Chandra Sharma
              I think, you need to bind the parameter in the Lov Query before executing it. As far as '%' is concerned check what is the behaviour of the function when this character is passed to it. That should give an idea as to how to proceed.

              Thanks
              • 4. Re: Lov Crashes even b4 reaching to PR
                546074
                Since you are comparing the bind parameter ':1' with 'meaning', try passing 'meaning' in place of ':1' in below function:
                xxpa_pa_util.Get_rate(:1, FLVV.lookup_type ,FLVV.lookup_code,SYSDATE )

                Thanks,
                Mitiksha