Profile Options PLSQL Query - Oracle EBS R12

http://systemadministratorsys.blogspot.com/2016/07/profile-options-query.html

Profile Options PLSQL Query

SELECT --fpo.profile_option_name SHORT_NAME,
 fpot.user_profile_option_name NAME,
 DECODE(fpov.level_id,
        10001,
        'Site',
        10002,
        'Application',
        10003,
        'Responsibility',
        10004,
        'User',
        10005,
        'Server',
        'UnDef') LEVEL_SET,
 DECODE(TO_CHAR(fpov.level_id),
        '10001',
        '',
        '10002',
        fap.application_short_name,
        '10003',
        frsp.RESPONSIBILITY_NAME,
        '10005',
        fnod.node_name,
        '10006',
        hou.name,
        '10004',
        fu.user_name,
        'UnDef') "CONTEXT",
 fpov.profile_option_value VALUE
  FROM /*fnd_profile_options fpo,*/ fnd_profile_option_values fpov,
       fnd_profile_options_vl    fpot,
       fnd_user                  fu,
       fnd_application           fap,
       fnd_responsibility_tl     frsp,
       fnd_nodes                 fnod,
       hr_operating_units        hou
 WHERE fpov.profile_option_id(+) = fpot.PROFILE_OPTION_ID
   and fpov.APPLICATION_ID(+) = fpot.APPLICATION_ID
      -- AND fpov.profile_option_name = fpot.profile_option_name(+)
   AND fu.user_id(+) = fpov.level_value
   AND frsp.application_id(+) = fpov.level_value_application_id
   AND frsp.responsibility_id(+) = fpov.level_value
   AND fap.application_id(+) = fpov.level_value
   AND fnod.node_id(+) = fpov.level_value
   AND hou.organization_id(+) = fpov.level_value
--  AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
--ORDER BY short_name;

--------------------------------------------------------------------------------------------------------------------------

select   a.user_profile_option_name NAME

,  DECODE (c.LEVEL_ID,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'User',

                 10005, 'Server',

                 c.LEVEL_ID) LEVEL_SET

                 

              ,   DECODE (TO_CHAR (c.level_id),

                 '10001', '',

                 '10002', fap.application_short_name,

                 '10003', frsp.RESPONSIBILITY_NAME,

                 '10005', fnod.node_name,

                 '10006', hou.name,

                 '10004', d.user_name,

                 TO_CHAR (c.level_id)) CONTEXT,

                 c.profile_option_value VALUE

                   from FND_PROFILE_OPTIONS_VL a/*, fnd_profile_options b*/, fnd_profile_option_values c, fnd_user d,

            hr_operating_units hou, fnd_responsibility_tl frsp , fnd_application fap, fnd_nodes fnod

where a.PROFILE_OPTION_ID = c.PROFILE_OPTION_ID(+)

and a.APPLICATION_ID = c.APPLICATION_ID(+)

and  d.user_id(+) = c.LEVEL_VALUE

and  hou.organization_id(+) = c.level_value

AND frsp.application_id(+) = c.level_value_application_id

AND frsp.responsibility_id(+) = c.level_value

AND fap.application_id(+) = c.level_value

 AND fnod.node_id(+) = c.level_value

/*and c.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID

and c.APPLICATION_ID=a.APPLICATION_ID

and a.PROFILE_OPTION_ID = c.PROFILE_OPTION_ID

and a.APPLICATION_ID=b.APPLICATION_ID*/