Monday, December 14, 2009

SQL Report - Submenu and Function Details (Responsibility Level)

This QUERY will retrieve the Function(s) and Submenu(s) which those all are attached to the given input responsibility.

SELECT        NVL2 (fme.sub_menu_id, '+', '-')
           || LPAD (NVL ((SELECT prompt
                            FROM apps.fnd_menu_entries_vl
                           WHERE menu_id = fme.menu_id
                             AND sub_menu_id = fme.sub_menu_id
                             AND fme.function_id IS NULL),
                         (SELECT prompt
                            FROM apps.fnd_menu_entries_vl
                           WHERE menu_id = fme.menu_id
                             AND function_id = fme.function_id
                             AND fme.sub_menu_id IS NULL)
                        ),
                      LENGTH (NVL ((SELECT prompt
                                      FROM apps.fnd_menu_entries_vl
                                     WHERE menu_id = fme.menu_id
                                       AND sub_menu_id = fme.sub_menu_id
                                       AND fme.function_id IS NULL),
                                   (SELECT prompt
                                      FROM apps.fnd_menu_entries_vl
                                     WHERE menu_id = fme.menu_id
                                       AND function_id = fme.function_id
                                       AND fme.sub_menu_id IS NULL)
                                  )
                             )
                    + (LEVEL * 5),
                    '-'
                   ) tree_structure
      FROM apps.fnd_menu_entries fme
START WITH fme.menu_id =
              (SELECT menu_id
                 FROM apps.fnd_responsibility fr,
                      apps.fnd_responsibility_tl frt
                WHERE fr.responsibility_id = frt.responsibility_id
                  AND frt.responsibility_name = &responsibility_name)  -- 'Application Developer'
CONNECT BY PRIOR fme.sub_menu_id = fme.menu_id

Query Output:

TREE_STRUCTURE
+-----Flexfield
+----------Key
----------------Register
----------------Segments
----------------Aliases
----------------CrossValidation
----------------Values
----------------Groups
----------------Accounts
+----------Descriptive
----------------Register
----------------Segments
----------------Values
-----------Flexfield Test
+-----Concurrent
-----------Program
-----------Executable
-----------Library
+-----Application
-----------Register
-----------Form
-----------Function
-----------Menu
-----------Messages
+----------Database
----------------Table
----------------Sequence
----------------View
+----------Lookups
----------------Application Object Library
----------------Common
----------------CE lookups
----------------GCS Lookups
----------------IA Lookups
----------------igi lookups
----------------XLA Lookups
----------------XLE Lookups
----------------FUN Lookups
----------------Service Lookups
+----------Validation
----------------Set
----------------Values
------Profile
+-----Attachments
-----------Document Entities
-----------Document Categories
-----------Attachment Functions
-----------Repositories
+-----Other
+----------Requests
----------------Run
----------------View
----------------Set
-----------Profile
-----------Concurrent
-----------Change Organization
-----------Running Jobs
+----------EDI
----------------EDI Setup
----------------EDI Data

2 comments: