-- -- acs_mail_body__new/15 -- create or replace function acs_mail_body__new( integer, integer, integer, timestamp with time zone, character varying, character varying, text, text, text, integer, character varying, date, integer, character varying, integer ) returns int4 as $$ declare p_body_id alias for $1; -- default null p_body_reply_to alias for $2; -- default null p_body_from alias for $3; -- default null p_body_date alias for $4; -- default null p_header_message_id alias for $5; -- default null p_header_reply_to alias for $6; -- default null p_header_subject alias for $7; -- default null p_header_from alias for $8; -- default null p_header_to alias for $9; -- default null p_content_item_id alias for $10; -- default null p_object_type alias for $11; -- default acs_mail_body p_creation_date alias for $12; -- default now() p_creation_user alias for $13; -- default null p_creation_ip alias for $14; -- default null p_context_id alias for $15; -- default null v_object_id integer; v_system_url varchar; v_domain_name varchar; v_idx integer; v_header_message_id acs_mail_bodies.header_message_id%TYPE; begin v_object_id := acs_mail_gc_object__new ( p_body_id, -- gc_object_id p_object_type, -- object_type p_creation_date, -- creation_date p_creation_user, -- creation_user p_creation_ip, -- creation_ip p_context_id -- context_id ); -- vinodk: get SystemURL parameter and use it to extract domain name select apm__get_value(package_id, 'SystemURL') into v_system_url from apm_packages where package_key='acs-kernel'; v_idx := position('http://' in v_system_url); v_domain_name := trim (substr(v_system_url, v_idx + 7)); v_header_message_id := coalesce (p_header_message_id, current_date || '.' || v_object_id || '@' || v_domain_name || '.sddd'); insert into acs_mail_bodies (body_id, body_reply_to, body_from, body_date, header_message_id, header_reply_to, header_subject, header_from, header_to, content_item_id) values (v_object_id, p_body_reply_to, p_body_from, p_body_date, v_header_message_id, p_header_reply_to, p_header_subject, p_header_from, p_header_to, p_content_item_id); return v_object_id; end; $$ language plpgsql;