Need logic

[vamsioracle]

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

[amitpatel66]

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

[debasisdas]

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.

[vamsioracle]

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

[debasisdas]

so can't you check the day of due date before sending the mail and cancel sending of the mails on Saturday and Sunday ?

[vamsioracle]

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