select
psp.security_profile_name,
(select haouv0.name from hr_all_organization_units_vl haouv0 where psp.business_group_id=haouv0.organization_id) business_group,
haouv.name operating_unit,
ftv.territory_short_name country,
hla.location_code,
decode(psp.org_security_mode,'NONE','View all (no security)','HIER','Organization hierarchy and/or organization list','OU','Single operating unit','OU_INV','Operating unit and inventory organizations') organization_security_mode,
xxen_util.meaning(psp.view_all_flag,'YES_NO',0) view_all_operating_units,
xxen_util.user_name(psp.created_by) created_by,
xxen_util.client_time(psp.creation_date) creation_date,
xxen_util.user_name(psp.last_updated_by) last_updated_by,
xxen_util.client_time(psp.last_update_date) last_update_date,
psp.security_profile_id
from
(
select
nvl(pol.organization_id,nvl(hou.organization_id,hou0.organization_id)) operating_unit_id,
psp.*
from
per_security_profiles psp,
(select pol.* from per_organization_list pol, hr_operating_units hou where pol.organization_id=hou.organization_id and hou.usable_flag is null) pol,
(select hou.* from hr_operating_units hou where hou.usable_flag is null) hou,
(select -1 view_all, hou.* from hr_operating_units hou where hou.usable_flag is null) hou0
where
decode(psp.view_all_flag,'N',psp.security_profile_id)=pol.security_profile_id(+) and
decode(psp.view_all_flag,'Y',psp.business_group_id)=hou.business_group_id(+) and
decode(psp.view_all_flag,'Y',nvl2(psp.business_group_id,null,-1))=hou0.view_all(+)
) psp,
hr_all_organization_units_vl haouv,
hr_locations_all hla,
fnd_territories_vl ftv
where
1=1 and
psp.operating_unit_id=haouv.organization_id(+) and
haouv.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+)
order by
psp.security_profile_name,
ftv.territory_short_name,
haouv.name |
