SELECT b.user_profile_option_name "Long Name",
a.profile_option_name "Short Name",
DECODE (TO_CHAR (c.level_id),
'10001', 'Site',
'10002', 'Application',
'10003', 'Responsibility',
'10004', 'User',
'Unknown'
) "Level",
DECODE (TO_CHAR (c.level_id),
'10001', 'Site',
'10002', NVL (h.application_short_name,
TO_CHAR (c.level_value)
),
'10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
'10004', NVL (e.user_name, TO_CHAR (c.level_value)),
'Unknown'
) "Level Value",
c.profile_option_value "Profile Value",
c.profile_option_id "Profile ID",
TO_CHAR (c.last_update_date, 'DD-MON-YYYY HH24:MI') "Updated Date",
NVL (d.user_name, TO_CHAR (c.last_updated_by)) "Updated By"
FROM apps_fnd.fnd_profile_options a,
apps_fnd.fnd_profile_options_vl b,
apps_fnd.fnd_profile_option_values c,
apps_fnd.fnd_user d,
apps_fnd.fnd_user e,
apps_fnd.fnd_responsibility_vl g,
apps_fnd.fnd_application h
WHERE
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name LIKE 'EGO%'
AND a.profile_option_name = b.profile_option_name
AND a.profile_option_id = c.profile_option_id
AND a.application_id = c.application_id
AND c.last_updated_by = d.user_id(+)
AND c.level_value = e.user_id(+)
AND c.level_value = g.responsibility_id(+)
AND c.level_value = h.application_id(+)
ORDER BY b.user_profile_option_name,
c.level_id,
DECODE (TO_CHAR (c.level_id),
'10001', 'Site',
'10002', NVL (h.application_short_name,
TO_CHAR (c.level_value)
),
'10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
'10004', NVL (e.user_name, TO_CHAR (c.level_value)),
'Unknown'
);
a.profile_option_name "Short Name",
DECODE (TO_CHAR (c.level_id),
'10001', 'Site',
'10002', 'Application',
'10003', 'Responsibility',
'10004', 'User',
'Unknown'
) "Level",
DECODE (TO_CHAR (c.level_id),
'10001', 'Site',
'10002', NVL (h.application_short_name,
TO_CHAR (c.level_value)
),
'10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
'10004', NVL (e.user_name, TO_CHAR (c.level_value)),
'Unknown'
) "Level Value",
c.profile_option_value "Profile Value",
c.profile_option_id "Profile ID",
TO_CHAR (c.last_update_date, 'DD-MON-YYYY HH24:MI') "Updated Date",
NVL (d.user_name, TO_CHAR (c.last_updated_by)) "Updated By"
FROM apps_fnd.fnd_profile_options a,
apps_fnd.fnd_profile_options_vl b,
apps_fnd.fnd_profile_option_values c,
apps_fnd.fnd_user d,
apps_fnd.fnd_user e,
apps_fnd.fnd_responsibility_vl g,
apps_fnd.fnd_application h
WHERE
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name LIKE 'EGO%'
AND a.profile_option_name = b.profile_option_name
AND a.profile_option_id = c.profile_option_id
AND a.application_id = c.application_id
AND c.last_updated_by = d.user_id(+)
AND c.level_value = e.user_id(+)
AND c.level_value = g.responsibility_id(+)
AND c.level_value = h.application_id(+)
ORDER BY b.user_profile_option_name,
c.level_id,
DECODE (TO_CHAR (c.level_id),
'10001', 'Site',
'10002', NVL (h.application_short_name,
TO_CHAR (c.level_value)
),
'10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
'10004', NVL (e.user_name, TO_CHAR (c.level_value)),
'Unknown'
);
No comments:
Post a Comment