ORACLE EBS: SQL to get the profile option values and key columns at all levels.

ORACLE EBS: SQL to get the profile option values and key columns at all levels.

/*
Description: This script will generate a list of all profile values in an Oracle Apps 11i env.
It lists the profiles by Level (Site,Application,Responsibility,User) with relevant fields
and provides the ability to search on any of the listed fields.

Running this SQL periodically and doing a different between the output provides the changes to profile option values..

Warning: This SQL runs for a long time and could generate about 170K rows when run against Large databases.

*/

SELECT DECODE(level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'USER') "Profile_Level",

DECODE(level_id,10001,NULL,10002,fa.application_name,10003,fr.responsibility_name,10004,fu.user_name) "Non_Site_Description"
, fu.user_name
, fpov.user_profile_option_name
, fpov.profile_option_name
, fpova.profile_option_value
, fpova.creation_date
, fpova.last_update_date
, fpov.HIERARCHY_TYPE
, fpov.START_DATE_ACTIVE
, fpov.END_DATE_ACTIVE
, fpov.WRITE_ALLOWED_FLAG
, fpov.READ_ALLOWED_FLAG
, fpov.USER_CHANGEABLE_FLAG
, fpov.USER_VISIBLE_FLAG
, fpov.SITE_ENABLED_FLAG
, fpov.SITE_UPDATE_ALLOWED_FLAG
, fpov.APP_ENABLED_FLAG
, fpov.APP_UPDATE_ALLOWED_FLAG
, fpov.RESP_ENABLED_FLAG
, fpov.RESP_UPDATE_ALLOWED_FLAG
, fpov.USER_ENABLED_FLAG
, fpov.USER_UPDATE_ALLOWED_FLAG
, fpov.SERVER_ENABLED_FLAG
, fpov.SERVER_UPDATE_ALLOWED_FLAG
, fpov.ORG_ENABLED_FLAG
, fpov.ORG_UPDATE_ALLOWED_FLAG
FROM fnd_profile_options_vl fpov
, fnd_profile_option_values fpova
, fnd_application_tl fa -- table inclusion when looking at application joins
, fnd_responsibility_tl fr -- table inclusion when looking at responsibility joins
, fnd_user fu -- table inclusion when looking at user joins
, fnd_user fu2
WHERE fpov.application_id = fpova.application_id
AND fpov.profile_option_id = fpova.profile_option_id
AND fpov.start_date_active <= SYSDATE
AND NVL(fpov.end_date_active,SYSDATE) >= SYSDATE
AND (fpov.site_enabled_flag = 'Y' OR fpov.app_enabled_flag = 'Y' OR fpov.resp_enabled_flag = 'Y' OR fpov.user_enabled_flag = 'Y')
AND fpova.level_value = fa.application_id (+) -- join for application values
AND fpova.level_value = fr.responsibility_id (+) -- join for responsibility values
AND fpova.level_value = fu.user_id (+) -- join for user values
AND fpova.last_updated_by = fu2.user_id (+) -- join for update by user values
ORDER BY "User Profile Option Name", "Profile Level";