Kindly compile first procedure APPS.XX_UTL_MAIL_SEND and then compile APPS.XXGLDAILYRATES_ALERT.
Register concurrent program for APPS.XXGLDAILYRATES_ALERT procedure as executable type PLSQL procedure and schedule this concurrent program for your desire time. It will give you all detail for GL rates updated/ created on current date with detail like from currency, to currency, conversion type, conversion date and conversion rate.
CREATE OR REPLACE PROCEDURE APPS.XXGLDAILYRATES_ALERT
--(ERRBUF OUT VARCHAR2,
--RETCODE OUT NUMBER)
IS
CURSOR C
IS
SELECT *
FROM GL_DAILY_RATES
WHERE TRUNC(LAST_UPDATE_DATE)=TRUNC(SYSDATE);
l_data VARCHAR2(32000);
l_header VARCHAR2(32000);
l_message VARCHAR2(200);
l_subject VARCHAR2(200);
l_err_message VARCHAR2(200);
l_email VARCHAR2(200);
BEGIN
l_data:= l_data || '<table border=1 BORDERCOLOR="#D6EBFF">';
l_data:= l_data || '<tr><td><b>FROM CURRENCY</b></td><td><b>TO CURRENCY</b></td><td><b>CONVESRION TYPE</b>
</td><td><b>CONVERSION DATE</b></td><td><b>CONVERSION RATE</b></td></tr>';
FOR I IN C LOOP
l_data:= l_data || '<tr>';
l_data:= l_data || '<td>';
l_data:= l_data || I.FROM_CURRENCY;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.TO_CURRENCY;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.CONVERSION_TYPE;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.CONVERSION_DATE;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.CONVERSION_RATE;
l_data:= l_data || '</td>';
l_data:= l_data || '</tr>';
END LOOP;
l_data:= l_data || '</table><br>';
l_email := '';
l_email := 'johnsmith123@hotmail.com';
l_header:= l_header || '<HTML>';
l_header:= l_header || '<HTML>GL DAILY RATES UPDATED <br><br> ';
l_header:= l_header || l_data;
l_header:= l_header || '<br><br>********************************************************************************************************************************';
l_header:= l_header || '</HTML>';
l_message := 'Kindly refer attached GL Daily Rates List ';
l_subject := 'GL DAILY STATUS REPORT'||'|'||sysdate;
XX_UTL_MAIL_SEND ('testmail@yahoo.com',
l_email,'will_smith@gmail.com',
l_subject,
l_message ,
l_header,
'gl_daily_rate_Import.html',
true);
l_data:= '';
l_header:= '';
EXCEPTION WHEN OTHERS THEN
l_err_message := SQLERRM;
XX_UTL_MAIL_SEND ('testmail@yahoo.com',
l_email,'will_smith@gmail.com',
'Error in GL DAILY RATE ALERT Program',
l_err_message ,
l_err_message,
'gl_daily_rate_Import.html',
true);
dbms_output.put_line('ERROR :' || SQLERRM);
END;
/
===========================================================================================
-----to execute this query you have give access to apps from SYS for utl mail packages
CREATE OR REPLACE PROCEDURE APPS.XX_UTL_MAIL_SEND
(p_email_frm varchar2,
p_email_to varchar2,
p_email_cc varchar2,
p_email_subj varchar2,
p_email_msg varchar2,
p_email_content varchar2,
p_email_attch varchar2,
p_attch_line boolean)
IS
l_err_message varchar2(1500);
Begin
utl_mail.send_attach_raw(sender=> p_email_frm,
recipients => p_email_to,
cc => p_email_cc,
subject => p_email_subj,
message => p_email_msg ,
attachment => utl_raw.cast_to_raw(p_email_content),
att_inline => p_attch_line,
att_filename => p_email_attch);
EXCEPTION WHEN OTHERS THEN
l_err_message := SQLERRM;
End;
/
Register concurrent program for APPS.XXGLDAILYRATES_ALERT procedure as executable type PLSQL procedure and schedule this concurrent program for your desire time. It will give you all detail for GL rates updated/ created on current date with detail like from currency, to currency, conversion type, conversion date and conversion rate.
CREATE OR REPLACE PROCEDURE APPS.XXGLDAILYRATES_ALERT
--(ERRBUF OUT VARCHAR2,
--RETCODE OUT NUMBER)
IS
CURSOR C
IS
SELECT *
FROM GL_DAILY_RATES
WHERE TRUNC(LAST_UPDATE_DATE)=TRUNC(SYSDATE);
l_data VARCHAR2(32000);
l_header VARCHAR2(32000);
l_message VARCHAR2(200);
l_subject VARCHAR2(200);
l_err_message VARCHAR2(200);
l_email VARCHAR2(200);
BEGIN
l_data:= l_data || '<table border=1 BORDERCOLOR="#D6EBFF">';
l_data:= l_data || '<tr><td><b>FROM CURRENCY</b></td><td><b>TO CURRENCY</b></td><td><b>CONVESRION TYPE</b>
</td><td><b>CONVERSION DATE</b></td><td><b>CONVERSION RATE</b></td></tr>';
FOR I IN C LOOP
l_data:= l_data || '<tr>';
l_data:= l_data || '<td>';
l_data:= l_data || I.FROM_CURRENCY;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.TO_CURRENCY;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.CONVERSION_TYPE;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.CONVERSION_DATE;
l_data:= l_data || '</td>';
l_data:= l_data || '<td>';
l_data:= l_data || I.CONVERSION_RATE;
l_data:= l_data || '</td>';
l_data:= l_data || '</tr>';
END LOOP;
l_data:= l_data || '</table><br>';
l_email := '';
l_email := 'johnsmith123@hotmail.com';
l_header:= l_header || '<HTML>';
l_header:= l_header || '<HTML>GL DAILY RATES UPDATED <br><br> ';
l_header:= l_header || l_data;
l_header:= l_header || '<br><br>********************************************************************************************************************************';
l_header:= l_header || '</HTML>';
l_message := 'Kindly refer attached GL Daily Rates List ';
l_subject := 'GL DAILY STATUS REPORT'||'|'||sysdate;
XX_UTL_MAIL_SEND ('testmail@yahoo.com',
l_email,'will_smith@gmail.com',
l_subject,
l_message ,
l_header,
'gl_daily_rate_Import.html',
true);
l_data:= '';
l_header:= '';
EXCEPTION WHEN OTHERS THEN
l_err_message := SQLERRM;
XX_UTL_MAIL_SEND ('testmail@yahoo.com',
l_email,'will_smith@gmail.com',
'Error in GL DAILY RATE ALERT Program',
l_err_message ,
l_err_message,
'gl_daily_rate_Import.html',
true);
dbms_output.put_line('ERROR :' || SQLERRM);
END;
/
===========================================================================================
-----to execute this query you have give access to apps from SYS for utl mail packages
CREATE OR REPLACE PROCEDURE APPS.XX_UTL_MAIL_SEND
(p_email_frm varchar2,
p_email_to varchar2,
p_email_cc varchar2,
p_email_subj varchar2,
p_email_msg varchar2,
p_email_content varchar2,
p_email_attch varchar2,
p_attch_line boolean)
IS
l_err_message varchar2(1500);
Begin
utl_mail.send_attach_raw(sender=> p_email_frm,
recipients => p_email_to,
cc => p_email_cc,
subject => p_email_subj,
message => p_email_msg ,
attachment => utl_raw.cast_to_raw(p_email_content),
att_inline => p_attch_line,
att_filename => p_email_attch);
EXCEPTION WHEN OTHERS THEN
l_err_message := SQLERRM;
End;
/
No comments:
Post a Comment