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.

Oracle Designer and Forms to Apex: List of Values

If you are converting your Oracle Designer and Forms to Application Express (Apex), there are some components you can easily re-use. One of them is domain List of Values.

Domains and domain values are typically stored in table CG_REF_CODES. From this table it is simple to generate Apex List of Values for each domain.


declare
  -- Modify v_flow_id to your application ID
  v_flow_id      number := 101;
  -- You can give the LOV name a prefix to easily recognize LOV's
  -- derived from a domain, e.g. DMN_GENDER
  v_lov_prefix   varchar2(10) := 'DMN_';
begin
  for r in
  (select distinct rc.rv_domain
   from cg_ref_codes rc
   where rc.rv_meaning is not null
   and not exists
     (select 1
      from   apex_application_lovs
      where  application_id = v_flow_id
      and    list_of_values_name = v_lov_prefix || rc.rv_domain
     )    
  )
  loop
    wwv_flow_api.create_list_of_values (
      p_id       => null,
      p_flow_id  => v_flow_id,
      p_lov_name => v_lov_prefix || r.rv_domain,
      p_lov_query=> 'select rv_meaning d, rv_low_value r'||unistr('\000a')||
'from   cg_ref_codes'||unistr('\000a')||
'where  rv_domain = '''||r.rv_domain||''''||unistr('\000a')||
'order by 1');
 
  end loop;
end;

That's it.

I will be posting more tips on the Forms to Apex migration.