4 Replies Latest reply: May 25, 2013 1:00 PM by 1010599 RSS

    Error in CO Extension: oracle.jbo.SQLStmtException: JBO-27122: SQL error du

    1010599
      I am trying to create CO Extension for Supplier Page with additional field. But I am facing following error. While I could the see the query is fine. Please check the below log and help asap. As it is very Urgent to fix.


      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select hp.party_name,
      hp.party_id as party_id,
      hp.duns_number_c as duns,
      pv.vendor_id,
      pv.vendor_name,
      decode(pv.organization_type_lookup_code,
      'INDIVIDUAL',pv.individual_1099,
      'FOREIGN INDIVIDUAL',pv.individual_1099, pv.num_1099) taxpayer_id,
      pv.vat_registration_num as tax_reg_num,
      pv.segment1,
      pv.VENDOR_NAME_ALT as alternate_name,
      pv.end_date_active as end_date_active,
      pv.start_date_active as start_date_active,
      pv.ONE_TIME_FLAG,
      pv.VENDOR_TYPE_LOOKUP_CODE,
      pv.PARENT_VENDOR_ID,
      parent.vendor_name as parent_vendor_name,
      pv.PAYMENT_PRIORITY,
      parent.segment1 as parent_Segment1,
      pv.TAX_REPORTING_NAME,
      pv.terms_id,
      terms.name as terms_desc,
      pv.FEDERAL_REPORTABLE_FLAG,
      pv.STATE_REPORTABLE_FLAG,
      pv.PAY_GROUP_LOOKUP_CODE,
      pay_group.description as pay_group_desc,
      aptt.description as income_tax_type,
      hp.organization_name_phonetic,
      plc.DISPLAYED_FIELD as VENDOR_TYPE_DISPLAY,
      pv.type_1099 as income_tax_type_code,
      pv.employee_id as employee_id,
      pecx.employee_num AS employee_number
      from hz_parties hp,
      ap_suppliers pv,
      ap_suppliers parent,
      ap_terms_tl terms,
      fnd_lookup_values pay_group,
      AP_INCOME_TAX_TYPES aptt,
      po_lookup_codes plc,
      per_employees_current_x pecx
      where pv.party_id = hp.party_id AND
      pv.party_id = pecx.party_id (+) and
      parent.vendor_id (+) = pv.parent_vendor_id and
      pv.terms_id = terms.term_id (+)
      and terms.language (+) = userenv('LANG') and
      terms.enabled_flag (+) ='Y' and
      pv.pay_group_lookup_code = pay_group.lookup_code (+) and
      pay_group.lookup_type (+)='PAY GROUP' and
      pay_group.language (+)=userenv('lang') and
      pv.type_1099 = aptt.income_tax_type (+) and
      pv.VENDOR_TYPE_LOOKUP_CODE = plc.LOOKUP_CODE (+) and
      plc.lookup_type (+) = 'VENDOR TYPE') QRSLT WHERE (PAY_GROUP_LOOKUP_CODE like '> 2 MILLION%') ORDER BY VENDOR_NAME ASC
           at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
           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:2972)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1852)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
           at OA.jspService(_OA.java:212)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)
           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:642)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
           at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:908)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)
           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 oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
           at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
           at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
           at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
           at java.lang.Thread.run(Thread.java:662)
      ## Detail 0 ##
      java.sql.SQLException: Invalid column index
           at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5168)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8098)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8034)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8767)
           at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8748)
           at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParamValue(BaseSQLBuilderImpl.java:1847)
           at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3893)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)
           at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:13827)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:804)
           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:4560)
           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 COTest5.oracle.apps.pos.supplier.webui.XXNWSSuppSummCO.processFormRequest(XXNWSSuppSummCO.java:37)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:819)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1214)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1031)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:997)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:852)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1031)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:997)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:852)
           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:2968)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1852)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
           at OA.jspService(_OA.java:212)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)
           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:642)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
           at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:908)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)
           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 oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
           at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
           at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
           at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
           at java.lang.Thread.run(Thread.java:662)
      java.sql.SQLException: Invalid column index
           at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5168)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8098)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8034)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8767)
           at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8748)
           at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParamValue(BaseSQLBuilderImpl.java:1847)
           at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3893)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)
           at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:13827)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:804)
           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:4560)
           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 COTest5.oracle.apps.pos.supplier.webui.XXNWSSuppSummCO.processFormRequest(XXNWSSuppSummCO.java:37)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:819)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1214)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1031)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:997)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:852)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:382)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1031)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:997)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:852)
           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:2968)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1852)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
           at OA.jspService(_OA.java:212)
           at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
           at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)
           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:642)
           at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
           at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:908)
           at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)
           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 oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
           at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
           at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
           at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
           at java.lang.Thread.run(Thread.java:662)
        • 1. Re: Error in CO Extension: oracle.jbo.SQLStmtException: JBO-27122: SQL error du
          keerthioaf
          Hey hi there ,

          Since you are adding an additional field in the page , you can go for VO substitution to capture the value, not CO extension .

          Tell us what is your exact requirement ,we will try to help you out .

          --Keerthi                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
          • 2. Re: Error in CO Extension: oracle.jbo.SQLStmtException: JBO-27122: SQL error du
            1010599
            The additional value which I am adding .. is already present in the standard VO.but not mapped to the search Page fields. So, I preferred CO, just to write the condition. Please find the below code which I have added and kindly hel[p me out.

            package COTest5.oracle.apps.pos.supplier.webui;

            import oracle.apps.fnd.framework.OAApplicationModule;
            import oracle.apps.fnd.framework.OAViewObject;
            import oracle.apps.fnd.framework.webui.OAPageContext;
            import oracle.apps.fnd.framework.webui.beans.OAWebBean;
            import oracle.apps.pos.supplier.webui.SuppSummCO;

            public class XXNWSSuppSummCO extends SuppSummCO
            {

              public XXNWSSuppSummCO()
              { }
            public void processFormRequest(OAPageContext paramOAPageContext, OAWebBean paramOAWebBean)
            {
            //to add in Log file
            String path="COTest4.oracle.apps.pos.supplier.webui.XXNWSSuppSummCO";
            paramOAPageContext.writeDiagnostics(path,"In Extension XXNWSSuppSummCO",-100);

            super.processFormRequest(paramOAPageContext,paramOAWebBean);
            OAApplicationModule am = paramOAPageContext.getApplicationModule(paramOAWebBean);
            OAViewObject VO=(OAViewObject)am.findViewObject("SuppSummVO");
            paramOAPageContext.writeDiagnostics(path,"paygroup value is mapping to string",-101);
            String str4 = paramOAPageContext.getParameter("SearchPayGroup");

            StringBuffer localStringBuffer = new StringBuffer();

            if (str4 != null)
            {
            paramOAPageContext.writeDiagnostics(path,"paygroup value is inserted in search region",-102);
            localStringBuffer.append("PAY_GROUP_LOOKUP_CODE like '" safe(str4.trim()) "%'");
            }

            paramOAPageContext.writeDiagnostics(path,"Mapping to where condition of View object SuppSummVO",-103);
            //VO.setWhereClauseParam(localStringBuffer.toString());
            VO.setWhereClause( localStringBuffer.toString());
            VO.executeQuery();
            paramOAPageContext.writeDiagnostics(path,"Where Condition is passed successfully",-104);
            }

            }
            • 3. Re: Error in CO Extension: oracle.jbo.SQLStmtException: JBO-27122: SQL error du
              Sandeep M.
              You can try re-checking the VO attribute mappings.

              And you can use this before setting the whereclause VO.setWhereClause(null);

              Also you can confirm the issue is because of controller extension by just removing your code for setting whereclause.
              • 4. Re: Error in CO Extension: oracle.jbo.SQLStmtException: JBO-27122: SQL error du
                1010599
                Thank you Sandeep.

                The issue was with Mapping the attributes. It got resolved.