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;

Oracle HRMS API – Update Employee Assignment

/*
To Update: Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Gre, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id

API -- hr_assignment_api.update_emp_asg

To Update: Grade, Location, Job, Payroll, Organization, Employee Category, People Group

API -- hr_assignment_api.update_emp_asg_criteria
*/

--Example --

DECLARE
   -- Local Variables
   -- -----------------------
   LC_DT_UD_MODE          VARCHAR2(100)     := NULL;
   LN_ASSIGNMENT_ID       NUMBER            := 9375;
   LN_SUPERVISOR_ID       NUMBER            := NULL;
   LN_OBJECT_NUMBER       NUMBER            := 1;
   ln_people_group_id  NUMBER               := 1;

   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                           BOOLEAN;
   lb_update                               BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert   BOOLEAN;
 
   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id      HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   LN_COMMENT_ID                  PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning         BOOLEAN;

 -- Out Variables for Update Employee Assgment Criteria
 -- -------------------------------------------------------------------------------
 ln_special_ceiling_step_id           PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
 lc_group_name                         VARCHAR2(30);
 ld_effective_start_date               PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
 LD_EFFECTIVE_END_DATE                 PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
 lb_org_now_no_manager_warning           BOOLEAN;
 lb_other_manager_warning                BOOLEAN;
 lb_spp_delete_warning                   BOOLEAN;
 lc_entries_changed_warning              VARCHAR2(30);
 lb_tax_district_changed_warn            BOOLEAN;



BEGIN
   -- Find Date Track Mode
   -- --------------------------------
   dt_api.find_dt_upd_modes
   (    p_effective_date                  => TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value            => ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert
     );


   IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
   THEN
       -- UPDATE_OVERRIDE
       -- ---------------------------------
       lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;



  IF ( lb_correction = TRUE )
  THEN
      -- CORRECTION
      -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
  END IF;



  IF ( lb_update = TRUE )
  THEN
      -- UPDATE
      -- --------------
      lc_dt_ud_mode := 'UPDATE';
   END IF;



  -- Update Employee Assignment
  -- ---------------------------------------------
 hr_assignment_api.update_emp_asg
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                              => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode         => lc_dt_ud_mode,
  p_assignment_id                            => ln_assignment_id,
  p_supervisor_id                              => NULL,
  p_change_reason                           => NULL,
  p_manager_flag                              => 'N',
  p_bargaining_unit_code              => NULL,
  p_labour_union_member_flag   => NULL,
  p_segment1                                       => 204,
  p_segment3                                       => 'N',
  p_normal_hours                              => 10,
  p_frequency                                       => 'W',
  -- Output data elements
  -- -------------------------------
  p_object_version_number             => ln_object_number,
  p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
  p_concatenated_segments             => lc_concatenated_segments,
  p_comment_id                                   => ln_comment_id,
  p_effective_start_date                      => ld_effective_start_date,
  p_effective_end_date                        => ld_effective_end_date,
  p_no_managers_warning               => lb_no_managers_warning,
  p_other_manager_warning            => lb_other_manager_warning
 );

 -- Find Date Track Mode for Second API
 -- ------------------------------------------------------
  dt_api.find_dt_upd_modes
  (  p_effective_date                   => TO_DATE('12-JUN-2011'),
     p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
     p_base_key_column           => 'ASSIGNMENT_ID',
     p_base_key_value               => ln_assignment_id,
     -- Output data elements
     -- -------------------------------
     p_correction                           => lb_correction,
     p_update                                 => lb_update,
     p_update_override               => lb_update_override,
     p_update_change_insert    => lb_update_change_insert
  );



  IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
  THEN
    -- UPDATE_OVERRIDE
    -- --------------------------------
    lc_dt_ud_mode := 'UPDATE_OVERRIDE';
  END IF;



   IF ( lb_correction = TRUE )
   THEN
     -- CORRECTION
     -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
  END IF;



   IF ( lb_update = TRUE )
   THEN
     -- UPDATE
     -- --------------
     lc_dt_ud_mode := 'UPDATE';
   END IF;



 -- Update Employee Assgment Criteria
 -- -----------------------------------------------------
 hr_assignment_api.update_emp_asg_criteria
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                                   => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode               => lc_dt_ud_mode,
  p_assignment_id                                 => ln_assignment_id,
  p_location_id                                        => 204,
  p_grade_id                                             => 29,
  p_job_id                                                  => 16,
  p_payroll_id                                          => 52,
  p_organization_id                               => 239,
  p_employment_category                    => 'FR',
  -- Output data elements
  -- -------------------------------
  p_people_group_id                              => ln_people_group_id,
  p_object_version_number                   => ln_object_number,
  p_special_ceiling_step_id                  => ln_special_ceiling_step_id,
  p_group_name                                        => lc_group_name,
  p_effective_start_date                           => ld_effective_start_date,
  p_effective_end_date                             => ld_effective_end_date,
  p_org_now_no_manager_warning  => lb_org_now_no_manager_warning,
  p_other_manager_warning                 => lb_other_manager_warning,
  p_spp_delete_warning                         => lb_spp_delete_warning,
  p_entries_changed_warning              => lc_entries_changed_warning,
  p_tax_district_changed_warning     => lb_tax_district_changed_warn
 );

 COMMIT;

EXCEPTION
         WHEN OTHERS THEN
                      ROLLBACK;
                      dbms_output.put_line(SQLERRM);
END;
/