Monday 23 June 2014

Script to get profile option values at different levels

SELECT fpo.profile_option_name "Profile Option Name",
       fpot.user_profile_option_name "User Profile Option Name",
       fpot.description,
       fpo.start_date_active "Start Active Date",
       fpo.end_date_active "End Date Active",
       fpo.creation_date "Creation Date",
       fu.user_name "Created By",
       'Site' "Level",
       'SITE' "Level Value",
       fpov.profile_option_value "Profile Option Value"
FROM fnd_profile_options_tl fpot,
     fnd_profile_options fpo,
     fnd_profile_option_values fpov,
     fnd_user fu
WHERE     fpot.user_profile_option_name LIKE '%' || :profile_name || '%'
      AND fpot.profile_option_name = fpo.profile_option_name
      AND fpo.application_id = fpov.application_id
      AND fpo.profile_option_id = fpov.profile_option_id
      AND fpo.created_by = fu.user_id
      AND fpot.language = USERENV ('Lang')
      AND fpov.level_id = 10001                               /* site level */
UNION ALL
SELECT fpo.profile_option_name "Profile Option Name",
       fpot.user_profile_option_name "User Profile Option Name",
       fpot.description,
       fpo.start_date_active "Start Active Date",
       fpo.end_date_active "End Date Active",
       fpo.creation_date "Creation Date",
       fu.user_name "Created By",
       'Appl' "Level",
       fa.application_name "Level Value",
       fpov.profile_option_value "Profile Option Value"
FROM fnd_profile_options_tl fpot,
     fnd_profile_options fpo,
     fnd_profile_option_values fpov,
     fnd_user fu,
     fnd_application_tl fa
WHERE     fpot.user_profile_option_name LIKE '%' || :profile_name || '%'
      AND fpot.profile_option_name = fpo.profile_option_name
      AND fpo.profile_option_id = fpov.profile_option_id
      AND fpo.created_by = fu.user_id
      AND fpot.language = USERENV ('Lang')
      AND fpov.level_id = 10002                        /* application level */
      AND fpov.level_value = fa.application_id
UNION ALL
SELECT fpo.profile_option_name "Profile Option Name",
       fpot.user_profile_option_name "User Profile Option Name",
       fpot.description,
       fpo.start_date_active "Start Active Date",
       fpo.end_date_active "End Date Active",
       fpo.creation_date "Creation Date",
       fu.user_name "Created By",
       'Resp' "Level",
       frt.responsibility_name "Level Value",
       fpov.profile_option_value "Profile Option Value"
FROM fnd_profile_options_tl fpot,
     fnd_profile_options fpo,
     fnd_profile_option_values fpov,
     fnd_user fu,
     fnd_responsibility_tl frt
WHERE     fpot.user_profile_option_name LIKE '%' || :profile_name || '%'
      AND fpot.profile_option_name = fpo.profile_option_name
      AND fpo.profile_option_id = fpov.profile_option_id
      AND fpo.created_by = fu.user_id
      AND frt.language = USERENV ('Lang')
      AND fpot.language = USERENV ('Lang')
      AND fpov.level_id = 10003                      /*responsibility level */
      AND fpov.level_value = frt.responsibility_id
      AND fpov.level_value_application_id = frt.application_id
UNION ALL
SELECT fpo.profile_option_name "Profile Option Name",
       fpot.user_profile_option_name "User Profile Option Name",
       fpot.description,
       fpo.start_date_active "Start Active Date",
       fpo.end_date_active "End Date Active",
       fpo.creation_date "Creation Date",
       fu.user_name "Created By",
       'User' "Level",
       fu2.user_name "Level Value",
       fpov.profile_option_value "Profile Option Value"
FROM fnd_profile_options_tl fpot,
     fnd_profile_options fpo,
     fnd_profile_option_values fpov,
     fnd_user fu,
     fnd_user fu2
WHERE     fpot.user_profile_option_name LIKE '%' || :profile_name || '%'
      AND fpot.profile_option_name = fpo.profile_option_name
      AND fpo.profile_option_id = fpov.profile_option_id
      AND fpo.created_by = fu.user_id
      AND fpov.level_id = 10004                               /* user level */
      AND fpov.level_value = fu2.user_id
      AND fpot.language = USERENV ('Lang')
ORDER BY "User Profile Option Name", "Level", "Level Value"

No comments:

Post a Comment