Need logic | Familiar Sight | | Join Date: Jun 2007
Posts: 151
| |
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. -
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
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Need logic
First thing i would suggest you to do is place the UTL_SMTP piece of code in a seperate sub program and call it where ever necessary.Your package body is having lots of redundant code of sending mail functionality.
Second, just add a condition to check if the due date falls on saturday or sunday...if yes then dont call your send mail routine else go ahead....you can handle this by a simple IF in your package body
|  | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,508
| | | re: Need logic
please do not post such lengthy code .
to solve your problem check the day of duedate, that will solve the entire issue.
Follow good programming practices as suggested by Amit.
| | Familiar Sight | | Join Date: Jun 2007
Posts: 151
| | | re: Need logic
Thanks a lot,
The due date is always the next day of the leave application. suppose if i apply leave today, the due date in the system will be tommorow
vamsi
| | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,508
| | | re: Need logic
so can't you check the day of due date before sending the mail and cancel sending of the mails on Saturday and Sunday ?
| | Familiar Sight | | Join Date: Jun 2007
Posts: 151
| | | re: Need logic
Hi
The code is written such that,
we compare sysdate-duedate.
So if sysdate-duedate = 1 ---- it sends a mail to some one
if sysdate-duedate =2 ----- it send the other mail
So, i am confused, because if i dont send mail on sunday, then on monday, sysdate - due date will change and i may end up sending worng mails on the wrong days
vamsi
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
