Execution details in email notification

[Ethender]

Hi All,

I am using the following function to send execution details of the process-flow. This process-flow is having 20 mappings. This is working fine.


The cIient requirment is to pass email-IDs from a back-end table(sam_mail_notification) with columns(smtp, port, from_address and to_address), right now the function is working on static(constant) values, Can I get the modified function from you.

--initialize variables here

-- main body

retval number := 0; --default 
 
mailhost CONSTANT VARCHAR2(30) := 'EMAILSERVER';
 
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
 
pSender VARCHAR2(30) := '[e-mail]';
 
pRecipient VARCHAR2(30) := '[e-mail]';
 
pSubject VARCHAR2(100) := 'Process REsults for: '||to_char(sysdate,'dd/mm/yyyy');
 
mesg VARCHAR2(32767);
 
mail_conn utl_smtp.connection;
 
cursor getResults is
 
select AREA.OBJECT_NAME,
AREA.CREATED_ON,
AREA.UPDATED_ON,
aramr.ELAPSE_TIME,
NUMBER_RECORDS_SELECTED,
NUMBER_RECORDS_INSERTED,
NUMBER_RECORDS_UPDATED,
NUMBER_ERRORS,
AREA.RETURN_RESULT,
AREA.EXECUTION_AUDIT_STATUS,
MESSAGE_SEVERITY,
MESSAGE_TEXT
from
all_Rt_audit_executions area,
all_Rt_audit_map_runs aramr,
all_rt_audit_exec_messages err
where AREA.execution_audit_id = ARAMR.execution_audit_id(+) AND
AREA.execution_audit_id = err.execution_audit_id(+)
and
trunc(area.created_on) = trunc(sysdate)
AND AREA.OBJECT_NAME IS NOT NULL AND AREA.TASK_TYPE!='ProcessFlow'
AND area.top_level_execution_audit_id =(select max(top_level_execution_audit_id)from all_Rt_audit_executions
where execution_name = (select execution_name from all_Rt_audit_executions where task_type ='ProcessFlow' and top_level_execution_audit_id =
(select max(top_level_execution_audit_id) from all_Rt_audit_executions)));
 
BEGIN 
 
mail_conn := utl_smtp.open_connection(mailhost, 25); 
 
mesg := 'Date: ' ||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf || 
 
'From: <'|| pSender ||'>' || crlf || 
 
'Subject: '|| pSubject || crlf || 
 
'To: '||pRecipient || crlf || '' || crlf || 
 
'MAPNAME START_TIME END_TIME ELAPSED SELECTED INSERTED UPDATED ERRORS RESULT AUDIT_STSTUS SEVERITY MESSAGE_TEXT'||crlf ; 
 
for rec in getResults 
 
loop 
 
mesg:=mesg || rpad(rec.OBJECT_NAME,30)|| 
 
rpad(to_char(rec.CREATED_ON, 'HH24:MI:SS DD-MON-YY'),20)|| 
 
rpad(to_char(rec.UPDATED_ON, 'HH24:MI:SS DD-MON-YY'),24)|| 
 
rpad(to_char(rec.ELAPSE_TIME),7)|| 
 
rpad(to_char(rec.NUMBER_RECORDS_SELECTED),8)|| 
 
rpad(to_char(rec.NUMBER_RECORDS_INSERTED),10)|| 
 
rpad(to_char(rec.NUMBER_RECORDS_UPDATED),10)|| 
 
rpad(to_char(rec.NUMBER_ERRORS),5)|| 
 
rpad(to_char(rec.RETURN_RESULT),8)|| 
 
rpad(to_char(rec.EXECUTION_AUDIT_STATUS),10)|| 
 
rpad(to_char(rec.MESSAGE_SEVERITY),10)|| 
 
rpad(to_char(rec.MESSAGE_TEXT),200)||crlf; 
 
end loop; 
 
utl_smtp.helo(mail_conn, mailhost); 
 
utl_smtp.mail(mail_conn, pSender); 
 
utl_smtp.rcpt(mail_conn, pRecipient); 
 
utl_smtp.data(mail_conn, mesg); 
 
utl_smtp.quit(mail_conn); 
 
return retval;
 
EXCEPTION 
 
WHEN OTHERS THEN 
 
return 1; 
END; 

Regards,

[amitpatel66]

Yes, of course you can change your function to accept input parameters as you require. Why dont you try changeing your function by including variables and posting back in case if you face any error!!

[Ethender]

Yes, of course you can change your function to accept input parameters as you require. Why dont you try changeing your function by including variables and posting back in case if you face any error!!

Hi Amit,

But the values should come from database table only.

Regards.