Query to get responsibility level profile option values

1--Add new subqueries for your profile options
2  SELECT resp_list.resp_name,
3         g.responsibility_name,
4         h.application_name,
5         g.responsibility_key,
6         i.request_group_name,
7         m.MENU_NAME,
8         m.user_menu_name,
9         (SELECT (SELECT L.MEANING User_Type
10                    FROM FND_COMMON_LOOKUPS L
11                   WHERE     L.LOOKUP_TYPE = 'HR_USER_TYPE'
12                         AND L.APPLICATION_ID BETWEEN 800 AND 899
13                         AND L.LOOKUP_CODE = fpov.profile_option_value)
14            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
15           WHERE     fpov.profile_option_id = fpo.profile_option_id
16                 AND USER_PROFILE_OPTION_NAME = 'HR:User Type'
17                 AND fpov.level_value = responsibility_id)
18            "HR:User Type",
19         (SELECT (SELECT NAME
20                    FROM HR_OPERATING_UNITS
21                   WHERE ORGANIZATION_ID = fpov.profile_option_value)
22            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
23           WHERE     fpov.profile_option_id = fpo.profile_option_id
24                 AND USER_PROFILE_OPTION_NAME = 'MO: Default Operating Unit'
25                 AND fpov.level_value = responsibility_id)
26            "MO: Default Operating Unit",
27         (SELECT (SELECT NAME
28                    FROM HR_OPERATING_UNITS
29                   WHERE ORGANIZATION_ID = fpov.profile_option_value)
30            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
31           WHERE     fpov.profile_option_id = fpo.profile_option_id
32                 AND USER_PROFILE_OPTION_NAME = 'MO: Operating Unit'
33                 AND fpov.level_value = responsibility_id)
34            "MO: Operating Unit",
35         (SELECT (SELECT ORGANIZATION_NAME
36                    FROM ORG_ORGANIZATION_DEFINITIONS
37                   WHERE     SYSDATE <= NVL (DISABLE_DATE, SYSDATE)
38                         AND ORGANIZATION_ID = fpov.profile_option_value)
39            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
40           WHERE     fpov.profile_option_id = fpo.profile_option_id
41                 AND USER_PROFILE_OPTION_NAME =
42                        'Service: Inventory Validation Organization'
43                 AND fpov.level_value = responsibility_id)
44            "Service: Inventory Vld Org",
45         (SELECT (SELECT ORGANIZATION_NAME
46                    FROM ORG_ORGANIZATION_DEFINITIONS
47                   WHERE     SYSDATE <= NVL (DISABLE_DATE, SYSDATE)
48                         AND ORGANIZATION_ID = fpov.profile_option_value)
49            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
50           WHERE     fpov.profile_option_id = fpo.profile_option_id
51                 AND USER_PROFILE_OPTION_NAME =
52                        'CSI: Operating Unit Restriction'
53                 AND fpov.level_value = responsibility_id)
54            "CSI: Operating Unit Restr"
55    FROM apps.fnd_responsibility_vl g,
56         apps.fnd_application_vl  h,
57         apps.fnd_request_groups  i,
58         fnd_menus_vl             m,
59         (SELECT 'Responsibility 1 Name' resp_name FROM DUAL
60          UNION
61          SELECT 'Responsibility 2 Name' resp_name FROM DUAL) resp_list
62   WHERE     h.application_id(+) = g.application_id
63         AND g.request_group_id = i.request_group_id(+)
64         AND g.menu_id = m.menu_id(+)
65         AND g.responsibility_name(+) = resp_list.resp_name
66ORDER BY 1;