oracle8.1.6
select to_char(next_day(to_date(:start_date, 'YYYY-MM-DD'), 'sunday')-7, 'YYYY-MM-DD') as start_date,
to_char(next_day(to_date(:start_date, 'YYYY-MM-DD'), 'sunday')-1, 'YYYY-MM-DD') as end_date
from dual
select to_char(last_day(to_date(:start_date, 'YYYY-MM-DD')), 'YYYY-MM-DD') as end_date
from dual
select j.mail_job_id, j.mail_class_id, j.subject, j.mails_sent, j.state,
j.track_links_p, p.first_names as user_first_names,
to_char(j.execution_date, 'YYYY-MM-DD HH24:MI') as execution_date,
p.last_name as user_last_name
from ml_mail_jobs j, acs_objects o, persons p
where j.state <> 'suspended'
and j.scheduled_date >= to_date(:start_date, 'YYYY-MM-DD')
and j.scheduled_date < to_date(:end_date, 'YYYY-MM-DD') + 1
and acs_permission.permission_p(j.mail_job_id, :user_id, 'read') = 't'
and o.object_id = j.mail_job_id
and p.person_id = o.creation_user
and j.package_id = :package_id
order by j.execution_date desc, lower(j.subject)