This discussion is archived
6 Replies Latest reply: Nov 15, 2012 9:08 AM by asmirnov RSS

how to clear query region result set

asmirnov Newbie
Currently Being Moderated
Hi,
Is there way to clear the query region result set without engaging the query region "Go" button? For example, when some submit button on the page is engaged?
I tried to clear the VO which supplies data on the query region by setting the "where" clause on that VO but this doesn't seem to take any effect on query region displayed data, though the VO is empty, the query region page table still shows the old data.

Thank you

Anatoliy
  • 1. Re: how to clear query region result set
    keerthioaf Expert
    Currently Being Moderated
    Hi Anatoliy ,

    You get the ID of that submit button causing this issue and pass the NULL value to the query associated to the
    table region , this will result in returning no rows . Write a separate method and invoke it on click on the submit
    method .

    Let me know if its not clear ,

    Keerthi
  • 2. Re: how to clear query region result set
    asmirnov Newbie
    Currently Being Moderated
    Keerthi ,

    Thank you much for looking.
    I have a query region, view object associated with query region is called CheckOwnerVO1.
    I did aout the same - in the controller PFR I do

    if (pageContext.getParameter("CancelBtn") != null)
    {
    OAApplicationModule am = pageContext.getApplicationModule(webBean);
    am.invokeMethod("clearCheckOwnerVO");
    ....

    In the module I have

    public void clearCheckOwnerVO()
    {
    CheckOwnerVOImpl checkOwnerVO = getCheckOwnerVO1();
    if ( checkOwnerVO != null )
    checkOwnerVO.clearVO();
    }

    and in the VO

    public void clearVO()
    {
    StringBuffer whereClause = new StringBuffer(100);
    setWhereClauseParams(null);
    whereClause.append(" 1 = 2");
    setWhereClause(whereClause.toString());
    executeQuery();
    }

    But the view stays populated on the page from the previous engage of the Go button on the query region.
    What would you change?

    Thank you
    Anatoliy
  • 3. Re: how to clear query region result set
    keerthioaf Expert
    Currently Being Moderated
    Hi Your code seems to be correct , just make some small changes as below

    Make changes in clearCheckOwnerVO() method

    public void clearCheckOwnerVO()
    {
    OAViewObject Vo =(OAViewObject)getsdaPacelineWorkupHeadeValVO1();
    if(Vo.isPreparedForExecution());
    {
    if(!Vo.isPreparedForExecution());
    {


    Vo.setWhereClause(null);
    Vo.setWhereClause("PACELINE_HEADER_ID=:1"); // (eg :PACELINE_HEADER_ID )any primary key column which is not null
    Vo.setWhereClauseParams(null);
    Vo.setWhereClauseParam(0,null);
    Vo.executeQuery();
    }
    }

    Its working for me , Let me know if its not clear .


    Keerthi

    Edited by: keerthioaf on Nov 5, 2012 5:32 AM
  • 4. Re: how to clear query region result set
    asmirnov Newbie
    Currently Being Moderated
    Keerthi,

    If I do

    checkOwnerVO.setWhereClauseParam(0,null);

    like in your example I am getting below error

    Exception Details.
    oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.
    Statement: SELECT * FROM (SELECT 'Y' SelectFlag,
    pere.full_name EmployeeName,
    pafe.assignment_number EmployeeNumber,
    jbt.name Jobtitle,
    pers.full_name SupervisorName,
    pafs.assignment_number SupervisorNumber,
    hou.name Department,
    pere.person_id PersonId
    FROM PER_JOBS_TL jbt
    ,PER_PEOPLE_F pere
    ,PER_PEOPLE_F pers
    ,HR_ORGANIZATION_UNITS hou
    ,PER_ASSIGNMENTS_F pafe
    ,PER_ASSIGNMENTS_F pafs
    ,tvap.TVAP_OIE_CARD_MANAGE_HIST cchist
    ,AP_CARD_PROGRAMS_ALL cp
    WHERE pafe.job_id = jbt.job_id(+)
    ...
    AND (cchist.record_status = 'ACTIVE' OR cchist.record_status = 'APPROVED' OR cchist.record_status = 'PENDING')
    ORDER BY 2) QRSLT WHERE (PERSONID = :1 AND ( UPPER(EmployeeName) like UPPER(:1) AND (EmployeeName like :2 OR EmployeeName like :3 OR EmployeeName like :4 OR EmployeeName like :5))) ORDER BY EmployeeName ASC
    ...     at TVA.oracle.apps.tvap.ccw.webui.CheckOwnerSearchCO.processFormRequest(CheckOwnerSearchCO.java:83)
         at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
    ...     at java.lang.Thread.run(Thread.java:595)
    ## Detail 0 ##
    java.sql.SQLException: Missing IN or OUT parameter at index:: 6


    So I made it 5 instead of 0 to not to cause the error:

    checkOwnerVO.setWhereClause(null);
    checkOwnerVO.setWhereClause("PERSONID = :1"); // (eg :PACELINE_HEADER_ID )any primary key column which is not null
    checkOwnerVO.setWhereClauseParams(null);
    checkOwnerVO.setWhereClauseParam(5,null);
    checkOwnerVO.executeQuery();

    If I do it like this, the VO still stays populated on the query region.

    Let me please just confirm with you - we are talking about the query region table, correct? I mean the table bean that renderes the results of the query, correct?

    Thank you again

    Anatoliy
  • 5. Re: how to clear query region result set
    keerthioaf Expert
    Currently Being Moderated
    Looks like the older query is also getting executed , make sure you do not call the method which
    you are executing which returns rows when you click on submit button .



    Keerthi
  • 6. Re: how to clear query region result set
    asmirnov Newbie
    Currently Being Moderated
    This is what I had to do

    OAQueryBean queryBean = (OAQueryBean)webBean.findChildRecursive("QueryRN");
    queryBean.clearSearchPersistenceCache(pageContext);

    See

    https://blogs.oracle.com/prajkumar/entry/clear_reset_search_result_table

    Thank you

    Anatoliy

Legend

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