Oracle E-Business Suite profile option values at different levels

 Oracle E-Business Suite profile option values at different levels

SELECT 

    p.profile_option_name AS short_name,                        -- Internal profile option name

    n.user_profile_option_name AS name,                         -- User-friendly profile name

    DECODE(v.level_id,                                          -- Level where the profile is set

           10001, 'Site',

           10002, 'Application',

           10003, 'Responsibility',

           10004, 'User',

           'UnDef') AS level_set,

    v.level_value AS level_val_id,                              -- ID of the level value (e.g., user_id, app_id)

    -- Get the actual name/key of the level value (e.g., user name, application short name)

    DECODE(v.level_id,

           10001, 'Site',

           10002, (SELECT application_short_name 

                   FROM fnd_application 

                   WHERE application_id = v.level_value),

           10003, (SELECT responsibility_key 

                   FROM fnd_responsibility 

                   WHERE responsibility_id = v.level_value 

                     AND application_id = v.application_id),

           10004, (SELECT user_name 

                   FROM fnd_user 

                   WHERE user_id = v.level_value),

           'UnDef') AS level_val_name,

    v.profile_option_value AS value                             -- Actual profile value set

FROM 

    fnd_profile_options p,

    fnd_profile_option_values v,

    fnd_profile_options_tl n

WHERE 

    p.profile_option_id = v.profile_option_id (+)

    AND p.profile_option_name = n.profile_option_name

    AND UPPER(n.user_profile_option_name) LIKE UPPER('%&ProfileName%');