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[code=oracle]
retval number := 0; --default

mailhost CONSTANT VARCHAR2(30) := 'EMAILSERVER';

crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);

pSender VARCHAR2(30) := 'abc@dub.ae';

pRecipient VARCHAR2(30) := 'abc@dub.ae';

pSubject VARCHAR2(100) := 'Process REsults for: '||to_char(sysd ate,'dd/mm/yyyy');

mesg VARCHAR2(32767) ;

mail_conn utl_smtp.connec tion;

cursor getResults is

select AREA.OBJECT_NAM E,
AREA.CREATED_ON ,
AREA.UPDATED_ON ,
aramr.ELAPSE_TI ME,
NUMBER_RECORDS_ SELECTED,
NUMBER_RECORDS_ INSERTED,
NUMBER_RECORDS_ UPDATED,
NUMBER_ERRORS,
AREA.RETURN_RES ULT,
AREA.EXECUTION_ AUDIT_STATUS,
MESSAGE_SEVERIT Y,
MESSAGE_TEXT
from
all_Rt_audit_ex ecutions area,
all_Rt_audit_ma p_runs aramr,
all_rt_audit_ex ec_messages err
where AREA.execution_ audit_id = ARAMR.execution _audit_id(+) AND
AREA.execution_ audit_id = err.execution_a udit_id(+)
and
trunc(area.crea ted_on) = trunc(sysdate)
AND AREA.OBJECT_NAM E IS NOT NULL AND AREA.TASK_TYPE! ='ProcessFlow'
AND area.top_level_ execution_audit _id =(select max(top_level_e xecution_audit_ id)from all_Rt_audit_ex ecutions
where execution_name = (select execution_name from all_Rt_audit_ex ecutions where task_type ='ProcessFlow' and top_level_execu tion_audit_id =
(select max(top_level_e xecution_audit_ id) from all_Rt_audit_ex ecutions)));

BEGIN

mail_conn := utl_smtp.open_c onnection(mailh ost, 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(re c.CREATED_ON, 'HH24:MI:SS DD-MON-YY'),20)||

rpad(to_char(re c.UPDATED_ON, 'HH24:MI:SS DD-MON-YY'),24)||

rpad(to_char(re c.ELAPSE_TIME), 7)||

rpad(to_char(re c.NUMBER_RECORD S_SELECTED),8)| |

rpad(to_char(re c.NUMBER_RECORD S_INSERTED),10) ||

rpad(to_char(re c.NUMBER_RECORD S_UPDATED),10)| |

rpad(to_char(re c.NUMBER_ERRORS ),5)||

rpad(to_char(re c.RETURN_RESULT ),8)||

rpad(to_char(re c.EXECUTION_AUD IT_STATUS),10)| |

rpad(to_char(re c.MESSAGE_SEVER ITY),10)||

rpad(to_char(re c.MESSAGE_TEXT) ,200)||crlf;

end loop;

utl_smtp.helo(m ail_conn, mailhost);

utl_smtp.mail(m ail_conn, pSender);

utl_smtp.rcpt(m ail_conn, pRecipient);

utl_smtp.data(m ail_conn, mesg);

utl_smtp.quit(m ail_conn);

return retval;

EXCEPTION

WHEN OTHERS THEN

return 1;
END; [/code]


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.