How to Display Data in a Hierarchical Structure in Oracle APEX

Oracle APEX Tree Page Item With Checkboxes For Multiple Selections

Oracle APEX provides you with a tree-type page item to display data in a hierarchical structure, based on parent-child relationships between records. You add this component to graphically communicate hierarchical or multiple-level data with the help of a SQL statement, and specify links in the SQL query to call other segments from the tree nodes. In this section, you will utilize a tree control, which comes with checkboxes. This enhancement enables you to select multiple nodes in a tree. It is a plug-in named APEX FancyTree Select, created by Ronny Weiß and is available on APEX World. Watch this video to download and use this plug-in.

The objective of this section is to demonstrate the practical utilization of this plug-in. You can utilize it in scenarios where you want users to select multiple options. For example, in this exercise you will create a simple security module in which you will grant or revoke application access rights to and from different users’ groups – see the following figure.

Here are the steps for this exercise, which uses the tables created earlier in the Authorization tutorial:

1. Import the tree plug-in file region_type_plugin_apex_fancytree_select.sql that you downloaded from APEX World.

2. Create a blank page (I created Page 23) and add a static content region (Groups) to the page. Set the template of this region to Blank with Attributes.

3. Add a select list to the static content region created in the previous step as follows. The select list will display group titles from the group master table. When you select a group from this list, the page is submitted and the tree region (created next) is refreshed. 

Property	Value
Name	P23_GROUPID
Type	Select List
Label	<b>Select a Group:</b>
Page Action on Selection	Submit Page
LOV Type	SQL Query
SQL Query	SELECT DISTINCT groupTitle d, groupID r FROM group_master ORDER BY groupID

 

4. Now, add a region for the tree plug-in as follows:

Property	Value
Title	Tree with Checkboxes
Type	APEX FancyTree [Plug-In]
SQL Query	SELECT case when connect_by_isleaf = 1 then 0                        when level = 1 then 1 else -1 end as status,      level,      s.segmentid AS ID,      s.segmentparent AS PARENT_ID,      s.segmenttitle AS TITLE,      s.segmentid AS VALUE,      CASE WHEN level = 1 THEN 1 WHEN level = 2 THEN 2                WHEN level = 3 THEN 3 WHEN level = 4 THEN 4 END AS TYPE,      'This is ' || s.segmenttitle AS TOOLTIP,      CASE WHEN level = 1 THEN 'fa fa-apex-square'                WHEN level = 2 THEN 'fa fa-folder-o'                 WHEN level = 3 THEN 'fa fa-file-o'                WHEN level = 4 THEN 'fa fa-button' END AS ICON,      (SELECT CASE  WHEN ALLOW_ACCESS='Y' THEN 1 END        FROM GROUP_DETAIL gd        WHERE gd.segmentid=s.segmentid and                       gd.groupid=:P23_GROUPID) AS SELECTED,      NULL EXPANDED,      1 AS CHECKBOX,      0 AS UNSELECTABLE FROM segments s START WITH s.segmentparent = 0 CONNECT BY PRIOR s.segmentid = s.segmentparent ORDER SIBLINGS BY s.segmentid

 5. Create a static content region beside the Tree with the Checkboxes region. Set the Title of this region to Allowed Segments.

6. Add four text field items to the Allowed Segments region, as follows. When you click a node in the tree region, these items are refreshed to display segment ids for each level that are allowed to the selected group.

 

Property	Text Field 1	Text Field 2	Text Field 3	Text Field 4
Name	P23_LEVEL1	P23_LEVEL2	P23_LEVEL3	P23_LEVEL4
Type	Text Field	Text Field	Text Field	Text Field
Label	Level 1	Level 2	Level 3	Level 4
Template	Optional Floating	Optional Floating	Optional Floating	Optional Floating

 

7. Click the Attributes node under the Tree with Checkboxes region, and enter the following code for ConfigJSON. Click the Help tab in Page Designer to see further details of this attribute.

Property	Value
ConfigJSON	{   "checkbox": "fa-square-o",   "checkboxSelected": "fa-check-square",   "checkboxUnknown": "fa-square",   "selectMode": 2,   "typeSettings": [       {"id": 1, "storeItem": "P23_LEVEL1"},       {"id": 2, "storeItem": "P23_LEVEL2"},       {"id": 3, "storeItem": "P23_LEVEL3"},       {"id": 4, "storeItem": "P23_LEVEL4"}   ],   "autoExpand2Level": 4,   "enableCheckBox": false,   "refresh": 0 }

8. Add a dynamic action using the properties and values provided in the following table. The dynamic action actually makes the text items empty when you select a different group from the select list.

Property	Value
Name	Refresh Level Text Items
Event	Change
Selection Type	Item(s)
Item(s)	P23_GROUPID
Action	Set Value
Set Type	Static Assignment
Selection Type	Items
Item(s)	P23_LEVEL1,P23_LEVEL2,P23_LEVEL3,P23_LEVEL4

If you run the page at this stage and pick a group from the select list, the tree gets refreshed to display the rights provided to the selected group. Further, if you check or uncheck an option in the tree, the Items region is updated to display segment ids allowed to the selected group. Note that this security module has four levels - App, Menu, Page, and Items. The App level displays the name of the application - Cloud Financials. The second level comprises the application menu - CF Home, Transactions, and Reports. The third level consists of application pages - Home, Vouchers, and Ledgers. The final level includes page items. In the current scenario, the final level displays buttons - Display and Print. Let’s move on to the concluding part of this module where we are going to save the application access rights.

9. Add a button to the page using the following table.

Property	Value
Button Name	SAVE
Label	Save
Region	Tree with Checkboxes
Button Position	Copy
Hot	Yes
Action	Submit Page

10. Finally, add the following PL/SQL process to save the access rights. The important thing to describe in this code is the use of regexp_substr() function, which is normally used to stack delimited values vertically. In this module, we used this function in a FOR loop to extract colon-delimited segment ids from the four text field items. Each selected value is then searched and updated in the database table.

Property	Value
Name	Save Selected Segments
Type	Execute Code
Location	Local Database
PL/SQL Code	DECLARE    Vsegments varchar2(1000);    Vlevel1 varchar2(1000);    Vlevel2 varchar2(1000);    Vlevel3 varchar2(1000);    Vlevel4 varchar2(1000);    Vfound number; BEGIN    update group_detail set allow_access='N' where groupid=:P23_GROUPID;    FOR i IN  -- Level 1 segments    (SELECT trim(regexp_substr(:P23_LEVEL1, '[^:]+', 1, LEVEL)) Vlevel1 FROM dual      CONNECT BY LEVEL <= regexp_count(:P23_LEVEL1, ':')+1    )    LOOP       if i.Vlevel1 >= 0 then          select count(*) into Vfound from group_detail          where segmentid=i.Vlevel1 and groupid=:P23_GROUPID;          if Vfound=1 then -- record exists             update group_detail set allow_access='Y'             where segmentid=i.Vlevel1 and groupid=:P23_GROUPID;             commit;          end if;       end if;      END LOOP;      FOR i IN  -- Level 2 segments    (SELECT trim(regexp_substr(:P23_LEVEL2, '[^:]+', 1, LEVEL)) Vlevel2 FROM dual      CONNECT BY LEVEL <= regexp_count(:P23_LEVEL2, ':')+1    )    LOOP       if i.Vlevel2 >= 0 then          select count(*) into Vfound from group_detail          where segmentid=i.Vlevel2 and groupid=:P23_GROUPID;          if Vfound=1 then -- record exists             update group_detail set allow_access='Y'             where segmentid=i.Vlevel2 and groupid=:P23_GROUPID;             commit;          end if;       end if;      END LOOP;      FOR i IN  -- Level 3 segments    (SELECT trim(regexp_substr(:P23_LEVEL3, '[^:]+', 1, LEVEL)) Vlevel3 FROM dual      CONNECT BY LEVEL <= regexp_count(:P23_LEVEL3, ':')+1    )    LOOP       if i.Vlevel3 >= 0 then          select count(*) into Vfound from group_detail          where segmentid=i.Vlevel3 and groupid=:P23_GROUPID;          if Vfound=1 then -- record exists             update group_detail set allow_access='Y'             where segmentid=i.Vlevel3 and groupid=:P23_GROUPID;             commit;          end if;       end if;      END LOOP;        FOR i IN  -- Level 4 segments    (SELECT trim(regexp_substr(:P23_LEVEL4, '[^:]+', 1, LEVEL)) Vlevel4 FROM dual      CONNECT BY LEVEL <= regexp_count(:P23_LEVEL4, ':')+1    )    LOOP       if i.Vlevel4 >= 0 then          select count(*) into Vfound from group_detail          where segmentid=i.Vlevel4 and groupid=:P23_GROUPID;          if Vfound=1 then -- record exists             update group_detail set allow_access='Y'             where segmentid=i.Vlevel4 and groupid=:P23_GROUPID;             commit;          end if;       end if;      END LOOP; END;
Point	Processing
When Button Pressed	SAVE

Save your work and test the module. When you allow or revoke an application segment from a group and click the Save button, the page is submitted and the change is reflected immediately in the tree region.