Calculate Leave Between Two Dates

Calculate Leave Between Two Dates

Following function will help you to get the number of leaves between two dates.

CREATE OR REPLACE function APPS.lsg_get_lv_btw_two_dates(p_person_id number,P_Period_Start_date date,P_Period_End_date date,p_leave_name varchar2) return number
is
ln_lv_curr_mnth_cnt number:=0;
ln_lv_curr_mnth_cnt1 number:=0;
ln_lv_curr_mnth_cnt2 number:=0;
ln_lv_curr_mnth_cnt3 number:=0;
ln_lv_curr_mnth_cnt4 number:=0;
p_lv_st_dt date;
p_lv_end_date date;

date1 date;
date2 date;

–Created on December 5, 2016 by Syed Farhan Ashraf
–This Function calcuates the leaves taken by employee between two dates

–Leave Starting and Ending in Same Period
cursor c_emp_lvs1
is
select paa.person_id ,paa.date_start,paa.date_end
from per_absence_attendances paa, per_absence_attendance_types paat
where paa.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
and paa.person_id = p_person_id
and UPPER (NAME) = upper(p_leave_name)
and paa.DATE_START >= date1 and paa.DATE_END <=date2;

–Leave Starting in Last Period and Ending in Current Period
cursor c_emp_lvs2
is
select paa.person_id ,paa.date_start,paa.date_end
from per_absence_attendances paa, per_absence_attendance_types paat
where paa.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
and paa.person_id = p_person_id
and UPPER (NAME) = upper(p_leave_name)
and paa.DATE_START < date1 and paa.DATE_END between date1 and date2;

–Leave Starting in Current Period but Ending in Next Period

cursor c_emp_lvs3 is
select paa.person_id ,paa.date_start,paa.date_end
from per_absence_attendances paa, per_absence_attendance_types paat
where paa.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
and paa.person_id = p_person_id and UPPER (NAME) = upper(p_leave_name)
and paa.DATE_START between date1 and date2 and paa.DATE_END > date2;

–Leave Starting in Last Period and Ending in Next Period
cursor c_emp_lvs4
is
select paa.person_id ,paa.date_start,paa.date_end
from per_absence_attendances paa, per_absence_attendance_types paat
where paa.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
and paa.person_id = p_person_id
and UPPER (NAME) = upper(p_leave_name)
and paa.DATE_START < date1 and paa.DATE_END > date2;

BEGIN

date1 := P_Period_Start_date;
date2 := P_Period_End_date;

dbms_output.put_line(‘start’);
–Leave Starting and Ending in Same Period
for r_emp_lv in c_emp_lvs1 loop
ln_lv_curr_mnth_cnt1 := r_emp_lv.date_end – r_emp_lv.date_start + 1;
ln_lv_curr_mnth_cnt := ln_lv_curr_mnth_cnt + ln_lv_curr_mnth_cnt1;
end loop;
dbms_output.put_line(‘test ln_lv_curr_mnth_cnt 1 ‘||ln_lv_curr_mnth_cnt);

–Leave Starting in Last Period and Ending in Current Period
for r_emp_lv2 in c_emp_lvs2 loop
ln_lv_curr_mnth_cnt2 := r_emp_lv2.date_end – P_Period_Start_date +1 ;
ln_lv_curr_mnth_cnt := ln_lv_curr_mnth_cnt + ln_lv_curr_mnth_cnt2;
end loop;
dbms_output.put_line(‘test ln_lv_curr_mnth_cnt 2 ‘||ln_lv_curr_mnth_cnt);

–Leave Starting in Current Period but Ending in Next Period
for r_emp_lv in c_emp_lvs3 loop
ln_lv_curr_mnth_cnt3 := P_Period_End_date – r_emp_lv.date_start +1;
ln_lv_curr_mnth_cnt := ln_lv_curr_mnth_cnt + ln_lv_curr_mnth_cnt3;
end loop;

–Leave Starting in Last Period and Ending in Next Period
dbms_output.put_line(‘test ln_lv_curr_mnth_cnt 3 ‘||ln_lv_curr_mnth_cnt);
for r_emp_lv in c_emp_lvs4 loop
ln_lv_curr_mnth_cnt4 := P_Period_End_date – P_Period_Start_date +1;
ln_lv_curr_mnth_cnt := ln_lv_curr_mnth_cnt + ln_lv_curr_mnth_cnt4;
end loop;
dbms_output.put_line(‘test ln_lv_curr_mnth_cnt 4 ‘||ln_lv_curr_mnth_cnt);
— ln_lv_curr_mnth_cnt := ln_lv_curr_mnth_cnt1 + ln_lv_curr_mnth_cnt2 + ln_lv_curr_mnth_cnt3 + ln_lv_curr_mnth_cnt4 ;

return ln_lv_curr_mnth_cnt;

end;
/

Sample to use the above Function

select lsg_get_lv_btw_two_dates(68567,’01-JAN-2016′,’31-JUL-2016′,’Annual Leave’)
from dual