Showing posts with label Query to Find Profile option Values. Show all posts
Showing posts with label Query to Find Profile option Values. Show all posts

Monday, 27 June 2016

Query to Find Profile option Values

If We need to Find a profile Option Value Through Database, when the user does not have system administrator Responsibility, this Query Will Help to Retrive the Values against each Profile Option at Various Levels.

I Have Included the Where Clause With Profile Option of Order Management. 

User May Change it as per their Requirement and/or Use Other Where Clause.

SELECT e.profile_option_name Profile,
  f.user_profile_option_name User_Profile_Name,
  c.application_short_name,
 DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') LevelSet_At,
  DECODE(a.level_id,10001,'Site',10002,c.application_short_name, 10003,b.responsibility_name,10004,d.user_name) LValue,
  NVL(a.profile_option_value,'Is Null') Value
FROM fnd_profile_option_values a,
  fnd_responsibility_tl b,
  fnd_application c,
  fnd_user d,
  fnd_profile_options e,
  fnd_profile_options_vl f
WHERE f.user_profile_option_name LIKE 'OM%'
AND e.profile_option_id = a.profile_option_id
AND e.profile_option_id = f.profile_option_id
AND a.level_value       = b.responsibility_id (+)
AND a.level_value       = c.application_id (+)
AND a.level_value       = d.user_id (+)
ORDER BY 1,2;