Query to find profile option values at user and responsibility level

 Query to find profile option values at user and responsibility level

In this post , We will be discuss about Query to find profile option values at user and responsibility level. in Oracle Application , We do set the profile option values at user , site and responsibility  level and based on that levels profile works in oracle application. This below sql query will help to get the profile option values set at different user and responsibility levels. This is one of the useful sql query to extract the complete profile option values  from backend. Here below is the complete details about Query to find profile option values at user and responsibility level.

Query to find profile option values at user and responsibility level

7 Important Table used by Query to find profile option values at user and responsibility level

1.FND_PROFILE_OPTIONS

2.FND_PROFILE_OPTION_VALUES

3.FND_USER

4.FND_APPLICATION

5.FND_RESPONSIBILITY 

6.FND_NODES

7.HR_OPERATING_UNITS

SQL Query to find profile option values at user and responsibility level

Here below is the details sql query , which will help to find out the complete details about oracle profile option values at different application user and responsibility levels.

SELECT po.profile_option_name "NAME",  

decode(to_char(pov.level_id),

'10001', 'SITE',

'10002', 'APP',

'10003', 'RESP',

'10005', 'SERVER',

'10006', 'ORG',

'10004', 'USER', '???') "LEV",

pov.profile_option_value

FROM   FND_PROFILE_OPTIONS po,

FND_PROFILE_OPTION_VALUES pov,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp,

fnd_nodes svr,

hr_operating_units org

WHERE  1 =1

and    po.profile_option_name LIKE '%PROFILE%'

AND    pov.application_id = po.application_id

AND    pov.profile_option_id = po.profile_option_id

AND    usr.user_id (+) = pov.level_value

AND    rsp.application_id (+) = pov.level_value_application_id

AND    rsp.responsibility_id (+) = pov.level_value

AND    app.application_id (+) = pov.level_value

AND    svr.node_id (+) = pov.level_value

AND    org.organization_id (+) = pov.level_value

ORDER BY "NAME", pov.level_id, "VALUE";

Query to find profile option values at user and responsibility level

Query to find profile option values at user and responsibility level