oracle8.1.6
select dotlrn_users.user_id,
dotlrn_users.first_names,
dotlrn_users.last_name,
dotlrn_users.email,
nvl((select 'full'
from dotlrn_full_user_profile_rels
where dotlrn_full_user_profile_rels.rel_id = dotlrn_users.rel_id),
'limited') as access_level,
acs_permission.permission_p(:dotlrn_package_id, dotlrn_users.user_id, 'read_private_data') as read_private_data_p,
acs_permission.permission_p(:root_object_id, dotlrn_users.user_id, 'admin') as site_wide_admin_p
from dotlrn_users
where dotlrn_users.type = :type
and (
lower(dotlrn_users.last_name) like lower('%' || :search_text || '%')
or lower(dotlrn_users.first_names) like lower('%' || :search_text || '%')
or lower(dotlrn_users.email) like lower('%' || :search_text || '%')
)
order by dotlrn_users.last_name
select cc_users.user_id,
cc_users.first_names,
cc_users.last_name,
cc_users.email,
'limited' as access_level,
'f' as read_private_data_p,
acs_permission.permission_p(:root_object_id, cc_users.user_id, 'admin') as site_wide_admin_p
from cc_users
where not exists (select 1
from dotlrn_users
where dotlrn_users.user_id = cc_users.user_id)
and cc_users.member_state = 'approved'
and (
lower(cc_users.last_name) like lower('%' || :search_text || '%')
or lower(cc_users.first_names) like lower('%' || :search_text || '%')
or lower(cc_users.email) like lower('%' || :search_text || '%')
)
order by cc_users.last_name
select cc_users.user_id,
cc_users.first_names,
cc_users.last_name,
cc_users.email,
'limited' as access_level,
'f' as read_private_data_p,
acs_permission.permission_p(:root_object_id, cc_users.user_id, 'admin') as site_wide_admin_p
from cc_users
where not exists (select 1
from dotlrn_users
where dotlrn_users.user_id = cc_users.user_id)
and cc_users.member_state = 'banned'
and (
lower(cc_users.last_name) like lower('%' || :search_text || '%')
or lower(cc_users.first_names) like lower('%' || :search_text || '%')
or lower(cc_users.email) like lower('%' || :search_text || '%')
)
order by cc_users.last_name