Query to find the profile option values at all levels



The below query is used to get the profile option values at all levels (Site,Application,Responsibility, User).

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : To get the Profile option information at all levels(Site,Application,Responsibility, User).                
Description     : It also shows the created by and Last updated by infn.Need to pass the profile option name either system name (Ex:'ORG_ID') or
                  user defined name (Ex:'MO: Operating Unit')
 It provides
 1)The value assigned at site level
 2)The value assigned at Application level and Application Name If it is assigned to it.
 3)The value assigned at Responsibility level and Responsibility Name If it is assigned to any resp.
 4)The value assigned at User level and User Name If it is assigned to any user.
****************************************************************** */

select FPO.PROFILE_OPTION_ID, FPOT.PROFILE_OPTION_NAME PROFILE_SHORT_NAME
, fpot.user_profile_option_name profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name,10003,fr.responsibility_name,10004,fu.user_name) level_value
, FPOV.PROFILE_OPTION_VALUE PROFILE_VALUE
, FPOV.CREATION_DATE
,(select USER_NAME from FND_USER
   where USER_ID = FPOV.CREATED_BY) "Created By"
, FPOV.LAST_UPDATE_DATE
,(select USER_NAME from FND_USER
   WHERE user_id = fpov.last_updated_by) "Last Update By"
, fpov.*
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, FND_RESPONSIBILITY_TL FR
, FND_USER FU  
where 1=1 and (FPO.PROFILE_OPTION_NAME like NVL(:PROFILE_OPTION_NAME,FPO.PROFILE_OPTION_NAME)
and fpot.user_profile_option_name like nvl(:User_Profile_Option_Name,fpot.user_profile_option_name))
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
AND FR.RESPONSIBILITY_ID(+)=FPOV.LEVEL_VALUE
and FU.USER_ID(+)=FPOV.LEVEL_VALUE
order by 3
;

Post a Comment