SELECT DECODE(RTRIM(fu1.user_name)
,'AUTOINSTALL', 'A'
,'B') order_by_user
, ap1.application_short_name application_name
, pos.profile_option_name user_profile_option_name
, to_char(res.responsibility_id) responsibility_level_value
, res.responsibility_name responsibility_name
, pov.profile_option_value profile_option_value
, SUBSTR(fu1.user_name, 1, 30)||' (last updated by: '
||SUBSTR(fu2.user_name, 1, 30)
||')' user_name
FROM fnd_application ap1
, fnd_responsibility_tl res
, fnd_user fu1
, fnd_user fu2
, fnd_profile_options pos
, fnd_profile_option_values pov
WHERE ap1.application_id = pov.application_id
AND pov.level_value = res.responsibility_id
AND pov.level_value_application_id = res.application_id
AND pov.last_updated_by = fu2.user_id
AND ( ( pov.created_by = fu1.user_id
AND (fu1.user_id NOT IN (-1,0,1,2)))
OR ( pov.created_by = fu1.user_id
AND fu1.user_id IN (-1,0,1,2)
AND pov.last_updated_by != fu1.user_id
AND pov.last_updated_by NOT IN (-1,0,1,2)))
AND pov.application_id = pos.application_id
AND pov.profile_option_id = pos.profile_option_id
ORDER BY 1, 2, 3
,'AUTOINSTALL', 'A'
,'B') order_by_user
, ap1.application_short_name application_name
, pos.profile_option_name user_profile_option_name
, to_char(res.responsibility_id) responsibility_level_value
, res.responsibility_name responsibility_name
, pov.profile_option_value profile_option_value
, SUBSTR(fu1.user_name, 1, 30)||' (last updated by: '
||SUBSTR(fu2.user_name, 1, 30)
||')' user_name
FROM fnd_application ap1
, fnd_responsibility_tl res
, fnd_user fu1
, fnd_user fu2
, fnd_profile_options pos
, fnd_profile_option_values pov
WHERE ap1.application_id = pov.application_id
AND pov.level_value = res.responsibility_id
AND pov.level_value_application_id = res.application_id
AND pov.last_updated_by = fu2.user_id
AND ( ( pov.created_by = fu1.user_id
AND (fu1.user_id NOT IN (-1,0,1,2)))
OR ( pov.created_by = fu1.user_id
AND fu1.user_id IN (-1,0,1,2)
AND pov.last_updated_by != fu1.user_id
AND pov.last_updated_by NOT IN (-1,0,1,2)))
AND pov.application_id = pos.application_id
AND pov.profile_option_id = pos.profile_option_id
ORDER BY 1, 2, 3
No comments:
Post a Comment