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;



No comments:

Post a Comment