This content has been marked as final. Show 9 replies
Can you check for the absence occurrence number?
Absence occrurance number is available wheren tranasaction is saved to base tables.
This is for transactions, which are still in HR_API_TRANSACTIONS in unapproved state.
If one absence is pending for approval, user should not be able to apply for another absence.
We are also face the same problem for one of our client but to over come this situation we modified the standard Absence API. Now it's working fine
We could overcome this situation without touching standard API.
Thanks for your response.
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.
One more thing...
If apporval is pending for approval, then how we check leaves absences overlapping?
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.
I'm facing the Same issue, my big question is : how can i derive (Transaction id) for the same absence in the user hook.