Oracle Responsibility: Menu, Function exclusion query

/* Formatted on 8/22/2017 11:51:57 AM (QP5 v5.114.809.3010) */
SELECT   res.responsibility_name,
         app.application_name,
         res.responsibility_key,
         res.description,
         res.start_date,
         res.end_date,
         dat.data_group_name,
         apd.application_name,
         mnu.menu_name,
         req.request_group_name,
         apr.application_name,
         DECODE (exc.rule_type, 'F', 'Function', 'M', 'Menu', rule_type)
            Exclusion_Type,
         DECODE (exc.rule_type,
                 'Ex  F', (SELECT   function_name || ',' || description
                         FROM   fnd_form_functions_vl fnc
                        WHERE   fnc.function_id = exc.action_id),
                 'Ex  M', (SELECT   menu_name || ',' || description
                         FROM   fnd_menus_vl imn
                        WHERE   imn.menu_id = exc.action_id),
                 TO_CHAR (exc.action_id))
            Excluded_Menu_Or_func
  FROM   apps.fnd_responsibility_vl res,
         apps.fnd_application_vl app,
         apps.fnd_data_groups dat,
         apps.fnd_menus_vl mnu,
         apps.fnd_request_groups req,
         apps.fnd_application_vl apd,
         apps.fnd_application_vl apr,
         apps.fnd_resp_functions exc
 WHERE       res.application_id = app.application_id
         AND res.data_group_id = dat.data_group_id
         AND res.data_group_application_id = apd.application_id
         AND res.menu_id = mnu.menu_id
         AND req.request_group_id = res.request_group_id
         AND req.application_id = res.group_application_id
         AND apr.application_id = req.application_id
         AND exc.application_id = res.application_id
         AND exc.responsibility_id = res.responsibility_id
         AND res.RESPONSIBILITY_NAME like 'Cash Management';

Output:-

query gives all function names under a menu considering submenus's in oracle apps

/* Formatted on 11/14/2016 3:45:04 PM (QP5 v5.114.809.3010) */
  SELECT   DISTINCT
           fmep.menu_id,
           DECODE (
              fmep.function_id,
              NULL,
              DECODE (
                 fmec.function_id,
                 NULL,
                 DECODE (fmec1.function_id, NULL, 'No Func', fmec1.function_id),
                 fmec.function_id
              ),
              fmep.function_id
           )
              funcid,
           fff.user_function_name,
           fff.description
    FROM   fnd_form_functions_tl fff,
           fnd_menu_entries fmec1,
           fnd_menu_entries fmec,
           fnd_menu_entries fmep
   WHERE       fmep.menu_id = (SELECT   menu_id
                                 FROM   fnd_menus
                                WHERE   menu_name = 'INV_NAVIGATE' --Change the menu according to your requirement
                                        AND ROWNUM = 1)
           AND fmep.sub_menu_id = fmec.menu_id(+)
           AND fmec.sub_menu_id = fmec1.menu_id(+)
           AND fff.function_id =
                 DECODE (
                    fmep.function_id,
                    NULL,
                    DECODE (
                       fmec.function_id,
                       NULL,
                       DECODE (fmec1.function_id,
                               NULL, -999,
                               fmec1.function_id),
                       fmec.function_id
                    ),
                    fmep.function_id
                 )
ORDER BY   DECODE (
              fmep.function_id,
              NULL,
              DECODE (
                 fmec.function_id,
                 NULL,
                 DECODE (fmec1.function_id,
                         NULL, 'No Func',
                         fmec1.function_id),
                 fmec.function_id
              ),
              fmep.function_id
           )

Appliocation, Menus , Submenus assigned to User oracle sql query

/* Formatted on 11/14/2016 2:31:06 PM (QP5 v5.114.809.3010) */
  SELECT   fu.USER_NAME,
           r.responsibility_name,
           f.user_menu_name,
           me.prompt "Main_Prompt",
           me.description "Main_Desc",
           metl.prompt "sub_prompt",
           metl.description "sub_desc"
    FROM   apps.fnd_user fu,
           apps.fnd_user_resp_groups urg,
           apps.fnd_menus_vl f,
           apps.fnd_responsibility fr,
           apps.fnd_responsibility_tl r,
           apps.fnd_menus fm,
           apps.fnd_menu_entries fme,
           apps.fnd_menu_entries_tl me,
           apps.fnd_menus fm2,
           apps.fnd_menu_entries fme2,
           apps.fnd_menu_entries_tl metl
   WHERE   1 = 1 AND fr.menu_id = f.menu_id AND fu.user_id = urg.user_id
           AND (urg.responsibility_id = fr.responsibility_id
                AND urg.responsibility_application_id = fr.application_id)
           AND (fr.responsibility_id = r.responsibility_id
                AND fr.application_id = r.application_id)
           AND (fme.menu_id = me.menu_id
                AND fme.entry_sequence = me.entry_sequence)
           AND fr.menu_id = fm.menu_id
           AND fm.menu_id = fme.menu_id
           AND fme.sub_menu_id = fm2.menu_id
           AND fm2.menu_id = fme2.menu_id
           AND (fme2.menu_id = metl.menu_id
                AND fme2.entry_sequence = metl.entry_sequence)
                 and fr.RESPONSIBILITY_ID=51348
           AND metl.language = 'US'
           AND me.language = 'US'
ORDER BY   urg.description

How To Show / Hide Diagnostics Menu Entry

At last moment you got a requirement to disable a particular field on a form. As you tried to navigate to access forms personalization what you see is 

There is no 'Diagnostics' option to navigate to Help -> Diagnostics.

Well the Diagnostics menu entry is controlled by profile option "Hide Diagnostics menu entry"

Navigate to System Administrator -> Profile -> System

Query for your user / responsibility for which you want to provide access.

Set the value at respective level to 

NO = If you want to show Diagnostics menu

YES = If you want to hide Diagnostics menu

Change the responsibility to take the profile option change into effect and try to access diagnostics menu.

Now we are able to see the diagnostics entry.

Query to get the Menu,Submenu and function details For Oracle Menus

/* Formatted on 6/24/2014 10:57:14 AM (QP5 v5.115.810.9015) */
SELECT second.application_id "App ID",
       second.application_name "App Name",
       second.responsibility_id "Resp ID",
       second.responsibility_name "Responsibility",
       second.menu_id "Menu ID",
       second.user_menu_name "Main Menu Name",
       second.entry_sequence "Seq",
       second.prompt "Prompt",
       second.function_id "Function ID",
       second.user_function_name "Function",
       second.func_descrip "Function Descrip",
       second.sub_menu_id "SubMenu ID",
       second.sub_menu_name "SubMenu Name",
       second.sub_seq "Sub Seq",
       second.sub_prompt "SubPrompt",
       second.sub_func_id "SubFunction ID",
       second.sub_func "SubFunction",
       second.sub_func_descrip "SubFunction Descrip",
       second.sub_sub_menu_id "Sub-SubMenu ID",
       second.grant_flag "Grant Flag",
       second.resp_end_date "Resp End Date",
       DECODE (exc.rule_type,
               'F',
               (SELECT 'Ex F: ' || exc.action_id
                FROM fnd_form_functions_vl fnc
                WHERE fnc.function_id = exc.action_id
                      AND second.function_id = exc.action_id)
       )
          excluded_function,
       DECODE (exc.rule_type,
               'F',
               (SELECT 'Ex SF: ' || exc.action_id
                FROM fnd_form_functions_vl fnc
                WHERE fnc.function_id = exc.action_id
                      AND second.sub_func_id = exc.action_id)
       )
          excluded_sub_function,
       DECODE (exc.rule_type,
               'M',
               (SELECT 'Ex M: ' || exc.action_id
                FROM fnd_form_functions_vl fnc
                WHERE fnc.function_id = exc.action_id
                      AND second.menu_id = exc.action_id)
       )
          excluded_menu,
       DECODE (exc.rule_type,
               'M',
               (SELECT 'Ex SM: ' || exc.action_id
                FROM fnd_form_functions_vl fnc
                WHERE fnc.function_id = exc.action_id
                      AND second.sub_menu_id = exc.action_id)
       )
          excluded_sub_menu,
       DECODE (exc.rule_type,
               'M',
               (SELECT 'Ex SSM: ' || exc.action_id
                FROM fnd_form_functions_vl fnc
                WHERE fnc.function_id = exc.action_id
                      AND second.sub_sub_menu_id = exc.action_id)
       )
          excluded_sub_sub_menu
FROM    (SELECT FIRST.application_id,
                FIRST.application_name,
                FIRST.responsibility_id,
                FIRST.responsibility_name,
                FIRST.end_date AS resp_end_date,
                FIRST.menu_id,
                FIRST.user_menu_name,
                FIRST.entry_sequence,
                FIRST.prompt,
                FIRST.function_id,
                ffft.user_function_name,
                ffft.description AS func_descrip,
                FIRST.sub_menu_id,
                fmv2.user_menu_name AS sub_menu_name,
                fme2.entry_sequence AS sub_seq,
                fmet2.prompt AS sub_prompt,
                fme2.function_id AS sub_func_id,
                ffft2.user_function_name AS sub_func,
                ffft2.description AS sub_func_descrip,
                fme2.sub_menu_id AS sub_sub_menu_id,
                FIRST.grant_flag
         FROM (SELECT fat.application_id,
                      fat.application_name,
                      fr.responsibility_id,
                      frt.responsibility_name,
                      fr.end_date,
                      fr.menu_id,
                      fmv.user_menu_name,
                      fme.entry_sequence,
                      fmet.prompt,
                      fme.sub_menu_id,
                      fme.function_id,
                      fme.grant_flag
               FROM apps.fnd_application_tl fat,
                    apps.fnd_responsibility fr,
                    apps.fnd_menus_vl fmv,
                    apps.fnd_responsibility_tl frt,
                    apps.fnd_menu_entries fme,
                    apps.fnd_menu_entries_tl fmet
               --joins and constant selection
               WHERE     fat.application_id = fr.application_id(+)
                     AND fr.menu_id = fmv.menu_id(+)
                     AND fr.responsibility_id = frt.responsibility_id(+)
                     AND fr.menu_id = fme.menu_id(+)
                     AND fme.menu_id = fmet.menu_id(+)
                     AND fme.entry_sequence = fmet.entry_sequence(+)
                     AND fmet.language = 'US'
                     AND fat.application_id = &appid
               ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST ---for application, responsibility and main menu info
                                                                    ,
              apps.fnd_menus_vl fmv2                       ---for submenu info
                                    ,
              apps.fnd_menu_entries fme2,
              apps.fnd_menu_entries_tl fmet2,
              apps.fnd_form_functions_tl ffft             ---for function info
                                             ,
              apps.fnd_form_functions_tl ffft2         ---for subfunction info
         --left outer joins keep original records and add any sub menu and function info
         WHERE     FIRST.function_id = ffft.function_id(+)
               AND FIRST.sub_menu_id = fmv2.menu_id(+)
               AND FIRST.sub_menu_id = fme2.menu_id(+)
               AND fme2.menu_id = fmet2.menu_id(+)
               AND fme2.entry_sequence = fmet2.entry_sequence(+)
               AND fme2.function_id = ffft2.function_id(+)
         ORDER BY 1,
                  2,
                  3,
                  4,
                  5,
                  6,
                  7,
                  8,
                  9,
                  10,
                  11,
                  12,
                  13,
                  14,
                  15,
                  16,
                  17,
                  18,
                  19,
                  20,
                  21) second            -- adds any sub menu and function info
     LEFT OUTER JOIN
        apps.fnd_resp_functions exc                          ---for exclusions
     ON (second.application_id = exc.application_id
         AND second.responsibility_id = exc.responsibility_id
         AND (   second.function_id = exc.action_id
              OR second.sub_func_id = exc.action_id
              OR second.menu_id = exc.action_id
              OR second.sub_menu_id = exc.action_id
              OR second.sub_sub_menu_id = exc.action_id))
ORDER BY 1,
         2,
         3,
         4,
         5,
         6,
         7,
         8,
         9,
         10,
         11,
         12,
         13,
         14,
         15,
         16,
         17,
         18,
         19,
         20,
         21;

Create Menu and responsibility to attached the User in oracle apps

Create Menu Based on the oracle form :-

Inventory --> item cost --> oracle form based menu creation process

To Create Menu

System administrator --> Application -->   Menu   To Create Responsibility System administrator --> Security --> Responsibility --> Define   Create Responsibility and attach the Menu:   Create User:- System administrator --> Security --> User --> Define  create responsibility and attached the user   user created user and attached the  Responsibility Go to EBS