1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to prevent overlapping absences of the same absence type ?

Discussion in 'Oracle HRMS & Payroll' started by falaisaac, Apr 7, 2014.

  1. falaisaac

    falaisaac Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    Accra-Ghana
    Kindly assist with how to prevent overlapping absences of the same absence type ?
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi falaisaac,

    1) Write a function (packages function usually) which will check the overlapping. The following SQL may be used:

    Code (SQL):
     SELECT
        SUM (absence_days)
     INTO total_abs
     FROM per_absence_attendances paa,
              per_absence_attendance_types paat
     WHERE paa.person_id = p_person_id
       AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
       AND (p_from_date BETWEEN paa.date_start AND paa.date_end OR p_to_date BETWEEN paa.date_start AND paa.date_end );

    p_person_id, p_from_date, p_to_date are the parameters to be passed from the fast formula

    2) Add the call to above function in BG_ABSENCE_DURATION formula and check the tot_abs returned value;
    If > 0 then there's overlapping.

    3) Incorporate the appropriate message in BG_ABSENCE_DURATION based on tot_abs.

    Hope this helps.

    Regards,
    Rajen.
     
  3. falaisaac

    falaisaac Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    Accra-Ghana
    Thanks so much!