SELECT
p.profile_option_name short_name,
n.user_profile_option_name name,
DECODE(
v.level_id,
10001,
'Site',
10002,
'Application',
10003,
'Responsibility',
10004,
'User',
10005,
'Server',
10006,
'Org',
10007,
DECODE(
TO_CHAR(v.level_value2),
'-1',
'Responsibility',
DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
),
'UnDef'
) level_set,
DECODE(
TO_CHAR(v.level_id),
'10001',
'',
'10002',
app.application_short_name,
'10003',
rsp.responsibility_key,
'10004',
usr.user_name,
'10005',
svr.node_name,
'10006',
org.name,
'10007',
DECODE(
TO_CHAR(v.level_value2),
'-1',
rsp.responsibility_key,
DECODE(
TO_CHAR(v.level_value),
'-1',
(
SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2
),
(
SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2
)
|| '-'
|| rsp.responsibility_key
)
),
'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
upper(p.profile_option_name) IN (
SELECT
profile_option_name
FROM
fnd_profile_options_tl
WHERE
upper(user_profile_option_name) LIKE upper('MO: Operating Unit')
)
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
AND
DECODE(
v.level_id,
10001,
'Site',
10002,
'Application',
10003,
'Responsibility',
10004,
'User',
10005,
'Server',
10006,
'Org',
10007,
DECODE(
TO_CHAR(v.level_value2),
'-1',
'Responsibility',
DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
),
'UnDef'
) = 'Site'
ORDER BY
short_name,
user_profile_option_name,
level_id,
level_set;
p.profile_option_name short_name,
n.user_profile_option_name name,
DECODE(
v.level_id,
10001,
'Site',
10002,
'Application',
10003,
'Responsibility',
10004,
'User',
10005,
'Server',
10006,
'Org',
10007,
DECODE(
TO_CHAR(v.level_value2),
'-1',
'Responsibility',
DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
),
'UnDef'
) level_set,
DECODE(
TO_CHAR(v.level_id),
'10001',
'',
'10002',
app.application_short_name,
'10003',
rsp.responsibility_key,
'10004',
usr.user_name,
'10005',
svr.node_name,
'10006',
org.name,
'10007',
DECODE(
TO_CHAR(v.level_value2),
'-1',
rsp.responsibility_key,
DECODE(
TO_CHAR(v.level_value),
'-1',
(
SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2
),
(
SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2
)
|| '-'
|| rsp.responsibility_key
)
),
'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
upper(p.profile_option_name) IN (
SELECT
profile_option_name
FROM
fnd_profile_options_tl
WHERE
upper(user_profile_option_name) LIKE upper('MO: Operating Unit')
)
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
AND
DECODE(
v.level_id,
10001,
'Site',
10002,
'Application',
10003,
'Responsibility',
10004,
'User',
10005,
'Server',
10006,
'Org',
10007,
DECODE(
TO_CHAR(v.level_value2),
'-1',
'Responsibility',
DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
),
'UnDef'
) = 'Site'
ORDER BY
short_name,
user_profile_option_name,
level_id,
level_set;
No comments:
Post a Comment