1 2 Previous Next 16 Replies Latest reply: Apr 1, 2013 10:56 PM by User108 RSS

    Apply Criteria to Child VO When Parent VO is Executed

    User108
      I am using Jdev 11.1.1.3

      I have a Parent VO and Child VO with a view Link associated between them.
      I have a Search Page with 4 Search Parameters which 2 from the Parent VO and 2 from the Child VO. From the AM Data Control, I have dropped the Parent VO and the Child VO associaed with it as 2 different tables.

      My question is when the user inputs in value for the Child VO, and then when I execute Query on the Parent VO, I want the criteria to be applied to the Child VO.

      I tried to override the executeQueryForCollection in my Parent VO , obtained the viewlink from the Parent VO, then obtained the Destination ( which is my Child VO ) and set the bind parameters.
      So my Code looks like

      public void executeQueryForCollection(Object qc, Object[] params,
      int noUserParams)
      {

      ViewLinkImpl viewLinkImpl = null;
      ViewLink vlu[] = this.getViewLinks();
      String vluNames[] = new String[vlu.length];
      for (int j = 0; j < vlu.length; j++)
      {
      vluNames[j] = vlu[j].getName();
      if (vluNames[j].equals("ViewLinkName"))
      {
      viewLinkImpl = (ViewLinkImpl) vlu[j];
      break;
      }
      }

      ChildVOImpl childVOImpl = (ChildVOImpl) viewLinkImpl.getDestination();

      childVOImpl.setBindXXX("Value");
      childVOImpl.setBindYYY("Value");
      super.executeQueryForCollection(qc, params, noUserParams);

      }

      However what happens is that when I run it, I get an exception "Missing IN or OUT parameter at index:: 1 " Because if I print the query for ChildVO , i see .

      Select * from Child Table where FiledName1 = :BindXXX and FieldName2 = :BindYYY and FieldName3= :BindAnotherID

      This BindAnotherId is coming from the where Clause attached to the ViewLink .
      I expected the framework to have the value for BindAnotherID, since BindAnotherID is the Field with which ParentVO and ChildVO are associated.

      Can somebody please throw some light on this or if you know of any other approach to meet the requirement of executing a criteria in Child VO when parent VO is executed as well is appreciated. Thanks in Advance.
        • 1. Re: Apply Criteria to Child VO When Parent VO is Executed
          User108
          Any thoughts on the above posted question ?
          • 2. Re: Apply Criteria to Child VO When Parent VO is Executed
            Timo Hahn
            Sorry, I don't get what you are trying to do. If you have a master detail relationship it doesn't make sense to look for a detail directly. You can look for child records independently from the master, but this is normal query then.

            Timo
            • 3. Re: Apply Criteria to Child VO When Parent VO is Executed
              User108
              To Explain briefly.

              I have a Search Section which Takes in Department Id as Input and Employee Name. My Results Section Contains Both Department as a Read Only Form and Employee as a Table.

              I could search for Employees Starting with Name 'A' and working in HR Department.

              I want the Criteria of ( Employee Name Starting with Name 'A' to be applied ) when the Department and Employee Results are rendered on Screen.

              and requirement is also that I use the EmployeeVO and DepartmentVO as such and not create a Custom ViewObject which has a query joining those 2 tables based on the criteria.

              Edited by: AgentRxA on Mar 28, 2013 5:09 PM

              Edited by: AgentRxA on Mar 28, 2013 5:14 PM
              • 4. Re: Apply Criteria to Child VO When Parent VO is Executed
                Timo Hahn
                See if this blog helps you some the problem http://jobinesh.blogspot.com/2011/05/setting-bind-variable-value-for.html

                Timo
                • 5. Re: Apply Criteria to Child VO When Parent VO is Executed
                  RaviNuka
                  Hi,

                  As you are saying about search section, does this departmentId field comes from DepartmentVO and EmployeeName come from EmployeeVO or both fields are coming from EmployeeVO? Is that you manually created some search region in your page and dropped these two fields from data controls? or you created in a different way?

                  There are couple of options you can try based on my understanding of the issue:
                  1. If your search fields are not created from DataControl pallette and using the EmployeeVO & DepartmentVO (incase you just dropped two inputtext fields from component palette) and using the userinput values, try to use VO attributes by creating view criteria.
                  2. If both search fields are coming from EmployeeVO (i.e. DepartmentId, EmployeeName) then you can create a view criteria in parentVO and try to populate the departmentid of childvo in the value section (assuming parentVO has view accessor to access childVO attributes)
                  3. If both search fields are coming from differentVOs(i.e. DepartmentId from DepartmentVO, EmployeeName from EmployeeVO), then it should not be any issue in your case as each VO has its own view criteria and that should handle the joins as well as filter.

                  Thanks,
                  Ravi Nuka.

                  Edited by: Ravi Nuka on Mar 28, 2013 5:58 PM
                  • 6. Re: Apply Criteria to Child VO When Parent VO is Executed
                    User108
                    Hi Ravi,

                    Department Id is coming from DepartmentVO and EmployeeName is coming from EmployeeVO and in my Search Page, I have a Transient View Object which Holds the Department Id and EmployeeName and have dropped that as a search Form from the Data Control and a method in AM which is dropped from the Data Control as a adf button.

                    I am reading the values from the Input Components ( from the Transient VO ) and setting the DepartmentId to the View Criteria which I created in DeparmentVO - in my AM method ( which I have dropped as a button to my page ) .

                    I am having difficulty in setting the bindParamater of Employee Name to the EmployeeVO and thus having difficultly in filtering my results based on my input to Employee Name.
                    • 7. Re: Apply Criteria to Child VO When Parent VO is Executed
                      User108
                      Hi Timo,

                      Thats Exactly the use case for which I am looking for an answer. However I am working in jdev 11.1.1.3. Any thoughts on how to implement the same in this version.
                      • 8. Re: Apply Criteria to Child VO When Parent VO is Executed
                        User108
                        Thanks for the link. I learnt couple of things today by reading those.

                        I referred the blog
                        http://jobinesh.blogspot.com/2011/05/setting-bind-variable-value-for.html
                        and the similar discussions in
                        http://jobinesh.blogspot.com/2011/05/preparing-viewobjects-query-for.html
                        Re: BUG on LOV : lov is empty for the first record.
                        Bind variable required/not required: strange behaviour

                        and Understand that
                        +// If you need to modify the bind parameters values present in the query, executeQueryForCollection(..) is not the place as its too late and the values has already been extracted for execution. //+

                        and since “prepareRowSetForQuery” method does not exist in the Version that I am using, which method should I be overriding to set the bind values so that its take into the account when the VO is executed.
                        • 9. Re: Apply Criteria to Child VO When Parent VO is Executed
                          RaviNuka
                          Hi,

                          Please try this link and see if this works, I created a small demo to replicate your situation and it looks working for me. See if you can use this approach. I didn't create any additional view objects (other than two base view objects) and created a sample page.
                          I created bind variable during runtime and used them (my view objects doesn't have any bind variables)

                          http://rnuka.blogspot.com/2013/03/master-detail-screen-with-search-fields.html



                          Hopefully this is helpful for you.

                          Thanks,
                          Ravi Nuka.

                          Edited by: Ravi Nuka on Mar 29, 2013 4:16 PM
                          • 10. Re: Apply Criteria to Child VO When Parent VO is Executed
                            User108
                            Hi Ravi,

                            First of all , It works . Thanks . However can you please explain my below question.

                            From your approach, wouldn't I have results from Employee Table which are not from Department Id for which you are searching as well. Because no where you are relating the employees table to department table.

                            Confused how would the employee table would have data which are filtered by department id as well. ( Because you are not setting the employee criteria to a viewlink accessors of Department. You are setting the employee name criteria to a separate instance of employee table )

                            Can you please clarify. Thanks for your time Ravi. Really appreciate it.
                            • 11. Re: Apply Criteria to Child VO When Parent VO is Executed
                              SteveMuench
                              The viewlink is taking care of constraining the child VO by the corresponding fields in the current row of the master VO via the view link's view link ends (typically derived from the association ends of the underlying entity association).
                              • 12. Re: Apply Criteria to Child VO When Parent VO is Executed
                                RaviNuka
                                Thanks Steve for the explanation.

                                Hi User108,
                                As Steve said, These joins are taken care in view links (view link will take care of join between two entities/tables). You can create view links in two ways

                                1. If you have both master and detail readonly, then you can simply create a view link based on your view objects
                                2. If you want to perform any DML Operations (like create/update/insert) in any underlying entities, then you need to create association between those entities and create view link based on the association.

                                For you better understanding, I updated the blog with screenshots how I created association between entities and viewlink based on that association.

                                http://rnuka.blogspot.com/2013/03/master-detail-screen-with-search-fields.html


                                Regards,
                                Ravi Nuka.
                                • 13. Re: Apply Criteria to Child VO When Parent VO is Executed
                                  User108
                                  Thanks Steve and Ravi for the explanation

                                  I did have an association and viewlink, however I thought I have to get the View link accessors and set the bind criteria to get the expected results. ( This is because I was just trying to execute the Parent VO query only )

                                  I implemented the functionality for now by executing both the parent and child VO.

                                  However for my understanding ,Which method in VIewojbjectImpl needs to be overridden in case if I want to execute only the parent VO and set the bind parameters of the Child in that overridden method in jdev version 11.1.1.3 ( in jdev version 11.1.15 i see that a method prepraeRowSetforquery can be used. any method that can be used in version 11.1.1.3. I Tried to override prepareVOForQuery in the ChildVOImpl class but however i wasnt able to get the expected results )
                                  • 14. Re: Apply Criteria to Child VO When Parent VO is Executed
                                    RaviNuka
                                    There are couple of options in below thread if you want to try with jdeveloper versions prior to 11.1.1.5 (where there is no prepareRowSetforQuery).

                                    Re: BUG on LOV : lov is empty for the first record.

                                    Regards,
                                    Ravi Nuka.
                                    1 2 Previous Next