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 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_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 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_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 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_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