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;

Oracle EBS Create Element Entry Procedure

CREATE OR REPLACE PROCEDURE APPS.MNJ_CREATE_ELEMENT_ENTRY( ERRBUF  OUT VARCHAR2, RETCODE OUT VARCHAR2,P_MONTH VARCHAR2, P_PAYROLL_ID NUMBER ,P_ELEMENT_ID NUMBER ,P_INPUT_VALUE_ID NUMBER, P_VALUE VARCHAR2)   

IS


   LN_ELEMENT_LINK_ID                   NUMBER;
   LD_EFFECTIVE_START_DATE              DATE;
   LD_EFFECTIVE_END_DATE                DATE;
   LN_ELEMENT_ENTRY_ID                  NUMBER;
   LN_OBJECT_VERSION_NUMBER             NUMBER;
   LB_CREATE_WARNING                    BOOLEAN;
   LN_INPUT_VALUE_ID                    NUMBER;
   LN_SCREEN_ENTRY_VALUE                VARCHAR2(60);
   LN_ELEMENT_TYPE_ID                   NUMBER;


CURSOR C_EMP IS

SELECT
    PPF.LAST_NAME,          
    PPF.PERSON_ID,             
    PAAF.ASSIGNMENT_ID,          
    PAAF.ASSIGNMENT_NUMBER,      
    PAAF.PAYROLL_ID          
FROM
    PER_PEOPLE_F            PPF,
    PER_ALL_ASSIGNMENTS_F   PAAF
WHERE
    PPF.PERSON_ID       =   PAAF.PERSON_ID
AND PAAF.PRIMARY_FLAG   =   'Y'
AND LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY')) BETWEEN PPF.EFFECTIVE_START_DATE     AND PPF.EFFECTIVE_END_DATE
AND LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY')) BETWEEN PAAF.EFFECTIVE_START_DATE    AND PAAF.EFFECTIVE_END_DATE
--AND PPF.EMPLOYEE_NUMBER =   '10595'
AND PAAF.PAYROLL_ID     =   P_PAYROLL_ID;


BEGIN

        LN_ELEMENT_LINK_ID  :=  -1;



                fnd_file.put_line(fnd_file.output, 'Assignment Number | Status' );


        FOR CUR IN C_EMP LOOP
       
        -- Get Element Link Id
        ----------------------
           
            Begin
           
                LN_ELEMENT_LINK_ID :=   HR_ENTRY_API.GET_LINK
                                                            (      
                                                             P_ASSIGNMENT_ID   => CUR.ASSIGNMENT_ID,
                                                             P_ELEMENT_TYPE_ID => P_ELEMENT_ID,
                                                             P_SESSION_DATE    => LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY'))
                                                            );
    
                DBMS_OUTPUT.PUT_LINE( 'Element Link Id: ' || LN_ELEMENT_LINK_ID );
           
            Exception When Others Then
           
                LN_ELEMENT_LINK_ID :=   -1;  
               
                fnd_file.put_line(fnd_file.output, CUR.ASSIGNMENT_NUMBER ||' | Unable to find element link.' );               
                   
            End;
           
            IF      LN_ELEMENT_LINK_ID > 0  THEN
           
                Begin
               
                    -- Create Element Entry
                    -- ------------------------------
                    PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY
                     (     -- INPUT DATA ELEMENTS
                           -- -----------------------------
                           P_EFFECTIVE_DATE                 => LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY')),
                           P_BUSINESS_GROUP_ID              => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
                           P_ASSIGNMENT_ID                  => CUR.ASSIGNMENT_ID,
                           P_ELEMENT_LINK_ID                => LN_ELEMENT_LINK_ID,
                           P_ENTRY_TYPE                     => 'E',
                           P_INPUT_VALUE_ID1                => P_INPUT_VALUE_ID,
                           P_ENTRY_VALUE1                   => P_VALUE,
                           -- OUTPUT DATA ELEMENTS
                           -- --------------------------------
                           P_EFFECTIVE_START_DATE           => LD_EFFECTIVE_START_DATE,
                           P_EFFECTIVE_END_DATE             => LD_EFFECTIVE_END_DATE,
                           P_ELEMENT_ENTRY_ID               => LN_ELEMENT_ENTRY_ID,
                           P_OBJECT_VERSION_NUMBER          => LN_OBJECT_VERSION_NUMBER,
                           P_CREATE_WARNING                 => LB_CREATE_WARNING
                     );
        
                    fnd_file.put_line(fnd_file.output, CUR.ASSIGNMENT_NUMBER ||' | Successfully Created.' );
                    dbms_output.put_line( 'Successfull - Element Entry Id: ' || ln_element_entry_id );
               
                    COMMIT;
                   
                    LN_ELEMENT_LINK_ID  := -1;
                        
                Exception When Others Then
                       
                    LN_ELEMENT_LINK_ID  := -1;
                    fnd_file.put_line(fnd_file.output, CUR.ASSIGNMENT_NUMBER ||' | Error Occured While Element Entry.' );
               
                    Rollback;
                   
                End;
               
            END IF;
           
        END LOOP;


Exception When Others Then

    dbms_output.put_line( 'Error : ' || sqlerrm );
    fnd_file.put_line(fnd_file.output, 'Error : ' || sqlerrm );   

End;
/