Wednesday, December 31, 2014

API to Create Appraisal for Employee

Salam Alaikum,

   I share with you guys this API, that i used to create Appraisal for employees.


DECLARE
 v_appraisal_id          NUMBER;
 v_object_version_number NUMBER;
 l_template_id           NUMBER;
 l_rating_level_id       NUMBER;
 l_appraisal_id          NUMBER;
 v_date_of_hire          DATE;
 v_start_date_var        DATE;
 v_end_date_var          DATE;

 l_object_version_number NUMBER;

 CURSOR app_data
 IS
  -- note1: over_all_rating need to be mapped to get performance id
  SELECT t.*,
         k.person_id,
         k.original_date_of_hire,
         t.ROWID,
         ppos.actual_termination_date actual_termination_date
    FROM xxxhrdata.xxx_emp_appraisals_info t,
         per_all_people_f k,
         per_periods_of_service ppos
   WHERE     1 = 1
         AND NVL (t.upload_status, 'N') = 'N'
         AND t.employee_number = k.employee_number
         AND t.over_all_rating IS NOT NULL
         AND EXTRACT (YEAR FROM t.start_date) IN ('2012', '2013')
         AND ppos.person_id = k.person_id;
BEGIN
 FOR i_rec IN app_data LOOP
  FND_PROFILE.PUT ('PER_BUSINESS_GROUP_ID', i_rec.PERSON_ID);
  FND_PROFILE.PUT ('PER_BUSINESS_GROUP_ID', i_rec.MAIN_APPRAISER_EMP_NO);

  HR_UTILITY.FND_INSERT (SYSDATE);

  ---Get the appraisal template
  SELECT appraisal_template_id
    INTO l_template_id
    FROM per_appraisal_templates
   WHERE attribute20 = 'HISTORICALAPPRAISAL';

  ---Get the Rating Level
  SELECT rating_level_id
    INTO l_rating_level_id
    FROM per_rating_levels a, per_rating_scales b
   WHERE     a.rating_scale_id = b.rating_scale_id
         AND b.default_flag = 'Y'
         AND a.step_value = i_rec.OVER_ALL_RATING;

  IF i_rec.ORIGINAL_DATE_OF_HIRE > i_rec.START_DATE THEN
   v_start_date_var := i_rec.ORIGINAL_DATE_OF_HIRE;
  ELSE
   v_start_date_var := i_rec.START_DATE;
  END IF;

  IF i_rec.actual_termination_date BETWEEN i_rec.START_DATE
                                       AND i_rec.end_date THEN
   v_end_date_var := i_rec.actual_termination_date;
  ELSE
   v_end_date_var := i_rec.end_date;
  END IF;

  hr_appraisals_api.create_appraisal (
   p_effective_date               => i_rec.START_DATE,
   p_business_group_id            => fnd_profile.VALUE ('PER_BUISNESS_GROUP_ID'),
   p_appraisal_template_id        => l_template_id,
   p_appraisee_person_id          => i_rec.person_id,              -- Employee
   p_appraiser_person_id          => i_rec.MAIN_APPRAISER_EMP_NO,   -- Manager
   p_appraisal_date               => i_rec.START_DATE,
   p_appraisal_period_start_date  => i_rec.START_DATE,
   p_appraisal_period_end_date    => NVL (i_rec.actual_termination_date,
                                          i_rec.end_date),
   p_overall_performance_level_id => l_rating_level_id,
   p_appraisal_system_status      => 'COMPLETED',
   p_main_appraiser_id            => i_rec.MAIN_APPRAISER_EMP_NO,   -- Manager
   p_open                         => '',
   p_system_type                  => 'MGRSTD',
   p_system_params                => 'pSystemType=MGRSTD',
   --changes based on the setup
   --p_attribute1                   => x,
   --p_attribute2                   => y,

   -- OUT
   p_appraisal_id                 => l_appraisal_id,
   p_object_version_number        => l_object_version_number);

  UPDATE xxx_emp_appraisals_info cbd
     SET cbd.upload_status = 'Y'
   WHERE cbd.ROWID = i_rec.ROWID;
 END LOOP;
END;

Hope you found it useful guys, feel free to leave a comment if have any note on the subject.  

1 comment:

  1. Thanks you for your sharing. Really helpful.

    ReplyDelete