Post date: Apr 12, 2016 7:48:21 AM
/* Formatted on 2016/04/12 14:22 (Formatter Plus v4.8.8) */
SPOOL bne_profiles
SET linesize 132
SET pagesize 132
COL NAME format A35
COL LEVEL_SET format a10
COL CONTEXT format a10
COL VALUE format A20 wrap
COL Server format a10
COL resp format a8 wrap
COL application format a10
BREAK on NAME
SELECT p.profile_option_name NAME,
DECODE (v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef'
) level_set,
DECODE (TO_CHAR (v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_name,
'UnDef'
) "CONTEXT",
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND n.user_profile_option_name LIKE '%BNE%'
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY NAME,
level_set
/