得到所有Profile Values, 由于EBS 目前是分成4个LEVEL,所以需要4个SQL union 起来:
----------------------------------------------------
TP:INV Miscellaneous Issue and Receipt form 1 - Site Site 0 1
XNP: Acknowledgement Required Flag for Send Message 1 - Site Site 0 N
PO: ERS Aging Period 1 - Site Site 0 0
PO: Allow Rate Override For User Rate Type 1 - Site Site 0 N
select
p.user_profile_option_name profile
, ' 1 - Site ' Scope
, ' Site ' Value_scope
, v.level_value
, v.profile_option_value v_profile
from fnd_profile_option_values v
, fnd_profile_options_vl p
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10001 )
-- and p.user_profile_option_name like '%&&profile%'
Union ALL
select p.user_profile_option_name
, ' 2 - Application. '
, a.application_short_name
, v.level_value
, v.profile_option_value
from fnd_profile_option_values v
, fnd_profile_options_vl p
, fnd_application a
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10002 and a.application_id = v.level_value)
-- and p.user_profile_option_name like '%&&profile%'
Union ALL
select p.user_profile_option_name
, ' 3 - Respon. '
, r.responsibility_name
, v.level_value
, v.profile_option_value
from fnd_profile_option_values v
, fnd_profile_options_vl p
, fnd_responsibility_vl r
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10003 and r.responsibility_id = v.level_value)
-- and p.user_profile_option_name like '%&&profile%'
Union ALL
select p.user_profile_option_name
, ' 3 - User '
, u. user_name
, v.level_value
, v.profile_option_value
from fnd_profile_option_values v
, fnd_profile_options_vl p
, fnd_user u
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10004 and u. user_id = v.level_value)
-- and p.user_profile_option_name like '%&&profile%'
-- order by 1,2,3
;
, ' 1 - Site ' Scope
, ' Site ' Value_scope
, v.level_value
, v.profile_option_value v_profile
from fnd_profile_option_values v
, fnd_profile_options_vl p
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10001 )
-- and p.user_profile_option_name like '%&&profile%'
Union ALL
select p.user_profile_option_name
, ' 2 - Application. '
, a.application_short_name
, v.level_value
, v.profile_option_value
from fnd_profile_option_values v
, fnd_profile_options_vl p
, fnd_application a
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10002 and a.application_id = v.level_value)
-- and p.user_profile_option_name like '%&&profile%'
Union ALL
select p.user_profile_option_name
, ' 3 - Respon. '
, r.responsibility_name
, v.level_value
, v.profile_option_value
from fnd_profile_option_values v
, fnd_profile_options_vl p
, fnd_responsibility_vl r
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10003 and r.responsibility_id = v.level_value)
-- and p.user_profile_option_name like '%&&profile%'
Union ALL
select p.user_profile_option_name
, ' 3 - User '
, u. user_name
, v.level_value
, v.profile_option_value
from fnd_profile_option_values v
, fnd_profile_options_vl p
, fnd_user u
where v.profile_option_id = p.profile_option_id
and (v.level_id = 10004 and u. user_id = v.level_value)
-- and p.user_profile_option_name like '%&&profile%'
-- order by 1,2,3
;
TP:INV Miscellaneous Issue and Receipt form 1 - Site Site 0 1
XNP: Acknowledgement Required Flag for Send Message 1 - Site Site 0 N
PO: ERS Aging Period 1 - Site Site 0 0
PO: Allow Rate Override For User Rate Type 1 - Site Site 0 N