Profile Options at all levels provided Profile Option Name

Profile Options at all levels provided Profile Option Name

Modules Involved: Application object library (AOL)
Purpose         : To get the Profile option information at all levels(Site,Application,Responsibility, User).                  
Description     : 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.*
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 5