Accordion menu in APEX

Menu system built into Application Express is quite easy and very useful when you develop simple application. However there are situations when this template doesn't work or has limitations like:

• when you have a lot of parent and standard tabs, you'll face problems with menu size;
• if you want to use some page as a common detail level of reports related to different tabs, you must choice one tab to be highlighted as current;
• to access submenu link, you must first load page, that is first in parent tab definition.

It would be nice to build more dynamic menu (like accordion) that solves above problems, but is still based on the same tabs system, so we don't need to create menu again in different way (using lists or hardcoded links).

The plan

To build accordion menu in APEX we need to do several things:

• attach jQuery and jQuery UI libraries and styles to our template;
• create dedicated place in our page template where we'll display menu;
• create process that will read parent and standard tabs (you must have them!) from APEX views and generate menu code including security rules;
• get it together and invoke jQuery accordion function.

The idea is to generate menu code before page is loaded, write the code to global variable and use jQuery to show menu based on its value.

jQuery integration

You can find jQuery and jQuery UI files on official website (www.jquery.com), where you can also customize its style. To integrate jQuery and APEX you must import downloaded files to the application or copy them directly on APEX server and refer them to in page HTML.

More information about integrating jQuery and APEX you can find in previous post (APEX and jQuery integration).

Menu building

First we must edit page template (Home>Application Builder>Application>Shared Components>Templates), to turn off standard menu, and create region to display accordion.
If you edit Two Level Tab page template, find #PARENT_TAB_CELLS# and # TAB_CELLS# strings and remove them. Simpler and faster is to use template without menu.
Then write <div id="menu"></div> in place where you want to see new menu.
You'll need to edit some HTML and CSS to get nice look and place menu region exactly where you want to see it.

If you don't have page zero, create it. Go there and create two hidden items (let's name them P0_MENU and P0_MENU_ACTIVE).

Go to Application Processes (Home>Application Builder>Application>Shared Components> Application Processes) and create new "On Load: Before header" (no conditions) process with following code:


DECLARE
v_result varchar2(32000); -- menu code
v_parent_tab number := 0;
v_active_parent_tab number; -- active parent tab
v_tabs_for_page number := 0;
-- session data
v_session_id NUMBER := v('SESSION'); -- session ID
v_application_id NUMBER := v('APP_ID'); -- application number
v_page_id NUMBER := v('APP_PAGE_ID'); -- current page


BEGIN
-- checking if current page has related tab
SELECT count(*) into v_tabs_for_page FROM apex_application_tabs WHERE application_id=v_application_id AND (tab_page = v_page_id OR instr(','||TAB_ALSO_CURRENT_FOR_PAGES||',',','||v_page_id||',')>0);
-- if so we generate menu
IF (v_tabs_for_page>0) THEN
-- In two loops we generate menu code
-- First loop - for each parent tab
FOR y IN (SELECT * FROM apex_application_parent_tabs WHERE application_id=v_application_id ORDER BY DISPLAY_SEQUENCE)
LOOP
-- If user can see parent tab, we add it and create second loop
IF (y.AUTHORIZATION_SCHEME is null OR APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION(y.AUTHORIZATION_SCHEME)) THEN
-- checking active parent tab
IF (v_parent_tab is not null and v_parent_tab>0) THEN 
v_result := v_result||'</ul></div>';
v_parent_tab := v_parent_tab + 1;
ELSE
v_parent_tab := 1;
END IF;
-- adding parent tab link to menu
v_result := v_result||'<h3><a href="#">'||y.tab_label||'</a></h3><div><ul>';
-- reating second loop of children tabs for current parent tab
FOR x IN (SELECT * FROM apex_application_tabs WHERE application_id=v_application_id AND TAB_SET = y.CURRENT_FOR_TABSET ORDER BY DISPLAY_SEQUENCE)
LOOP
-- Authorization check
IF (x.AUTHORIZATION_SCHEME is null OR APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION(x.AUTHORIZATION_SCHEME)) THEN
-- if tab is related to current page, we make its name bold, if not we just add its code
IF (x.tab_page = v_page_id OR instr(','||x.TAB_ALSO_CURRENT_FOR_PAGES||',',','||v_page_id||',')>0) THEN
v_result := v_result||'<li><B><a href="'||APEX_UTIL.PREPARE_URL('f?p='||v_application_id||':'||x.tab_page||':'||v_session_id)||'">'||x.tab_label||'</a></B></li>';
v_active_parent_tab := v_parent_tab; -- setting active parent tab
ELSE
v_result := v_result||'<li><a href="'||APEX_UTIL.PREPARE_URL('f?p='||v_application_id||':'||x.tab_page||':'||v_session_id)||'">'||x.tab_label||'</a></li>';
END IF;
END IF;

END LOOP;
END IF;

END LOOP;
-- Closing tabs list
v_result := v_result||'</ul></div>';
-- setting menu code to global variable
:P0_MENU := '<div id="accordion">'||v_result||'</div>';
:P0_MENU_ACTIVE := v_active_parent_tab-1;
END IF; -- if not we don't change menu
END;


It'll generate and adapt menu, and then write its code to P0_MENU item every time you enter new page in your application.

Return to page template and in Header section write:

<script type="text/javascript">
$(document).ready(function () {
   $("#menu").html($('#P0_MENU').val());
   $("#accordion").accordion({
      autoHeight: false, 
      animated: 'fold', 
      active: Number($('#P0_MENU_ACTIVE').val())
   });
});
</script>

Apply changes and run application. If your page uses template that you've just changed, you should see accordion menu filled with parent (as sections) and standard tabs (as links).

This method has been tested with APEX 3.2.1 and jQuery 1.4.2 (UI 1.8). It works with all browsers.