This content has been marked as final. Show 9 replies
Can u please explain how you handle this situation ?
We also implement such a case, but we allow overlapping of leave dates if its previous leave is pending for approval. Once its 1st leave is approved (it will create a row in per_abs table) and at the time of approval of 2nd leave, overlapping of dates will check and error will be thrown using hooks at the time of Final Approval.
If I got your problem correctly, then you don't want to have more than one absence in a base table for a date range.
In our case it was bit different, we didn't want to allow overlapping, even if it is pending for approval.
Nonethless for your case, you can use following code in API Hook. It should serve your purpose.
FUNCTION exists_in_base_table ( p_person_id IN NUMBER, p_absence_attendance_type_id IN NUMBER, p_date_start IN DATE, p_date_end IN DATE ) RETURN VARCHAR2
l_exists VARCHAR2 (1);
SELECT NVL (COUNT (*), 0) INTO l_count_exists FROM per_absence_attendances paa WHERE paa.person_id = p_person_id
AND (p_date_start <= paa.date_end AND p_date_end >= paa.date_start);
IF l_count_exists > 0 THEN
l_exists := 'Y';
l_exists := 'N';
API Hook Code
IF l_exists = 'Y'
hr_utility.set_message (800, 'HR_LOA_ABSENCE_OVERLAP');
In this case, at the time of approval, if any overlapping absence exists in base table, it will approve, but initiator will receive error notification with overlapping error in red color.
That's possible after too much of complex coding.
You need to derive transaction id of the same abasnce in your hook and then you need to check overlapping absences using above code in base table and HR API Transaction table.
I suggest not to do this as in any case exact mirror absences will create issues.