Sunday, December 5, 2010

Profile Option Values

Profile Option Details

po.profile_option_name as name
,decode(to_char(pov.level_id),'10001','SITE','10002','APP','10003','RESP','10005','SERVER','10006','ORG','10004','USER', '???') as "LEVEL"
, decode(to_char(pov.level_id),'10001','','10002', app.application_short_name,'10003', rsp.responsibility_key,'10005', svr.node_name,'10006',,'10004', usr.user_name,'???') as context
, pov.profile_option_value as value
fnd_profile_options_vl po
, fnd_profile_option_values pov
, fnd_user usr
, fnd_application app
, fnd_responsibility rsp
, fnd_nodes svr
, hr_operating_units org
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 1, pov.level_id, 5

Note- Initialize your SQL session before running above query

Profile option with Modification Date and User

SELECT t.user_profile_option_name, profile_option_value,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.