Performance Rating/Review Load

Performance Rating Load

From Application : Performance rating can be entered from application as in below

·         Select Appropriate Business Group Responsibility:  ex: HR Operations US

·         Navigation : HR Operations US > People > Enter and Maintain > Query for Employee > Assignments > Others > Performance

·         Enter, 'Effective Date' and 'Performance Rating', 'Next Date'

·         Save

Tables effected : PER_PERFORMANCE_REVIEWS

Loading through Program or backend: Performance rating can also be entered from backend or by developing a script and registering as Program for bulk Load

·         Api : HR_PERF_REVIEW_API.CREATE_PERF_REVIEW

·         Required data: 

Created_by,

person_id ( can be derived if Employee number provided)

full_name (just  to validate or to confirm the exact employee provided in csv or for any typo)

performance_rating  

Effective_date

next_review_date

Business_Group 

Single Insert : using the below script  done a single insert and the data get inserted into application and could able to see it ( refer to the below screenshot)

The below script is just a sample single insert script followed by a a package /procedure with required validations with commit and Roll back options

DECLARE

  -- Start of Variable declarations, Initialize Variables with appropriate values to test the script

   -- Input Variables

  V_VALIDATE BOOLEAN;

  -- Output Variables

  V_PERFORMANCE_REVIEW_ID NUMBER;

  -- Input Variables

  V_PERSON_ID NUMBER := 21227;

  V_EVENT_ID  NUMBER;

  V_REVIEW_DATE DATE                  := '01-NOV-2014' ;

  V_PERFORMANCE_RATING VARCHAR2(2000) := 'XX_4';

  V_NEXT_PERF_REVIEW_DATE DATE        := '01-NOV-2015';

  -- Output Variables

  V_OBJECT_VERSION_NUMBER    NUMBER;

  V_NEXT_REVIEW_DATE_WARNING BOOLEAN;

begin

  fnd_global.apps_initialize(23248,52441,800 );

  --  Calling API HR_PERF_REVIEW_API.CREATE_PERF_REVIEW

  HR_PERF_REVIEW_API.CREATE_PERF_REVIEW (

            P_VALIDATE => V_VALIDATE ,

            P_PERFORMANCE_REVIEW_ID => V_PERFORMANCE_REVIEW_ID ,

            P_PERSON_ID => V_PERSON_ID ,

            P_EVENT_ID => V_EVENT_ID ,

            P_REVIEW_DATE => V_REVIEW_DATE ,

            P_PERFORMANCE_RATING => V_PERFORMANCE_RATING ,

            P_NEXT_PERF_REVIEW_DATE => V_NEXT_PERF_REVIEW_DATE

            --,P_ATTRIBUTE_CATEGORY           => V_ATTRIBUTE_CATEGORY

            ,P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER ,

            P_NEXT_REVIEW_DATE_WARNING => V_NEXT_REVIEW_DATE_WARNING

            );

  dbms_output.put_line('V_PERFORMANCE_REVIEW_ID : ' || V_PERFORMANCE_REVIEW_ID);

  dbms_output.put_line('V_OBJECT_VERSION_NUMBER : ' || V_OBJECT_VERSION_NUMBER);

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('error : ' || sqlerrm);

END;

 


CREATE or replace package Apps.XX_HR_PERFORMANCE_PKG AS
 /*
  *************************************************************************
  ** XX Performance Review Load by Apps Associates
  *************************************************************************
  FILE_NAME         : XX_HR_PERFORMANCE_PKG.pks
  File Type         : PACKAGE SPECIFICATION
  PURPOSE           : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.
  Created    by        : Jithendra
  Creation Date        : 17-Sep-2014
  Updated by        :
  Updation Date        :
  VERSION           : 1.0
  Comments            :
 ***************************************************************************/

PROCEDURE XX_PERF_REVIEW_LOAD_PROC(
                           X_ERRBUF              OUT VARCHAR2,
                           X_RETCODE             OUT NUMBER,
                           V_PATH_LOCATION in varchar2,
                           v_file_name varchar2,
                           V_REVIEW_DATE in varchar2,
                           V_NEXT_PERF_REVIEW_DATE in varchar2
                            );
end XX_HR_PERFORMANCE_PKG;

/

CREATE or replace package body Apps.XX_HR_PERFORMANCE_PKG AS
 /*
  *************************************************************************
  ** XX Performance Review Load by Apps Associates
  *************************************************************************
  FILE_NAME         : XX_HR_PERFORMANCE_PKG.pkb
  File Type         : PACKAGE body
  PURPOSE           : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.
  Created    by        : Jithendra
  Creation Date        : 17-Sep-2014
  Updated by        :
  Updation Date        :
  VERSION           : 1.0
  Comments            :
 ***************************************************************************/

PROCEDURE XX_PERF_REVIEW_LOAD_PROC (
                           X_ERRBUF              OUT VARCHAR2,
                           X_RETCODE             OUT NUMBER,
                           V_PATH_LOCATION in varchar2,
                           v_file_name varchar2,                         
                           V_REVIEW_DATE in varchar2,
                           V_NEXT_PERF_REVIEW_DATE in varchar2
                            )
as
    V_FILE_TYPE UTL_FILE.FILE_TYPE;
    V_LINE varchar2 (1000);
    V_EMPLOYEE_NUMBER number;
    V_BUSINESS_GROUP   varchar2(200);
    V_LAST_NAME     varchar2(200);
    V_FIRST_NAME   varchar2(200);
    V_PERF_RATING_NAME varchar2(30);
    V_PERF_RATING_CODE VARCHAR2(30);
    V_REC_COUNT number := 0;
    V_VALIDATE                      BOOLEAN;
    V_PERSON_ID                     number ;
    V_PERFORMANCE_REVIEW_ID         NUMBER;
    V_OBJECT_VERSION_NUMBER         NUMBER;
    V_NEXT_REVIEW_DATE_WARNING      BOOLEAN;
    L_NEXT_REVIEW_DATE date;
    V_TOT_count Number :=0;
    v_SUC_count Number :=0;
    V_FAIL_Count Number :=0;
BEGIN

  fnd_file.put_line(fnd_file.output,'File Name:' ||V_PATH_LOCATION||'/'||v_file_name);
  fnd_file.put_line(fnd_file.output,'Review Date:' ||V_REVIEW_DATE);
  fnd_file.put_line(fnd_file.output,'Next Review Date:' ||V_NEXT_PERF_REVIEW_DATE);
 
 
  V_FILE_TYPE := UTL_FILE.FOPEN (v_path_location, v_file_name, 'R');
 
  if V_NEXT_PERF_REVIEW_DATE = null
  then
  L_NEXT_REVIEW_DATE := null;
  else
  L_NEXT_REVIEW_DATE := TO_DATE(V_NEXT_PERF_REVIEW_DATE,'dd-mon-yyyy');
  end if;
 
  IF UTL_FILE.IS_OPEN(V_FILE_TYPE) THEN
    LOOP
--    fnd_file.put_line(fnd_file.output,'start/repeat :- '||V_REC_COUNT);
        BEGIN
          
            begin
                UTL_FILE.GET_LINE(V_FILE_TYPE, V_LINE);
                V_TOT_count := V_TOT_count +1;
                Exception when no_data_found THEN
                    fnd_file.put_line(fnd_file.log,'No More Reccords ' ||V_REC_COUNT);
                    UTL_FILE.FCLOSE(V_FILE_TYPE);
                    EXIT;
            END;          
            if V_REC_COUNT = 0  then
                fnd_file.put_line(fnd_file.log,'skipiping the first line to read values '||V_LINE);
                V_REC_COUNT := V_REC_COUNT+1;
              
            else
              
                V_REC_COUNT := V_REC_COUNT+1;
              
            --    fnd_file.put_line(fnd_file.output,'Rec :- '||V_LINE);

                v_employee_number := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
                --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_employee_number : '|| V_EMPLOYEE_NUMBER);

                v_business_group := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
                --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_business_group : '||V_BUSINESS_GROUP);

                v_last_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
                --fnd_file.put_line(fnd_file.output,'v_last_name : '||v_last_name);

                v_first_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
                --fnd_file.put_line(fnd_file.output,'v_first_name : '||v_first_name);
              
                --V_PERF_RATING_NAME := nvl(trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5)),trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4)));
                V_PERF_RATING_NAME := trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5));
              
  
                --fnd_file.put_line(fnd_file.output,'V_PERF_RATING_NAME : '||V_PERF_RATING_NAME);
              
                select papf.PERSON_ID , DECODE(V_PERF_RATING_NAME ,'1 - Lowest' , 'XX_1',
                                              '2' , 'XX_2',
                                               '3',  'XX_3',
                                              '4' , 'XX_4'
                                               ,'5 - Highest' ,'XX_5')
                into V_PERSON_ID, V_PERF_RATING_CODE
                from PER_ALL_PEOPLE_F papf,  per_business_groups pbg
                where EMPLOYEE_NUMBER = V_EMPLOYEE_NUMBER
                and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
                AND papf.business_group_id=pbg.business_group_id
                and pbg.name=v_business_group;
          
                --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_person_id :'||V_PERSON_ID||', V_PERF_RATING_CODE :'||V_PERF_RATING_CODE);  
                          
                HR_PERF_REVIEW_API.CREATE_PERF_REVIEW( P_VALIDATE                     => V_VALIDATE
                                                      ,P_PERFORMANCE_REVIEW_ID        => V_PERFORMANCE_REVIEW_ID
                                                      ,P_PERSON_ID                    => V_PERSON_ID
                                                      ,P_EVENT_ID                     => null
                                                      ,P_REVIEW_DATE                  => TO_DATE(V_REVIEW_DATE,'dd-mon-yyyy')
                                                      ,P_PERFORMANCE_RATING           => V_PERF_RATING_CODE
                                                      ,P_NEXT_PERF_REVIEW_DATE        => L_NEXT_REVIEW_DATE
                                                      ,P_OBJECT_VERSION_NUMBER        => V_OBJECT_VERSION_NUMBER
                                                      ,P_NEXT_REVIEW_DATE_WARNING     => V_NEXT_REVIEW_DATE_WARNING
                                                      );
      
              
        
            --    FND_FILE.PUT_LINE(FND_FILE.log,V_LINE);
               --rollback;
                v_SUC_count := v_SUC_count +1;                
                 --COMMIT;
            end if;
      EXCEPTION when OTHERS then
        --fnd_file.put_line(fnd_file.log,'V_PERF_RATING_NAME'||V_PERF_RATING_NAME);
        --fnd_file.put_line(fnd_file.log,V_LINE);
        fnd_file.put_line(fnd_file.log,'Failed - '||V_EMPLOYEE_NUMBER||' with Error :'||sqlerrm);
        --EXIT;
        --continue;
        V_FAIL_Count := V_FAIL_Count +1;
      end ;
    
    END LOOP;
 end if;
--rollback;
 commit;
 V_TOT_count:=V_TOT_count-1;
          fnd_file.put_line(fnd_file.output,'Total No of records in file : '||V_TOT_count);
        fnd_file.put_line(fnd_file.output,'Failed records count: '||V_FAIL_Count);
        fnd_file.put_line(fnd_file.output,'Successful records count: '||V_Suc_Count);

EXCEPTION when OTHERS then
        FND_FILE.PUT_LINE(FND_FILE.log,'Other errors :'||SQLERRM);
        UTL_FILE.FCLOSE(V_FILE_TYPE);
END XX_PERF_REVIEW_LOAD_PROC;
end XX_HR_PERFORMANCE_PKG;