Search on Master-detail table in OAF

Search on Master-detail table in OAF

Hello friends,

Oracle application framework provides me another opportunity to work on and to get more acquainted with. So here I am eager to share another short article on OAF.  Just finished a project on OAF and came across various fact and findings about the framework.
You guys may have come across master – detail relationship in OAF and ‘how-to’ behind that as well.  I have recently read articles on how to insert a row into child table. Well this article is quite a different one.
While developing an application, there might be a requirement wherein we need to work on master detail relationship.  In my case, the requirement was to develop the search page having result table including master and detail table records.  The user may enter any search criteria that may fall under master table and/or detail table. Depending upon the search performed by the user, the master and detail table records must be populated.
For e.g. consider employee and department table.  We have to develop a search page which populates department details along with employee details based on search criteria.
(1) If user searches for department no 10 then the result table should display the department no 10 along with employees who works under the same.

Hide
Dept No
Department
Location
(-)
10
Accounting
New york

Empno
Name
Job
Mgr
Hiredate
 Salary
Commission
7934
Millar
Clerk
7782
23-Jan-82
 30000

7839
King
President

17-Nov-81
15000000


(2) If user searches for employee number 7839 then result table should display department in which employee 7839 works and that employee details only.

Hide
Dept No
Department
Location
(-)
10
Accounting
New york
   
Empno
Name
Job
Mgr
Hiredate
Salary
Commission
7839
King
President

17-Nov-81
15000000


To achieve such results in oaf in master detail search, we need to first execute master table VO so as to populate master and detail table records then we need to traverse to each record in detail table and ignore/ remove unwanted records.
Here is the java code to get results:

/* You may change the search criteria according to business requirement. */
public void showMasterDetail(OAPageContext pageContext, OAWebBean webBean, OAApplicationModule am) {
        // get a handle to inner table
        RowSet innerRowSet = null;
        OAAdvancedTableBean innerTable = (OAAdvancedTableBean)webBean.findChildRecursive("region12");
        int counter = 0;
        OARow row = null;
        int loginId = pageContext.getLoginId();
        String deptno = pageContext.getParameter("deptno");
        String dname = pageContext.getParameter("dname");
        String location = pageContext.getParameter("location");
        String empno = pageContext.getParameter("empno");
        String ename = pageContext.getParameter("ename");
        String job = pageContext.getParameter("job");
        String mgr = pageContext.getParameter("mgr");
        String hiredate = pageContext.getParameter("hiredate");
        String salary = pageContext.getParameter("salary");
        String HdrsearchQuery = null;
        Row lineRow = null;
        Row[] rowsInRange = {};
        OAViewObject  HdrsearchVo = (OAViewObject)am.findViewObject("xxDeptVO1") ;
        OAViewObject LinesearchVo = (OAViewObject)am.findViewObject("xxEmpVO1");
        pageContext.putSessionValue("retainAMValue","N");
//        processRequest(pageContext,webBean);
        if (!("N".equalsIgnoreCase(pageContext.getParameter("searchHdrLine")))) {
            if (deptno!= "" && deptno!= null) {
                if ("".equalsIgnoreCase(HdrsearchQuery)) {
                    HdrsearchQuery =
                            HdrsearchQuery + " DEPTNO = "+ deptno;
                } else {
                    HdrsearchQuery =
                            HdrsearchQuery + " AND DEPTNO = "+ deptno;
                }
            }
            if (dname!= "" && dname!= null) {
                if ("".equalsIgnoreCase(HdrsearchQuery)) {
                    HdrsearchQuery =
                            HdrsearchQuery + " UPPER(DNAME) LIKE UPPER('" + dname +  "')";
                } else {
                    HdrsearchQuery =
                            HdrsearchQuery + " AND UPPER(DNAME) LIKE UPPER('" + dname +  "')";
                }
            }

            if (location!= "" && location!= null) {
                if ("".equalsIgnoreCase(HdrsearchQuery)) {
                    HdrsearchQuery =
                            HdrsearchQuery + " UPPER(LOCATION) LIKE UPPER('" + location + "')";
                } else {
                    HdrsearchQuery =
                            HdrsearchQuery + " AND UPPER(LOCATION) LIKE UPPER('" + location + "')";                }
            }

            if (empno!= "" && empno!= null) {
                if ("".equalsIgnoreCase(LineSearchQuery)) {
                    LineSearchQuery =
                            LineSearchQuery + " EMPNO = "+empno;
                } else {
                    LineSearchQuery =
                            LineSearchQuery + " AND EMPNO = "+empno;
                }
            }

            if (ename!= "" && ename!= null) {
                if ("".equalsIgnoreCase(LineSearchQuery)) {
                    LineSearchQuery =
                            LineSearchQuery + " UPPER(ENAME) LIKE UPPER('" + ename + "')";
                } else {
                    LineSearchQuery =
                            LineSearchQuery + " AND UPPER(ENAME) LIKE UPPER('" + ename + "')";
                }
            }

            if (hiredate!= "" && hiredate!= null) {
                if ("".equalsIgnoreCase(LineSearchQuery)) {
                    LineSearchQuery =
                            LineSearchQuery + " HIREDATE ='" + hiredate +
                            "'";
                } else {
                    LineSearchQuery =
                            LineSearchQuery + " AND HIREDATE = '" + hiredate +
                            "'";
                }
            }

            if (job!= "" && job!= null) {
                if ("".equalsIgnoreCase(LineSearchQuery)) {
                    LineSearchQuery =
                            LineSearchQuery + " UPPER(JOB) LIKE UPPER('" + job + "')";
                } else {
                    LineSearchQuery =
                            LineSearchQuery + " AND UPPER(JOB) LIKE UPPER('" + job + "')";
                }
            }
           
            if (mgr!= "" && mgr!= null) {
                if ("".equalsIgnoreCase(LineSearchQuery)) {
                    LineSearchQuery =
                            LineSearchQuery + " MGR = " + mgr ;
               } else {
                    LineSearchQuery =
                            LineSearchQuery + " AND MGR = " + mgr ;
                }
            }

            if (salary!= "" && salary!= null) {
                if ("".equalsIgnoreCase(LineSearchQuery)) {
                    LineSearchQuery =
                            LineSearchQuery + " SALARY = " +salary;
                } else {                         
                    LineSearchQuery =
                            LineSearchQuery + " AND SALARY = " +salary;
                }
            }

            System.out.println(" Header Set where clause Query is : " + HdrsearchQuery);
           
            if (!("".equalsIgnoreCase(LineSearchQuery))) {
                if (LinesearchVo != null)
                {
                    LinesearchVo.setWhereClause(null);
                    LinesearchVo.setWhereClause(LineSearchQuery);
                    LinesearchVo.executeQuery();
                    String lstOfdepts = "";
                    lineRow = LinesearchVo.first();
                    if (lineRow != null) {
                       while(lineRow != null )
                       {
                          lstOfdepts = lstOfdepts + lineRow.getAttribute("deptNo").toString() + ",";
                          lineRow = LinesearchVo.next();
                       }
                       if (!("".equalsIgnoreCase(lstOfHdrIds)))
                       {
                           lstOfdepts =  lstOfdepts.substring(0,lstOfHdrIds.length()-1);
                           if ("".equalsIgnoreCase(HdrsearchQuery)) {
                              HdrsearchQuery = HdrsearchQuery + “ DEPTNO IN ("+lstOfdepts +")";
                           } else {
                              HdrsearchQuery = HdrsearchQuery + " AND DEPTNO IN ("+lstOfdepts +")";
                           }
                       }   
                    }
                    else
                    {
                         if ("".equalsIgnoreCase(HdrsearchQuery)) {
                           HdrsearchQuery = HdrsearchQuery + "  DEPTNO IS NULL ";
                         }
                         else {
                            HdrsearchQuery = HdrsearchQuery + " AND DEPTNO IS NULL ";
                        }
                    }
                }   
            } else {
                LinesearchVo.setWhereClause(null);
                LinesearchVo.clearCache();
                LinesearchVo.executeQuery();
            }
           
            if (!("".equalsIgnoreCase(HdrsearchQuery))) {                
//               HdrsearchVo.setWhereClause(null);
//               HdrsearchVo.clearCache();
//               HdrsearchVo.setWhereClause(HdrsearchQuery);
//               HdrsearchVo.executeQuery();
                 HashMap hmap = new HashMap();
                 hmap.put("HdrwhereClauseQ", HdrsearchQuery);
                 hmap.put("LinewhereClauseQ", LineSearchQuery);
                 hmap.put("ToPage","CurrPage");

/*Once search parameter for department i.e. master gets initialize master view object and refresh the page to reflect the changes in master detail hierarchy */

pageContext.forwardImmediatelyToCurrentPage(hmap,false,null);
            } else {
                HdrsearchVo.setWhereClause(null);
                HdrsearchVo.clearCache();
                HdrsearchVo.executeQuery();
            }
        }
        else {
           /* When Master View object is initialized with search criteria
            Here is time to initialize detail VO. */                       
            HdrsearchQuery = pageContext.getParameter("HdrwhereClauseQ");
            LineSearchQuery = pageContext.getParameter("LinewhereClauseQ");
        }
       
        if (!("".equalsIgnoreCase(LineSearchQuery))) {
             int chkFlag = 0;
//             innerTable.clearCache(pageContext);
//             innerTable.setAttributeValue(OAWebBeanConstants.CLEAR_CACHE_ATTR,Boolean.TRUE); 
   /* If user has entered one of the search criteria from detail table then  
      we need to remove unwanted records from detail table once master table 
      is initialized. One way to get handle of detail table is use of enumerator       */             
             LinesearchVo.setWhereClause(LineSearchQuery);
             LinesearchVo.executeQuery();
            // create an enumerator.
            OAInnerDataObjectEnumerator enum1 = new OAInnerDataObjectEnumerator(pageContext, innerTable);
            while (enum1.hasMoreElements())
            {
                innerRowSet = (RowSet)enum1.nextElement();
                // get all rows.
                rowsInRange = innerRowSet.getAllRowsInRange();
                System.out.println(" Inner Table Fetched Row Count :" +innerRowSet.getFetchedRowCount());

            /* Here we go to traverse each record in detail table returned after initialization of master VO */ 

                for (int i = 0; i < rowsInRange.length; i++ )
                {
                    String empnofrmquery = rowsInRange[i].getAttribute("EmpNo").toString();
                    String enamefrmquery = rowsInRange[i].getAttribute("Ename").toString();
                    String jobfrmquery = rowsInRange[i].getAttribute("Job").toString();
                    chkFlag = 0;
                    if (grrNumber != "" && grrNumber != null) {
                        if (!(empnofrmquery.equalsIgnoreCase(empno))) {
                            lineRow = LinesearchVo.first();
                            while(lineRow != null) {
                                if(!(empno.equalsIgnoreCase(lineRow.getAttribute("EmpNo").toString()))) {
                                    chkFlag = 1;
                                }
                                else {
                                    chkFlag = 0;
                                    break;
                                }
                                lineRow = LinesearchVo.next();
                            }
                        }
                    }
                    if (enamefrmquery!= "" && enamefrmquery!= null) {
                        if (!(enamefrmquery.equalsIgnoreCase(ename))) {
                             lineRow = LinesearchVo.first();
                             while(lineRow != null) {
                                 if(!(ename.equalsIgnoreCase(lineRow.getAttribute("Ename").toString()))) {
                                     chkFlag = 1;
                                 }
                                 else {
                                     chkFlag = 0;
                                     break;
                                 }
                                 lineRow = LinesearchVo.next();
                             }
                        }
                       
                    }
                   
                    if (jobfrmquery!= "" && jobfrmquery!= null) {
                        if (!(jobfrmquery.equalsIgnoreCase(job))){
                            lineRow = LinesearchVo.first();
                            while(lineRow != null) {
                                if(!(jobfrmquery.equalsIgnoreCase(lineRow.getAttribute("Job").toString()))) {
                                    chkFlag = 1;
                                }
                                else {
                                    chkFlag = 0;
                                    break;
                                }
                                lineRow = LinesearchVo.next();
                            }
                        }
                    }
                  /*Remove the rows that does not fit in search criteria */ 
if (chkFlag == 1) {
                        rowsInRange[i].remove();
                    }
                }
            }
                         
        }
        else {
            if (!("".equalsIgnoreCase(HdrsearchQuery))) {
                HdrsearchVo.setWhereClause(null);
                HdrsearchVo.clearCache();
                HdrsearchVo.setWhereClause(HdrsearchQuery);
                HdrsearchVo.executeQuery();
            }
            else {
                HashMap hmap = new HashMap();
                hmap.put("HdrwhereClauseQ",null);
                hmap.put("LinewhereClauseQ",null);
                hmap.put("ToPage",null);
                pageContext.forwardImmediatelyToCurrentPage(hmap,false,null);   
            }
//              
        }
    }

In Process request attach the following code
    /**
     * Layout and page setup logic for a region.
     * @param pageContext the current OA page context
     * @param webBean the web bean corresponding to the region
     */
    public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
        super.processRequest(pageContext, webBean);
        OAApplicationModule am = pageContext.getApplicationModule(webBean);
        if (pageContext.getSessionValue("retainAMValue") != null ) {
            System.out.println("Retain AM Value 
::"+pageContext.getSessionValue("retainAMValue").toString());

   if(!("Y".equalsIgnoreCase(pageContext.getSessionValue("retainAMValue").toString()))) {
                am.invokeMethod("rollbackTxn");
                if (pageContext.getParameter("HdrwhereClauseQ") != ""  &&  pageContext.getParameter("HdrwhereClauseQ")  != null)
                whereClause = pageContext.getParameter("HdrwhereClauseQ");
                /* Refresh the page with master VO */    
                Serializable param[] = {whereClause};
                am.invokeMethod("initHdrSearchVO",param);
                
                if ("CurrPage".equalsIgnoreCase(page)) {
                     /* Once master VO is initialized then invoke procedure 
                        to initialize detail VO */  
                     pageContext.putParameter("searchHdrLine","N");
                     showMasterDetail (pageContext,webBean,am);
                     System.out.println("Page Status :"+page);
                }
            }
            else {
                Serializable param[] = {whereClause};
                am.invokeMethod("initHdrSearchVO",param);
            }
        }
        else {
            Serializable param[] = {whereClause};
            am.invokeMethod("initHdrSearchVO",param);
        }
     
    }
  In process form request handle the event
    /**
     * Layout and page setup logic for a region.
     * @param pageContext the current OA page context
     * @param webBean the web bean corresponding to the region
     */
   public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) {
        super.processFormRequest(pageContext, webBean);
      /*Check search button pressed */      
      if(pageContext.getParameter(“SearchBtn”) != null )
      {
         /*Populate header and detail table*/
          showMasterDetail(pageContext, webBean, am);
      }
  }

/*******************************************************************************
Note:  initHdrSearchVO is master view object holding department details
And LinesearchVo is detail view object holding employee details. You may change the naming conventions according to standards. Here I have put deptno, department and location as search criteria from master table and empno and name and job from detail table.
*******************************************************************************/

Cheers !!!!

  

Comments

  1. Varun Kashyap5 November 2012 at 19:54

    Hi
    I am implementing a show/hide details component in a table.
    I have a employee table. In the table row on OAF page, 5 of the total 10 columns will be displayed. If the user clicks on show, remaining 5 columns will get displayed in the region below the row.
    I created two VOs on EmployeeEO, one master and another child. The view link created has EmployeeId as the key.
    When i am displaying the data it is coming properly. I click on show and i could see the details. However, on doing a create or adding a new row to VO, i am getting "stale data error , please check primary key comparison section".
    Could you please tell me the steps how to implement this? And what i am doing wrong

    ReplyDelete
    Replies
    1. Ravibhushan5 November 2012 at 23:50

      If you want to add row into detail Region i.e. innermost table, you need to follow diffrent steps.

      refer Jdev Guide
      search Detail Disclosure under advance table section.

      If u find any doubt still let me know


      Delete
  2. Ravibhushan27 November 2012 at 05:04

    Check association Object properties for Employee Master and detail

    Tick on following options
    Use Database key Constraints
    Composition Association
    Optimize for Databse Casecade delete
    Implement Cacade delete
    Lock Top Level Container


    To add row into inner most table try out following code
    change variable names, VO name accordingly

    // get a handle to inner table
    OATableBean innerTable =
    (OATableBean)webBean.findChildRecursive("InnerTableBean");

    // create an enumerator
    OAInnerDataObjectEnumerator enum =
    new OAInnerDataObjectEnumerator(pageContext, innerTable);

    while (enum.hasMoreElements())
    {
    RowSet innerRowSet = (RowSet) enum.nextElement();

    // get all rows
    Row []rowsInRange = innerRowSet.getAllRowsInRange();
    for (int i = 0; i < rowsInRange.length; i++)
    {
    Row nextRow = (Row) rowsInRange[i];
    }

    // In case you want to add new rows in this RowSet, you can do the same
    OARow newRow = (OARow) innerRowSet.createRow();

    // initialize value for some attribute and insert the row
    newRow.setAttribute("SomeAttr", "SomeValue");
    innerRowSet.insertRow(newRow);
    }

    ReplyDelete
  3. Mohammed Azhar7 January 2015 at 02:44

    TekSlate is the reputed e-learning tutorial across the globe delivers brief understanding about oaf in oracle oaf free tutorial with advanced teaching methodologies

    To Learn More Follow Below Link:
    http://bit.ly/1yJXoH8

    ReplyDelete
  4. Oracle Fusion3 March 2017 at 23:40

    Nice blog and great info about OA Frameworks thanks for sharing this blog and post as many blogs as different topics.........visit our website Oracle R12 financials online training

    ReplyDelete
  5. Unknown8 March 2017 at 01:48

    Nice article and explanation is good,Thank you for sharing your experience on OA frame work.It is very useful and informative article.

    Oracle Fusion Cloud HCM Training

    ReplyDelete
  6. oracle procurement21 March 2017 at 23:53

    You have got virtually defined about the system as a consequence it's miles very a lot interesting and that i got more records out of your weblog.for greater data about oracle fusion procurement online coaching please go to our website.
    Oracle Fusion procurement Coaching
    Oracle Fusion procurement Training Institute

    ReplyDelete
  7. oraclehcm30 March 2017 at 23:52

    Thanks a lot for using the great services in this blog and thanks for sharing. If you are looking for best Oracle Online Training is one of the leading Online Training institute in Hyderabad.
    Oracle fusion financials training

    ReplyDelete
  8. Unknown3 April 2017 at 00:20

    Hi,
    It looks like you spent much time and effort in writing this blog. I am appreciating your effort. It Was Very Good Information For oracle community.surely i will refer my friends to read this blog it will help them at certain time. Oracle Financials training
    Thank you

    ReplyDelete

Post a Comment

Popular posts from this blog

Implement 3-level master detail hierarchy using table region in OA framework.

Image
Implement 3-level master detail hierarchy using table region in OA framework. After depth searching on various oracle forums, I came to know that multi-level hierarchy in advance table is not achievable by easy means. There could be at the most one detail region inside an advance table.  If the business requirement is to display records in master->detail->detail format then advance table won’t be effective. For e.g. If the requirement is to show item transaction details year wise and when user clicks on show-hide region against any record will navigate to the month wise details and when user clicks on show-hide region against any record will navigate to the day wise details such hierarchy is possible, The OA framework provides the table region to achieve the same functionality. Procedure: Create view object for corresponding master – detail – detail regions. Define view link for 1 st level of master-detail hierarchy. Define view link for 2 nd leve...
Read more

Shuttle Control in OA Framework

Image
Shuttle Control in OA Framework While working on various business scenarios, we may come across multiple situations wherein we need to select specific values from the list and require moving to another list which will be considered as final list.  OA framework provides shuttle control to fulfill the same. For e.g.  In Jdeveloper, while attaching view object to the application module we need to select correct view object from view object definition list in left side panel and move it to right side panel/list so as to include view object definition into the  application module. Well, to achieve such operations Oracle application framework provides such option by means of shuttle control.  Shuttle Control allows user to move values from one list region to other. Design:  Select page region and right click -> new region set region style property of this region to shuttle.  After setting region style you will get shuttle region with default leading l...
Read more