Validation in absence screen to prevent overlap leaves entered both in HR Absence screen and SSHR Absence

 To make Validation in absence screen to prevent overlap leaves entered both in HR Absence screen and SSHR Absence Management screen.

1- Create Define Function

Package source is at the end of document.

2- Create Context usage

Create Parameters

4- Add Condition in BG_ABSENCE_DURATION formula

Default overlap = 0

OVERLAP = CHECK_ABSENCE_OVERLAP(date_start,date_end)

/********************** Over Lap ******************************/

IF OVERLAP > 0

THEN

(

duration = 'FAILED'

invalid_msg = 'There exists an overlapping Leave during these dates'

return duration, invalid_msg

)

/********************************************************************/

BODY OF FUNCTION

FUNCTION CHECK_ABSENCE_OVERLAP (

P_BUSINESS_GROUP_ID in number,

P_ASSIGNMENT_ID in number,

P_Process_Start_Date in date,

P_Process_end_Date in date

)

RETURN NUMBER IS

COUNTER NUMBER:=0;

P_PERSON_ID NUMBER:=0;

BEGIN

-- Fetch Person Id from Assignment ID

Select PERSON_ID into P_PERSON_ID

From PER_ALL_ASSIGNMENTS_F

Where ASSIGNMENT_ID = P_ASSIGNMENT_ID

And P_Process_Start_Date Between effective_start_Date and effective_end_Date ;

BEGIN

SELECT nvl(count(AT.ABSENCE_ATTENDANCE_ID),0)

INTO COUNTER

FROM

Per_Absence_Attendances AT

WHERE

AT.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID AND

AT.PERSON_ID = P_PERSON_ID AND

(

(AT.date_start <= P_Process_Start_Date

and AT.date_end between P_Process_Start_Date and P_Process_end_Date)

OR (AT.date_start between P_Process_Start_Date and P_Process_end_Date

and AT.date_end >= P_Process_end_Date)

OR (AT.date_start between P_Process_Start_Date and P_Process_end_Date

and AT.date_end between P_Process_Start_Date and P_Process_end_Date )

OR (AT.date_start <= P_Process_Start_Date and AT.date_end >= P_Process_end_Date)

) ;

EXCEPTION WHEN OTHERS THEN COUNTER:=0;

END;

RETURN(COUNTER);

END CHECK_ABSENCE_OVERLAP;

Testing: