Update Element Entry API

CREATE OR REPLACE PROCEDURE MNJ_UPDATE_LEAVE_HOURS( ERRBUF  OUT VARCHAR2, RETCODE OUT VARCHAR2)
AS
   L_EFFECTIVE_START_DATE   DATE    := NULL;
   L_EFFECTIVE_END_DATE     DATE    := NULL;
   L_UPDATE_WARNING         BOOLEAN;
   L_ELEMENT_TYPE_ID        NUMBER  := NULL;
   L_BUSINESS_GROUP_ID      NUMBER  := 81;

   CURSOR DAYS_CUR
   IS
                   
    SELECT
        PAAF.ASSIGNMENT_NUMBER,
        PEEF.ASSIGNMENT_ID,
        PEEF.ELEMENT_ENTRY_ID,
        PEEF.ELEMENT_TYPE_ID,
        PEEF.EFFECTIVE_START_DATE,
        PEEF.EFFECTIVE_END_DATE,
        PEEF.OBJECT_VERSION_NUMBER,
        PIVF.INPUT_VALUE_ID,
        PETF.ELEMENT_NAME,
        VAL.SCREEN_ENTRY_VALUE,
        UPPER(PIVF.NAME)    INPUT_VALUE
    FROM
        PAY_INPUT_VALUES_F          PIVF,
        PAY_ELEMENT_ENTRIES_F       PEEF,
        PAY_ELEMENT_ENTRY_VALUES_F  VAL,
        PER_ALL_ASSIGNMENTS_F       PAAF,
        PAY_ELEMENT_TYPES_F         PETF
    WHERE
        PEEF.ELEMENT_ENTRY_ID       =   VAL.ELEMENT_ENTRY_ID
    AND PEEF.EFFECTIVE_START_DATE   =   VAL.EFFECTIVE_START_DATE
    AND PEEF.EFFECTIVE_END_DATE     =   VAL.EFFECTIVE_END_DATE
    AND PIVF.ELEMENT_TYPE_ID        =   PEEF.ELEMENT_TYPE_ID
    AND PIVF.INPUT_VALUE_ID         =   VAL.INPUT_VALUE_ID
    AND PAAF.ASSIGNMENT_ID          =   PEEF.ASSIGNMENT_ID
    AND PETF.ELEMENT_TYPE_ID        =   PEEF.ELEMENT_TYPE_ID
    AND SYSDATE     BETWEEN             PAAF.EFFECTIVE_START_DATE   AND PAAF.EFFECTIVE_END_DATE
    AND PAAF.PRIMARY_FLAG           =   'Y'
    AND NVL(PEEF.ATTRIBUTE1,'N')    =   'N'
    AND UPPER(PIVF.NAME)            =   'DAYS'
    AND PEEF.ELEMENT_TYPE_ID        IN  ( '676' , '677' );   --Casual Leave First Half  , Casual Leave Second Half

   CURSOR DATE_CUR ( P_ASSIGNMENT_ID NUMBER , P_ELEMENT_TYPE_ID NUMBER )
   IS
                   
    SELECT
        PEEF.ASSIGNMENT_ID,
        PEEF.ELEMENT_ENTRY_ID,
        PEEF.ELEMENT_TYPE_ID,
        PEEF.EFFECTIVE_START_DATE,
        PEEF.EFFECTIVE_END_DATE,
        PEEF.OBJECT_VERSION_NUMBER,
        PIVF.INPUT_VALUE_ID,
        VAL.SCREEN_ENTRY_VALUE,
        UPPER(PIVF.NAME)    INPUT_VALUE
    FROM
        PAY_INPUT_VALUES_F          PIVF,
        PAY_ELEMENT_ENTRIES_F       PEEF,
        PAY_ELEMENT_ENTRY_VALUES_F  VAL       
    WHERE
        PEEF.ELEMENT_ENTRY_ID       =   VAL.ELEMENT_ENTRY_ID
    AND PEEF.EFFECTIVE_START_DATE   =   VAL.EFFECTIVE_START_DATE
    AND PEEF.EFFECTIVE_END_DATE     =   VAL.EFFECTIVE_END_DATE
    AND PIVF.ELEMENT_TYPE_ID        =   PEEF.ELEMENT_TYPE_ID
    AND PIVF.INPUT_VALUE_ID         =   VAL.INPUT_VALUE_ID
    AND NVL(PEEF.ATTRIBUTE1,'N')    =   'N'
    AND UPPER(PIVF.NAME)            =   'EFFECTIVE DATE'
    AND PEEF.ELEMENT_TYPE_ID        IN   ('676' , '677' )   --Casual Leave First Half , Casual Leave Second Half
    AND PEEF.ASSIGNMENT_ID          =   P_ASSIGNMENT_ID   
    AND PEEF.ELEMENT_TYPE_ID        =   P_ELEMENT_TYPE_ID;

BEGIN

   fnd_file.put_line(fnd_file.output, 'Assignment Number | Element Name | Status' );
               
   FOR REC IN DAYS_CUR LOOP


      BEGIN
     
            PAY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY (
            P_VALIDATE                => FALSE,
            P_DATETRACK_UPDATE_MODE   => 'CORRECTION',
            P_EFFECTIVE_DATE          => SYSDATE, --L_EFFECTIVE_DATE,
            P_BUSINESS_GROUP_ID       => L_BUSINESS_GROUP_ID,
            P_ELEMENT_ENTRY_ID        => REC.ELEMENT_ENTRY_ID,
            P_OBJECT_VERSION_NUMBER   => REC.OBJECT_VERSION_NUMBER,
            P_INPUT_VALUE_ID1         => REC.INPUT_VALUE_ID,
            P_ENTRY_VALUE1            => NVL(REC.SCREEN_ENTRY_VALUE,0)/2,
            P_EFFECTIVE_START_DATE    => REC.EFFECTIVE_START_DATE,
            P_EFFECTIVE_END_DATE      => REC.EFFECTIVE_END_DATE,
            P_UPDATE_WARNING          => L_UPDATE_WARNING);


               FOR D_REC IN DATE_CUR(REC.ASSIGNMENT_ID , REC.ELEMENT_TYPE_ID) LOOP


                  BEGIN
                 
                    PAY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY (
                        P_VALIDATE                => FALSE,
                        P_DATETRACK_UPDATE_MODE   => 'CORRECTION',
                        P_EFFECTIVE_DATE          => SYSDATE, --L_EFFECTIVE_DATE,
                        P_BUSINESS_GROUP_ID       => L_BUSINESS_GROUP_ID,
                        P_ELEMENT_ENTRY_ID        => D_REC.ELEMENT_ENTRY_ID,
                        P_OBJECT_VERSION_NUMBER   => D_REC.OBJECT_VERSION_NUMBER,
                        P_INPUT_VALUE_ID1         => D_REC.INPUT_VALUE_ID,
                        P_ENTRY_VALUE1            => SYSDATE,
                        P_EFFECTIVE_START_DATE    => D_REC.EFFECTIVE_START_DATE,
                        P_EFFECTIVE_END_DATE      => D_REC.EFFECTIVE_END_DATE,
                        P_UPDATE_WARNING          => L_UPDATE_WARNING);

                  END;
              
               END LOOP;


        UPDATE
            PAY_ELEMENT_ENTRIES_F       PEEF
        SET
            PEEF.ATTRIBUTE1         =   'Y'
        WHERE
            PEEF.ASSIGNMENT_ID      =   REC.ASSIGNMENT_ID
        AND PEEF.ELEMENT_ENTRY_ID   =   REC.ELEMENT_ENTRY_ID
        AND PEEF.ELEMENT_TYPE_ID    =   REC.ELEMENT_TYPE_ID;

        COMMIT;

        fnd_file.put_line(fnd_file.output, REC.ASSIGNMENT_NUMBER || ' | ' || REC.ELEMENT_NAME || ' | Successfully Updated.' );        
            DBMS_OUTPUT.PUT_LINE ('Element Entry has been Updated: ' || REC.ELEMENT_ENTRY_ID);
      EXCEPTION
         WHEN OTHERS
         THEN
           
        fnd_file.put_line(fnd_file.output, REC.ASSIGNMENT_NUMBER || ' | ' || REC.ELEMENT_NAME ||  ' | Error Occured.'||sqlerrm );        

      END;
  
   END LOOP;
  
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line(fnd_file.output,'Error Occured While Executing Procedure : ' || SQLERRM);

END;