OracleArea51

Sponsored By Chola Global Services LLC

Advance Search Page without using Query Bean

Friday, 17 July 2009 03:47
E-mail
(1 vote, average 3.00 out of 5)

Oracle has provided Query bean to implement search pages, but we are having little control over that. If we want to develop custom search pages that we can not develop using Query bean then we have to implement it without using Query bean.

In this article I will discuss how to implement Advance Search Page manually, I did one project where the client wasn't requiring add button to add columns in advance search region so I impelemented the advance search page manually.

To implement Advance search page, we need followings:
1. Poplists to show conditions
2. One Radio group to select AND/OR option.

Poplists which displays conditions varies with datatype. The page I developed on that I have taken three Fields one character,one number and one datetype but date field I implemented as Poplist which will be showing the values like Today,last 90 days etc..

Conditions are defined as lookup and lookup type name is 'ICX_CONDITIONS' So to display conditions for Character field I used one VO which is having following query:

SELECT lookup_code,meaning
FROM   fnd_lookups
WHERE  lookup_type = 'ICX_CONDITIONS'  
AND    meaning IN ('is','is not','contains','starts with', 'ends with')

For Number field I used following Query:

SELECT lookup_code,meaning
FROM   fnd_lookups
WHERE  lookup_type = 'ICX_CONDITIONS'  
AND    meaning IN ('is','is not','greater than','less than')

To Handle Date field I used following Query:

select 'Last 30 days' as display, 'sysdate-30' as value from dual
union all
select 'Last 60 days' as display, 'sysdate-60' as value from dual
union all
select 'Last 90 days' as display, 'sysdate-90' as value from dual
union all
select 'Today' as display, 'sysdate' as value from dual

Now second thing is to Display AND/OR options to user for this I created one Radio group and attached with VO, Query for VO is:

select 'Search results where each must contain all values entered.' as Display, 'AND' as meaning from dual
union all
select 'Search results where each may contain any value entered.' as display, 'OR' as Meaning from dual

After this I will design the page, while designing the page I have given the names to items like condition_0.....condition_n(Name of the Items which are used to display conditions), the Items which are used to capture values having name like value_0....value_n.
It means first condition poplist name will be condition_0 second one will be having name condition_1, similarly first value item will have name like value_0, second will have name value_1.

Now It comes to build the Query at runtime, in where clause any clause consists of Column_name,condition and value like supplier_id = 1
again this clause depends on datatype of column like name = 'RS', character values will be enclosed in single quotes and numbers will not be. So for building my where clause I need column_name, datatype, condition selected by user and corresponding value entered for search.

To get these clauses to be added to where clause I used one method, code for this method is:
 String getCondition(String Condition, String value, String datatype, String ColumnName)
 {
 String s = null;
 if(Condition.equals("AIS")) 
 if(datatype.equals("char"))
 s="= "+"'"+value+"'";
 else
 s="= "+value;
 else if (Condition.equals("BNOT")) 
 if(datatype.equals("char"))
 s=" "+"'"+value+"'";
 else
 s=" "+value;
 else if (Condition.equals("CCONTAIN"))
 s=" like "+"'%"+value+"%'";
 else if (Condition.equals("DSTART"))
 s=" like "+"'"+value+"%'";
 else if (Condition.equals("EEND"))
 s=" like "+"'%"+value+"'";
 else if (Condition.equals("FGREATER"))
 s="> "+value;
 else if (Condition.equals("GLESS"))
 s="< "+value;
 else{}
return ColumnName+" "+s;
 }

Now in the last to build the where clause I should get the what user has selected to connet the clauses AND/OR, this I will get from radio button group. I will make the where clause and attach with SuppliersVO, on which I need to perform search and I will execute query, the code for this is:

 public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
 {
 super.processFormRequest(pageContext, webBean);
 OAApplicationModule am = pageContext.getApplicationModule(webBean);
 if(pageContext.getParameter("Go")!= null)
 {
 String[][] Columns = {{"Name","Supplier_Id"},
 {"char","Number"}};
 String Flag = pageContext.getParameter("AndOrOption"); 
 String WhereClause = null;
 for(int i=0;i<2;i++)
 {
 String condition = pageContext.getParameter("conditions_"+i);
 String value = pageContext.getParameter("value_"+i);
System.out.println("Cond: "+condition);
 System.out.println("val: "+value);
 if(value !=null && !(value.trim().equals("")) )
 {
 if(WhereClause == null)
 WhereClause = getCondition(condition,value,Columns[1][i],Columns[0][i]);
 else
 WhereClause = WhereClause+" "+Flag+" "+getCondition(condition,value,Columns[1][i],Columns[0][i]);
 }
 }
 if(pageContext.getParameter("dateMC")!=null && !("".equals(pageContext.getParameter("dateMC").trim())))
 {
 if(WhereClause == null)
 WhereClause = "Start_date >="+pageContext.getParameter("dateMC");
 else
 WhereClause = WhereClause+" "+Flag+" "+"Start_date "+pageContext.getParameter("dateMC");
 }
 Serializable[] parameters =  {WhereClause}; 
 System.out.println(">>>> "+WhereClause);
 am.invokeMethod("query",parameters);     
 }
 }

In the above method I used one dimensional array it is to get Column_name and corresponding dtattype, and after that as I have given name like condition_0 and value_0 and so on, now it is easy for me to iterate through all the items on the search page.

As the date field is not having anycondition attached to it, I have not followed any convension to name it(like condition_n or value_n).

Hope it will help, If any thing is not clear please feel free to contact me.

 

Reetesh
Points: 2031
Contact Author | Website :
 
Attachments:
Download this file (search.zip)Advance Search Code[ ]12 Kb23 Downloads

Related Posts:

relatedArticles
Trackback(0)
Comments (0)add comment
feedSubscribe to this comment's feed

Write comment

busy