Tree with Checkboxes - Pre-Select Data with JSON

After you have modified the standard APEX tree to a tree with checkboxes (see this blogpost) you will probably need to find a way to load data from the database and show the values previously checked by the user.
In this blogpost I will show you how to load the data from the database using an AJAX-call and check the appropriate checkboxes in the tree.
The method described here retrieves the data uses the APEX_UTIL.JSON_FROM_SQL procedure and an AJAX call. This method can be used for many more things besides populating a Tree with checkboxes :)
For the rest of this blogpost, I will make the assumption that you have created a page where a Tree is present which are made up of checkboxes instead of folder-icons.
For my example I used the good-ol' DEPT and EMP table. For the demo-page I created a Form with a Report Based on the DEPT table. On the form-page I created an APEX tree using the region wizard. This tree was modified as per the method described in "the other blogpost".
This example will only check the boxes for the employees who work for the currently selected department. I know this is a contrived example, but hey...
There will be a follow-up blogpost to describe how to store the values in the database.

Step 1: The AJAX Call

For this to work, we will need two things:

1. AJAX and the Callback
2. On Demand Process

Let' start with number 1.
For this you will need create a little bit of javascript which is to be executed when the page loads. You can either do this in a Dynamic Action with where the event is "Page Load" or you can do this at Page Level in the Javascript section: "Execute when Page Loads". Which one you choose is up to you.
For a Dynamic Action: right click in the page rendering section, labelled "Dynamic Actions" and choose "Create".

Follow the wizard:

1. Name: Check Department Emps
2. Event: Page Load
3. Action: Execute Javascript Code

The javascript that you will need is as follows:

var ajaxRequest = new htmldb_Get(null,&APP_ID., 'APPLICATION_PROCESS=GET_EMPS',&APP_PAGE_ID.);
ajaxRequest.addParam('x01',$v('P16_DEPTNO'));
gReturn = ajaxRequest.get();
if(gReturn){
    var obj = jQuery.parseJSON(gReturn);
    for (var i=0,cnt=obj.row.length; i < cnt; i++) {
      $('#'+obj.row[i].EMPNO+' a:first').removeClass('unchecked').addClass ('checked');
    }
};
get = null;

Line 1 creates the ajaxrequest object which references the Application Process called "GET_EMPS". This application process is defined on the page where the tree is, hence the last argument &APP_PAGE_ID.
On line 2 we add an argument to be used when we do the actual AJAX call. The value of the item P16_DEPTNO will be placed in argument "x01". When we make the AJAX call and the PL/SQL procedure is executed, the value of the argument can be found in APEX_COLLECTION.G_X01. (see the code below how we will use this information).
Line 3 makes the actual AJAX-call.
When the AJAX-call on line 3 is successful, lines 5 through 8 are executed. Because the PL/SQL procedure (GET_EMPS) will return a JSON object, it is parsed as such (line 5). The FOR Loop will find the appropriate employee in the tree and change the class associated with it.

The second part that you need to do is create an On-Demand process.
Right click on the "Ajax Callback" in the page processing section of the page. Make sure to name the Ajax Call "GET_EMP", otherwise the javascript can't find it. Follow the wizard and choose PL/SQL block.
Follow the wizard and enter the following data:

1. Category of Process: PL/SQL
2. Name: GET_EMPS
3. Point: On Demand: Run this process when requested by AJAX

Enter the following code in the appropriate section:

declare
   l_sql varchar2(32000);
begin
   l_sql := '
select empno
  from emp
 where deptno = ' ||
            dbms_assert.enquote_literal(apex_application.g_x01);
   apex_util.json_from_sql(l_sql);
exception
   when no_data_found then   
       htp.p('{"row":[]}');
end;

On line 9 the packaged procedure APEX_UTIL.JSON_FROM_SQL is used to transform the result of a SQL statement to a JSON Object. Unfortunately this procedure takes the SQL statement as a string. Guarding against SQL Injection it is a good idea to use the DBMS_ASSERT pacakage to sanitize the user input.
The argument which is being passed down from the JavaScript code (x01) can be found in the APEX_APPLICATION.G_X01 collection.
When an exception occurs

Note that the Javascript code is not really fancy: when some of the child records are checked, the parent checkbox doesn't show the "square" indicating that only part of the children are checked. The javascript code provided in this article simply shows a check mark in the checkbox where there should be one.

As this blogpost is long overdue, the first article describing the transformation from a regular APEX tree to one with checkboxes was written in July, J. Eberhard has written a blog describing a different method of loading and saving data using an APEX Tree with Checkboxes in the mean time. You can find that blogpost here.
DBMS_ASSERT: Oracle Base on DBMS_ASSERT