Query used for audit point of view i.e. when a profile is changed and by which user

SELECT t.user_profile_option_name,

profile_option_value,

v.creation_date,

v.last_update_date,

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 =  :p_value

AND t.LANGUAGE IN (SELECT language_code

FROM fnd_languages

WHERE installed_flag = ‘B’

UNION

SELECT nls_language

FROM fnd_languages

WHERE installed_flag = ‘B’)

ORDER BY user_profile_option_name;

:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)