oracle8.1.6
select l.list_id, l.name, nvl(su.user_count,0) as user_count,
nvl(uu.users_need_confirmation,0) as users_need_confirmation,
nvl(ue.users_need_email_confirmation,0) as users_need_email_confirmation,
(case when l.expiration_date > sysdate then 'f' else 't' end) as expired_p,
acs_permission.permission_p(l.list_id, :user_id, 'admin') as admin_p
from ml_mailing_lists l,
(select count(*) as user_count, m.list_id
from ml_mailing_list_user_map m
where m.subscribed_p = 't'
group by m.list_id) su,
(select count(*) as users_need_confirmation,
m2.list_id
from ml_mailing_list_user_map m2
where m2.confirmed_p = 'f'
group by m2.list_id) uu,
(select count(*) as users_need_email_confirmation,
m2.list_id
from ml_mailing_list_user_map m2, users u
where m2.subscribed_p = 'f'
and m2.user_id = u.user_id
and u.email_verified_p = 'f'
group by m2.list_id) ue
where l.package_id = :package_id
and su.list_id(+) = l.list_id
and uu.list_id (+) = l.list_id
and ue.list_id (+) = l.list_id
and acs_permission.permission_p (l.list_id, :user_id, 'read') = 't'
order by lower(l.name)
select mail_class_id, name,
acs_permission.permission_p(mail_class_id, :user_id, 'admin') as admin_p
from ml_mail_classes
where package_id = :package_id
and acs_permission.permission_p (mail_class_id, :user_id, 'read') = 't'
order by lower(name)