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.

ADF: Dynamic Menu based on roles in ADF Web Application

This post explains how to create dynamic menus based on the roles/user types  in the ADF Web Application. 

When I wanted to create a dynamic menu for my application, I googled and found lots of videos/blogs on dynamic menus. In most of them, the dynamic behaviour was for the second level menu. Here I will explain how to achieve this for the first level menu itself.

1. Create the View Activities in the Task Flow
2. Generate the menu model
3. Bind the item nodes in the menu model with an EL
4. Use Navigation Pane to display the menu in the page

You can refer this video for creating a menu model in ADF Web Application.

• Create the view activities in the adfc-config.xml. Right click and select 'Create and Update ADF Menu'.

• Now the menu model is created. Now we can set the properties of each item Node. 

Here we set the rendered property of each item Node. These settings will reflect in the pages in the runtime. We can set the rendered property in various ways. Below mentioned are the 3 options which I am aware of.

   Option 1 : Based on Specific Role. 

      ex:- #{securityContext.userInRole['MANAGER-ROLE']}

   Option 2: Based on current SecurityContext, whether the user has access to the corresponding page Definition.

      ex:- #{securityContext.regionViewable['view.pageDefs.managersPageDef']}

   Option 3: Based on custom managedBean property. You can define a boolean variable in your Managed Bean                     (session scope) and use that as an EL. You need to set this variable after the login Activity.

      ex:- #{loginBean.dispManagerMenu}

Note:- ADF Security has to be configured for option1 and option2. The below screen shots are based in the option1.

• Create a ADF page template and add a Navigation Pane.
  
     Note:- Make sure that you set the rendered property for the navigation item. This is the property which                   dynamically show/hide the menu during runtime.
• Create the pages using this template.
• ADF Security is configured as below.

                    Manager User --> Manager Enterprise Role --> Manager Role

                    Department User --> Department Enterprise Role --> Department Role

                     Manager Role has access to Employees and Managers.

                     Department Role has access to Employees and Departments.

• Now lets run the page. 

           Login as MANAGER/Welcome1

           Login as DEPARTMENT/Welcome1

Sample application built using Jdev 12.1.3 can be downloaded from here.

Ref: http://www.oracle.com/technetwork/developer-tools/adf/learnmore/48-sitemenu-protection-169190.pdf

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