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