Posted By

theonlyalterego on 08/10/12


Tagged

sql Oracle diagnostic


Versions (?)

Oracle Find Profile Values for User


 / Published in: SQL
 

URL: http://www.notesbit.com/index.php/oracle-applications/11i-scripts/how-do-you-check-the-profile-option-values-using-sql-oracle-applications/

find what profile values are set for a particular user

  1. SELECT po.profile_option_name "NAME",
  2. po.USER_PROFILE_OPTION_NAME,
  3. decode(to_char(pov.level_id),
  4. '10001', 'SITE',
  5. '10002', 'APP',
  6. '10003', 'RESP',
  7. '10005', 'SERVER',
  8. '10006', 'ORG',
  9. '10004', 'USER', '???') "LEV",
  10. decode(to_char(pov.level_id),
  11. '10001', '',
  12. '10002', app.application_short_name,
  13. '10003', rsp.responsibility_key,
  14. '10005', svr.node_name,
  15. '10006', org.name,
  16. '10004', usr.user_name,
  17. '???') "CONTEXT",
  18. pov.profile_option_value "VALUE"
  19. FROM FND_PROFILE_OPTIONS_VL po,
  20. FND_PROFILE_OPTION_VALUES pov,
  21. fnd_user usr,
  22. fnd_application app,
  23. fnd_responsibility rsp,
  24. fnd_nodes svr,
  25. hr_operating_units org
  26. WHERE po.profile_option_name LIKE :profile_name
  27. AND pov.application_id = po.application_id
  28. AND pov.profile_option_id = po.profile_option_id
  29. AND usr.user_id (+) = pov.level_value
  30. AND rsp.application_id (+) = pov.level_value_application_id
  31. AND rsp.responsibility_id (+) = pov.level_value
  32. AND app.application_id (+) = pov.level_value
  33. AND svr.node_id (+) = pov.level_value
  34. AND org.organization_id (+) = pov.level_value
  35. AND decode(to_char(pov.level_id),
  36. '10001', '',
  37. '10002', app.application_short_name,
  38. '10003', rsp.responsibility_key,
  39. '10005', svr.node_name,
  40. '10006', org.name,
  41. '10004', usr.user_name,
  42. '???') LIKE :user_name
  43. ORDER BY "NAME", pov.level_id, "VALUE";

Report this snippet  

You need to login to post a comment.