Profile option query in oracle apps r12

Profile option query in oracle apps r12

This query helps to Extract the Profiles options and their values in Oracle apps R12. We will get all the details of the Profile options and their values with the Level of settings. In which level this profile has set.

 

 

query to find profile option values at user and responsibility level

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";