add

About Me

My photo
Oracle Apps - Techno Functional consultant

Sunday, July 31

PROFILE OPTIONS: COMPARING LEVEL VALUES

SELECT a.user_profile_option_name,
a.profile_option_value,
b.user_profile_option_name,
b.profile_option_value
FROM ((SELECT c1.responsibility_name,
c2.user_profile_option_name,
decode(level_id,10001,'Site', 10002,'Appl', 10003,'Resp',
10004,'User', 10005,'Server', 10006,'Organization', level_id) "Level",
profile_option_value,
level_value lvl_val
FROM fnd_profile_option_values a1,
fnd_profile_options b1,
fnd_responsibility_tl c1,
fnd_profile_options_tl c2
WHERE a1.profile_option_id = b1.profile_option_id
AND c1.responsibility_id = nvl(a1.level_value,c1.responsibility_id)
AND c1.responsibility_name = 'XXXX'
and b1.profile_option_name = c2.profile_option_name
and c2.language = 'US'
AND c1.language = 'US')) a,
(SELECT c1.responsibility_name,
c2.user_profile_option_name,
decode(level_id,10001,'Site', 10002,'Appl', 10003,'Resp',
10004,'User', 10005,'Server', 10006,'Organization', level_id) "Level",
profile_option_value,
level_value lvl_val
FROM fnd_profile_option_values a1,
fnd_profile_options b1,
fnd_responsibility_tl c1,
fnd_profile_options_tl c2
WHERE a1.profile_option_id = b1.profile_option_id
AND c1.responsibility_id = nvl(a1.level_value,c1.responsibility_id)
AND c1.responsibility_name = 'XXXXX'
and b1.profile_option_name = c2.profile_option_name
and c2.language = 'US'
AND c1.language = 'US') b
WHERE a.user_profile_option_name = b.user_profile_option_name (+)
AND (a.profile_option_value != nvl(b.profile_option_value,a.profile_option_value)
OR b.profile_option_value IS NULL);

No comments: