A common APEX implementation is to create a master application and then build multiple modular applications to support the main application. Commonly the modular applications are referenced and navigated to by the use of tabs. One problem of this tab approach is that the developers must maintain the same tab sets among the multiple applications. There is no way to subscribe to tab sets in Application Express. Adding one module application to the tabs involves edit all applications and modifying the tab set in each application.
What if we could define the tab set in one application and use it others applications? Although not perfect I will describe how this can be achieved. Basically we will replace the standard tabs definition with a conditional PL/SQL region on page 0 the Global Page. The region will call a stored procedure created that will query the APEX_APPLICATION_PARENT_TABS APEX view for all the tabs defined in our master application. For each tab row found the stored procedure will determine whether to display the tab or not by checking the tabs authorizations and build status.
A few limitations in this example include:
-
--Only sharing the parent tabs of a two-level tab template.
-
--Tab "conditions" will not be evaluated
-
--Each tab (even the main tab) must be an URL link to a unique application. (I.e. f?p=23372:1:&SESSION..)
First create the page template. Make a copy of the Two Level Tabs page template you are using. In this example I am using theme 22 and made a copy of the "Two Level Tabs - Right Sidebar (optional / table-based )" page template and named it "Two Level Tabs - [Shared Tabs]". Find in the page template where the #PARENT_TAB_CELLS# substitution string is used and replace it with #REGION_POSITION_08#. Remove the original #REGION_POSITION_08# substitution string.
In this example the code in the page template was changed from this:
<div id="navbar">
<div class="app-user">#WELCOME_USER#</div>
#NAVIGATION_BAR#
#REGION_POSITION_08#
</div>
<div id="parent-tabs">
<div class="tab-holder">#PARENT_TAB_CELLS#</div>
</div>
to this:
<div id="navbar">
<div class="app-user">#WELCOME_USER#</div>
#NAVIGATION_BAR#
</div>
<div id="parent-tabs">
<div class="tab-holder">#REGION_POSITION_08#</div>
</div>
Take note of the Parent Tab Attributes definitions. 
On the global page (page 0 pre APEX 4.2) create a PL/SQL (anonymous block) region. Set the display point to "Page Template Region Position 8" and the template to "No Template". In the region source the stored procedure which renders the tabs will be executed. The stored procedure passes in as parameters the application id of the "master application" and the template definitions of the current and non-current tabs. Enter in the previously noted parent tab template definitions. Don't include the #TAB_INLINE_EDIT# substitution string since the stored procedure is only taking care of the #TAB_LABEL# string substitution. For the region to only display if the page we are on is using our custom page template an "Exists (SQL query returns at least one row)" condition is created.
Region Source
shared_tabs( p_master_app_id => 23372,
p_curr_tab_template => '<div class="current">
<div>#TAB_LABEL#</div>
</div>',
p_noncurr_tab_template => '<div class="noncurrent">
<div>
<a href="#TAB_LINK#">
#TAB_LABEL#</a>
</div>
</div>');
Condition Expression 1
SELECT 1
FROM APEX_APPLICATION_PAGES aap,
APEX_APPLICATIONS aa
WHERE aa.application_id = aap.application_id
AND aa.APPLICATION_ID = :APP_ID
AND aap.PAGE_ID = :APP_PAGE_ID
AND NVL(aap.PAGE_TEMPLATE,aa.PAGE_TEMPLATE) =
'Two Level Tabs - [Shared Tabs]'
Now set our pages to use the "Two Level Tabs - [Shared Tabs]" page template.
In the modular applications referenced by the tabs:
-
Create the same region on the global page.
-
Import (copy and subscribe) the "Two Level Tabs - [Shared Tabs]" template from the main application. Change the application's default page template to the copied page template.
-
Ensure that any authorizations defined in our main application and used in the defined tabs are created and named the same. Ideally, copy and subscribe all the authorizations from the main application.
Now for new modular application we can add the tab to the main application and then it will show up in all the other modular applications without having to edit each individual application.
SHARED_TABS procedure
create or replace PROCEDURE "SHARED_TABS" (
p_master_app_id IN NUMBER,
p_curr_tab_template IN VARCHAR2,
p_noncurr_tab_template IN VARCHAR2)
IS
l_sql VARCHAR2 (4000) ;
v_location VARCHAR2 (100) ;
v_count INTEGER;
v_build_option_status VARCHAR2(20);
v_condition_expression VARCHAR2(3000);
BEGIN
v_location := '0-Begin SHARED_TABS';
FOR i_tabs IN
(
SELECT tab_label,
REPLACE(
REPLACE(
REPLACE(tab_target,
CHR (38)||'DEBUG.', v ('DEBUG')),
CHR (38)||'SESSION.', v ('SESSION')),
CHR (38)||'APP_ID.', v ('APP_ID')) tab_target,
authorization_scheme,
authorization_scheme_id,
condition_type,
condition_expression1,
build_option
FROM APEX_APPLICATION_PARENT_TABS
WHERE application_id = p_master_app_id
ORDER BY display_sequence
)
LOOP
BEGIN
wwv_flow.g_boolean := true;
v_location := '100-Authorization Check';
IF (INITCAP (i_tabs.tab_label) != 'Home')
AND (i_tabs.authorization_scheme IS NOT NULL) THEN
SELECT COUNT (*)
INTO v_count
FROM apex_application_authorization
WHERE application_id = v ('APP_ID')
AND authorization_scheme_name =
i_tabs.authorization_scheme;
IF (v_count = 0) THEN
RAISE_APPLICATION_ERROR (-20199,
'Missing Authorization: '
|| i_tabs.authorization_scheme) ;
END IF;
wwv_flow.g_boolean :=
apex_util.public_check_authorization(i_tabs.authorization_scheme);
END IF;
v_location := '200-Build Option Check';
IF (i_tabs.build_option IS NOT NULL) THEN
SELECT MAX (build_option_status)
INTO v_build_option_status
FROM apex_application_build_options
WHERE build_option_name = i_tabs.build_option
AND application_id = p_master_app_id;
IF (v_build_option_status = 'Exclude') THEN
wwv_flow.g_boolean := false;
END IF;
END IF;
v_location := '500-Render Tab';
IF (wwv_flow.g_boolean) THEN
IF (instr (i_tabs.tab_target, 'p='||v('APP_ID')) > 0) THEN
htp.p( REPLACE( p_curr_tab_template,
'#TAB_LABEL#',i_tabs.tab_label ) );
ELSE
htp.p ( REPLACE(
REPLACE(p_noncurr_tab_template,
'#TAB_LINK#', i_tabs.tab_target ),
'#TAB_LABEL#', i_tabs.tab_label ) );
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
wwv_flow.debug('Error in Procedure Shared Tabs - Location: '
||v_location||
' App. ID: '||v('APP_ID')||
' Page: '||v('APP_PAGE_ID')||
' Tab Label: '|| i_tabs.tab_label );
END;
END LOOP;
END;