9 Replies Latest reply on Jun 20, 2013 11:34 AM by User523893-OC

    Overlapping Absence

      Dear All,

      I want to check overlapping absences either in absence table or in hr_api_transactions using absence hooks
      I am almost through but failing in case of mirror absences.
      For e.g. two absences with same dates and type.

      As I don't get current hr_api_transaction.transaction_id in hook, I am matching start date, end date and absence attendance type id to find current transaction id and then excluding this transaction id from checking.
      But when it is a mirror absence, it fails.

      Thanks for your suggestions in advance.

      Ashish Shah
        • 1. Re: Overlapping Absence
          Can you check for the absence occurrence number?


          • 2. Re: Overlapping Absence
            Hi Tim,

            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.

            Ashish Shah
            • 3. Re: Overlapping Absence
              Asif Ali Khan
              Hi Ashish,

              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

              • 4. Re: Overlapping Absence
                Hi Asif,

                We could overcome this situation without touching standard API.
                Thanks for your response.

                Ashish Shah
                • 5. Re: Overlapping Absence
                  Hi Ashish,
                  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.
                  • 6. Re: Overlapping Absence
                    Hi Aziz,

                    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.

                    Common Function
                    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_count_exists NUMBER;
                    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';
                    END IF;
                    RETURN l_exists;

                    API Hook Code
                    l_exists :=
                    exists_in_base_table (p_person_id,

                    IF l_exists = 'Y'
                    hr_utility.set_message (800, 'HR_LOA_ABSENCE_OVERLAP');
                    END IF;

                    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.

                    Ashish Shah
                    • 7. Re: Overlapping Absence
                      Thanks Ashish,
                      One more thing...
                      If apporval is pending for approval, then how we check leaves absences overlapping?
                      • 8. Re: Overlapping Absence
                        Hi Aziz,

                        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.

                        Ashish Shah
                        • 9. Re: Overlapping Absence

                          Hi all,


                          I'm facing the Same issue, my big question is : how can i derive (Transaction id) for the same absence in the user hook.


                          Please Advice.


                          Thank you