Tree with Checkboxes: Save the Data - JS Array to PL/SQL

In two previous blogposts I described a method to transform a regular APEX Tree component to a Tree component with checkboxes. The second blogpost described a way to retrieve the data from the database using JSON and AJAX and check the appropriate checkboxes. In this last part of the series on Tree with Checkboxes I will describe a method to save the checked values to the database.
In this blogpost I will assume that you created the APEX tree as described in the other two blogposts (the links to these blogpost are at the bottom).
The method that I used will involve the following:

1. Database procedure to add a new value
2. Database procedure to register the changed values
3. Page process to call the first procedure (add values)
4. Page process to call the second procedure (changed values)
5. Before Page submit process to fill an APEX collection with the checked values

Database Procedures

Implementing the database procedures will not be part of this blogpost. The basic idea behind the stored procedures is to insert and update the data in the table.
For this blogpost I will assume you have created the procedures as required. The signature of the procedure that I used are as follows:

   procedure add_department(p_deptname      in varchar2
                           ,p_location      in varchar2
                           ,p_emp_arrayname in varchar2);
   procedure modify_department(p_deptno        in number
                              ,p_deptname      in varchar2
                              ,p_location      in varchar2
                              ,p_emp_arrayname in varchar2);

The values that are checked in the APEX Tree are saved in an APEX collection which is then used to insert or update the appropriate values in the table.
In the stored procedures the name of the APEX collection is passed in (the last argument) and the content of the APEX collection is retrieved using the available APEX views.
If this sounds like gibberish, leave a comment and I will address this in a later blogpost (no promises on when ;) )

Dynamic Action: Before Page Submit

Create a Dynamic Action with the event set to "Before Page Submit". The action that you want to perform is "Execute Javascript code". Add the following javascript code in the code section:

var lArray = [];
$('#catalog-tree div.tree')
   .find ('.checked,.undetermined')
   .parent()
   .each(
      function(){
          lArray.push(this.id);
});
apex.server.process('SAVE_EMPS',
                    {"f01":lArray}
                   );

The first part of the code (lines 1-8) will find all the checked checkboxes in the tree. In order to find the correct tree, the static ID of the tree is use. In this case the ID is "catalog-tree" (line 2). It will not only find the checked checkboxes, but also the ones that have the class "undetermined". Those are the ones with the filled checkbox, but not the checkmark. All the ID's are stored in a javascript array.
The second part of the code (lines 9-11) calls an AJAX process called "SAVE_EMPS" and passes in the javascript array of the checked checkboxes.

AJAX Callback

Create an AJAX Callback (in the processing section of the page, at the bottom) of type "PL/SQL". Choose "On Demand: Run this process when requested by AJAX" for the point of execution.
Enter the following PL/SQL code when prompted to do so:

begin
   if (apex_collection.collection_exists('P21_SELECTED_EMP'))
   then
     apex_collection.delete_collection('P21_SELECTED_EMP');
   end if;
   apex_collection.create_or_truncate_collection('P21_SELECTED_EMP');

   for i in 1 .. apex_application.g_f01.count() loop
      apex_collection.add_member(p_collection_name => 'P21_SELECTED_EMP'
                                ,p_c001 => apex_application.g_f01(i)
                                );
   end loop;
end;

The code above will be called from the dynamic action that we created earlier. The javascript array is "transformed" and stored in an APEX collection named "P21_SELECTED_EMP".

Page Process

The last part of the puzzle is to call the actual database procedure which writes the APEX collection to the database tables. This is a "regular" process (On Submit - After Computations and Validations) in the Page Processing section of the page.

begin
   department_pkg.modify_department(p_deptno        => :P21_DEPTNO
                                   ,p_deptname      => :P21_DNAME
                                   ,p_location      => :P21_LOC
                                   ,p_emp_arrayname => 'P21_SELECTED_EMP'
                                   );
end;

Bug in my example

Yes, there is a bug in my example. Not in the actual code or anything, but in the functionality... King, the president in the EMP table cannot be assigned to a department separately without all his subordinates being assigned to a different department.
The demo page does not use the actual EMP and DEPT table, so other examples on my demo pages do not reflect the changed emp-dept relations.

Links

Tree with Checkboxes: the first blogpost.
Tree with Checkboxes - Pre-select the data: the second blogpost
Demo