This discussion is archived
4 Replies Latest reply: May 25, 2013 11:00 AM by 1010599 RSS

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

1010599 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    SandeepM. Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you Sandeep.

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

Legend

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