Generate an Apex menu from Designer

In my previous post I showed you how can re-use Designer domains to generate domain LOV's.
Another Designer to Apex feature is the Module Structure to Menu List conversion.

Apex 5 now uses Lists for the menu structure instead of the old Tabs in Apex 4. This makes it really easy to make a menu structure based on the Module Structure in Designer.

Start with a menu structure table in your application.

CREATE TABLE  "APX_MENU_STRUCTURE"
   (    "ID" NUMBER(*,0),
    "APP" VARCHAR2(40),
    "TYPE" VARCHAR2(1),
    "SORT_ORDER" NUMBER(*,0),
    "LABEL" VARCHAR2(50),
    "PAGE" VARCHAR2(40),
    "PARENT" VARCHAR2(40)
   )

Create a BEFORE INSERT trigger to populate the primary key ID based on a sequence. I will skip that part here.

TYPE in this table is either M (for Menu) or P (for Page), as forms can be called from a menu or from another page.

 Next step is to generate a script to populate the menu structure table. For forms called from a menu:

select 'insert into apx_menu_structure (APP,TYPE,SORT_ORDER,LABEL,PAGE,PARENT) ' ||
       'select ' ||
      '''MY_APEX_APP''' || ',' ||
      decode(mc.general_module_type,'MENU','''M''','''P''') || ',' ||
      mn.CALLED_SEQUENCE || ',' ||
      '''' || mc.short_title || ''',' ||
      '''' || mc.short_name || ''',' ||
      '''' || mp.short_name || '''' ||
     ' from dual;'
from ci_module_networks mn
   , ci_modules mp
   , ci_modules mc
   , sdd_folder_members mem -- the application system reference goes through sdd_folder_members
   , ci_application_systems app
where mn.PARENT_MODULE_REFERENCE = mp.ID   
and mn.CHILD_MODULE_REFERENCE = mc.id
and mp.general_module_type = 'MENU'
and mc.general_module_type in ('MENU','DEFAULT')
and mem.member_object = mp.irid
and mem.folder_reference = app.irid
and app.name = 'MY_APPLICATION'
start with lower(mp.IMPLEMENTATION_NAME) = 'MNUMAIN'  -- your top menu
connect by prior mc.id = mp.id
order siblings by  mn.CALLED_SEQUENCE


For forms calling forms:

select 'insert into apx_menu_structure (APP,TYPE,SORT_ORDER,LABEL,PAGE,PARENT) ' ||
        'select ' ||
       '''MY_APEX_APP''' || ',' ||
       decode(mc.general_module_type,'MENU','''M''','''P''') || ',' ||
       mn.CALLED_SEQUENCE || ',' ||
       '''' || mc.short_title || ''',' ||
       '''' || mc.short_name || ''',' ||
       '''' || mp.short_name || '''' ||
      ' from dual;'
from ci_module_networks mn
   , ci_modules mp
   , ci_modules mc
   , sdd_folder_members mem -- the application system reference goes through sdd_folder_members
   , ci_application_systems app
where mn.PARENT_MODULE_REFERENCE = mp.ID   
and mn.CHILD_MODULE_REFERENCE = mc.id
and mp.general_module_type = 'DEFAULT'
and mc.general_module_type = 'DEFAULT'
and mem.member_object = mp.irid
and mem.folder_reference = app.irid
and app.name = 'MY_APPLICATION'



Run the generated scripts and you have the whole menu structure in the table.

Last step is to create the Apex menu list.

select level
      ,label
      ,decode(type,'M','#','f?p='||app||':'||page||':'||:APP_SESSION||'::'||:DEBUG) as target
from his_apx_menu_structure
where parent like 'MNU%'   -- see note below
start with parent = 'MNUMAIN'
connect by prior page = parent
order siblings by sort_order

As I said before, the whole module structure consists of forms called from menus and form called from other forms. If you have an easy way of recognizing a menu just by it's name, you can use this simple line in the WHERE clause :

where parent like 'MNU%'   -- a menu module starts with MNU

If not, it will be a bit more complicated.

For forms calling forms you have to decide how your forms are calling other forms. It could be an extra, context sensitive, menu option, or it could be that forms are called using buttons. In any case, you can still use the menu structure table to dynamically call Apex pages from another Apex page.