Post a Comment On: Oracle Help

In Oracle Apps you don't have a standard report that lists all the functions that are part of a particular Menu. Though in Application developer>Menu you can see in Front end the List of Functions, but using this in the code is not straight forward.
Below is the code that was available from metalink and modified to generate the list of Function ids for a specific Menu and store in Oracle database custom table.

Note: script ignores any exclusions setup at Responsibility level.

Database Object creation script:
CREATE OR REPLACE TYPE  STACK_REC AS OBJECT
(
  SUB_MENU_ID NUMBER,
  PROMPT      VARCHAR(500) ,
  T_LEVEL       NUMBER ) ;


CREATE OR REPLACE TYPE STACK_REC_ARRAY AS VARRAY(1000) OF STACK_REC ;

CREATE OR REPLACE  TYPE TRA_STACK AS OBJECT
( MAX_SIZE INTEGER ,
  TOP      INTEGER ,
  POSITION STACK_REC_ARRAY ,
  MEMBER PROCEDURE INITIALIZE ,
  MEMBER FUNCTION FULL RETURN BOOLEAN ,
  MEMBER FUNCTION EMPTY RETURN BOOLEAN ,
  MEMBER PROCEDURE PUSH(STACK_REC_IN_PAR IN STACK_REC),
  MEMBER PROCEDURE POP(STACK_REC_OUT_PAR OUT  STACK_REC)
) ;

CREATE OR REPLACE TYPE BODY TRA_STACK AS

MEMBER PROCEDURE INITIALIZE IS
BEGIN
 TOP:=0;
 POSITION :=STACK_REC_ARRAY(NULL);
 MAX_SIZE :=POSITION.LIMIT ;
 POSITION.EXTEND(MAX_SIZE -1,1);
END INITIALIZE ;

MEMBER FUNCTION FULL RETURN BOOLEAN IS
BEGIN
 RETURN (TOP=MAX_SIZE);
END FULL ;

MEMBER FUNCTION EMPTY RETURN BOOLEAN IS
BEGIN
 RETURN (TOP=0);
END EMPTY ;

MEMBER PROCEDURE PUSH(STACK_REC_IN_PAR IN STACK_REC) IS
BEGIN
IF NOT FULL THEN
 TOP := TOP+ 1;
 POSITION(TOP) :=STACK_REC_IN_PAR ;
ELSE
 RAISE_APPLICATION_ERROR(-20101,'STACK OVERFLOW');
END IF;
END PUSH ;

MEMBER PROCEDURE POP(STACK_REC_OUT_PAR OUT  STACK_REC)
IS
BEGIN
IF NOT EMPTY THEN
  STACK_REC_OUT_PAR :=POSITION(TOP) ;
  TOP := TOP -1 ;
ELSE
  RAISE_APPLICATION_ERROR(-20102,'STACK UNDERFLOW');
END IF;
END POP;

END ;
/



Table creation script: To store the Tree structure
Create table tree_tab
(
col1 varchar2(200),
col2 varchar2(200),
col3 varchar2(200),
col4 varchar2(200),
col5 varchar2(200),
col6 varchar2(200),
col7 varchar2(200),
col8 varchar2(200),
col9 varchar2(200),
col10 varchar2(200),
col11 varchar2(200),
col12 varchar2(200),
col13 varchar2(200),
col14 varchar2(200),
col15 varchar2(200)
)
/


Oracle apps database Procedure to generate the structure:
CREATE OR REPLACE PROCEDURE GENERATE_TREE (P_ROOT_MENU_ID IN NUMBER) IS
    Temp_stack_rec stack_rec ;
    Temp_id_var number ;
    M_id_var number ;
    I number ;
    N number ;
    Sql_str varchar2(200);
    Stack_obj TRA_STACK ;
    Cursor m_cursor (p_menu_id NUMBER) is
    Select A.menu_id , B.sub_menu_id ,b.function_id prompt ,B.entry_sequence from fnd_menus A ,
    Fnd_menu_entries B ,
    Fnd_menu_entries_tl C
    Where
    A.menu_id = B.menu_id and
    B.menu_id=C.menu_id  and
    B.entry_sequence=C.entry_sequence and
    C.Language='US' and
    A.menu_id=p_menu_id ;

Begin
    Stack_obj:=TRA_STACK(NULL,NULL,NULL);
    Stack_obj.initialize ;
    Temp_stack_rec := stack_rec(NULL,NULL,NULL);
    M_id_var := P_ROOT_MENU_ID ;
    N :=0;
    <<label_outer>>
    I:=0;
    N:=N+1;


    For s_tab_rec in m_cursor(M_id_var) loop
        I := I+1;   
        If I=1 then

    Sql_str :=   'insert into tree_tab' || '(' || concat('col',N) || ')' ||  ' values ' || '(' || concat('''',s_tab_rec.prompt) || '''' ||  ')' ;
    Execute immediate sql_str ;
         If s_tab_rec.sub_menu_id is NULL then
              Temp_id_var := NULL ;
         Else
              Temp_id_var := s_tab_rec.sub_menu_id ;
         End if ;
      Else
          Temp_stack_rec.sub_menu_id :=s_tab_rec.sub_menu_id ;
          Temp_stack_rec.prompt := s_tab_rec.prompt ;
          Temp_stack_rec.T_level := N ;
          Stack_obj.push(Temp_stack_rec);
      End if ;
    End loop ;
    If Temp_id_var is not NULL then
       M_id_var := Temp_id_var ;
       Goto label_outer ;
    Else
       <<label_inner>>
           if Stack_obj.empty=true then
         return ;
           else
         Stack_obj.pop(Temp_stack_rec) ;
           end if ;
         if Temp_stack_rec.sub_menu_id is not NULL then
             Sql_str :=   'insert into tree_tab'    || '(' ||      concat('col',Temp_stack_rec.T_level) || ')' ||  ' values  ' || '(' || concat('''',Temp_stack_rec.prompt) || '''' ||  ')' ;
             Execute immediate sql_str ;
             M_id_var :=Temp_stack_rec.sub_menu_id ;
             N := Temp_stack_rec.T_level ;
             Goto label_outer ;
         Else
             Sql_str :=   'insert into tree_tab'    || '(' ||      concat('col',Temp_stack_rec.T_level) || ')' ||  ' values  ' || '(' || concat('''',Temp_stack_rec.prompt) || '''' ||  ')' ;
             Execute immediate sql_str ;
             N := Temp_stack_rec.T_level ;
             Goto label_inner ;
         End if ;
    End if ;
    commit ;
End    ;
/



Script to Call the Procedure:
Begin
GENERATE_TREE (991);
end;