Thursday 28 January 2016

Oracle Apps: Query to get the profile option values from backend

The below given is a query to get the Profile Option values at all levels from the backend. This query is very useful when you want to check which all levels the Profile is being set and what are the values.




 select fpo.profile_option_id   
       ,fpo.profile_option_name   
       ,fpo.user_profile_option_name   
       ,decode(level_id,10001,'Site'             
                       ,10002,'Application'  
                       ,10003,'Responsibility'  
                       ,10004,'User'  
                       ,10006,'Organization') profile_level   
       ,decode(level_id,10002, (select application_name    
                                from apps.fnd_application_vl    
                                where application_id = level_value )   
                       ,10003, (select responsibility_name    
                                from apps.fnd_responsibility_vl    
                                where responsibility_id = level_value)   
                       ,10004, (select user_name    
                                from apps.fnd_user    
                                where user_id = level_value)   
                       ,10006, (select name      
                                from apps.hr_all_organization_units       
                                where organization_id = level_value)) level_value   
       ,profile_option_value   
  from apps.fnd_profile_options_vl fpo   
      ,apps.fnd_profile_option_values fpov   
  where fpo.profile_option_id = fpov.profile_option_id    
  and   (fpo.profile_option_name like 'AJ_TEST_PROFILE'    
         or fpo.user_profile_option_name like 'AJ Test Profile');   





Feel free to point out if anything is missing/wrong in this blog.

No comments:

Post a Comment