Employee Absence uploading in Oracle HRMS

How to find correct API for Data Uploading

Please find the table definition and procedure below.

TABLE



CREATE TABLE DEV_ABSENCE_ATTENDANCES
(
  LINE_ID                        NUMBER(10),
  ABSENCE_ATTENDANCE_ID          NUMBER(10),
  BUSINESS_GROUP_ID              NUMBER(15),
  ABSENCE_ATTENDANCE_TYPE        VARCHAR2(200 BYTE),
  ABS_ATTENDANCE_REASON          VARCHAR2(200 BYTE),
  EMPLOYEE_NUMBER                VARCHAR2(200 BYTE),
  AUTHORISING_PERSON             VARCHAR2(200 BYTE),
  REPLACEMENT_PERSON             VARCHAR2(200 BYTE),
  PERIOD_OF_INCAPACITY           NUMBER(9),
  ABSENCE_DAYS                   NUMBER(9,4),
  ABSENCE_HOURS                  NUMBER(9,4),
  DATE_END                       DATE,
  DATE_NOTIFICATION              DATE,
  DATE_PROJECTED_END             DATE,
  DATE_PROJECTED_START           DATE,
  DATE_START                     DATE,
  OCCURRENCE                     NUMBER(15),
  SSP1_ISSUED                    VARCHAR2(30 BYTE),
  TIME_END                       VARCHAR2(5 BYTE),
  TIME_PROJECTED_END             VARCHAR2(5 BYTE),
  TIME_PROJECTED_START           VARCHAR2(5 BYTE),
  TIME_START                     VARCHAR2(5 BYTE),
  REQUEST_ID                     NUMBER(15),
  PROGRAM_APPLICATION_ID         NUMBER(15),
  PROGRAM_ID                     NUMBER(15),
  PROGRAM_UPDATE_DATE            DATE,
  ATTRIBUTE_CATEGORY             VARCHAR2(30 BYTE),
  ATTRIBUTE1                     VARCHAR2(150 BYTE),
  ATTRIBUTE2                     VARCHAR2(150 BYTE),
  ATTRIBUTE3                     VARCHAR2(150 BYTE),
  ATTRIBUTE4                     VARCHAR2(150 BYTE),
  ATTRIBUTE5                     VARCHAR2(150 BYTE),
  ATTRIBUTE6                     VARCHAR2(150 BYTE),
  ATTRIBUTE7                     VARCHAR2(150 BYTE),
  ATTRIBUTE8                     VARCHAR2(150 BYTE),
  ATTRIBUTE9                     VARCHAR2(150 BYTE),
  ATTRIBUTE10                    VARCHAR2(150 BYTE),
  ATTRIBUTE11                    VARCHAR2(150 BYTE),
  ATTRIBUTE12                    VARCHAR2(150 BYTE),
  ATTRIBUTE13                    VARCHAR2(150 BYTE),
  ATTRIBUTE14                    VARCHAR2(150 BYTE),
  ATTRIBUTE15                    VARCHAR2(150 BYTE),
  ATTRIBUTE16                    VARCHAR2(150 BYTE),
  ATTRIBUTE17                    VARCHAR2(150 BYTE),
  ATTRIBUTE18                    VARCHAR2(150 BYTE),
  ATTRIBUTE19                    VARCHAR2(150 BYTE),
  ATTRIBUTE20                    VARCHAR2(150 BYTE),
  LAST_UPDATE_DATE               DATE,
  LAST_UPDATED_BY                NUMBER(15),
  LAST_UPDATE_LOGIN              NUMBER(15),
  CREATED_BY                     NUMBER(15),
  CREATION_DATE                  DATE,
  MATERNITY_ID                   NUMBER,
  SICKNESS_START_DATE            DATE,
  SICKNESS_END_DATE              DATE,
  PREGNANCY_RELATED_ILLNESS      VARCHAR2(30 BYTE),
  REASON_FOR_NOTIFICATION_DELAY  VARCHAR2(2000 BYTE),
  ACCEPT_LATE_NOTIFICATION_FLAG  VARCHAR2(30 BYTE) DEFAULT 'Y',
  LINKED_ABSENCE_ID              NUMBER,
  BATCH_ID                       NUMBER(9),
  ABS_INFORMATION_CATEGORY       VARCHAR2(30 BYTE),
  ABS_INFORMATION1               VARCHAR2(150 BYTE),
  ABS_INFORMATION2               VARCHAR2(150 BYTE),
  ABS_INFORMATION3               VARCHAR2(150 BYTE),
  ABS_INFORMATION4               VARCHAR2(150 BYTE),
  ABS_INFORMATION5               VARCHAR2(150 BYTE),
  ABS_INFORMATION6               VARCHAR2(150 BYTE),
  ABS_INFORMATION7               VARCHAR2(150 BYTE),
  ABS_INFORMATION8               VARCHAR2(150 BYTE),
  ABS_INFORMATION9               VARCHAR2(150 BYTE),
  ABS_INFORMATION10              VARCHAR2(150 BYTE),
  ABS_INFORMATION11              VARCHAR2(150 BYTE),
  ABS_INFORMATION12              VARCHAR2(150 BYTE),
  ABS_INFORMATION13              VARCHAR2(150 BYTE),
  ABS_INFORMATION14              VARCHAR2(150 BYTE),
  ABS_INFORMATION15              VARCHAR2(150 BYTE),
  ABS_INFORMATION16              VARCHAR2(150 BYTE),
  ABS_INFORMATION17              VARCHAR2(150 BYTE),
  ABS_INFORMATION18              VARCHAR2(150 BYTE),
  ABS_INFORMATION19              VARCHAR2(150 BYTE),
  ABS_INFORMATION20              VARCHAR2(150 BYTE),
  ABS_INFORMATION21              VARCHAR2(150 BYTE),
  ABS_INFORMATION22              VARCHAR2(150 BYTE),
  ABS_INFORMATION23              VARCHAR2(150 BYTE),
  ABS_INFORMATION24              VARCHAR2(150 BYTE),
  ABS_INFORMATION25              VARCHAR2(150 BYTE),
  ABS_INFORMATION26              VARCHAR2(150 BYTE),
  ABS_INFORMATION27              VARCHAR2(150 BYTE),
  ABS_INFORMATION28              VARCHAR2(150 BYTE),
  ABS_INFORMATION29              VARCHAR2(150 BYTE),
  ABS_INFORMATION30              VARCHAR2(150 BYTE),
  OBJECT_VERSION_NUMBER          NUMBER(9),
  COMMENTS                       LONG,
  ABSENCE_CASE_ID                NUMBER(10),
  APPROVAL_STATUS                VARCHAR2(30 BYTE),
  CONFIRMED_UNTIL                DATE,
  SOURCE                         VARCHAR2(30 BYTE),
  ERROR_DESCRIPTION              VARCHAR2(2000 BYTE)
)


CREATION PROCEDURE



CREATE OR REPLACE PROCEDURE APPS.DEV_UPLOAD_PERSON_ABSENCE
IS
   CURSOR C_ABSENCE
   IS
      SELECT   LINE_ID, ABSENCE_ATTENDANCE_ID, BUSINESS_GROUP_ID,
               ABSENCE_ATTENDANCE_TYPE, ABS_ATTENDANCE_REASON,
               EMPLOYEE_NUMBER, AUTHORISING_PERSON, REPLACEMENT_PERSON,
               PERIOD_OF_INCAPACITY, ABSENCE_DAYS, ABSENCE_HOURS, DATE_END,
               DATE_NOTIFICATION, DATE_PROJECTED_END, DATE_PROJECTED_START,
               DATE_START, OCCURRENCE, SSP1_ISSUED, TIME_END,
               TIME_PROJECTED_END, TIME_PROJECTED_START, TIME_START,
               REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
               PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
               ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
               ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
               ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
               ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19,
               ATTRIBUTE20, LAST_UPDATE_DATE, LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, MATERNITY_ID,
               SICKNESS_START_DATE, SICKNESS_END_DATE,
               PREGNANCY_RELATED_ILLNESS, REASON_FOR_NOTIFICATION_DELAY,
               ACCEPT_LATE_NOTIFICATION_FLAG, LINKED_ABSENCE_ID, BATCH_ID,
               ABS_INFORMATION_CATEGORY, ABS_INFORMATION1, ABS_INFORMATION2,
               ABS_INFORMATION3, ABS_INFORMATION4, ABS_INFORMATION5,
               ABS_INFORMATION6, ABS_INFORMATION7, ABS_INFORMATION8,
               ABS_INFORMATION9, ABS_INFORMATION10, ABS_INFORMATION11,
               ABS_INFORMATION12, ABS_INFORMATION13, ABS_INFORMATION14,
               ABS_INFORMATION15, ABS_INFORMATION16, ABS_INFORMATION17,
               ABS_INFORMATION18, ABS_INFORMATION19, ABS_INFORMATION20,
               ABS_INFORMATION21, ABS_INFORMATION22, ABS_INFORMATION23,
               ABS_INFORMATION24, ABS_INFORMATION25, ABS_INFORMATION26,
               ABS_INFORMATION27, ABS_INFORMATION28, ABS_INFORMATION29,
               ABS_INFORMATION30, OBJECT_VERSION_NUMBER, COMMENTS,
               ABSENCE_CASE_ID, APPROVAL_STATUS, CONFIRMED_UNTIL, SOURCE,
               ERROR_DESCRIPTION
          FROM DEV_ABSENCE_ATTENDANCES
      ORDER BY 1 ASC;

   -- VARIABLES
   LC_C_ABSENCE                   C_ABSENCE%ROWTYPE;
   L_ABSENCE_ATTENDANCE_TYPE_ID   NUMBER;
   L_ABS_ATTENDANCE_REASON_ID     NUMBER;
   L_PERSON_ID                    NUMBER;
   L_AUTHORISING_PERSON_ID        NUMBER;
   L_REPLACEMENT_PERSON_ID        NUMBER;
   L_ABSENCE_CASE_ID              NUMBER;
   L_ABSENCE_ATTENDANCE_ID        NUMBER;
   L_OBJECT_VERSION_NUMBER        NUMBER;
   L_OCCURRENCE                   NUMBER;
   L_DUR_DYS_LESS_WARNING         BOOLEAN;
   L_DUR_HRS_LESS_WARNING         BOOLEAN;
   L_EXCEEDS_PTO_ENTIT_WARNING    BOOLEAN;
   L_EXCEEDS_RUN_TOTAL_WARNING    BOOLEAN;
   L_ABS_OVERLAP_WARNING          BOOLEAN;
   L_ABS_DAY_AFTER_WARNING        BOOLEAN;
   L_DUR_OVERWRITTEN_WARNING      BOOLEAN;
   ERROR_DESC                     VARCHAR2 (2000);
BEGIN
   OPEN C_ABSENCE;

   LOOP
      FETCH C_ABSENCE
       INTO LC_C_ABSENCE;

      BEGIN
         SELECT DISTINCT PPF.PERSON_ID
                    INTO L_PERSON_ID
                    FROM PER_PEOPLE_F PPF
                   WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.EMPLOYEE_NUMBER;
      EXCEPTION
         WHEN OTHERS
         THEN
            L_PERSON_ID := NULL;
      END;

      BEGIN
         SELECT DISTINCT PPF.PERSON_ID
                    INTO L_AUTHORISING_PERSON_ID
                    FROM PER_PEOPLE_F PPF
                   WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.AUTHORISING_PERSON;
      EXCEPTION
         WHEN OTHERS
         THEN
            L_AUTHORISING_PERSON_ID := NULL;
      END;

      BEGIN
         SELECT DISTINCT PPF.PERSON_ID
                    INTO L_REPLACEMENT_PERSON_ID
                    FROM PER_PEOPLE_F PPF
                   WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.REPLACEMENT_PERSON;
      EXCEPTION
         WHEN OTHERS
         THEN
            L_REPLACEMENT_PERSON_ID := NULL;
      END;

      BEGIN
         SELECT ABSENCE_ATTENDANCE_TYPE_ID
           INTO L_ABSENCE_ATTENDANCE_TYPE_ID
           FROM PER_ABSENCE_ATTENDANCE_TYPES
          WHERE UPPER (NAME) = UPPER (LC_C_ABSENCE.ABSENCE_ATTENDANCE_TYPE);
      EXCEPTION
         WHEN OTHERS
         THEN
            L_ABSENCE_ATTENDANCE_TYPE_ID := NULL;
      END;

      BEGIN
         SELECT PAAR.ABS_ATTENDANCE_REASON_ID
           INTO L_ABS_ATTENDANCE_REASON_ID
           FROM PER_ABS_ATTENDANCE_REASONS PAAR, HR_LOOKUPS HL
          WHERE HL.LOOKUP_TYPE = 'ABSENCE_REASON'
            AND PAAR.NAME = HL.LOOKUP_CODE
            AND UPPER (HL.MEANING) =
                                    UPPER (LC_C_ABSENCE.ABS_ATTENDANCE_REASON);
      EXCEPTION
         WHEN OTHERS
         THEN
            L_ABS_ATTENDANCE_REASON_ID := NULL;
      END;

      EXIT WHEN C_ABSENCE%NOTFOUND;

      BEGIN
         HR_PERSON_ABSENCE_API.CREATE_PERSON_ABSENCE
            (P_VALIDATE                          => FALSE,
             P_EFFECTIVE_DATE                    => SYSDATE,
             P_PERSON_ID                         => L_PERSON_ID,
             P_BUSINESS_GROUP_ID                 => 0,
             P_ABSENCE_ATTENDANCE_TYPE_ID        => L_ABSENCE_ATTENDANCE_TYPE_ID,
             P_ABS_ATTENDANCE_REASON_ID          => L_ABS_ATTENDANCE_REASON_ID,
             P_COMMENTS                          => LC_C_ABSENCE.COMMENTS,
             P_DATE_NOTIFICATION                 => LC_C_ABSENCE.DATE_NOTIFICATION,
             P_DATE_PROJECTED_START              => LC_C_ABSENCE.DATE_PROJECTED_START,
             P_TIME_PROJECTED_START              => LC_C_ABSENCE.TIME_PROJECTED_START,
             P_DATE_PROJECTED_END                => LC_C_ABSENCE.DATE_PROJECTED_END,
             P_TIME_PROJECTED_END                => LC_C_ABSENCE.TIME_PROJECTED_END,
             P_DATE_START                        => LC_C_ABSENCE.DATE_START,
             P_TIME_START                        => LC_C_ABSENCE.TIME_START,
             P_DATE_END                          => LC_C_ABSENCE.DATE_END,
             P_TIME_END                          => LC_C_ABSENCE.TIME_END,
             P_ABSENCE_DAYS                      => LC_C_ABSENCE.ABSENCE_DAYS,
             P_ABSENCE_HOURS                     => LC_C_ABSENCE.ABSENCE_HOURS,
             P_AUTHORISING_PERSON_ID             => L_AUTHORISING_PERSON_ID,
             P_REPLACEMENT_PERSON_ID             => L_REPLACEMENT_PERSON_ID,
             P_ATTRIBUTE_CATEGORY                => LC_C_ABSENCE.ATTRIBUTE_CATEGORY,
             P_ATTRIBUTE1                        => LC_C_ABSENCE.ATTRIBUTE1,
             P_ATTRIBUTE2                        => LC_C_ABSENCE.ATTRIBUTE2,
             P_ATTRIBUTE3                        => LC_C_ABSENCE.ATTRIBUTE3,
             P_ATTRIBUTE4                        => LC_C_ABSENCE.ATTRIBUTE4,
             P_ATTRIBUTE5                        => LC_C_ABSENCE.ATTRIBUTE5,
             P_ATTRIBUTE6                        => LC_C_ABSENCE.ATTRIBUTE6,
             P_ATTRIBUTE7                        => LC_C_ABSENCE.ATTRIBUTE7,
             P_ATTRIBUTE8                        => LC_C_ABSENCE.ATTRIBUTE8,
             P_ATTRIBUTE9                        => LC_C_ABSENCE.ATTRIBUTE9,
             P_ATTRIBUTE10                       => LC_C_ABSENCE.ATTRIBUTE10,
             P_ATTRIBUTE11                       => LC_C_ABSENCE.ATTRIBUTE11,
             P_ATTRIBUTE12                       => LC_C_ABSENCE.ATTRIBUTE12,
             P_ATTRIBUTE13                       => LC_C_ABSENCE.ATTRIBUTE13,
             P_ATTRIBUTE14                       => LC_C_ABSENCE.ATTRIBUTE14,
             P_ATTRIBUTE15                       => LC_C_ABSENCE.ATTRIBUTE15,
             P_ATTRIBUTE16                       => LC_C_ABSENCE.ATTRIBUTE16,
             P_ATTRIBUTE17                       => LC_C_ABSENCE.ATTRIBUTE17,
             P_ATTRIBUTE18                       => LC_C_ABSENCE.ATTRIBUTE18,
             P_ATTRIBUTE19                       => LC_C_ABSENCE.ATTRIBUTE19,
             P_ATTRIBUTE20                       => LC_C_ABSENCE.ATTRIBUTE20,
             P_PERIOD_OF_INCAPACITY_ID           => LC_C_ABSENCE.PERIOD_OF_INCAPACITY,
             P_SSP1_ISSUED                       => 'N',
             P_MATERNITY_ID                      => LC_C_ABSENCE.MATERNITY_ID,
             P_SICKNESS_START_DATE               => LC_C_ABSENCE.SICKNESS_START_DATE,
             P_SICKNESS_END_DATE                 => LC_C_ABSENCE.SICKNESS_END_DATE,
             P_PREGNANCY_RELATED_ILLNESS         => 'N',
             P_REASON_FOR_NOTIFICATION_DELA      => LC_C_ABSENCE.REASON_FOR_NOTIFICATION_DELAY,
             P_ACCEPT_LATE_NOTIFICATION_FLA      => 'N',
             P_LINKED_ABSENCE_ID                 => LC_C_ABSENCE.LINKED_ABSENCE_ID,
             P_BATCH_ID                          => LC_C_ABSENCE.BATCH_ID,
             P_CREATE_ELEMENT_ENTRY              => TRUE,
             P_ABS_INFORMATION_CATEGORY          => LC_C_ABSENCE.ABS_INFORMATION_CATEGORY,
             P_ABS_INFORMATION1                  => LC_C_ABSENCE.ABS_INFORMATION1,
             P_ABS_INFORMATION2                  => LC_C_ABSENCE.ABS_INFORMATION2,
             P_ABS_INFORMATION3                  => LC_C_ABSENCE.ABS_INFORMATION3,
             P_ABS_INFORMATION4                  => LC_C_ABSENCE.ABS_INFORMATION4,
             P_ABS_INFORMATION5                  => LC_C_ABSENCE.ABS_INFORMATION5,
             P_ABS_INFORMATION6                  => LC_C_ABSENCE.ABS_INFORMATION6,
             P_ABS_INFORMATION7                  => LC_C_ABSENCE.ABS_INFORMATION7,
             P_ABS_INFORMATION8                  => LC_C_ABSENCE.ABS_INFORMATION8,
             P_ABS_INFORMATION9                  => LC_C_ABSENCE.ABS_INFORMATION9,
             P_ABS_INFORMATION10                 => LC_C_ABSENCE.ABS_INFORMATION10,
             P_ABS_INFORMATION11                 => LC_C_ABSENCE.ABS_INFORMATION11,
             P_ABS_INFORMATION12                 => LC_C_ABSENCE.ABS_INFORMATION12,
             P_ABS_INFORMATION13                 => LC_C_ABSENCE.ABS_INFORMATION13,
             P_ABS_INFORMATION14                 => LC_C_ABSENCE.ABS_INFORMATION14,
             P_ABS_INFORMATION15                 => LC_C_ABSENCE.ABS_INFORMATION15,
             P_ABS_INFORMATION16                 => LC_C_ABSENCE.ABS_INFORMATION16,
             P_ABS_INFORMATION17                 => LC_C_ABSENCE.ABS_INFORMATION17,
             P_ABS_INFORMATION18                 => LC_C_ABSENCE.ABS_INFORMATION18,
             P_ABS_INFORMATION19                 => LC_C_ABSENCE.ABS_INFORMATION19,
             P_ABS_INFORMATION20                 => LC_C_ABSENCE.ABS_INFORMATION20,
             P_ABS_INFORMATION21                 => LC_C_ABSENCE.ABS_INFORMATION21,
             P_ABS_INFORMATION22                 => LC_C_ABSENCE.ABS_INFORMATION22,
             P_ABS_INFORMATION23                 => LC_C_ABSENCE.ABS_INFORMATION23,
             P_ABS_INFORMATION24                 => LC_C_ABSENCE.ABS_INFORMATION24,
             P_ABS_INFORMATION25                 => LC_C_ABSENCE.ABS_INFORMATION25,
             P_ABS_INFORMATION26                 => LC_C_ABSENCE.ABS_INFORMATION26,
             P_ABS_INFORMATION27                 => LC_C_ABSENCE.ABS_INFORMATION27,
             P_ABS_INFORMATION28                 => LC_C_ABSENCE.ABS_INFORMATION28,
             P_ABS_INFORMATION29                 => LC_C_ABSENCE.ABS_INFORMATION29,
             P_ABS_INFORMATION30                 => LC_C_ABSENCE.ABS_INFORMATION30,
             P_ABSENCE_CASE_ID                   => L_ABSENCE_CASE_ID,
             P_ABSENCE_ATTENDANCE_ID             => L_ABSENCE_ATTENDANCE_ID,
             P_OBJECT_VERSION_NUMBER             => L_OBJECT_VERSION_NUMBER,
             P_OCCURRENCE                        => L_OCCURRENCE,
             P_DUR_DYS_LESS_WARNING              => L_DUR_DYS_LESS_WARNING,
             P_DUR_HRS_LESS_WARNING              => L_DUR_HRS_LESS_WARNING,
             P_EXCEEDS_PTO_ENTIT_WARNING         => L_EXCEEDS_PTO_ENTIT_WARNING,
             P_EXCEEDS_RUN_TOTAL_WARNING         => L_EXCEEDS_RUN_TOTAL_WARNING,
             P_ABS_OVERLAP_WARNING               => L_ABS_OVERLAP_WARNING,
             P_ABS_DAY_AFTER_WARNING             => L_ABS_DAY_AFTER_WARNING,
             P_DUR_OVERWRITTEN_WARNING           => L_DUR_OVERWRITTEN_WARNING
            );

         IF L_ABSENCE_ATTENDANCE_ID IS NOT NULL
         THEN
            ERROR_DESC := 'NO ERROR';

            UPDATE DEV_ABSENCE_ATTENDANCES
               SET ERROR_DESCRIPTION = ERROR_DESC
             WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
         END IF;

         IF L_ABSENCE_ATTENDANCE_ID IS NULL
         THEN
            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_ABSENCE_ATTENDANCES
               SET ERROR_DESCRIPTION = ERROR_DESC
             WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
         END IF;
--         DBMS_OUTPUT.PUT_LINE ('ELEMENT TYPE ID    : ' || L_ELEMENT_TYPE_ID);*/
      EXCEPTION
         WHEN OTHERS
         THEN
            UPDATE DEV_ABSENCE_ATTENDANCES
               SET ERROR_DESCRIPTION = ERROR_DESC
             WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
      --DBMS_OUTPUT.PUT_LINE (SQLERRM);
      END;
   --COMMIT;
   END LOOP;

   CLOSE C_ABSENCE;
END;
/


UPDATE PROCEDURE



CREATE OR REPLACE PROCEDURE APPS.DEV_UPDATE_PERSON_ABSENCE
IS
   CURSOR C_ABSENCE
   IS

SELECT LINE_ID, ABSENCE_ATTENDANCE_ID, BUSINESS_GROUP_ID,
       ABSENCE_ATTENDANCE_TYPE, ABS_ATTENDANCE_REASON, EMPLOYEE_NUMBER,
       AUTHORISING_PERSON, REPLACEMENT_PERSON, PERIOD_OF_INCAPACITY,
       ABSENCE_DAYS, ABSENCE_HOURS, DATE_END, DATE_NOTIFICATION,
       DATE_PROJECTED_END, DATE_PROJECTED_START, DATE_START, OCCURRENCE,
       SSP1_ISSUED, TIME_END, TIME_PROJECTED_END, TIME_PROJECTED_START,
       TIME_START, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
       PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
       ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
       ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
       ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
       ATTRIBUTE19, ATTRIBUTE20, LAST_UPDATE_DATE, LAST_UPDATED_BY,
       LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, MATERNITY_ID,
       SICKNESS_START_DATE, SICKNESS_END_DATE, PREGNANCY_RELATED_ILLNESS,
       REASON_FOR_NOTIFICATION_DELAY, ACCEPT_LATE_NOTIFICATION_FLAG,
       LINKED_ABSENCE_ID, BATCH_ID, ABS_INFORMATION_CATEGORY,
       ABS_INFORMATION1, ABS_INFORMATION2, ABS_INFORMATION3, ABS_INFORMATION4,
       ABS_INFORMATION5, ABS_INFORMATION6, ABS_INFORMATION7, ABS_INFORMATION8,
       ABS_INFORMATION9, ABS_INFORMATION10, ABS_INFORMATION11,
       ABS_INFORMATION12, ABS_INFORMATION13, ABS_INFORMATION14,
       ABS_INFORMATION15, ABS_INFORMATION16, ABS_INFORMATION17,
       ABS_INFORMATION18, ABS_INFORMATION19, ABS_INFORMATION20,
       ABS_INFORMATION21, ABS_INFORMATION22, ABS_INFORMATION23,
       ABS_INFORMATION24, ABS_INFORMATION25, ABS_INFORMATION26,
       ABS_INFORMATION27, ABS_INFORMATION28, ABS_INFORMATION29,
       ABS_INFORMATION30, OBJECT_VERSION_NUMBER, COMMENTS, ABSENCE_CASE_ID,
       APPROVAL_STATUS, CONFIRMED_UNTIL, SOURCE, ERROR_DESCRIPTION
  FROM DEV_ABSENCE_ATTENDANCES
  ORDER BY 1 ASC;
        -- VARIABLES

                   LC_C_ABSENCE                 C_ABSENCE%ROWTYPE;
                   L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;
                   L_ABS_ATTENDANCE_REASON_ID   NUMBER;
                   L_PERSON_ID                  NUMBER;
                   L_AUTHORISING_PERSON_ID      NUMBER;
                   L_REPLACEMENT_PERSON_ID      NUMBER;
                   L_ABSENCE_CASE_ID            NUMBER;
                   L_ABSENCE_ATTENDANCE_ID      NUMBER;
                   L_OBJECT_VERSION_NUMBER      NUMBER;
                   L_OCCURRENCE                 NUMBER;
                   L_DUR_DYS_LESS_WARNING       BOOLEAN;
                   L_DUR_HRS_LESS_WARNING       BOOLEAN;
                   L_EXCEEDS_PTO_ENTIT_WARNING  BOOLEAN;
                   L_EXCEEDS_RUN_TOTAL_WARNING  BOOLEAN;
                   L_ABS_OVERLAP_WARNING        BOOLEAN;
                   L_ABS_DAY_AFTER_WARNING      BOOLEAN;
                   L_DUR_OVERWRITTEN_WARNING    BOOLEAN;
                   L_DEL_ELEMENT_ENTRY_WARNING  BOOLEAN;
                   ERROR_DESC                   VARCHAR2(2000);
                
                
                



BEGIN
   OPEN C_ABSENCE;

  LOOP
    FETCH C_ABSENCE
       INTO LC_C_ABSENCE;
    BEGIN
  
       SELECT DISTINCT PPF.PERSON_ID INTO L_PERSON_ID FROM PER_PEOPLE_F PPF
       WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.EMPLOYEE_NUMBER;
    
       EXCEPTION WHEN OTHERS THEN
                L_PERSON_ID := NULL;
    
    END;
    
    BEGIN
       SELECT DISTINCT PPF.PERSON_ID INTO L_AUTHORISING_PERSON_ID FROM PER_PEOPLE_F PPF
       WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.AUTHORISING_PERSON;
    
       EXCEPTION WHEN OTHERS THEN
                L_AUTHORISING_PERSON_ID := NULL;
    END;
  
    BEGIN
       SELECT DISTINCT PPF.PERSON_ID INTO L_REPLACEMENT_PERSON_ID FROM PER_PEOPLE_F PPF
       WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.REPLACEMENT_PERSON;
    
       EXCEPTION WHEN OTHERS THEN
                L_REPLACEMENT_PERSON_ID := NULL;
    
    END;
    
    BEGIN
    
       SELECT ABSENCE_ATTENDANCE_TYPE_ID INTO L_ABSENCE_ATTENDANCE_TYPE_ID FROM PER_ABSENCE_ATTENDANCE_TYPES
       WHERE UPPER(NAME) = UPPER(LC_C_ABSENCE.ABSENCE_ATTENDANCE_TYPE);
    
       EXCEPTION WHEN OTHERS THEN
                L_ABSENCE_ATTENDANCE_TYPE_ID  := NULL;
    
    END;
    
    BEGIN

       SELECT PAAR.ABS_ATTENDANCE_REASON_ID INTO L_ABS_ATTENDANCE_REASON_ID FROM PER_ABS_ATTENDANCE_REASONS PAAR , HR_LOOKUPS HL
       WHERE HL.LOOKUP_TYPE = 'ABSENCE_REASON'
       AND PAAR.NAME = HL.LOOKUP_CODE
       AND UPPER(HL.MEANING) = UPPER(LC_C_ABSENCE.ABS_ATTENDANCE_REASON);
    
       EXCEPTION WHEN OTHERS THEN
                L_ABS_ATTENDANCE_REASON_ID  := NULL;
    
    END;
    

    
    
    
    
  
     EXIT WHEN C_ABSENCE%NOTFOUND;
  

  
      BEGIN

                 hr_person_absence_api.update_person_absence
                  (P_VALIDATE                      => FALSE
                  ,P_EFFECTIVE_DATE                => SYSDATE
                  ,P_ABSENCE_ATTENDANCE_ID         => LC_C_ABSENCE.ABSENCE_ATTENDANCE_ID
                  ,P_ABS_ATTENDANCE_REASON_ID      => L_ABS_ATTENDANCE_REASON_ID
                  ,P_COMMENTS                      => LC_C_ABSENCE.COMMENTS
                  ,P_DATE_NOTIFICATION             => LC_C_ABSENCE.DATE_NOTIFICATION
                  ,P_DATE_PROJECTED_START          => LC_C_ABSENCE.DATE_PROJECTED_START
                  ,P_TIME_PROJECTED_START          => LC_C_ABSENCE.TIME_PROJECTED_START
                  ,P_DATE_PROJECTED_END            => LC_C_ABSENCE.DATE_PROJECTED_END
                  ,P_TIME_PROJECTED_END            => LC_C_ABSENCE.TIME_PROJECTED_END
                  ,P_DATE_START                    => LC_C_ABSENCE.DATE_START
                  ,P_TIME_START                    => LC_C_ABSENCE.TIME_START
                  ,P_DATE_END                      => LC_C_ABSENCE.DATE_END
                  ,P_TIME_END                      => LC_C_ABSENCE.TIME_END
                  ,P_ABSENCE_DAYS                  => LC_C_ABSENCE.ABSENCE_DAYS
                  ,P_ABSENCE_HOURS                 => LC_C_ABSENCE.ABSENCE_HOURS
                  ,P_AUTHORISING_PERSON_ID         => L_AUTHORISING_PERSON_ID
                  ,P_REPLACEMENT_PERSON_ID         => L_REPLACEMENT_PERSON_ID
                  ,P_ATTRIBUTE_CATEGORY            => LC_C_ABSENCE.ATTRIBUTE_CATEGORY
                  ,P_ATTRIBUTE1                    => LC_C_ABSENCE.ATTRIBUTE1
                  ,P_ATTRIBUTE2                    => LC_C_ABSENCE.ATTRIBUTE2
                  ,P_ATTRIBUTE3                    => LC_C_ABSENCE.ATTRIBUTE3
                  ,P_ATTRIBUTE4                    => LC_C_ABSENCE.ATTRIBUTE4
                  ,P_ATTRIBUTE5                    => LC_C_ABSENCE.ATTRIBUTE5
                  ,P_ATTRIBUTE6                    => LC_C_ABSENCE.ATTRIBUTE6
                  ,P_ATTRIBUTE7                    => LC_C_ABSENCE.ATTRIBUTE7
                  ,P_ATTRIBUTE8                    => LC_C_ABSENCE.ATTRIBUTE8
                  ,P_ATTRIBUTE9                    => LC_C_ABSENCE.ATTRIBUTE9
                  ,P_ATTRIBUTE10                   => LC_C_ABSENCE.ATTRIBUTE10
                  ,P_ATTRIBUTE11                   => LC_C_ABSENCE.ATTRIBUTE11
                  ,P_ATTRIBUTE12                   => LC_C_ABSENCE.ATTRIBUTE12
                  ,P_ATTRIBUTE13                   => LC_C_ABSENCE.ATTRIBUTE13
                  ,P_ATTRIBUTE14                   => LC_C_ABSENCE.ATTRIBUTE14
                  ,P_ATTRIBUTE15                   => LC_C_ABSENCE.ATTRIBUTE15
                  ,P_ATTRIBUTE16                   => LC_C_ABSENCE.ATTRIBUTE16
                  ,P_ATTRIBUTE17                   => LC_C_ABSENCE.ATTRIBUTE17
                  ,P_ATTRIBUTE18                   => LC_C_ABSENCE.ATTRIBUTE18
                  ,P_ATTRIBUTE19                   => LC_C_ABSENCE.ATTRIBUTE19
                  ,P_ATTRIBUTE20                   => LC_C_ABSENCE.ATTRIBUTE20
                  ,P_PERIOD_OF_INCAPACITY_ID       => LC_C_ABSENCE.PERIOD_OF_INCAPACITY
                  ,P_SSP1_ISSUED                   => 'N'
                  ,P_MATERNITY_ID                  => LC_C_ABSENCE.MATERNITY_ID
                  ,P_SICKNESS_START_DATE           => LC_C_ABSENCE.SICKNESS_START_DATE
                  ,P_SICKNESS_END_DATE             => LC_C_ABSENCE.SICKNESS_END_DATE
                  ,P_PREGNANCY_RELATED_ILLNESS     => 'N'
                  ,P_REASON_FOR_NOTIFICATION_DELA  => LC_C_ABSENCE.REASON_FOR_NOTIFICATION_DELAY
                  ,P_ACCEPT_LATE_NOTIFICATION_FLA  => 'N'
                  ,P_LINKED_ABSENCE_ID             => LC_C_ABSENCE.LINKED_ABSENCE_ID
                  ,P_BATCH_ID                      => LC_C_ABSENCE.BATCH_ID
                  ,P_ABS_INFORMATION_CATEGORY      => LC_C_ABSENCE.ABS_INFORMATION_CATEGORY
                  ,P_ABS_INFORMATION1              => LC_C_ABSENCE.ABS_INFORMATION1
                  ,P_ABS_INFORMATION2              => LC_C_ABSENCE.ABS_INFORMATION2
                  ,P_ABS_INFORMATION3              => LC_C_ABSENCE.ABS_INFORMATION3
                  ,P_ABS_INFORMATION4              => LC_C_ABSENCE.ABS_INFORMATION4
                  ,P_ABS_INFORMATION5              => LC_C_ABSENCE.ABS_INFORMATION5
                  ,P_ABS_INFORMATION6              => LC_C_ABSENCE.ABS_INFORMATION6
                  ,P_ABS_INFORMATION7              => LC_C_ABSENCE.ABS_INFORMATION7
                  ,P_ABS_INFORMATION8              => LC_C_ABSENCE.ABS_INFORMATION8
                  ,P_ABS_INFORMATION9              => LC_C_ABSENCE.ABS_INFORMATION9
                  ,P_ABS_INFORMATION10             => LC_C_ABSENCE.ABS_INFORMATION10
                  ,P_ABS_INFORMATION11             => LC_C_ABSENCE.ABS_INFORMATION11
                  ,P_ABS_INFORMATION12             => LC_C_ABSENCE.ABS_INFORMATION12
                  ,P_ABS_INFORMATION13             => LC_C_ABSENCE.ABS_INFORMATION13
                  ,P_ABS_INFORMATION14             => LC_C_ABSENCE.ABS_INFORMATION14
                  ,P_ABS_INFORMATION15             => LC_C_ABSENCE.ABS_INFORMATION15
                  ,P_ABS_INFORMATION16             => LC_C_ABSENCE.ABS_INFORMATION16
                  ,P_ABS_INFORMATION17             => LC_C_ABSENCE.ABS_INFORMATION17
                  ,P_ABS_INFORMATION18             => LC_C_ABSENCE.ABS_INFORMATION18
                  ,P_ABS_INFORMATION19             => LC_C_ABSENCE.ABS_INFORMATION19
                  ,P_ABS_INFORMATION20             => LC_C_ABSENCE.ABS_INFORMATION20
                  ,P_ABS_INFORMATION21             => LC_C_ABSENCE.ABS_INFORMATION21
                  ,P_ABS_INFORMATION22             => LC_C_ABSENCE.ABS_INFORMATION22
                  ,P_ABS_INFORMATION23             => LC_C_ABSENCE.ABS_INFORMATION23
                  ,P_ABS_INFORMATION24             => LC_C_ABSENCE.ABS_INFORMATION24
                  ,P_ABS_INFORMATION25             => LC_C_ABSENCE.ABS_INFORMATION25
                  ,P_ABS_INFORMATION26             => LC_C_ABSENCE.ABS_INFORMATION26
                  ,P_ABS_INFORMATION27             => LC_C_ABSENCE.ABS_INFORMATION27
                  ,P_ABS_INFORMATION28             => LC_C_ABSENCE.ABS_INFORMATION28
                  ,P_ABS_INFORMATION29             => LC_C_ABSENCE.ABS_INFORMATION29
                  ,P_ABS_INFORMATION30             => LC_C_ABSENCE.ABS_INFORMATION30
                  ,P_ABSENCE_CASE_ID               => L_ABSENCE_CASE_ID
                  ,P_OBJECT_VERSION_NUMBER         => L_OBJECT_VERSION_NUMBER
                  ,P_DUR_DYS_LESS_WARNING          => L_DUR_DYS_LESS_WARNING
                  ,P_DUR_HRS_LESS_WARNING          => L_DUR_HRS_LESS_WARNING
                  ,P_EXCEEDS_PTO_ENTIT_WARNING     => L_EXCEEDS_PTO_ENTIT_WARNING
                  ,P_EXCEEDS_RUN_TOTAL_WARNING     => L_EXCEEDS_RUN_TOTAL_WARNING
                  ,P_ABS_OVERLAP_WARNING           => L_ABS_OVERLAP_WARNING
                  ,P_ABS_DAY_AFTER_WARNING         => L_ABS_DAY_AFTER_WARNING
                  ,P_DUR_OVERWRITTEN_WARNING       => L_DUR_OVERWRITTEN_WARNING
                  ,P_DEL_ELEMENT_ENTRY_WARNING     => L_DEL_ELEMENT_ENTRY_WARNING
                  );
                        
                        
            IF L_OBJECT_VERSION_NUMBER IS NOT NULL
         THEN         
            ERROR_DESC := 'NO ERROR';
        
            UPDATE DEV_ABSENCE_ATTENDANCES
               SET ERROR_DESCRIPTION = ERROR_DESC
             WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
         END IF;

         IF L_OBJECT_VERSION_NUMBER IS NULL
         THEN
            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_ABSENCE_ATTENDANCES
               SET ERROR_DESCRIPTION = ERROR_DESC
             WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
         END IF;

--         DBMS_OUTPUT.PUT_LINE ('ELEMENT TYPE ID    : ' || L_ELEMENT_TYPE_ID);*/
      EXCEPTION
         WHEN OTHERS
         THEN
        

            UPDATE DEV_ABSENCE_ATTENDANCES
               SET ERROR_DESCRIPTION = ERROR_DESC
             WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;

            --DBMS_OUTPUT.PUT_LINE (SQLERRM);
      END;

  
      --COMMIT;               

    
  END LOOP;

   CLOSE C_ABSENCE;
  
END;
/


DELETE PROCEDURE



CREATE OR REPLACE PROCEDURE APPS.DEV_DELETE_ABSENCES
IS
   CURSOR C_COMP
   IS
      SELECT PPF.EMPLOYEE_NUMBER, PAA.ABSENCE_DAYS,
             PAA.ABSENCE_ATTENDANCE_ID, PAA.OBJECT_VERSION_NUMBER,
            
--DISTINCT
             FU.USER_NAME, PAAT.NAME, PAA.DATE_START
        FROM PER_PEOPLE_F PPF,
             PER_ASSIGNMENTS_F PAF,
             PER_ABSENCE_ATTENDANCES PAA,
             FND_USER FU,
             PER_ABSENCE_ATTENDANCE_TYPES PAAT
       WHERE PPF.PERSON_ID = PAF.PERSON_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 PPF.PERSON_ID = PAA.PERSON_ID
         AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
         AND PAA.CREATED_BY = FU.USER_ID
         AND PAA.ABSENCE_ATTENDANCE_TYPE_ID <> 61
         AND FU.USER_NAME IN ('AFF.PAYROLL', 'ANONYMOUS');

   LC_C_COMP                C_COMP%ROWTYPE;
   L_COMP_ID                NUMBER;
   L_EFFECTIVE_START_DATE   DATE;
   L_EFFECTIVE_END_DATE     DATE;
   L_ORG_ID                 NUMBER;
   L_LOC_ID                 NUMBER;
   L_BASIS_ID               NUMBER;
   L_PPL_GROUP_ID           NUMBER;
   L_ENTRIES_WARNING        BOOLEAN;
BEGIN
   OPEN C_COMP;

   LOOP
      FETCH C_COMP
       INTO LC_C_COMP;

      EXIT WHEN C_COMP%NOTFOUND;

      BEGIN
         DBMS_OUTPUT.PUT_LINE ('RAN');

         HR_PERSON_ABSENCE_API.DELETE_PERSON_ABSENCE
                 (P_VALIDATE                   => FALSE,
                  P_ABSENCE_ATTENDANCE_ID      => LC_C_COMP.ABSENCE_ATTENDANCE_ID,
                  P_OBJECT_VERSION_NUMBER      => LC_C_COMP.OBJECT_VERSION_NUMBER
                 );
      --DBMS_OUTPUT.PUT_LINE(L_EFFECTIVE_START_DATE);
      END;
   END LOOP;

   CLOSE C_COMP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 254));
END;
/




I hope this post is helpful. Feedback is much appreciated.!


0 comments: