Script To List The Values Of A Profile Option At All Levels At User Level With All LAnguages

--Script To List The Values Of A Profile Option At All Levels At User Level With All LAnguages

SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name

, substr(fpot.user_profile_option_name,1,60) profile_name

, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level

, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name

,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value

, fpov.profile_option_value profile_value

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

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

AND fu.USER_NAME = UPPER(:P_USER_NAME) --'SANTOSH'; 'REYAZ' '

--and

--(fpo.profile_option_name like nvl('X',fpo.profile_option_name)

-- Not mandatory. Replace X with profile short name, ie 'ORG_ID'

--or fpot.user_profile_option_name like nvl('MO: Op%',fpot.user_profile_option_name))

-- Not mandatory. Replace Y with profile user name, ie 'MO: Op%'

-- How To Check If a Profile Option Is Set In Oracle Applications? (Doc ID 470102.1)

 SELECT   po.user_profile_option_name,

              po.profile_option_name "NAME" ,

              DECODE (TO_CHAR (pov.level_id), '10001', 'SITE' , '10002', 'APP', '10003', 'RESP', '10004', 'USER', '???') "LEV",

              DECODE (TO_CHAR (pov.level_id) , '10001', '', '10002', app.application_short_name , '10003', rsp.responsibility_key, '10004', usr.user_name, '???')  "CONTEXT",

              pov.profile_option_value "VALUE"

         FROM fnd_profile_options_vl po,

                  fnd_profile_option_values pov,

                  fnd_user usr,

                  fnd_application app,

                  fnd_responsibility rsp

      WHERE 1 = 1 -- (po.profile_option_name = upper('&profil_name'))

          AND pov.application_id = po.application_id

          AND pov.profile_option_id =   po.profile_option_id

          AND usr.user_id(+) = pov.level_value

          AND rsp.application_id(+) =  pov.level_value_application_id

          AND rsp.responsibility_id(+) = pov.level_value

          AND app.application_id(+)   = pov.level_value

ORDER BY "NAME", pov.level_id, "VALUE"

--How To Set A System Profile Value Without Logging In To The Applications (Doc ID 364503.1)