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:
