10 Replies Latest reply: Jan 2, 2007 10:00 AM by 493307 RSS

    Menus, submenus, and functions

    493307
      Does anyone know of a procedure or query that can output each menu, submenu, and function for a given responsibility? I can do it with a recursive HTP HTML report but my brain tells me that I'm not up for that this morning.

      Thanks!
        • 1. Re: Menus, submenus, and functions
          61204
          Would be one hell of a script :)

          Sam
          http://www.appsdbablog.com
          • 2. Re: Menus, submenus, and functions
            493307
            Really it shouldn't be that bad. Whatever query is used in the Navigator window is really all I need. I'm sure it's a very complex query/procedure. Then all I have to do is expand it to cover all responsibilities rather than the one you are logged in as.

            Anyone have any ideas? Thanks!
            • 3. Re: Menus, submenus, and functions
              540182
              What is the underlying requirement for doing this? What are you trying to achieve?

              Chung


              ----------
              The Application Management Blog - http://blogs.ittoolbox.com/oracle/cwu
              • 4. Re: Menus, submenus, and functions
                493307
                Chung -

                I'm attempting to get a list of all menus, submenus, and functions available to each responsibility.

                I'm almost certain someone else has done this same thing before. We're trying to get this kind of info to help us with our SOX audit preparations.

                Thanks!
                • 5. Re: Menus, submenus, and functions
                  61204
                  I'm almost certain someone else has done this same thing before
                  I agree with that absolutely.
                  Would be nice to have the script posted here.

                  Sam
                  http://www.appsdbablog.com
                  • 6. Re: Menus, submenus, and functions
                    542380
                    I am looking for something of this sort too.
                    • 7. Re: Menus, submenus, and functions
                      493307
                      Does anyone have access to the Navigator form? It should be pretty easy to grab the SQL from there.
                      • 8. Re: Menus, submenus, and functions
                        450408
                        You can execute "Function Security Function Report" report from your system administrator responsibility.
                        • 9. Re: Menus, submenus, and functions
                          512041
                          Hi,

                          This might help you...but not sure about HTML part.
                          SELECT LEVEL,
                                 (SELECT Lpad(' ',(LEVEL * 2) - 1)
                                         ||Prompt  --prompt
                                  FROM   fnd_Menu_entries_vl
                                  WHERE  Menu_Id = a.Menu_Id
                                         AND Entry_Sequence = a.Entry_Sequence) Prompt,
                                 (SELECT Description
                                  FROM   fnd_Menus_tl
                                  WHERE  Menu_Id = a.Sub_Menu_Id) Sub_Menu_desc,
                                 (SELECT Menu_Name
                                  FROM   fnd_Menus_vl
                                  WHERE  Menu_Id = a.Sub_Menu_Id) Sub_Menu,
                                 (SELECT TYPE
                                  FROM   fnd_Form_Functions_vl
                                  WHERE  Function_Id = a.Function_Id) TYPE,
                                 (SELECT User_Form_Name
                                  FROM   fnd_Form_vl f,
                                         fnd_Form_Functions_vl ff
                                  WHERE  f.Form_Id = ff.Form_Id
                                         AND Function_Id = a.Function_Id
                                         AND f.Application_Id = ff.Application_Id) Form_Name,
                                 (SELECT User_Function_Name
                                  FROM   fnd_Form_Functions_vl
                                  WHERE  Function_Id = a.Function_Id) Form_Functions,
                                 (SELECT Description
                                  FROM   fnd_Menu_entries_tl
                                  WHERE  Menu_Id = a.Menu_Id
                                         AND Entry_Sequence = a.Entry_Sequence) Description
                          FROM   fnd_Menu_entries a
                          CONNECT BY PRIOR a.Sub_Menu_Id = a.Menu_Id
                          START WITH a.Menu_Id = (SELECT Menu_Id
                                                  FROM   fnd_Responsibility_vl r
                                                  WHERE  Responsibility_Name = <B><Your Responsibility></B>)
                          Regards
                          Prashant Pathak
                          • 10. Re: Menus, submenus, and functions
                            493307
                            Can you explain what the CONNECT BY and START WITH are doing? I'm getting an error that a ORA-01427 single-row subquery returns more than one row. I ran the following:

                            SELECT menu_id, responsibility_name
                            FROM fnd_responsibility_vl r
                            WHERE responsibility_name like 'MFSG Administrator';

                            to make sure that the <your responsibility> part was not returning more than one row. Should this work for seeded as well as non-seeded responsibilities? I don't see any reason that it wouldn't but never hurts to check.

                            Thanks!

                            Message was edited by:
                            mateoc15