Absence Management

This is the package and formula to calculate the duration and validations for different type of leaves.


CREATE OR REPLACE PACKAGE APPS.xxtad_hr_absence_pkg AS
-- +===================================================================+
-- | Name : _HR_ABSENCE_PKG
-- | Author : Syed Jaffer |
-- | Description : Package Specification for Absence Managemnt
-- | Created Date : 27-Dec-2008
-- | Updated Date : |
-- +===================================================================+
FUNCTION get_absence_type(
p_business_group_id IN NUMBER
,p_absence_attendance_type_id IN NUMBER)
RETURN VARCHAR2;

FUNCTION xxcheck_leave_eligibility( xassignment_id in number
,xstart_date in date )
RETURN NUMBER;

FUNCTION xxabs_annual_leave_elig_date( xassignment_id in number
,xstart_date in date)
RETURN VARCHAR2;
FUNCTION xxabs_sick_leave_elig_date( xassignment_id in number
,xstart_date in date)
RETURN VARCHAR2;
FUNCTION xxabs_maternity_leav_elig_date( xassignment_id in number
,xstart_date in date )
RETURN VARCHAR2;

FUNCTION xxabs_haj_leave_elig_date( xassignment_id in number
,xstart_date in date )
RETURN VARCHAR2;

FUNCTION xxabs_marr_leave_elig_date( xassignment_id in number
,xstart_date in date )
RETURN VARCHAR2;


FUNCTION xxabs_study_leave_elig_date( xassignment_id in number
,xstart_date in date )
RETURN VARCHAR2;


FUNCTION xxabs_get_committed_leave_days(
x_assignment_id IN NUMBER
,x_business_group_id IN NUMBER
,x_leave_type IN VARCHAR2
,x_year IN VARCHAR2
,x_absence_attendance_type_id IN NUMBER)
RETURN NUMBER;

FUNCTION xxabs_get_casual_days(
x_assignment_id IN NUMBER
,x_business_group_id IN NUMBER
,x_start_date IN DATE
,x_end_date IN DATE
,x_absence_attendance_type_id IN NUMBER)
RETURN VARCHAR2;

FUNCTION xxabs_get_unpaid_days(
x_assignment_id IN NUMBER
,x_business_group_id IN NUMBER
,x_start_date IN DATE
,x_end_date IN DATE
,x_absence_attendance_type_id IN NUMBER)
RETURN VARCHAR2;

END xxtad_hr_absence_pkg;
/

_____________________________________________________________________

CREATE OR REPLACE PACKAGE BODY APPS.xxtad_hr_absence_pkg AS
-- +===================================================================+
-- | Name : XXTAD_HR_ABSENCE_PKG
-- | Author : Syed Jaffer |
-- | Description : Package Body for Absence Managemnt
-- | Created Date : 27-Dec-2008
-- | Updated Date : |
-- +===================================================================+

--Function to get the absence type
FUNCTION get_absence_type( p_business_group_id IN NUMBER
,p_absence_attendance_type_id IN NUMBER)
RETURN VARCHAR2 AS
lc_absence_type VARCHAR2(60);
CURSOR lcu_get_absence_type (p_business_group_id NUMBER, p_absence_attendance_type_id NUMBER) IS
SELECT name
FROM per_absence_attendance_types
WHERE absence_attendance_type_id = p_absence_attendance_type_id
and business_group_id=102
AND business_group_id = p_business_group_id;
BEGIN
OPEN lcu_get_absence_type (p_business_group_id, p_absence_attendance_type_id);

FETCH lcu_get_absence_type INTO lc_absence_type;
IF lcu_get_absence_type%NOTFOUND THEN
lc_absence_type :='Error';
END IF;

CLOSE lcu_get_absence_type;

RETURN lc_absence_type;

END get_absence_type;
--End of function to Get absence Type

--Function to Check Leave Eligibilty of an Employee
FUNCTION xxcheck_leave_eligibility( xassignment_id IN NUMBER
,xstart_date IN DATE )
return number is
BEGIN
DECLARE
xdays NUMBER := 0;
BEGIN
/* FORMATTED ON 2008/07/19 16:13 (FORMATTER PLUS V4.8.8) */
--This is the Query to get the days of eligibility from Employee contract
select to_number(substr(ppg.segment2,1,2))
INTO XDAYS
from per_all_assignments_f paaf
,pay_people_groups ppg
where paaf.assignment_id = XASSIGNMENT_ID
and paaf.business_group_id=102
and paaf.people_group_id = ppg.people_group_id
and sysdate between paaf.effective_start_date and paaf.effective_end_date;
return xdays;
exception when others then xdays := 0;
return xdays;
END;

END xxcheck_leave_eligibility;

--End of Leave Eligibility Function

--Function to get the Eligibile date for Annual Leave
FUNCTION xxabs_annual_leave_elig_date( xassignment_id IN NUMBER
,xstart_date in DATE)
RETURN VARCHAR2 IS

BEGIN

DECLARE
xdate_ann DATE;
xdate_ann_doj DATE;
xdate_ann_rodd DATE;
xreturn_msg VARCHAR2(30) DEFAULT 'SUCCESS';
x_resume_duty VARCHAR2(100);
x_accrual_group VARCHAR2(100);
x_expiry_days NUMBER;

BEGIN
--This is the block to check that Resumtion of duty exist or not of an Employee
BEGIN

select pab.attribute_category
INTO X_RESUME_DUTY
from per_all_people_f ppf,
per_all_assignments_f paa,
per_absence_attendances pab
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.assignment_id=XASSIGNMENT_ID
and pab.person_id(+)=ppf.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and pab.absence_attendance_type_id(+)=61
and pab.occurrence= ( select max(pab.occurrence)
from per_absence_attendances pab,
per_all_assignments_f paa
where pab.absence_attendance_type_id=61
and pab.person_id(+)=paa.person_id
and paa.assignment_id=XASSIGNMENT_ID
);

EXCEPTION
when no_data_found then
X_RESUME_DUTY := null;
END;
/* Check RP Expiry and if Date of Expiry is Less than the start date of Leave then
then do not allow to enter the Leave*/

/* select (trunc(hdei.DATE_TO)-trunc(sysdate))
into x_expiry_days
from hr_document_extra_info hdei
,per_all_people_f ppf
,per_all_assignments_f paa
where paa.assignment_id=XASSIGNMENT_ID
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and ppf.person_id=paa.person_id
and ppf.person_id=hdei.person_id
and hdei.document_type_id=52
and ppf.business_group_id=102;*/

/*If there is Resumtion of duty for an Employee then the Query in the below if statement
gets executed and and return resumption of duty Date*/

if X_RESUME_DUTY='Annual Leave'
then

select max(to_date(to_char(max(pab.attribute1)),'rrrr/mm/dd hh24:mi:ss' ))
INTO XDATE_ANN_RODD --this variable is for resumption of duty date
from per_all_people_f ppf,
per_all_assignments_f paa,
per_absence_attendances pab
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.assignment_id=XASSIGNMENT_ID
and pab.person_id(+)=ppf.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and pab.attribute_category='Annual Leave'
and ppf.effective_end_date=to_date ('31/12/4712', 'DD/MM/YYYY')
and pab.absence_attendance_type_id(+)=61
group by pab.attribute1,ppf.start_date;

elsif
--If Date of Resumption is null the below query get executed and return date of Join
X_RESUME_DUTY is null

then

select pps.date_start
INTO XDATE_ANN_DOJ --this variable is for date of join
from per_all_people_f ppf,
per_all_assignments_f paa,
per_periods_of_service pps
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.assignment_id=XASSIGNMENT_ID
and pps.person_id = ppf.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pps1
where pps1.person_id = pps.person_id)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and ppf.effective_end_date=to_date ('31/12/4712', 'DD/MM/YYYY');

end if;

--This is the Query to get the Accrual Group of an Employee

select ppg.segment2
INTO X_ACCRUAL_GROUP
from per_all_assignments_f paaf
,pay_people_groups ppg
where paaf.assignment_id = XASSIGNMENT_ID
and paaf.business_group_id=102
and paaf.people_group_id = ppg.people_group_id
and sysdate between paaf.effective_start_date and paaf.effective_end_date;

--Here if Accrual Group is 30 Days per Year and Date of Resumption is Null the below if Statement gets executed

if
X_ACCRUAL_GROUP ='30 Days per Year' and XDATE_ANN_RODD is null
then

/*Now we have the date starting i .e date of Join from which 1 year should be counted
to give the earliest date the employee is eligible to take annual leave. So we add
365 days to this date to get the earliest eligible date on or after which the employee
can take the next leave */

XDATE_ANN := XDATE_ANN_DOJ + 365;


elsif

--Here if Accrual Group is 30 Days per Year and Date of Resumption is Not Null the below if Statement gets executed

X_ACCRUAL_GROUP ='30 Days per Year' and XDATE_ANN_RODD is not null

then

/*Now we have the date starting i.e date of Resumption from which 6 Months should be counted
to give the earliest date the employee is eligible to take annual leave. So we add 180 days
to this date to get the earliest eligible date on or after which the employee can take the
next leave */

XDATE_ANN := XDATE_ANN_RODD + 180;

elsif

/*Here if Accrual Group is 30 Days per Year and Date of Resumption is Null the below if Statement gets executed
Basically this is for Junior Staff and Labours*/

X_ACCRUAL_GROUP <> '30 Days per Year' and XDATE_ANN_RODD is null

then

/*Now we have the date starting i .e date of Join from which 2 Years should be counted
to give the earliest date the employee is eligible to take annual leave. So we add 730
days to this date to get the earliest eligible date on or after which the employee can
take the next leave */

XDATE_ANN := XDATE_ANN_DOJ + 730;

elsif

/*Here if Accrual Group is 30 Days per Year and Date of Resumption is Not Null the below if Statement gets executed
Basically this is for Junior Staff and Labours*/

X_ACCRUAL_GROUP <> '30 Days per Year' and XDATE_ANN_RODD is not null

then
/*Now we have the date starting i.e date of Resumption of and Employee from which
2 Years should be counted to give the earliest date the employee is eligible
to take annual leave. So we add 730 days to this date to get the earliest
eligible date on or after which the employee can take the next leave */

XDATE_ANN := XDATE_ANN_RODD + 730;

end if;
/* If absence start date is earlier than eligibility date, then throw an error */

if XDATE_ANN > XSTART_DATE THEN
XRETURN_MSG := 'Error';
end if;

return XRETURN_MSG;

exception when others then
XRETURN_MSG := 'Error';
return XRETURN_MSG;

END;
END xxabs_annual_leave_elig_date;

--Function get the Sick Leave Eligible Date

FUNCTION xxabs_sick_leave_elig_date( xassignment_id IN NUMBER
,xstart_date IN DATE )
RETURN VARCHAR2 IS
BEGIN
declare
xdate_sick DATE;
xreturn_msg VARCHAR2(30) DEFAULT 'Success';
x_resume_duty VARCHAR2(100);

BEGIN
--This is the Query to get the date of Join of an Employee for Sick Leave

select pps.date_start
INTO XDATE_SICK
from per_all_people_f ppf,
per_all_assignments_f paa,
per_periods_of_service pps
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.assignment_id=XASSIGNMENT_ID
and pps.person_id = ppf.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pps1
where pps1.person_id = pps.person_id)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and ppf.effective_end_date=to_date ('31/12/4712', 'DD/MM/YYYY');

XDATE_SICK := XDATE_SICK + 180;

/* If absence start date is earlier than eligibility date, then throw an error */

if XDATE_SICK > XSTART_DATE
then

XRETURN_MSG := 'Error';

end if;

return XRETURN_MSG;

exception when others then

XRETURN_MSG := 'Error';


END;
END xxabs_sick_leave_elig_date;

--Function get the Maternity Leave Eligible Date

FUNCTION xxabs_maternity_leav_elig_date( xassignment_id IN NUMBER
,xstart_date IN DATE )
RETURN VARCHAR2 IS
BEGIN
DECLARE
xdate_mat DATE;
xreturn_msg VARCHAR2(30) DEFAULT 'Success';

BEGIN
--This is the Query to get the date of Join of an Employee for Maternity Leave
select pps.date_start
INTO XDATE_MAT
from per_all_people_f ppf,
per_all_assignments_f paa,
per_periods_of_service pps
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.assignment_id=XASSIGNMENT_ID
and pps.person_id = ppf.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pps1
where pps1.person_id = pps.person_id
)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and ppf.effective_end_date=to_date ('31/12/4712', 'DD/MM/YYYY');

--Eligible for Maternity Leave After 6 months

XDATE_MAT := XDATE_MAT + 180;
/* If absence start date is earlier than eligibility date, then throw an error */
if XDATE_MAT > XSTART_DATE
then

XRETURN_MSG := 'Error';

end if;

return XRETURN_MSG;

exception when others then
XRETURN_MSG := 'Error';
END;
END xxabs_maternity_leav_elig_date;

--Function get the Hajj Leave Eligible Date
FUNCTION xxabs_haj_leave_elig_date( xassignment_id IN NUMBER
,xstart_date IN DATE )
RETURN VARCHAR2 IS

BEGIN
DECLARE
xdate_haj DATE;
x_resume_duty VARCHAR2(100);
xfirst_haj VARCHAR2(5);
xreturn_msg VARCHAR2(30);

--Cursor to Check the Occrance, It should not be more than 1
CURSOR IS_FIRST_HAJ_LEAVE(XASSIGNMENT_ID NUMBER) IS
select 'X'
from per_absence_attendances paa
,per_all_people_f papf
,per_all_assignments_f paaf
where paaf.assignment_id=XASSIGNMENT_ID
and papf.business_group_id=102
and papf.person_id = paaf.person_id
and paa.person_id = papf.person_id
and paa.absence_attendance_type_id=63
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between paaf.effective_start_date and paaf.effective_end_date;
BEGIN
--This is the Query to get the date of Join of an Employee for Hajj Leave
select pps.date_start
INTO XDATE_HAJ
from per_all_people_f ppf,
per_all_assignments_f paa,
per_periods_of_service pps
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.assignment_ID=XASSIGNMENT_ID
and pps.person_id = ppf.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pps1
where pps1.person_id = pps.person_id
)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and ppf.effective_end_date=to_date ('31/12/4712', 'DD/MM/YYYY');

/* If absence start date is earlier than eligibility date, then throw an error */
XDATE_HAJ := XDATE_HAJ + 365;

if XDATE_HAJ > XSTART_DATE
then
XRETURN_MSG := 'Error';

return XRETURN_MSG;

end if;

/* Now check whether the employee had previous occurance of Haj Leave. If yes, then return Error */
OPEN IS_FIRST_HAJ_LEAVE(XASSIGNMENT_ID);
FETCH IS_FIRST_HAJ_LEAVE INTO XFIRST_HAJ;

IF IS_FIRST_HAJ_LEAVE%NOTFOUND then
/* So this is the first Haj leave for the employee */
XRETURN_MSG := 'YES';

return XRETURN_MSG;

ELSE
/* tHis is NOT the first Haj leave, so return appropriate message */
XRETURN_MSG := 'Not Eligible';

END IF;

close is_first_haj_leave;
return XRETURN_MSG;

exception when others then
XRETURN_MSG := 'Error';
END;

END xxabs_haj_leave_elig_date;

--Function get the Marriage Leave Eligible Date
FUNCTION xxabs_marr_leave_elig_date( XASSIGNMENT_ID IN NUMBER
,XSTART_DATE IN DATE )
return varchar2 is

BEGIN
DECLARE
XDATE_MARR DATE;
X_RESUME_DUTY VARCHAR2(100);
XFIRST_MARR VARCHAR2(5);
XRETURN_MSG VARCHAR2(30);

--Cursor to Check the Occrance, It should not be more than 1
cursor is_first_marr_leave(XASSIGNMENT_ID NUMBER) is
select 'X'
from per_absence_attendances paa
,per_all_people_f papf
,per_all_assignments_f paaf
where paaf.assignment_id=XASSIGNMENT_ID
and papf.person_id = paaf.person_id
and papf.business_group_id=102
and paa.person_id = papf.person_id
and paa.absence_attendance_type_id=64
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between paaf.effective_start_date and paaf.effective_end_date;
BEGIN
--This is the Query to get the date of Join of an Employee for Marriage Leave
select pps.date_start
INTO XDATE_MARR
from per_all_people_f ppf,
per_all_assignments_f paa,
per_periods_of_service pps
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.assignment_id=XASSIGNMENT_ID
and pps.person_id = ppf.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pps1
where pps1.person_id = pps.person_id
)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and ppf.effective_end_date=to_date ('31/12/4712', 'DD/MM/YYYY');

/* If absence start date is earlier than eligibility date, then throw an error */
XDATE_MARR := XDATE_MARR + 365;

if XDATE_MARR > XSTART_DATE then
XRETURN_MSG := 'Error';
return XRETURN_MSG;
end if;

/* Now check whether the employee had previous occurance of Marriage Leave. If yes, then return Error */
open is_first_marr_leave(XASSIGNMENT_ID);
fetch is_first_marr_leave INTO XFIRST_MARR;
if is_first_marr_leave%NOTFOUND then
/* So this is the first Marriage leave for the employee */
XRETURN_MSG := 'YES';
return XRETURN_MSG;
else
/* tHis is NOT the first Marriage leave, so return appropriate message */
XRETURN_MSG := 'Not Eligible';
end if;
close is_first_marr_leave;
return XRETURN_MSG;

exception when others then
XRETURN_MSG := 'Error';
END;

END xxabs_marr_leave_elig_date;

--Function get the Study Leave Eligible Date
FUNCTION xxabs_study_leave_elig_date( XASSIGNMENT_ID IN NUMBER
,XSTART_DATE IN DATE )
return varchar2 is
BEGIN
DECLARE
XDATE_STUDY DATE;
XRETURN_MSG VARCHAR2(30) DEFAULT 'Success';
BEGIN
--This is the Query to get the date of Join of an Employee for Study Leave
select pps.date_start
into XDATE_STUDY
from per_all_people_f ppf,
per_all_assignments_f paa,
per_periods_of_service pps
where ppf.person_id=paa.person_id
and ppf.business_group_id=102
and paa.ASSIGNMENT_ID=XASSIGNMENT_ID
and pps.person_id = ppf.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pps1
where pps1.person_id = pps.person_id)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between paa.effective_start_date and paa.effective_end_date
and ppf.effective_end_date=to_date ('31/12/4712', 'DD/MM/YYYY');

XDATE_STUDY := XDATE_STUDY + 1095;
/* If absence start date is earlier than eligibility date, then throw an error */
if XDATE_STUDY > XSTART_DATE then
XRETURN_MSG := 'Error';
end if;
return XRETURN_MSG;

exception when others then
XRETURN_MSG := 'Error';
END;
END xxabs_study_leave_elig_date;


FUNCTION xxabs_get_committed_leave_days
(
X_ASSIGNMENT_ID IN NUMBER
,X_BUSINESS_GROUP_ID IN NUMBER
,X_LEAVE_TYPE IN VARCHAR2
,X_YEAR IN VARCHAR2
,X_ABSENCE_ATTENDANCE_TYPE_ID IN NUMBER
)
return number as
LD_START_DATE DATE;
LD_END_DATE DATE;
LN_TOTAL_LEAVE_DAYS NUMBER DEFAULT 0;
CURSOR lcu_absence(ln_business_group_id NUMBER, ln_absence_attendance_type_id NUMBER) IS
SELECT paa.*
FROM per_absence_attendances paa
,per_all_people_f papf
,per_all_assignments_f paaf
WHERE paa.absence_attendance_type_id = (select absence_attendance_type_id from per_absence_attendance_types where name = x_leave_type)
AND paa.person_id = papf.person_id
and papf.business_group_id=102
AND papf.person_id = paaf.person_id
AND paaf.assignment_id = x_assignment_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
lr_abs_row lcu_absence%ROWTYPE;
--
BEGIN
OPEN lcu_absence(x_business_group_id, x_absence_attendance_type_id);
--now loop through all the absence records. if any absence days fall within the year,
--then add those days to a running total and return the total.
LOOP
FETCH lcu_absence INTO lr_abs_row;
EXIT WHEN lcu_absence%NOTFOUND;
--
--now start the processing logic
ld_start_date := GREATEST(lr_abs_row.date_start,to_date('01-JAN-'||x_year,'DD-MON-YYYY'));
ld_end_date := LEAST(lr_abs_row.date_end,to_date('31-DEC-'||x_year,'DD-MON-YYYY'));
IF ld_end_date < date =" '||x_start_date,14);" date =" '||x_end_date,15);" global_name =" 'XXTAD_CASUAL_DAYS_LIMIT'" business_group_id="102"> to_char(ld_end_date,'YYYY') THEN
--end date is in the following year. so get sum of all other absences of type Sick Leave until 31-Dec of this year
ln_count_of_prev_casual_days := xxabs_get_committed_leave_days(x_assignment_id,x_business_group_id,'Casual Leave',to_char(ld_start_date,'YYYY'),x_absence_attendance_type_id);
--insert into log_text values ('ln_count_of_prev_casual_days '||ln_count_of_prev_casual_days);
ld_end_date_year_end := LEAST(ld_end_date,to_date('31-DEC-'||to_char(ld_start_date,'YYYY'),'DD-MON-YYYY'));
-- insert into log_text values ('ld_end_date '||ld_end_date);
ln_count_of_total_casual_days := to_char(fffunc.days_between(ld_end_date,ld_start_date) + 1) + ln_count_of_prev_casual_days;
commit;
END IF;
--Return appropriate message if number of sick days requested for the year (including this absence) is greater than policy limits.
IF ln_count_of_total_casual_days > ln_max_casual_days_allwd THEN
lc_count_of_casual_days := 'OVER_MAX_LIMIT';
--insert into temp2 values ('IN '||lc_count_of_sick_days);
RETURN lc_count_of_casual_days;
END IF;
--
lc_count_of_casual_days := to_char(fffunc.days_between(ld_end_date,ld_start_date) + 1);
--insert into temp2 values ('OUT '||lc_count_of_sick_days);
RETURN lc_count_of_casual_days;

END xxabs_get_casual_days;

FUNCTION xxabs_get_unpaid_days(
X_ASSIGNMENT_ID IN NUMBER
,X_BUSINESS_GROUP_ID IN NUMBER
,X_START_DATE IN DATE
,X_END_DATE IN DATE
,X_ABSENCE_ATTENDANCE_TYPE_ID IN NUMBER)
RETURN VARCHAR2 AS
ld_start_date DATE;
ld_end_date DATE;
ld_end_date_year_end DATE; --in case end date falls in different year than start date, this variable stores 31-Dec of the year of start date
ln_max_unpaid_days_allwd NUMBER;
ln_count_of_prev_unpaid_days NUMBER DEFAULT 0;
ln_count_of_total_unpaid_days NUMBER DEFAULT 0;
lc_count_of_unpaid_days VARCHAR2(30) DEFAULT '0';
BEGIN
hr_utility.set_location('Entering xxtadmur_hr_absence_pkg.xxabs_get_unpaid_days',13);
hr_utility.set_location('Parameter : Start Date = '||x_start_date,14);
hr_utility.set_location('Parameter : End Date = '||x_end_date,15);
--
--return duration of 0 if end date is less than start date

IF x_end_date < global_name =" 'XXTAD_UNPAID_DAYS_LIMIT'" business_group_id="102"> to_char(ld_end_date,'YYYY') THEN
--end date is in the following year. so get sum of all other absences of type unpaid Leave until 31-Dec of this year
ln_count_of_prev_unpaid_days := xxabs_get_committed_leave_days(x_assignment_id,x_business_group_id,'Unpaid Leave',to_char(ld_start_date,'YYYY'),x_absence_attendance_type_id);
--insert into temp2 values ('ln_count_of_prev_unpaid_days '||ln_count_of_prev_unpaid_days);
ld_end_date_year_end := LEAST(ld_end_date,to_date('31-DEC-'||to_char(ld_start_date,'YYYY'),'DD-MON-YYYY'));
--insert into temp2 values ('ld_end_date '||ld_end_date);
ln_count_of_total_unpaid_days := to_char(fffunc.days_between(ld_end_date,ld_start_date) + 1) + ln_count_of_prev_unpaid_days;
END IF;
--Return appropriate message if number of sick days requested for the year (including this absence) is greater than policy limits.
IF ln_count_of_total_unpaid_days > ln_max_unpaid_days_allwd THEN
lc_count_of_unpaid_days := 'OVER_MAX_LIMIT';
--insert into temp2 values ('IN '||lc_count_of_sick_days);
RETURN lc_count_of_unpaid_days;
END IF;
--
lc_count_of_unpaid_days := to_char(fffunc.days_between(ld_end_date,ld_start_date) + 1);
--insert into temp2 values ('OUT '||lc_count_of_sick_days);
RETURN lc_count_of_unpaid_days;

END xxabs_get_unpaid_days;


END xxtad_hr_absence_pkg ;
/