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;
/