7 Replies Latest reply on Oct 14, 2010 5:02 AM by AnilSharma

    How to Replace the View Object with the New View Object

    user13079906
    user13079906 Oct 14, 2010 3:26 AM
      Hello---


      There is an existing LOV in the system to populate the client code.

      Now i need to modify the LOV to get the data from different table.

      How can i do this?

      The current LOV is picking the data from the View create on the Fnd_Lookups table.

      Actually i have dropped the view and created a new table with the same name of the view. But unable to get the data and getting error. So i hope i have to create a new view object and attach to the LOV.

      Please advice.


      Thanks
      Krishna
      • 63 Views
      • Tags:
        • 1. Re: How to Replace the View Object with the New View Object
          AnilSharma
          AnilSharma Oct 14, 2010 3:42 AM (in response to user13079906)
          But unable to get the data and getting error
          What is the error stack you are getting ...Post here..

          Thanks
          --Anil                                                                                                                                                                                                                                                   
          • 2. Re: How to Replace the View Object with the New View Object
            user13079906
            user13079906 Oct 14, 2010 3:51 AM (in response to AnilSharma)
            Thanks for you quick help Anil..

            The exisiting view name is : XXOTL_XX_XX_PORTFOLIOV_V ( this is a view on the fnd_lookup table).

            So i have dropped this view. and with the same name created the table same no of attibutes with the old view.

            And inserted test record. But when i am trying to access, i m getting the error in the LOV as

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

            Logout

            Error Page
            Exception Details.
            oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT PORTFOLIO_CODE PortfolioCode
            , PORTFOLIO_NAME PortfolioName
            , PORTFOLIO_DESCRIPTION PortfolioDescription
            , ATTRIBUTE1 Attribute1
            , ATTRIBUTE2 Attribute2
            , ATTRIBUTE3 Attribute3
            FROM XXOTL_XX_XX_PORTFOLIOV_V
                 at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:896)
                 at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)
                 at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)
                 at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2867)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1840)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:538)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:426)
                 at OA.jspService(_OA.java:212)
                 at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
                 at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
                 at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
                 at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
                 at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
                 at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
                 at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
                 at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
                 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
                 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
                 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
                 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
                 at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
                 at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
                 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
                 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
                 at java.lang.Thread.run(Thread.java:619)
            ## Detail 0 ##
            java.sql.SQLSyntaxErrorException: ORA-00904: "PORTFOLIO_DESCRIPTION": invalid identifier

                 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
                 at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
                 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
                 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
                 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
                 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
                 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
                 at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
                 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1189)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
                 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
                 at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
                 at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
                 at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3723)
                 at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4559)
                 at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:892)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:806)
                 at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:800)
                 at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3643)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:743)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2330)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(OAListOfValuesHelper.java:1584)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:847)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(OAListOfValuesHelper.java:1490)
                 at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(OAListOfValuesBean.java:423)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1027)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:993)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:848)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
                 at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
                 at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2863)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1840)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:538)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:426)
                 at OA.jspService(_OA.java:212)
                 at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
                 at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
                 at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
                 at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
                 at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
                 at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
                 at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
                 at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
                 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
                 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
                 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
                 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
                 at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
                 at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
                 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
                 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
                 at java.lang.Thread.run(Thread.java:619)
            java.sql.SQLSyntaxErrorException: ORA-00904: "PORTFOLIO_DESCRIPTION": invalid identifier

                 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
                 at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
                 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
                 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
                 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
                 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
                 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
                 at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
                 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1189)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
                 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
                 at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
                 at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
                 at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3723)
                 at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4559)
                 at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:892)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:806)
                 at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:800)
                 at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3643)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:743)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2330)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(OAListOfValuesHelper.java:1584)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:847)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(OAListOfValuesHelper.java:1490)
                 at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(OAListOfValuesBean.java:423)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1027)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:993)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:848)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
                 at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
                 at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2863)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1840)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:538)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:426)
                 at OA.jspService(_OA.java:212)
                 at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
                 at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
                 at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
                 at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
                 at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
                 at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
                 at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
                 at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
                 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
                 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
                 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
                 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
                 at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
                 at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
                 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
                 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
                 at java.lang.Thread.run(Thread.java:619)


            Logout

            Copyright (c) 2006, Oracle. All rights reserved.

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

            Thanks again for your help


            Krishna
            • 3. Re: How to Replace the View Object with the New View Object
              AnilSharma
              AnilSharma Oct 14, 2010 4:02 AM (in response to user13079906)
              Make sure PORTFOLIO_DESCRIPTION is there in your table with exactly the same name as mentioned here.


              Thanks
              --Anil                                                                                                                                                                                                                                               
              • 4. Re: How to Replace the View Object with the New View Object
                user13079906
                user13079906 Oct 14, 2010 4:04 AM (in response to user13079906)
                Hi Anil...

                I just figured the problem.. i have missed one column in the newly created table. Now i am able to get the record..

                Thank you so much.

                And i have another 2 doubuts


                1) In the same Page i have a deparment no which is getting populated from the LOV.

                And i need to get the records from the client table where the deparment equal to the selected departed from the lov or employee own department.

                So can you advice how to change the VO.


                2) In case if i need to created the new LOV ViewObject and replace the Old LOV View object
                what are the ways to achieve that. ( Becasue in so many cases the old VO (table or view) might be used in the system, so in those cases need to create a new table for the new view object)


                Thanks again
                Krishna
                • 5. Re: How to Replace the View Object with the New View Object
                  AnilSharma
                  AnilSharma Oct 14, 2010 4:34 AM (in response to user13079906)
                  And i need to get the records from the client table where the deparment equal to the selected departed from the lov or employee own >department.
                  You can bind VO query with this department.means at runtime you can set the where clause of VO .

                  In addition please read devguide for more information.

                  Thanks
                  --Anil                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                  • 6. Re: How to Replace the View Object with the New View Object
                    user13079906
                    user13079906 Oct 14, 2010 4:58 AM (in response to AnilSharma)
                    Thanks anil..

                    could you plz give me some web links which demonstrates ...

                    And do i need to change the VO query by extending it or only the code part change in the controller or some where?


                    Plz advice...
                    • 7. Re: How to Replace the View Object with the New View Object
                      AnilSharma
                      AnilSharma Oct 14, 2010 5:02 AM (in response to user13079906)
                      You can check the code in below link

                      http://oracleanil.blogspot.com/2010/09/controller-extension-r12-oaf.html

                      Here we are setting the where clause programmatically.

                      Thanks
                      --Anil