SQL Query to Get Diffrence in Values for Profile Options assigned on USER Level

I got this Query from Gareth Roberts BLOG …Very Very Useful during implementation specially if there are some custom profile options .

I used to work for a home appliances Manufacturing Organization . we used some custom profile options to setup level of security for Sales Manage, Salesman and Warehouse User. Some Time we faced issues and the and discovered that it’s wrong profile option assignment.

/* Formatted on 2009/08/19 08:41 (Formatter Plus v4.8.8) */
SELECT *
FROM (WITH prof_di AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘SYSADMIN’), /*User Name Working Fine*/
prof_gr AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘AHMADBILAL’) /*User Name Where we are facing some issue*/
SELECT pd.profile_option_id, pd.user_profile_option_name,
pd.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pd.profile_option_value,
pg.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pd.profile_option_name = pg.profile_option_name(+)
UNION
SELECT pg.profile_option_id, pg.user_profile_option_name,
pg.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pg.profile_option_value,
pd.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pg.profile_option_name = pd.profile_option_name(+))
WHERE status != ‘EQUAL’;

Advertisements

One thought on “SQL Query to Get Diffrence in Values for Profile Options assigned on USER Level

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s