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.


0 comments: