/* ***************************************************************************/
/* Purpose : This Procedure to send email Status of Concurrent Request*/
/* ***************************************************************************/
CREATE OR REPLACE
PROCEDURE conc_request_status_email(p_conc_request_id IN NUMBER)
IS
lv_email_address VARCHAR2(1000);
lv_cc_email_address VARCHAR2(1000) := fnd_profile.value('CC_EMAIL');
lv_error_count NUMBER;
lv_total_count NUMBER;
lv_proc_count NUMBER;
lv_preproc_err_count VARCHAR2(1);
lv_preproc_vend_err_cnt NUMBER;
lv_smtp_port NUMBER := fnd_profile.VALUE ('FND_SMTP_PORT');
lv_smtp_host VARCHAR2(100):= fnd_profile.VALUE ('FND_SMTP_HOST');
lv_mail_conn UTL_SMTP.connection;
lv_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
lv_msg_body CLOB;
lv_request_id NUMBER;
lv_instance VARCHAR2(100);
lv_subject VARCHAR2(400);
crlf VARCHAR2(2) := chr(13)||chr(10);
l_exp_end_date VARCHAR2(100) := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
l_exp_start_date VARCHAR2(100) ;
l_req_status VARCHAR2(100) ;
CURSOR cur_req_details IS
SELECT *
FROM fnd_concurrent_requests
WHERE request_id = p_conc_request_id;
l_req_det_rec fnd_concurrent_requests%ROWTYPE;
BEGIN
OPEN cur_req_details;
FETCH cur_req_details INTO l_req_det_rec;
CLOSE cur_req_details;
--insert into xx_debug values('Parameter Count'||p_tot_files);
SELECT DECODE(l_req_det_rec.status_code,'C','Success','E','Error','X','Warning','NA')
INTO l_req_status
FROM dual;
lv_email_address := 'TEST@XYZ.com';
lv_subject := 'Subject Line - '||l_req_det_rec.request_id||' - '||l_req_status;
lv_msg_body := '<html> <head> <title>Notification:</title> </head> <body> <p>
===========================================================================<br>
================ TEST MAIL HEADER ================== =====================<br>
===========================================================================<br>
Concurrent Request Id : '||l_req_det_rec.request_id||'<br>
Parameters: <br>
Operating Unit: '||l_req_det_rec.argument1||'<br>
List of Operating Units: '||l_req_det_rec.argument2||'<br>
Invoice Date From: '||l_req_det_rec.argument3||'<br>
Invoice Date To: '||l_req_det_rec.argument4||'<br>
Sort Invoices By: '||l_req_det_rec.argument5||'<br>
Minimum Amount Due: '||l_req_det_rec.argument6||'<br>
Maximum Amount Due: '||l_req_det_rec.argument7||'<br>
Invoice Type: '||l_req_det_rec.argument8||'<br>
Trading Partner: '||l_req_det_rec.argument9||'<br>
Business Classification: '||l_req_det_rec.argument10||'<br>
List of Business Classifications '||l_req_det_rec.argument11||'<br>
Aging Period Name: '||l_req_det_rec.argument12||'<br>
Include Credit Balances: '||l_req_det_rec.argument13||'<br>
Email Status to: '||l_req_det_rec.argument17||'<br><br>
Start Time: '||to_char(l_req_det_rec.actual_start_date,'DD-MON-YYYY HH24:MI:SS')||'<br>
End Time : '||to_char(l_req_det_rec.actual_completion_date,'DD-MON-YYYY HH24:MI:SS')||'<br>
</p>
</body>
</html>';
lv_mail_conn := UTL_SMTP.open_connection (lv_smtp_host, lv_smtp_port);
UTL_SMTP.helo (lv_mail_conn, lv_smtp_host);
UTL_SMTP.mail (lv_mail_conn, 'donotreply-orcl-sys-@oracle.com');
UTL_SMTP.rcpt (lv_mail_conn, lv_email_address);
for i in (SELECT LEVEL AS id, REGEXP_SUBSTR(lv_cc_email_address, '[^;]+', 1, LEVEL) AS cc_email_name
FROM dual
CONNECT BY REGEXP_SUBSTR(lv_cc_email_address, '[^;]+', 1, LEVEL) IS NOT NULL) loop
utl_smtp.Rcpt(lv_mail_conn,i.cc_email_name);
end loop;
UTL_SMTP.open_data (lv_mail_conn);
UTL_SMTP.write_data (lv_mail_conn,'Date: '|| TO_CHAR (SYSTIMESTAMP, 'DD Mon YYYY HH24:MI:SS TZH:TZM')|| UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'To: ' || lv_email_address || UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'Cc: ' || lv_cc_email_address || UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'From: ' || 'donotreply-orcl-sys@oracle.com' || UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'Subject: ' || lv_subject || UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'Reply-To: '|| 'donotreply-orcl-sys@oracle.com'|| UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'Content-Type: multipart/mixed; boundary="'|| lv_boundary|| '"'|| UTL_TCP.crlf|| UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'--' || lv_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'Content-Type: text/html; charset="iso-8859-1"'|| UTL_TCP.crlf|| UTL_TCP.crlf);
--write email body
UTL_SMTP.write_data(lv_mail_conn, lv_msg_body);
UTL_SMTP.write_data (lv_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data (lv_mail_conn,'--' || lv_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data (lv_mail_conn);
UTL_SMTP.quit (lv_mail_conn);
dbms_output.put_line('Successfully Send the Mail '||SQLERRM);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Un-Expected Error '||SQLERRM);
END conc_request_status_email;
Adding/Calling this using the Shell Script
statusmail=`sqlplus -s ${cp_apps_passwd} <<!
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET SERVEROUT ON SIZE 100000
declare
l_resp_id number :=0;
l_appl_id number :=0;
l_user_id number :=0;
BEGIN
dbms_output.enable(100000);
select responsibility_id,RESPONSIBILITY_APPLICATION_ID,requested_by
INTO l_resp_id,l_appl_id,l_user_id
FROM fnd_concurrent_requests
WHERE request_id = $cp_request_id;
fnd_global.apps_initialize(l_user_id,l_resp_id,l_appl_id);
abc_pkg.conc_request_status_email(p_conc_request_id => '$cp_parent_req_id');
COMMIT;
END;
/
EXIT;
!`
No comments:
Post a Comment