Oracle Apps: Helpful Queries on FND Users / Roles / Menus / Responsibilities

Some queries which will be helpful when querying on User / Responsibility / Menu in Oracle Apps (eBusiness Suite).

#1. Query to get the Menu/Submenu/Function list (prompts) for a responsibility as you see when you login.

 SELECT (lpad(' ', (LEVEL-1) * 5, ' ') || LEVEL)                        lvl  
       ,(lpad(' ', (LEVEL-1) * 5, ' ') || x.entry_sequence)             entry_sequence  
       ,(lpad(' ', (LEVEL-1) * 5, ' ') || x.prompt)                     prompt  
       ,(SELECT (a.user_menu_name || '[' || a.menu_name || ']')  
         FROM   fnd_menus_vl a  
         WHERE  a.menu_id = x.sub_menu_id)                              menu_name  
       ,(SELECT (a.user_function_name || '[' || a.function_name || ']')  
         FROM   fnd_form_functions_vl a  
         WHERE  a.function_id = x.function_id)                          function_name  
       ,x.sub_menu_id  
       ,x.function_id       
       ,x.grant_flag  
 FROM apps.fnd_menu_entries_vl x  
     ,(SELECT a.menu_id  
             ,a.responsibility_id  
       FROM  apps.fnd_responsibility_vl a  
       WHERE UPPER(a.responsibility_name) = UPPER('System Administrator')) y     -- Replace the responsibility based on your requirement  
 START WITH x.menu_id = y.menu_id  
       AND x.prompt IS NOT NULL -- Comment this if you want all the Functions/Menus  
       --Menu/Function Exclusions  
       AND NVL(x.menu_id,-1)     NOT IN (SELECT b.action_id 
                                         FROM   apps.fnd_resp_functions b   
                                         WHERE  b.responsibility_id = y.responsibility_id 
                                         AND    b.rule_type         = 'M')  
       AND NVL(x.sub_menu_id,-1) NOT IN (SELECT b.action_id 
                                         FROM   apps.fnd_resp_functions b   
                                         WHERE  b.responsibility_id = y.responsibility_id 
                                         AND    b.rule_type         = 'M')  
       AND NVL(x.function_id,-1) NOT IN (SELECT b.action_id 
                                         FROM   apps.fnd_resp_functions b   
                                         WHERE  b.responsibility_id = y.responsibility_id 
                                         AND    b.rule_type         = 'F')  
 CONNECT BY PRIOR x.sub_menu_id = x.menu_id  
            AND  x.prompt IS NOT NULL -- Comment this if you want all the Functions/Menus  
            --Menu/Function Exclusions  
            AND  NVL(x.sub_menu_id,-1) NOT IN (SELECT b.action_id 
                                               FROM   apps.fnd_resp_functions b   
                                               WHERE  b.responsibility_id = y.responsibility_id 
                                               AND    b.rule_type            = 'M')  
            AND  NVL(x.function_id,-1) NOT IN (SELECT b.action_id 
                                               FROM   apps.fnd_resp_functions b   
                                               WHERE  b.responsibility_id = y.responsibility_id 
                                               AND    b.rule_type         = 'F')  
 ORDER SIBLINGS BY x.entry_sequence;

#2. Query to get all the Responsibilities associated with a specific User / all the users which have a specific Responsibility 

 SELECT resp_type  
       ,user_id  
       ,user_name  
       ,responsibility_id  
       ,responsibility_name  
       ,start_date  
       ,end_date  
       ,application_short_name  
       ,application_name  
 FROM  (SELECT 'DIRECT' resp_type  
              ,fu.user_id  
              ,fu.user_name  
              ,resp.responsibility_id  
              ,resp.responsibility_name  
              ,frd.start_date  
              ,frd.end_date  
              ,app.application_short_name  
              ,app.application_name  
        FROM  fnd_user                    fu  
             ,fnd_user_resp_groups_direct frd  
             ,fnd_responsibility_vl       resp  
             ,fnd_application_vl          app  
        WHERE fu.user_id            = frd.user_id  
        AND   frd.responsibility_id = resp.responsibility_id  
        AND   resp.application_id   = app.application_id  
        UNION ALL  
        SELECT 'INDIRECT' resp_type  
              ,fu.user_id  
              ,fu.user_name  
              ,resp.responsibility_id  
              ,resp.responsibility_name  
              ,fri.start_date  
              ,fri.end_date  
              ,app.application_short_name  
              ,app.application_name  
        FROM  fnd_user                      fu  
             ,fnd_user_resp_groups_indirect fri  
             ,fnd_responsibility_vl         resp  
             ,fnd_application_vl            app  
        WHERE fu.user_id           = fri.user_id  
        AND  fri.responsibility_id = resp.responsibility_id  
        AND  resp.application_id   = app.application_id)  
 WHERE 1=1  
 AND   user_name           = 'AJTEST'                -- Comment this if you need all user of a responsibility  
 AND   responsibility_name = 'System Administrator'; -- Comment this if you need all responsibilities of a user  

Feel free to point out if anything is missing/wrong in this blog.