Hi Folks
I have written a code to send emails from oracle. Below is the code. I have registered this is Apps and scheduled the concurrent program to run every day. Now i want implement logic, such that even though the concurrent program runs, it should not send email on Saturdays and Sundays.
The due date in the below code is always the next_day of the day I apply leave. It can be Saturday/Sunday. So my package is dependent on due date.
thanks
vamsi
I have written a code to send emails from oracle. Below is the code. I have registered this is Apps and scheduled the concurrent program to run every day. Now i want implement logic, such that even though the concurrent program runs, it should not send email on Saturdays and Sundays.
The due date in the below code is always the next_day of the day I apply leave. It can be Saturday/Sunday. So my package is dependent on due date.
Code:
CREATE OR REPLACE PACKAGE BODY APPS.CBTT_LOA_REM_SUP_PKG AS
PROCEDURE LOA_REM_SUP_MAIL (errbuf out varchar2,
retcode out varchar2) is
l_transaction_id VARCHAR2(2000);
l_person_name varchar2(240);
l_itemtype varchar2(5) :='HRSSA';
l_itemkey varchar2(10);
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
usrname VARCHAR2( 30 );
l_usraddr VARCHAR2( 100 );
l_user_name varchar2(240);
subj varchar2(100) := 'has applied for Leave of Absence';
Body varchar2(4000);
l_state varchar2(10);
addrlist addresslist_tab;
addrcnt BINARY_INTEGER:= 0;
l_current_forward_to_id number := 0;
l_creator_person_id number := 0;
l_position_name varchar2(100);
l_absence_attendance_type_id number;
l_absence_days number;
l_date_end date;
l_date_start date;
l_absence_name varchar2(100);
l_manager_id number;
l_supervisor_name varchar2(240);
temp_var1 varchar2(200);
temp_superviosr_id varchar2(200);
temp_var3 varchar2(100);
cursor cur_pending_transactions is
select hat.transaction_id,
hat.item_key,
wias.due_date,
hat.transaction_effective_date
from hr_api_transactions hat,
WF_ITEM_ACTIVITY_STATUSES wias
where hat.item_type ='HRSSA'
and hat.item_type = wias.item_type
and hat.item_key = wias.item_key
and hat.process_name ='HR_LOA_JSP_PRC'
and hat.status = 'Y' ----Pending For Approval
and to_date(wias.due_date) < sysdate
and trunc(TRANSACTION_EFFECTIVE_DATE) >= trunc(sysdate-5);
cursor cur_email_Address (cp_person_id number) is
select distinct papf.full_name,
fu.email_address
from fnd_user fu,
per_all_people_f papf
where papf.person_id = cp_person_id
and papf.person_id =fu.employee_id
and trunc(sysdate) between effective_start_date and effective_end_date;
cursor cur_full_name (cp_person_id number) is
select distinct full_name
from per_all_people_f
where person_id =cp_person_id
and business_group_id = 81
and trunc(sysdate) between effective_start_date and effective_end_date;
cursor cur_absence_details(cp_transaction_id number,cp_absence_par varchar2) is
select distinct name,
decode(datatype,'NUMBER',to_char(number_value),
'DATE',to_char(date_value,'DD-MON-YYYY'),NULL) value from hr_api_transactions hat,
hr_api_transaction_steps hats,
hr_api_transaction_values hatv
where hat.transaction_id = cp_transaction_id
and hat.transaction_id = hats.transaction_id
and hats.transaction_step_id = hatv.transaction_step_id
and hatv.name = cp_absence_par;
cursor cur_selected_person_id (cp_transaction_id number) is
select creator_person_id
from hr_api_transactions hat
where hat.transaction_id = cp_transaction_id; --- changed to creator_person_id from selector_person_id
cursor cur_hr_person_id is
select distinct person_id,
name
from per_all_assignments_f paaf,
hr_all_positions_f hapf
where hapf.position_id = paaf.position_id
and hapf.name ='87.Assistant Manager,HR.'
and paaf.business_group_id =81
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_date;
cursor cur_absence_name (cp_attendance_type_id number) is
select name
from per_absence_attendance_types
where absence_attendance_type_id = cp_attendance_type_id
and business_group_id = 81;
BEGIN for rec_pending in cur_pending_transactions
loop
l_transaction_id := rec_pending.transaction_id;
l_itemkey := rec_pending.item_key;
l_current_forward_to_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => l_itemtype ,itemkey => l_itemkey ,aname => 'FORWARD_TO_PERSON_ID'),wf_engine.GetItemAttrNumber(itemtype => l_itemtype,itemkey => l_itemkey,aname => 'CREATOR_PERSON_ID'));
for rec_person_id in cur_selected_person_id(l_transaction_id)
loop
l_creator_person_id := rec_person_id.creator_person_id; --changed to creator from selected on jan 5th,vamsidhar pala
end loop;
for rec_full_name in cur_full_name(l_creator_person_id)
loop
l_person_name := rec_full_name.full_name;
end loop;
For rec_absence in cur_absence_details(l_transaction_id,'P_ABSENCE_ATTENDANCE_TYPE_ID')
loop
l_absence_attendance_type_id := rec_absence.value;
end loop;
for rec_absence_name in cur_absence_name(l_absence_attendance_type_id)
loop
l_absence_name := rec_absence_name.name;
end loop;
For rec_absence in cur_absence_details(l_transaction_id,'P_ABSENCE_DAYS')
loop
l_absence_days := rec_absence.value;
end loop;
For rec_absence in cur_absence_details(l_transaction_id,'P_DATE_START')
loop
l_date_start := rec_absence.value;
end loop;
For rec_absence in cur_absence_details(l_transaction_id,'P_DATE_END')
loop
l_date_end := rec_absence.value;
end loop;
---------------------------------------------------------------------------------------------------------------------------
if trunc(sysdate) = trunc(rec_pending.due_date) then
for i in 1..2
loop
if (i=1) then
For rec_person in cur_email_address(l_current_forward_to_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
---changed by vamsi on 5th january 2009
select distinct supervisor_id into temp_superviosr_id from per_all_people_f a, per_all_assignments_f b
where a.person_id = b.person_id and a.person_id = l_creator_person_id and trunc(b.effective_end_date) >trunc(sysdate);
select distinct email_address into temp_var3 from fnd_user where employee_id = temp_superviosr_id;
l_usraddr := temp_var3;
body := crlf||l_user_name||crlf||crlf||
'Mr./Ms./Mrs '||l_person_name||' submitted an application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||
'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||'Please ensure that the application is processed within 24 hours of receipt of this reminder.';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
elsif (i =2) then
l_manager_id := TO_NUMBER(substr(CBTT_AME_PKG.CBTT_AME_MANAGER(l_transaction_id),11));
For rec_person in cur_email_address(l_manager_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
For rec_person in cur_email_address(l_current_forward_to_id)
loop
l_supervisor_name := rec_person.full_name;
End loop;
body := crlf||l_user_name||crlf||crlf||
'Mr./Ms./Mrs '||l_person_name||' submitted an application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||
'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY ')||crlf||crlf|| 'The Supervisor has not yet processed the application. Kindly ensure that the Supervisor processes this application within 24 hours of receipt of this reminder. ';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
end if;
end loop;
---------------------------------------------------------------------------------------------------------------------------
elsif trunc((sysdate) - rec_pending.due_date) = 1
then
---email to the supervisor
for rec_count in 1..3
loop
if rec_count = 1
then
For rec_person in cur_email_address(l_current_forward_to_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
---changed by vamsi on 5th january 2009
select distinct supervisor_id into temp_superviosr_id from per_all_people_f a, per_all_assignments_f b
where a.person_id = b.person_id and a.person_id = l_creator_person_id and b.effective_end_date ='31-DEC-4712';
select distinct email_address into temp_var3 from fnd_user where employee_id = temp_superviosr_id;
l_usraddr := temp_var3;
body := crlf||l_user_name||crlf||crlf||
'Mr./Ms./Mrs '||l_person_name||' submitted an application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||
'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||
'However, you failed to process the application within the stipulated 48 hour period and have affected the efficiency of the system.
The Departmental Manager has been advised of this delay and will liaise with you in this regard. To ensure the integrity of the system, you are required to process the application via the system.';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
elsif rec_count = 2 then ---email to employee who has submitted the application
for i in 1..2
loop
if (i=1)
then
For rec_person in cur_email_address(l_creator_person_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
body := crlf||l_user_name||crlf||crlf||
'Your application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||crlf||
'submitted on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||
'was not processed by your Supervisor within the stipulated 48 hour period.'||crlf||crlf||
'This has affected the efficiency of the system.'||crlf||crlf||
'Please liaise with your Supervisor to ensure that your application is immediately processed.';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
elsif rec_count = 3 then ---email to Departmental manager of the employee who has submitted the application
l_manager_id := TO_NUMBER(substr(CBTT_AME_PKG.CBTT_AME_MANAGER(l_transaction_id),11));
For rec_person in cur_email_address(l_manager_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
For rec_person in cur_email_address(l_current_forward_to_id)
loop
l_supervisor_name := rec_person.full_name;
End loop;
body := crlf||l_user_name||crlf||crlf||
'Mr./Ms./Mrs '||l_person_name||' submitted an application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||
'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY ')||crlf||crlf||
'Mr./Ms./Mrs '||l_supervisor_name||' has not processed the application within the stipulated 48 hour period and this has affected the efficiency of the system. You intervention is required. Kindly instruct the Supervisor to immediately process the application. If the Supervisor is unable to electronically process the application at this time, kindly email the employee advising whether you have approved or rejected the application and submit a copy of the email to the Assistant Manger, HR. The supervisor will be required to approve the application via Self Service at a later date.';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
----------added on 15th august 2009
elsif(1=2)
then
For rec_person in cur_email_address(l_creator_person_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
body := crlf||l_user_name||crlf||crlf||
'Your application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||crlf||
'submitted on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||
'was not processed by your Supervisor within the stipulated 48 hour period.'||crlf||crlf||
'This has affected the efficiency of the system.'||crlf||crlf||
'Your Departmental Manager has been advised of this delay and will liaise with you, to advise whether your application has been approved or not';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
end if;
end loop;
end if;
end loop;
-------------------------------------------
elsif trunc((sysdate) - rec_pending.due_date) = 2
then
l_manager_id := TO_NUMBER(substr(CBTT_AME_PKG.CBTT_AME_MANAGER(l_transaction_id),11));
For rec_person in cur_email_address(l_manager_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
For rec_person in cur_email_address(l_current_forward_to_id)
loop
l_supervisor_name := rec_person.full_name;
End loop;
body := crlf||l_user_name||crlf||crlf||
'Mr./Ms./Mrs '||l_person_name||' submitted an application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||
'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY ')||crlf||crlf||
'Please ensure that the application is processed within 24 hours of receipt of this reminder.';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
-----------------------------------------------------------------------------
elsif trunc((sysdate) - rec_pending.due_date) = 3
then
for j in 1..2
loop
if (j=1) --- Mail to HR Manager
then
For rec_hr_manager in cur_hr_person_id
loop
For rec_person in cur_email_address(rec_hr_manager.person_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
End loop;
body := crlf||l_user_name||crlf||crlf||
'Mr./Ms./Mrs '||l_person_name||' submitted an application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||
'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf|| 'The Manager did not process the application within the stipulated 48 hour period and has affected the efficiency of the system. Please contact the employee, the relevant Supervisor and Departmental Manager accordingly. ';
select distinct email_address into l_usraddr from fnd_user where employee_id = 1557; -----Request from Kurtis Clarke to hard code;
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
elsif(j=2) then
l_manager_id := TO_NUMBER(substr(CBTT_AME_PKG.CBTT_AME_MANAGER(l_transaction_id),11));
For rec_person in cur_email_address(l_manager_id)
loop
l_usraddr := rec_person.email_address;
l_user_name := rec_person.full_name;
End loop;
For rec_person in cur_email_address(l_current_forward_to_id)
loop
l_supervisor_name := rec_person.full_name;
End loop;
body := crlf||l_user_name||crlf||crlf||
'Mr./Ms./Mrs '||l_person_name||' submitted an application for '||crlf||crlf||
'Leave Type : '|| substr(L_absence_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days||' day(s)'||crlf||
'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY ')||crlf||crlf||
'Please ensure that the application is processed within 24 hours of receipt of this reminder.';
conn:= utl_smtp.open_connection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );
utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;
mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
l_usraddr := '';
l_user_name := '';
addrcnt := 0;
end if;
end loop;
end if;
end loop;
end LOA_REM_SUP_MAIL;
End CBTT_LOA_REM_SUP_PKG;
/
thanks
vamsi
Comment