how to add where clause in LOV..?

1. Re: how to add where clause in LOV..?

Braj Pratap Feb 9, 2011 10:33 AM (in response to 784264)

Hi

Please share complete error stack .

thanks
Pratap

2. Re: how to add where clause in LOV..?

784264 Feb 9, 2011 10:39 AM (in response to Braj Pratap)

hi Braj
thanx for reply..

this is the error message

Logout

Error Page
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT organization_id,name
FROM hr_organization_units
where COST_ALLOCATION_KEYFLEX_ID in
(SELECT pcak.COST_ALLOCATION_KEYFLEX_ID FROM pay_cost_allocation_keyflex pcak WHERE pcak.segment2 like :1)
     at oracle.apps.fnd.framework.OAException.wrapperException(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at OA.jspService(_OA.java:71)
     at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
     at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
     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.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
     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.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
     at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
     at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
     at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
     at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
     at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
     at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
     at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
     at java.lang.Thread.run(Thread.java:595)
## Detail 0 ##
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
     at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1566)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2996)
     at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3043)
     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(Unknown Source)
     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(Unknown Source)
     at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at OA.jspService(_OA.java:71)
     at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
     at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
     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.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
     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.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
     at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
     at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
     at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
     at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
     at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
     at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
     at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
     at java.lang.Thread.run(Thread.java:595)
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
     at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1566)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2996)
     at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3043)
     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(Unknown Source)
     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(Unknown Source)
     at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
     at OA.jspService(_OA.java:71)
     at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
     at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
     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.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
     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.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
     at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
     at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
     at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
     at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
     at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
     at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
     at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
     at java.lang.Thread.run(Thread.java:595)

3. Re: how to add where clause in LOV..?

Gyan Darpan Feb 9, 2011 10:41 AM (in response to 784264)

Hi,

For this you need to create a Controller (CO) for that LOV.

And in this CO And in ther PR, you need to add the dynamic where clause.

Regards,
Gyan

4. Re: how to add where clause in LOV..?

784264 Feb 9, 2011 10:43 AM (in response to 784264)

hi

This is my LOV EVENT Code for your reference..

if(lovBean1 != null)
{
if("lovEvent".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))||"lovPrepare".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM) )
{

System.out.println("i m in LovEvent RCDES ");

String org11= pageContext.getParameter("RcDesc");

OAViewObjectImpl vo= (OAViewObjectImpl)am.findViewObject("MASOrganizationLOVVO1");

if(vo!=null)
{
vo.setWhereClause(null);
vo.setWhereClauseParams(null);

//vo.setWhereClause("COST_ALLOCATION_KEYFLEX_ID LIKE "+"'"+rc+"'");
vo.setWhereClauseParam(0,rc);
//vo.executeQuery();

}}

5. Re: how to add where clause in LOV..?

784264 Feb 9, 2011 10:44 AM (in response to Gyan Darpan)

HI Gyan,

Thanx for reply..

Its Custom page.

i write It in in PFR of CO

6. Re: how to add where clause in LOV..?

Gyan Darpan Feb 9, 2011 11:05 AM (in response to 784264)

Hi,

Step 1 - You need to attach a CO for your LOV.
Step 2 - In the PR of this CO you need to set where clause.

Hope you know how to add controller to LOV. IF not the let me know.

Regards,
Gyan

Edited by: Gyan on Feb 9, 2011 4:33 PM

7. Re: how to add where clause in LOV..?

784264 Feb 9, 2011 10:50 AM (in response to Gyan Darpan)

hi Gyan

this is my mail id:

khushal.kul511@gmail.com

8. Re: how to add where clause in LOV..?

Braj Pratap Feb 9, 2011 11:42 AM (in response to 784264)

hi

use

String where_clause="COST_ALLOCATION_KEYFLEX_ID LIKE ' %:1%' " ;
vo.setWhereClause( where_clause);
vo.setWhereClauseParam(0,rc);

thanks
Pratap

9. Re: how to add where clause in LOV..?

784264 Feb 9, 2011 11:21 AM (in response to Braj Pratap)

hi Braj,

i tried what u suggest earlier But i got COST_ALLOCATION_KEYFLEX_ID is invalid identifier ..

This is my LOVVO..

SELECT organization_id,name
FROM hr_organization_units hou
WHERE hou.COST_ALLOCATION_KEYFLEX_ID in(SELECT pcak.COST_ALLOCATION_KEYFLEX_ID FROM pay_cost_allocation_keyflex pcak WHERE pcak.segment2 like :1);

Please prepare whereclause for me..
i did but i gor error.

Thanx

10. Re: how to add where clause in LOV..?

Braj Pratap Feb 9, 2011 11:50 AM (in response to 784264)

Hi

if your VO is like this exactly then u r doing in wrong way ,firt you need to modift the query like this

SELECT organization_id,name, hou.COST_ALLOCATION_KEYFLEX_ID as COST_ALLOCATION_KEYFLEX_ID
FROM hr_organization_units hou
WHERE hou.COST_ALLOCATION_KEYFLEX_ID in(SELECT pcak.COST_ALLOCATION_KEYFLEX_ID FROM pay_cost_allocation_keyflex pcak WHERE pcak.segment2 like :1);

then set the where clause exactly which i mentioned in previous post .Actually when ever we put the dyanmic where clause ,it always get set on result columns ,and the query that u were using was giving only organization_id in result columns so it was throwing invalid identifier.hope it will help.

thanks
Pratap