6 Replies Latest reply: Nov 15, 2012 11:08 AM by Asmirnov-Oracle RSS

    how to clear query region result set

    Asmirnov-Oracle
      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
          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-Oracle
            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
              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-Oracle
                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
                  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-Oracle
                    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