4 Replies Latest reply: Jan 28, 2013 11:21 PM by user633859 RSS

    How to bind the variables to LOV which is in Advanced table

    user633859
      Hi


      I have a requirement like this, There is advanced table with one column as LOV type. When I add a row to the table, i click on the LOV icon. When i search for the lov values in the lov search screen to add, it ended up with the below error. I am executing the LOV VO both in processrequest and processrequest form.


      CLItemDtlsPVOImpl vo = (CLItemDtlsPVOImpl)contAm.getCLItemDtlsPVO1();
      vo.setWhereClause(null);
      vo.setWhereClauseParams(null);
      vo.setWhereClauseParam(0,invItemId);
      vo.setWhereClauseParam(1,chr_id);
      System.out.println("query after in PR: "+vo.getQuery());
      vo.executeQuery();



      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select a.inventory_item_id,a.segment1,a.description
      ,b.OPERAND unit_price
      from mtl_system_items_b a,
      qp_list_lines_v b,
      (select msi.inventory_item_id
      from Bom_structures_b bbm
      , Bom_components_b bic
      , mtl_system_items msi
      where bbm.bill_Sequence_id = bic.bill_sequence_id
      and msi.inventory_item_id = bic.component_item_id
      and msi.organization_id = bbm.organization_id
      and ( msi.description like '%Inplan%' or msi.description like '%Overage%')
      and bbm.organization_id = 1406
      start with bbm.assembly_item_id = :1
      connect by prior bic.component_item_id = bbm.assembly_item_id
      minus
      select INVENTORY_ITEM_ID from CL_ADD_PROD_CONT
      where chr_id = :2 ) c
      where a.inventory_item_id = c.inventory_item_id
      and to_char(a.inventory_item_id) = b.PRODUCT_ATTR_VALUE
      and b.list_header_id =7007) QRSLT WHERE (( UPPER(SEGMENT1) like :1 AND (SEGMENT1 like :2 OR SEGMENT1 like :3 OR SEGMENT1 like :4 OR SEGMENT1 like :5)))
           at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2680)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1683)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:509)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:430)
           at oa_html._OA._jspService(_OA.java:84)
           at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
           at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
           at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
           at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
           at oracle.jsp.JspServlet.service(JspServlet.java:156)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
           at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
           at org.apache.jserv.JServConnection.run(JServConnection.java:294)
           at java.lang.Thread.run(Thread.java:619)
      ## Detail 0 ##
      java.sql.SQLException: ORA-01008: not all variables bound

           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:590)
           at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
           at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
           at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2566)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:631)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:518)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3375)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:828)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4507)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:574)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:544)
           at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java:619)
           at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java:3339)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3326)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:716)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2314)
           at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(OAListOfValuesHelper.java:1493)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:842)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(OAListOfValuesHelper.java:1399)
           at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(OAListOfValuesBean.java:423)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1022)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:988)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:843)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2676)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1683)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:509)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:430)
           at oa_html._OA._jspService(_OA.java:84)
           at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
           at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
           at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
           at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
           at oracle.jsp.JspServlet.service(JspServlet.java:156)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
           at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
           at org.apache.jserv.JServConnection.run(JServConnection.java:294)
           at java.lang.Thread.run(Thread.java:619)



      Please help some body on this.



      Thanks,
      Pal.