Wednesday, February 11, 2015

Script to Push Performance Appraisal back to Manager

Use below queries to push performance appraisal back to managers

SELECT pap1.full_name appraiser,
  Pa.Appraisal_Id,
  Pap.Full_Name,
  Pa.Appraisal_System_Status,
  Pa.Appraiser_Person_Id,
  Pa.Appraisee_Person_Id,
  Pa.Appraisal_Template_Id,
  Pat.Name,
  Pa.Appraisal_Period_Start_Date,
  Pa.Appraisal_Period_End_Date
FROM Per_Appraisals Pa,
  Per_All_People_F Pap ,
  Per_Appraisal_Templates Pat,
  per_all_people_f pap1
WHERE Pa.Appraiser_Person_Id=20780
  --and appraisee_person_id=27405
AND Pap.Person_Id            =Pa.Appraisee_Person_Id
AND Pap1.Person_Id           =Pa.Appraiser_Person_Id
AND Pat.Appraisal_Template_Id=Pa.Appraisal_Template_Id
AND TRUNC(Sysdate) BETWEEN TRUNC(Pap.Effective_Start_Date) AND TRUNC(Pap.Effective_End_Date)
AND TRUNC(Sysdate) BETWEEN TRUNC(Pap1.Effective_Start_Date) AND TRUNC(Pap1.Effective_End_Date)
ORDER BY Pap.Full_Name;

UPDATE per_appraisals pa
SET system_params         = SUBSTR(system_params,1,instr(pa.system_params,'pItemKey=')-2),
  Appraisal_System_Status = 'ONGOING'
WHERE Pa.Appraisal_Id    IN (330409,330396);

No comments: