create or replace package ml_mailing_list
as
	function new (
		 list_id		in ml_mailing_lists.list_id%TYPE default null,
		 package_id		in ml_mailing_lists.package_id%TYPE default null,
		 name			in ml_mailing_lists.name%TYPE,
		 locale			in ml_mailing_lists.locale%TYPE,
		 teaser			in ml_mailing_lists.teaser%TYPE default null,
		 sender_email		in ml_mailing_lists.sender_email%TYPE default null,
		 confirm_subject	in ml_mailing_lists.confirm_subject%TYPE default null,
		 confirm_body		in ml_mailing_lists.confirm_body%TYPE default null,
		 confirm_mime_type	in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
		 welcome_subject	in ml_mailing_lists.welcome_subject%TYPE default null,
		 welcome_body		in ml_mailing_lists.welcome_body%TYPE default null,
		 welcome_mime_type	in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
		 remind_subject		in ml_mailing_lists.remind_subject%TYPE default null,
		 remind_body		in ml_mailing_lists.remind_body%TYPE default null,
		 remind_mime_type	in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
		 expiration_date	in ml_mailing_lists.expiration_date%TYPE default sysdate,
		 first_reminder		in ml_mailing_lists.first_reminder%TYPE default 7,
		 second_reminder	in ml_mailing_lists.second_reminder%TYPE default 30,
		 comments		in ml_mailing_lists.comments%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer;

	procedure delete (
		 list_id		in ml_mailing_lists.list_id%TYPE
	);

	procedure edit (
		 list_id		in ml_mailing_lists.list_id%TYPE default null,
		 name			in ml_mailing_lists.name%TYPE,
		 locale			in ml_mailing_lists.locale%TYPE,
		 teaser			in ml_mailing_lists.teaser%TYPE default null,
		 sender_email		in ml_mailing_lists.sender_email%TYPE default null,
		 confirm_subject	in ml_mailing_lists.confirm_subject%TYPE default null,
		 confirm_body		in ml_mailing_lists.confirm_body%TYPE default null,
		 confirm_mime_type	in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
		 welcome_subject	in ml_mailing_lists.welcome_subject%TYPE default null,
		 welcome_body		in ml_mailing_lists.welcome_body%TYPE default null,
		 welcome_mime_type	in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
		 remind_subject		in ml_mailing_lists.remind_subject%TYPE default null,
		 remind_body		in ml_mailing_lists.remind_body%TYPE default null,
		 remind_mime_type	in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
		 expiration_date	in ml_mailing_lists.expiration_date%TYPE default sysdate,
		 first_reminder		in ml_mailing_lists.first_reminder%TYPE default 7,
		 second_reminder	in ml_mailing_lists.second_reminder%TYPE default 30,
		 comments		in ml_mailing_lists.comments%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	);

	function name (
		 list_id		in ml_mailing_lists.list_id%TYPE
	) return varchar2;

end ml_mailing_list;
/
show errors

create or replace package body ml_mailing_list
as
	function new (
		 list_id		in ml_mailing_lists.list_id%TYPE default null,
		 package_id		in ml_mailing_lists.package_id%TYPE default null,
		 name			in ml_mailing_lists.name%TYPE,
		 locale			in ml_mailing_lists.locale%TYPE,
		 teaser			in ml_mailing_lists.teaser%TYPE default null,
		 sender_email		in ml_mailing_lists.sender_email%TYPE default null,
		 confirm_subject	in ml_mailing_lists.confirm_subject%TYPE default null,
		 confirm_body		in ml_mailing_lists.confirm_body%TYPE default null,
		 confirm_mime_type	in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
		 welcome_subject	in ml_mailing_lists.welcome_subject%TYPE default null,
		 welcome_body		in ml_mailing_lists.welcome_body%TYPE default null,
		 welcome_mime_type	in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
		 remind_subject		in ml_mailing_lists.remind_subject%TYPE default null,
		 remind_body		in ml_mailing_lists.remind_body%TYPE default null,
		 remind_mime_type	in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
		 expiration_date	in ml_mailing_lists.expiration_date%TYPE default sysdate,
		 first_reminder		in ml_mailing_lists.first_reminder%TYPE default 7,
		 second_reminder	in ml_mailing_lists.second_reminder%TYPE default 30,
		 comments		in ml_mailing_lists.comments%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer
	is
		v_list_id	ml_mailing_lists.list_id%TYPE;
	begin
                v_list_id := acs_object.new (
                        object_id       =>      new.list_id,
                        object_type     =>      'mailing_list',
                        creation_date   =>      new.creation_date,
                        creation_user   =>      new.creation_user,
                        creation_ip     =>      new.creation_ip,
                        context_id      =>      new.context_id
                );

		insert into acs_named_objects (object_id, object_name, package_id) 
        	 values (v_list_id, name, package_id);

		insert into ml_mailing_lists
		       (list_id, package_id, name, locale, teaser, sender_email,
		       welcome_subject, welcome_body, welcome_mime_type,
		       confirm_subject, confirm_body, confirm_mime_type,
		       remind_subject, remind_body, remind_mime_type,
		       expiration_date, first_reminder, second_reminder, comments)
		values
		       (v_list_id, package_id, name, locale, teaser, sender_email,
		       welcome_subject, welcome_body, welcome_mime_type,
		       confirm_subject, confirm_body, confirm_mime_type,
		       remind_subject, remind_body, remind_mime_type,
		       expiration_date, first_reminder, second_reminder, comments);

		return v_list_id;
	end new;

	procedure delete (
		 list_id		in ml_mailing_lists.list_id%TYPE
	) is
	begin
		delete from ml_mailing_lists where list_id = ml_mailing_list.delete.list_id;
		acs_object.delete(list_id);
	end delete;

	procedure edit (
		 list_id		in ml_mailing_lists.list_id%TYPE default null,
		 name			in ml_mailing_lists.name%TYPE,
		 locale			in ml_mailing_lists.locale%TYPE,
		 teaser			in ml_mailing_lists.teaser%TYPE default null,
		 sender_email		in ml_mailing_lists.sender_email%TYPE default null,
		 confirm_subject	in ml_mailing_lists.confirm_subject%TYPE default null,
		 confirm_body		in ml_mailing_lists.confirm_body%TYPE default null,
		 confirm_mime_type	in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
		 welcome_subject	in ml_mailing_lists.welcome_subject%TYPE default null,
		 welcome_body		in ml_mailing_lists.welcome_body%TYPE default null,
		 welcome_mime_type	in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
		 remind_subject		in ml_mailing_lists.remind_subject%TYPE default null,
		 remind_body		in ml_mailing_lists.remind_body%TYPE default null,
		 remind_mime_type	in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
		 expiration_date	in ml_mailing_lists.expiration_date%TYPE default sysdate,
		 first_reminder		in ml_mailing_lists.first_reminder%TYPE default 7,
		 second_reminder	in ml_mailing_lists.second_reminder%TYPE default 30,
		 comments		in ml_mailing_lists.comments%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	) is
	begin
		update ml_mailing_lists
		set name = edit.name,
		    locale = edit.locale,
		    teaser = edit.teaser,
		    sender_email = edit.sender_email,
		    confirm_subject = edit.confirm_subject,
		    confirm_body = edit.confirm_body,
		    confirm_mime_type = edit.confirm_mime_type,
		    welcome_subject = edit.welcome_subject,
		    welcome_body = edit.welcome_body,
		    welcome_mime_type = edit.welcome_mime_type,
		    remind_subject = edit.remind_subject,
		    remind_body = edit.remind_body,
		    remind_mime_type = edit.remind_mime_type,
		    expiration_date = edit.expiration_date,
		    first_reminder = edit.first_reminder,
		    second_reminder = edit.second_reminder,
		    comments = edit.comments
		where list_id = edit.list_id;

		update acs_named_objects
		set object_name = edit.name
		where object_id = edit.list_id;

		update acs_objects
		set modifying_user = edit.modifying_user,
		    modifying_ip = edit.modifying_ip
		where object_id = edit.list_id;
	end edit;

	function name (
		 list_id		in ml_mailing_lists.list_id%TYPE
	) return varchar2
	is
		v_name	ml_mailing_lists.name%TYPE;
	begin
		select name into v_name
		from ml_mailing_lists
		where list_id = name.list_id;

		return v_name;
	end name;
end ml_mailing_list;
/
show errors





create or replace package ml_mail_class
as
	function new (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE default null,
		 package_id		in ml_mail_classes.package_id%TYPE default null,
		 name			in ml_mail_classes.name%TYPE default null,
		 locale			in ml_mail_classes.locale%TYPE default null,
		 sender_email		in ml_mail_classes.sender_email%TYPE default null,
		 subject		in ml_mail_classes.subject%TYPE default null,
		 subject_change_p	in ml_mail_classes.subject_change_p%TYPE default 't',
		 text_header		in ml_mail_classes.text_header%TYPE default null,
		 text_header_change_p	in ml_mail_classes.text_header_change_p%TYPE default 't',
		 text_body		in ml_mail_classes.text_body%TYPE default null,
		 text_body_change_p	in ml_mail_classes.text_body_change_p%TYPE default 't',
		 text_footer		in ml_mail_classes.text_footer%TYPE default null,
		 text_footer_change_p	in ml_mail_classes.text_footer_change_p%TYPE default 't',
		 html_header		in ml_mail_classes.html_header%TYPE default null,
		 html_header_change_p	in ml_mail_classes.html_header_change_p%TYPE default 't',
		 html_body		in ml_mail_classes.html_body%TYPE default null,
		 html_body_change_p	in ml_mail_classes.html_body_change_p%TYPE default 't',
		 html_footer		in ml_mail_classes.html_footer%TYPE default null,
		 html_footer_change_p	in ml_mail_classes.html_footer_change_p%TYPE default 't',
		 mime_type		in ml_mail_classes.mime_type%TYPE default 'text/plain',
		 comments		in ml_mail_classes.comments%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer;

	procedure delete (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE
	);

	procedure edit (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE default null,
		 name			in ml_mail_classes.name%TYPE default null,
		 locale			in ml_mail_classes.locale%TYPE default null,
		 sender_email		in ml_mail_classes.sender_email%TYPE default null,
		 subject		in ml_mail_classes.subject%TYPE default null,
		 subject_change_p	in ml_mail_classes.subject_change_p%TYPE default 't',
		 text_header		in ml_mail_classes.text_header%TYPE default null,
		 text_header_change_p	in ml_mail_classes.text_header_change_p%TYPE default 't',
		 text_body		in ml_mail_classes.text_body%TYPE default null,
		 text_body_change_p	in ml_mail_classes.text_body_change_p%TYPE default 't',
		 text_footer		in ml_mail_classes.text_footer%TYPE default null,
		 text_footer_change_p	in ml_mail_classes.text_footer_change_p%TYPE default 't',
		 html_header		in ml_mail_classes.html_header%TYPE default null,
		 html_header_change_p	in ml_mail_classes.html_header_change_p%TYPE default 't',
		 html_body		in ml_mail_classes.html_body%TYPE default null,
		 html_body_change_p	in ml_mail_classes.html_body_change_p%TYPE default 't',
		 html_footer		in ml_mail_classes.html_footer%TYPE default null,
		 html_footer_change_p	in ml_mail_classes.html_footer_change_p%TYPE default 't',
		 mime_type		in ml_mail_classes.mime_type%TYPE default 'text/plain',
		 comments		in ml_mail_classes.comments%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	);

	function name (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE
	) return varchar2;

end ml_mail_class;
/
show errors

create or replace package body ml_mail_class
as
	function new (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE default null,
		 package_id		in ml_mail_classes.package_id%TYPE default null,
		 name			in ml_mail_classes.name%TYPE default null,
		 locale			in ml_mail_classes.locale%TYPE default null,
		 sender_email		in ml_mail_classes.sender_email%TYPE default null,
		 subject		in ml_mail_classes.subject%TYPE default null,
		 subject_change_p	in ml_mail_classes.subject_change_p%TYPE default 't',
		 text_header		in ml_mail_classes.text_header%TYPE default null,
		 text_header_change_p	in ml_mail_classes.text_header_change_p%TYPE default 't',
		 text_body		in ml_mail_classes.text_body%TYPE default null,
		 text_body_change_p	in ml_mail_classes.text_body_change_p%TYPE default 't',
		 text_footer		in ml_mail_classes.text_footer%TYPE default null,
		 text_footer_change_p	in ml_mail_classes.text_footer_change_p%TYPE default 't',
		 html_header		in ml_mail_classes.html_header%TYPE default null,
		 html_header_change_p	in ml_mail_classes.html_header_change_p%TYPE default 't',
		 html_body		in ml_mail_classes.html_body%TYPE default null,
		 html_body_change_p	in ml_mail_classes.html_body_change_p%TYPE default 't',
		 html_footer		in ml_mail_classes.html_footer%TYPE default null,
		 html_footer_change_p	in ml_mail_classes.html_footer_change_p%TYPE default 't',
		 mime_type		in ml_mail_classes.mime_type%TYPE default 'text/plain',
		 comments		in ml_mail_classes.comments%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer
	is
		v_class_id	ml_mail_classes.mail_class_id%TYPE;
	begin
                v_class_id := acs_object.new (
                        object_id       =>      new.mail_class_id,
                        object_type     =>      'mail_class',
                        creation_date   =>      new.creation_date,
                        creation_user   =>      new.creation_user,
                        creation_ip     =>      new.creation_ip,
                        context_id      =>      new.context_id
                );

		insert into acs_named_objects (object_id, object_name, package_id) 
        	 values (v_class_id, name, package_id);

		insert into ml_mail_classes
		       (mail_class_id, package_id, name, locale, sender_email,
			subject, subject_change_p, text_header, text_header_change_p,
			text_body, text_body_change_p, text_footer, text_footer_change_p,
			html_header, html_header_change_p, html_body, html_body_change_p,
			html_footer, html_footer_change_p, mime_type, comments)
		values
		       (v_class_id, package_id, name, locale, sender_email,
			subject, subject_change_p, text_header, text_header_change_p,
			text_body, text_body_change_p, text_footer, text_footer_change_p,
			html_header, html_header_change_p, html_body, html_body_change_p,
			html_footer, html_footer_change_p, mime_type, comments);
		return v_class_id;
	end new;

	procedure delete (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE
	) is
	begin
		for attachment in (select item_id from cr_items
				where parent_id = mail_class_id) loop
			content_item.delete(attachment.item_id);
		end loop;

		delete from ml_mail_classes where mail_class_id = ml_mail_class.delete.mail_class_id;
		acs_object.delete(mail_class_id);
	end delete;

	procedure edit (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE default null,
		 name			in ml_mail_classes.name%TYPE default null,
		 locale			in ml_mail_classes.locale%TYPE default null,
		 sender_email		in ml_mail_classes.sender_email%TYPE default null,
		 subject		in ml_mail_classes.subject%TYPE default null,
		 subject_change_p	in ml_mail_classes.subject_change_p%TYPE default 't',
		 text_header		in ml_mail_classes.text_header%TYPE default null,
		 text_header_change_p	in ml_mail_classes.text_header_change_p%TYPE default 't',
		 text_body		in ml_mail_classes.text_body%TYPE default null,
		 text_body_change_p	in ml_mail_classes.text_body_change_p%TYPE default 't',
		 text_footer		in ml_mail_classes.text_footer%TYPE default null,
		 text_footer_change_p	in ml_mail_classes.text_footer_change_p%TYPE default 't',
		 html_header		in ml_mail_classes.html_header%TYPE default null,
		 html_header_change_p	in ml_mail_classes.html_header_change_p%TYPE default 't',
		 html_body		in ml_mail_classes.html_body%TYPE default null,
		 html_body_change_p	in ml_mail_classes.html_body_change_p%TYPE default 't',
		 html_footer		in ml_mail_classes.html_footer%TYPE default null,
		 html_footer_change_p	in ml_mail_classes.html_footer_change_p%TYPE default 't',
		 mime_type		in ml_mail_classes.mime_type%TYPE default 'text/plain',
		 comments		in ml_mail_classes.comments%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	) is
	begin
		update ml_mail_classes
		set name = edit.name,
		    locale = edit.locale,
		    sender_email = edit.sender_email,
		    subject = edit.subject,
		    subject_change_p = edit.subject_change_p,
		    text_header = edit.text_header,
		    text_header_change_p = edit.text_header_change_p,
		    text_body = edit.text_body,
		    text_body_change_p = edit.text_body_change_p,
		    text_footer = edit.text_footer,
		    text_footer_change_p = edit.text_footer_change_p,
		    html_header = edit.html_header,
		    html_header_change_p = edit.html_header_change_p,
		    html_body = edit.html_body,
		    html_body_change_p = edit.html_body_change_p,
		    html_footer = edit.html_footer,
		    html_footer_change_p = edit.html_footer_change_p,
		    mime_type = edit.mime_type,
		    comments = edit.comments
		where mail_class_id = edit.mail_class_id;

		update acs_named_objects
		set object_name = edit.name
		where object_id = edit.mail_class_id;

		update acs_objects
		set modifying_user = edit.modifying_user,
		    modifying_ip = edit.modifying_ip
		where object_id = edit.mail_class_id;
	end edit;

	function name (
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE
	) return varchar2
	is
		v_name	ml_mail_classes.name%TYPE;
	begin
		select name into v_name
		from ml_mail_classes
		where mail_class_id = name.mail_class_id;

		return v_name;
	end name;
end ml_mail_class;
/
show errors





create or replace package ml_mail_job
as
	function new (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE default null,
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE,
		 list_id		in ml_mail_jobs.list_id%TYPE default null,
		 selection_id		in ml_mail_jobs.selection_id%TYPE default null,
		 package_id		in ml_mail_jobs.package_id%TYPE default null,
		 locale			in ml_mail_jobs.locale%TYPE,
		 sender_email		in ml_mail_jobs.sender_email%TYPE default null,
		 track_links_p		in ml_mail_jobs.track_links_p%TYPE default 'f',
		 subject		in ml_mail_jobs.subject%TYPE default null,
		 text_header		in ml_mail_jobs.text_header%TYPE default null,
		 text_body		in ml_mail_jobs.text_body%TYPE default null,
		 text_footer		in ml_mail_jobs.text_footer%TYPE default null,
		 html_header		in ml_mail_jobs.html_header%TYPE default null,
		 html_body		in ml_mail_jobs.html_body%TYPE default null,
		 html_footer		in ml_mail_jobs.html_footer%TYPE default null,
		 template_p		in ml_mail_jobs.template_p%TYPE default 'f',
		 mime_type		in ml_mail_jobs.mime_type%TYPE default 'text/plain',
		 state			in ml_mail_jobs.state%TYPE default 'active',
		 scheduled_date		in ml_mail_jobs.scheduled_date%TYPE default sysdate,
		 bind_vars		in ml_mail_jobs.bind_vars%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer;

	procedure delete (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE
	);

	procedure edit (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE,
		 locale			in ml_mail_jobs.locale%TYPE,
		 sender_email		in ml_mail_jobs.sender_email%TYPE default null,
		 track_links_p		in ml_mail_jobs.track_links_p%TYPE default 'f',
		 subject		in ml_mail_jobs.subject%TYPE default null,
		 text_header		in ml_mail_jobs.text_header%TYPE default null,
		 text_body		in ml_mail_jobs.text_body%TYPE default null,
		 text_footer		in ml_mail_jobs.text_footer%TYPE default null,
		 html_header		in ml_mail_jobs.html_header%TYPE default null,
		 html_body		in ml_mail_jobs.html_body%TYPE default null,
		 html_footer		in ml_mail_jobs.html_footer%TYPE default null,
		 template_p		in ml_mail_jobs.template_p%TYPE default 'f',
		 mime_type		in ml_mail_jobs.mime_type%TYPE default 'text/plain',
		 state			in ml_mail_jobs.state%TYPE default 'active',
		 scheduled_date		in ml_mail_jobs.scheduled_date%TYPE default sysdate,
		 bind_vars		in ml_mail_jobs.bind_vars%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	);

	function name (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE
	) return varchar2;

end ml_mail_job;
/
show errors

create or replace package body ml_mail_job
as
	function new (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE default null,
		 mail_class_id		in ml_mail_classes.mail_class_id%TYPE,
		 list_id		in ml_mail_jobs.list_id%TYPE default null,
		 selection_id		in ml_mail_jobs.selection_id%TYPE default null,
		 package_id		in ml_mail_jobs.package_id%TYPE default null,
		 locale			in ml_mail_jobs.locale%TYPE,
		 sender_email		in ml_mail_jobs.sender_email%TYPE default null,
		 track_links_p		in ml_mail_jobs.track_links_p%TYPE default 'f',
		 subject		in ml_mail_jobs.subject%TYPE default null,
		 text_header		in ml_mail_jobs.text_header%TYPE default null,
		 text_body		in ml_mail_jobs.text_body%TYPE default null,
		 text_footer		in ml_mail_jobs.text_footer%TYPE default null,
		 html_header		in ml_mail_jobs.html_header%TYPE default null,
		 html_body		in ml_mail_jobs.html_body%TYPE default null,
		 html_footer		in ml_mail_jobs.html_footer%TYPE default null,
		 template_p		in ml_mail_jobs.template_p%TYPE default 'f',
		 mime_type		in ml_mail_jobs.mime_type%TYPE default 'text/plain',
		 state			in ml_mail_jobs.state%TYPE default 'active',
		 scheduled_date		in ml_mail_jobs.scheduled_date%TYPE default sysdate,
		 bind_vars		in ml_mail_jobs.bind_vars%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer
	is
		v_mail_job_id	ml_mail_jobs.mail_job_id%TYPE;
		v_revision_id	cr_revisions.revision_id%TYPE;
	begin
                v_mail_job_id := acs_object.new (
                        object_id       =>      new.mail_job_id,
                        object_type     =>      'mail_job',
                        creation_date   =>      new.creation_date,
                        creation_user   =>      new.creation_user,
                        creation_ip     =>      new.creation_ip,
                        context_id      =>      new.context_id
                );

		begin
		  select live_revision into v_revision_id
		  from cr_items
		  where parent_id = new.mail_class_id;
		exception when no_data_found then
		  v_revision_id := null;
		end;

		insert into acs_named_objects (object_id, object_name, package_id) 
        	 values (v_mail_job_id, subject, package_id);

		insert into ml_mail_jobs
		       (mail_job_id, list_id, selection_id, package_id, locale,
		       sender_email, track_links_p, subject, text_header, text_body,
		       text_footer, html_header, html_body, html_footer,
		       template_p, mime_type, state, scheduled_date,
		       css_revision_id, mail_class_id, bind_vars)
		values
		       (v_mail_job_id, list_id, selection_id, package_id, locale,
		       sender_email, track_links_p, subject, text_header, text_body,
		       text_footer, html_header, html_body, html_footer,
		       template_p, mime_type, state, scheduled_date,
		       v_revision_id, mail_class_id, bind_vars);

		if (new.selection_id is not null) then
		  insert into ml_mail_job_bind_vars
		  (select v_mail_job_id as mail_job_id, b.name, b.description,
		          b.default_value as value
		   from us_bind_vars b
		   where b.selection_id = new.selection_id);
		end if;

		return v_mail_job_id;
	end new;

	procedure delete (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE
	) is
	begin
		for attachment in (select item_id from cr_items
				where parent_id = mail_job_id) loop
			content_item.delete(attachment.item_id);
		end loop;
		delete from ml_mail_jobs where mail_job_id = ml_mail_job.delete.mail_job_id;
		acs_object.delete(mail_job_id);
	end delete;

	procedure edit (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE,
		 locale			in ml_mail_jobs.locale%TYPE,
		 sender_email		in ml_mail_jobs.sender_email%TYPE default null,
		 track_links_p		in ml_mail_jobs.track_links_p%TYPE default 'f',
		 subject		in ml_mail_jobs.subject%TYPE default null,
		 text_header		in ml_mail_jobs.text_header%TYPE default null,
		 text_body		in ml_mail_jobs.text_body%TYPE default null,
		 text_footer		in ml_mail_jobs.text_footer%TYPE default null,
		 html_header		in ml_mail_jobs.html_header%TYPE default null,
		 html_body		in ml_mail_jobs.html_body%TYPE default null,
		 html_footer		in ml_mail_jobs.html_footer%TYPE default null,
		 template_p		in ml_mail_jobs.template_p%TYPE default 'f',
		 mime_type		in ml_mail_jobs.mime_type%TYPE default 'text/plain',
		 state			in ml_mail_jobs.state%TYPE default 'active',
		 scheduled_date		in ml_mail_jobs.scheduled_date%TYPE default sysdate,
		 bind_vars		in ml_mail_jobs.bind_vars%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	) is
	begin
		update ml_mail_jobs
		set locale = edit.locale,
		    sender_email = edit.sender_email,
		    track_links_p = edit.track_links_p,
		    subject = edit.subject,
		    text_header = edit.text_header,
		    text_body = edit.text_body,
		    text_footer = edit.text_footer,
		    html_header = edit.html_header,
		    html_body = edit.html_body,
		    html_footer = edit.html_footer,
		    template_p = edit.template_p,
		    mime_type = edit.mime_type,
		    state = edit.state,
		    scheduled_date = edit.scheduled_date,
		    bind_vars = edit.bind_vars
		where mail_job_id = edit.mail_job_id
		and state <> 'done';

		update acs_named_objects
		set object_name = edit.subject
		where object_id = edit.mail_job_id;

		update acs_objects
		set modifying_user = edit.modifying_user,
		    modifying_ip = edit.modifying_ip
		where object_id = edit.mail_job_id;
	end edit;

	function name (
		 mail_job_id		in ml_mail_jobs.mail_job_id%TYPE
	) return varchar2
	is
		v_name	ml_mail_jobs.subject%TYPE;
	begin
		select subject into v_name
		from ml_mail_jobs
		where mail_job_id = name.mail_job_id;

		return v_name;
	end name;
end ml_mail_job;
/
show errors