Profile Options for a User

[big]

Hi, On 11.5.10.2 Is there any query to have such a result which is the profile option values for a specific user: Thanks and regards.

[ErmanArslansOracleBlog]

The following is query the values using the profile name .. However; you can modify it to query the values for a specific user. Basically you will remove that condition and add a condition for querying according to the user name.. fu.user_name.. fnd_user = fu in this query.

SELECT
fpo.user_profile_option_name
,fpo.profile_option_name
,site.profile_option_value site_val
,r.responsibility_name resp_name
,resp.profile_option_value resp_val
,fu.user_name
,usr.profile_option_value user_val
FROM fnd_profile_options_vl fpo
,fnd_profile_option_values site
,fnd_profile_option_values resp
,fnd_responsibility_vl r
,fnd_profile_option_values usr
,fnd_user fu
WHERE 1=1
AND UPPER( fpo.user_profile_option_name) LIKE 'ICX%'    -- Like ICX for example
AND site.profile_option_id(+) = fpo.profile_option_id
AND site.level_id(+) = 10001
AND resp.profile_option_id(+) = fpo.profile_option_id
AND r.responsibility_id (+) = resp.level_value
AND resp.level_id(+) = 10003
AND usr.profile_option_id(+) = fpo.profile_option_id
AND usr.level_id(+) = 10004
AND fu.user_id(+) = usr.level_value
ORDER BY 1

[big]

Thank you Erman.

[big]

Hi,
Your code brings only SYSADMIN as user.

And when I filter using:

AND fu.user_name='user1'

It selects no row:

no row selected.

What is wrong with my filter?

Thanks and Regards.

[ErmanArslansOracleBlog]

We don't have a 11.5.10.2 environment to test this, but we tested the following in a EBS 12.1 env and it returns the values as expected..
Replace <YOUR_USER_NAME> and "USER_PROFILE_OPTION_NAME) LIKE UPPER ('ICX%'))" according to your needs and retry.

SELECT USR.USER_NAME,
       P.PROFILE_OPTION_NAME SHORT_NAME,
       N.USER_PROFILE_OPTION_NAME NAME,
       DECODE (
          V.LEVEL_ID,
          10001, 'Site',
          10002, 'Application',
          10003, 'Responsibility',
          10004, 'User',
          10005, 'Server',
          10006, 'Org',
          10007, DECODE (
                    TO_CHAR (V.LEVEL_VALUE2),
                    '-1', 'Responsibility',
                    DECODE (TO_CHAR (V.LEVEL_VALUE),
                            '-1', 'Server',
                            'Server+Resp')),
          'UnDef')
          LEVEL_SET,
       DECODE (
          TO_CHAR (V.LEVEL_ID),
          '10001', '',
          '10002', APP.APPLICATION_SHORT_NAME,
          '10003', RSP.RESPONSIBILITY_KEY,
          '10004', USR.USER_NAME,
          '10005', SVR.NODE_NAME,
          '10006', ORG.NAME,
          '10007', DECODE (TO_CHAR (V.LEVEL_VALUE2),
                           '-1', RSP.RESPONSIBILITY_KEY,
                           DECODE (TO_CHAR (V.LEVEL_VALUE),
                                   '-1', (SELECT NODE_NAME
                                            FROM FND_NODES
                                           WHERE NODE_ID = V.LEVEL_VALUE2),
                                      (SELECT NODE_NAME
                                         FROM FND_NODES
                                        WHERE NODE_ID = V.LEVEL_VALUE2)
                                   || '-'
                                   || RSP.RESPONSIBILITY_KEY)),
          'UnDef')
          "CONTEXT",
       V.PROFILE_OPTION_VALUE VALUE
  FROM FND_PROFILE_OPTIONS P,
       FND_PROFILE_OPTION_VALUES V,
       FND_PROFILE_OPTIONS_TL N,
       FND_USER USR,
       FND_APPLICATION APP,
       FND_RESPONSIBILITY RSP,
       FND_NODES SVR,
       HR_OPERATING_UNITS ORG
 WHERE  P.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID(+)
         AND P.PROFILE_OPTION_NAME = N.PROFILE_OPTION_NAME
         AND UPPER (P.PROFILE_OPTION_NAME) IN (SELECT PROFILE_OPTION_NAME
                                                 FROM FND_PROFILE_OPTIONS_TL
                                                WHERE UPPER (
                                                         USER_PROFILE_OPTION_NAME) LIKE
                                                         UPPER ('ICX%'))
         AND USR.USER_ID(+) = V.LEVEL_VALUE
         AND RSP.APPLICATION_ID(+) = V.LEVEL_VALUE_APPLICATION_ID
         AND RSP.RESPONSIBILITY_ID(+) = V.LEVEL_VALUE
         AND APP.APPLICATION_ID(+) = V.LEVEL_VALUE
         AND SVR.NODE_ID(+) = V.LEVEL_VALUE
         AND ORG.ORGANIZATION_ID(+) = V.LEVEL_VALUE
         and usr.user_name = '<YOUR_USER_NAME>'
ORDER BY user_name,
         SHORT_NAME,
         USER_PROFILE_OPTION_NAME,
         LEVEL_ID,
         LEVEL_SET;