4 Replies Latest reply on Mar 6, 2014 6:23 AM by Shoaib

    save changed in master-detail

    Shoaib

      Hi all,

       

      My forms version is Forms [32 Bit] Version 6.0.8.27.0

       

      Database version :-

       

      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

      PL/SQL Release 10.2.0.3.0 - Production

      CORE 10.2.0.3.0 Production

      TNS for 64-bit Windows: Version 10.2.0.3.0 - Production

      NLSRTL Version 10.2.0.3.0 - Production

       

      I have 3 block in my forms namely A,B,C.

       

      B is the detail block of A and C is the detail block of B.

       

      Block A :

      BranchName
      Branch 1TEST BRANCH 1

       

      Block B :

      DepartmentName
      Dept1TEST DEPT 1
      Dept 2TEST DEPT2
      Dept 3TEST DEPT3

       

      Block C :

      DesignationName
      Designation 1TEST DESIG 1
      Designation 2TEST DESIG 2
      Designation 3TEST DESIG 3

       

      Each branch has many department and each departments has many designation.I have made a master-detail relationship between A and B and between B and C in my forms.

       

      My steps required for data entry is

      1) Create a new record in A

      2) Then create multiple details records for A in B.

      3) Create multiple details records for B in C.

       

      when i navigate back to B to create more records in B without committing changes in C,then forms  builder prompt for save of the record in C.

       

      I want all the changed to saved at the last single commit.

        • 1. Re: save changed in master-detail
          tony.g

          Hi

           

          What you are asking for is not the normal fashion that Forms works.

           

          When you navigate back to block B from block C then select a different row in block B, then it will try and change the contents of the coordinated block C, thus prompting you to save the contents before they are lost.

           

          One way you could achieve this maybe is to store all of the results as you are entering them in background arrays.  But this is made more complex if you want to go back and change things before saving.  Then when you commit, you save the contents of the arrays.

           

          cheers

          Tony


          • 2. Re: save changed in master-detail
            Zlatko Sirotic

            Developing proper transactional behavior with Oracle Forms in Grandparent-Parent-Child case is a problem.

            ADF doesn’t have this problem.

             

            In Forms, we must use some "container" for the "non-visible" Child records

            (Child records with a Parent different from current record in Parent block).

             

            I am trying to solve this problem in two different ways:

             

            1. Using the POST built-in.

            In this case the "container" is a Child database table.

            But, there could be a problem because POST built-in actually executes DML statements

            (this locks records and fires database triggers - so a deadlock can occur) before COMMIT_FORM.

             

            2. Using a "temporary container".

            "Temporary container" can be (for example) a temporary database table, a PL/SQL table of records (defined in Forms package or database package), or a Forms record group.

            This version has no problems with record locks / database triggers, but is much more complex for programming than the first one.

             

             

            During development, when I don’t have a problem with record locks or database triggers, I use POST.

            In other cases, I use the second version, with PL/SQL table of records (defined in Forms package) as a "temporary container".

             

            Moreover, when we do use POST, we can do it in a number of ways.

            One method is to use POST in Forms level WHEN-NEW-RECORD-INSTANCE trigger.

             

            Another method is to change code in CLEAR_ALL_MASTER_DETAILS.

            Instead of:

             

            CLEAR_BLOCK (ASK_COMMIT);

             

            we can write:

             

            IF mastblk = "name_of_master_block" THEN

              CLEAR_BLOCK (ASK_COMMIT);

            ELSE

              POST;

            END IF;

             

            It’s not easy job, and there are a lot of little details to solve.

             

             

            Regards

            • 3. Re: save changed in master-detail
              Andreas Weiden

              I would also go with the POST-solution and use the ON-CLEAR-DETAILS to do the POST. As POST sets the form-status to QUERY, you should "tick" an item in your master-blcok after the POST, so that the status is CHANGED again (could be something like :MASTERBLOCK.ID:=:MASTERBLOCK.ID;

              • 4. Re: save changed in master-detail
                Shoaib

                Thanks Tony ,Zlatko and Andreas for replying.

                 

                Zlatko your method solved my problem.

                 

                Rgds

                Shoaib