How to get Employee Absence in a period in Oracle HRMS Payroll

Please find the PL-SQL Function to get the employee absence in a given period. Function also calculates the absence days even if the absence are split on pay_proc_start_date or pay_proc_end_date.

Here is the code!!

CREATE OR REPLACE FUNCTION CUST_GET_ABSENCE_DAYS (

   P_ASG_ID         IN   NUMBER,

   PAY_START_DATE   IN   DATE,

   PAY_END_DATE     IN   DATE,

   P_ABSENCE_TYPE   IN   CHAR

)

   RETURN NUMBER

IS

   ABS_DAYS_PERIOD              NUMBER (15);

   ABS_START_DATE               PER_ABSENCE_ATTENDANCES.DATE_START%TYPE;         --DATE;

   ABS_END_DATE                 PER_ABSENCE_ATTENDANCES.DATE_END%TYPE;           --DATE;

   MISSING_DAYS                 NUMBER (15,1);

   SUB_TOTAL                    NUMBER (15,1);

   ACTUAL_DAYS                  NUMBER (15,1);

   MISSING_DAYS_1               NUMBER (15,1);

   SUB_TOTAL_1                  NUMBER (15,1);

   ACTUAL_DAYS_1                NUMBER (15,1);

   CONDITION_1                  NUMBER (15,1);

   CONDITION_2                  NUMBER (15,1);

   TOTAL_ABS_DAYS               NUMBER (15,1);

   ABSENCE_ATTENDANCE_ID        VARCHAR2(200);

   P_ABSENCE_ATTENDANCE_ID      VARCHAR2(200);

   CURSOR ABS_DATES

   IS

      SELECT PABA.DATE_START, PABA.DATE_END

        --INTO ABS_START_DATE ,ABS_END_DATE

      FROM   PER_ABSENCE_ATTENDANCES PABA, PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE PABA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID

       AND UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE)

       AND PABA.PERSON_ID =

                (SELECT DISTINCT PPF.PERSON_ID

                   FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                  WHERE PPF.PERSON_ID = PAF.PERSON_ID

                    AND PAF.ASSIGNMENT_ID = P_ASG_ID);

BEGIN

   TOTAL_ABS_DAYS := 0;

  

   --P_ABSENCE_ATTENDANCE_ID :=0;

   --ABSENCE_ATTENDANCE_ID := 0;

--   SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID

--        INTO P_ABSENCE_ATTENDANCE_ID

--      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

--       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE);

--   

--   ABSENCE_ATTENDANCE_ID := P_ABSENCE_ATTENDANCE_ID;

   SELECT TO_NUMBER (SUM (ABSENCE_DAYS))

     INTO ABS_DAYS_PERIOD

     FROM PER_ABSENCE_ATTENDANCES PABA

    WHERE PABA.PERSON_ID =

             (SELECT DISTINCT PPF.PERSON_ID

                FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

               WHERE PPF.PERSON_ID = PAF.PERSON_ID

                 AND PAF.ASSIGNMENT_ID = P_ASG_ID

                 /*AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE

                                 AND PPF.EFFECTIVE_END_DATE

                 AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE

                                 AND PAF.EFFECTIVE_END_DATE*/)

      AND PABA.ABSENCE_ATTENDANCE_TYPE_ID = (SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID     

      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE))

      AND PABA.DATE_START BETWEEN PAY_START_DATE AND PAY_END_DATE

      AND PABA.DATE_END BETWEEN PAY_START_DATE AND PAY_END_DATE;

   OPEN ABS_DATES;

   LOOP

      FETCH ABS_DATES

       INTO ABS_START_DATE, ABS_END_DATE;

      EXIT WHEN ABS_DATES%NOTFOUND;

      IF (ABS_START_DATE > PAY_START_DATE AND ABS_END_DATE > PAY_END_DATE)

      THEN

         SELECT TO_NUMBER (DATE_END - DATE_START) + 1

           INTO SUB_TOTAL

           FROM PER_ABSENCE_ATTENDANCES PABA

          WHERE PERSON_ID =

                   (SELECT DISTINCT PPF.PERSON_ID

                      FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                     WHERE PPF.PERSON_ID = PAF.PERSON_ID

                       AND PAF.ASSIGNMENT_ID = P_ASG_ID)                   

            AND PABA.ABSENCE_ATTENDANCE_TYPE_ID = (SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID     

      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE))

            AND PABA.DATE_START > PAY_START_DATE

            AND PABA.DATE_END > PAY_END_DATE;

         SELECT TO_NUMBER (TO_DATE(PAY_END_DATE) - DATE_START) + 1

           INTO MISSING_DAYS

           FROM PER_ABSENCE_ATTENDANCES PABA

          WHERE PERSON_ID =

                   (SELECT DISTINCT PPF.PERSON_ID

                      FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                     WHERE PPF.PERSON_ID = PAF.PERSON_ID

                       AND PAF.ASSIGNMENT_ID = P_ASG_ID)                   

            AND PABA.ABSENCE_ATTENDANCE_TYPE_ID = (SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID     

      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE))

            AND PABA.DATE_START > PAY_START_DATE

            AND PABA.DATE_END > PAY_END_DATE;

         SELECT TO_NUMBER (SUM (ABSENCE_DAYS))

           INTO ACTUAL_DAYS

           FROM PER_ABSENCE_ATTENDANCES PABA

          WHERE PERSON_ID =

                   (SELECT DISTINCT PPF.PERSON_ID

                      FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                     WHERE PPF.PERSON_ID = PAF.PERSON_ID

                       AND PAF.ASSIGNMENT_ID = P_ASG_ID)                 

            AND PABA.ABSENCE_ATTENDANCE_TYPE_ID = (SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID     

      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE))

            AND PABA.DATE_START > PAY_START_DATE

            AND PABA.DATE_END > PAY_END_DATE;

         CONDITION_1 := ROUND((NVL (MISSING_DAYS, 0) / NVL (SUB_TOTAL, 0)) * NVL (ACTUAL_DAYS, 0),1);

      

      ELSIF (ABS_START_DATE < PAY_START_DATE AND ABS_END_DATE < PAY_END_DATE)

      THEN

         SELECT TO_NUMBER (DATE_END - DATE_START) + 1

           INTO SUB_TOTAL_1

           FROM PER_ABSENCE_ATTENDANCES PABA

          WHERE PERSON_ID =

                   (SELECT DISTINCT PPF.PERSON_ID

                      FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                     WHERE PPF.PERSON_ID = PAF.PERSON_ID

                       AND PAF.ASSIGNMENT_ID = P_ASG_ID)

           AND PABA.ABSENCE_ATTENDANCE_TYPE_ID =  (SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID     

      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE))

            AND PABA.DATE_START < PAY_START_DATE

            AND PABA.DATE_END < PAY_END_DATE

            AND PABA.DATE_END > PAY_START_DATE;

         SELECT TO_NUMBER (DATE_END - TO_DATE(PAY_START_DATE)) + 1

           INTO MISSING_DAYS_1

           FROM PER_ABSENCE_ATTENDANCES PABA

          WHERE PERSON_ID =

                   (SELECT DISTINCT PPF.PERSON_ID

                      FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                     WHERE PPF.PERSON_ID = PAF.PERSON_ID

                      AND PAF.ASSIGNMENT_ID = P_ASG_ID)

             AND PABA.ABSENCE_ATTENDANCE_TYPE_ID =  (SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID     

      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE))

            AND PABA.DATE_START < PAY_START_DATE

            AND PABA.DATE_END < PAY_END_DATE

            AND PABA.DATE_END > PAY_START_DATE;

         SELECT TO_NUMBER (SUM (ABSENCE_DAYS))

           INTO ACTUAL_DAYS_1

           FROM PER_ABSENCE_ATTENDANCES PABA

          WHERE PERSON_ID =

                   (SELECT DISTINCT PPF.PERSON_ID

                      FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                     WHERE PPF.PERSON_ID = PAF.PERSON_ID

                       AND PAF.ASSIGNMENT_ID = P_ASG_ID)

           AND PABA.ABSENCE_ATTENDANCE_TYPE_ID =  (SELECT PAAT.ABSENCE_ATTENDANCE_TYPE_ID     

      FROM   PER_ABSENCE_ATTENDANCE_TYPES PAAT

       WHERE UPPER(PAAT.NAME) = UPPER(P_ABSENCE_TYPE))

            AND PABA.DATE_START < PAY_START_DATE

            AND PABA.DATE_END < PAY_END_DATE

            AND PABA.DATE_END > PAY_START_DATE;

         CONDITION_2 := ROUND((NVL (MISSING_DAYS_1, 0) / NVL (SUB_TOTAL_1, 0)) * NVL (ACTUAL_DAYS_1, 0),1);

      END IF;

   END LOOP;

   CLOSE ABS_DATES;

   TOTAL_ABS_DAYS :=

          NVL (ABS_DAYS_PERIOD, 0) + NVL (CONDITION_1, 0) + NVL (CONDITION_2, 0);

      

   RETURN TOTAL_ABS_DAYS;

          -- ABS_DAYS_PERIOD;

           --ABSENCE_ATTENDANCE_ID;

          --CONDITION_1;

          --CONDITION_2;

          --SUB_TOTAL_1;

          --MISSING_DAYS_1;

          --ACTUAL_DAYS_1;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN 0;

END;

If something you could not understand, ask your query in the comment.

Feedback is much appreciated.