Calculate Leave Between Two Dates

By | December 5, 2016

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

If you have liked contents in this post and you think it can be helpful to others, please share it at least once in your circle, in this way, you will join me in my cause to Learn Share and Grow. Come on, Lets Grow Together.

Related Article
Payroll Processes and Relevant Tables
Payroll Mistakes Correction Methodology
Element Termination Rules
Get Free Evaluation of your HRMS Skills
9 Proven Steps to become Oracle Apps Consultant
XML Bursting Feature and its Solution files