The below sample query can be used to extract the objectives , competencies for an employee in Oracle EBS PMS
Query: KRA Report (Objectives & Competencies) for an person id, if person id is not given on the parameter all the employees KRAs will be extracted.
Plan ID: Parameter can be used to extract it for a particular plans( i.e for the appraisal plan for the period)
SELECT ppmp.plan_id,
papf.person_id,
papf.employee_number,
papf.full_name,
ppmp.plan_name,
what kra_type,
a.name kra_type_name,
a.w score,
(SELECT job.name
FROM per_jobs job
WHERE job.job_id = paaf.job_id
AND SYSDATE BETWEEN job.date_from
AND NVL (job.date_to, SYSDATE))
job_name,
(SELECT grd.name
FROM per_grades grd
WHERE grd.grade_id = paaf.grade_id
AND SYSDATE BETWEEN grd.date_from
AND NVL (grd.date_to, SYSDATE))
grade,
(SELECT org.name
FROM hr_all_organization_units org
WHERE org.organization_id = paaf.organization_id
AND SYSDATE BETWEEN org.date_from
AND NVL (org.date_to, SYSDATE))
organization_unit
FROM per_all_people_f papf,
per_perf_mgmt_plans ppmp,
per_all_assignments_f paaf,
(SELECT pa1.business_group_id,
pa1.appraisee_person_id appraisee_person_id,
pa1.plan_id,
pass1.appraisal_id,
‘Competencies’ what,
pc1.name name,
pass1.total_score w
FROM per_appraisals pa1,
per_assessments pass1,
per_assessment_types pat1,
per_competence_elements pce1,
per_competences pc1
WHERE pass1.appraisal_id = pa1.appraisal_id
AND pat1.assessment_type_id = pass1.assessment_type_id
AND pat1.TYPE LIKE ‘COMPETENCE’
AND pce1.TYPE LIKE ‘ASSESSMENT’
AND pce1.assessment_id = pass1.assessment_id
AND pc1.competence_id = pce1.competence_id
AND NVL (pat1.business_group_id, pa1.business_group_id) =
pa1.business_group_id
UNION
SELECT pa.business_group_id,
pa.appraisee_person_id appraisee_person_id,
pa.plan_id,
pa.appraisal_id,
‘Objectives’ what,
obj.name name,
weighting_percent w
FROM per_appraisals pa,
per_objectives obj,
per_appraisal_templates pat
WHERE pat.appraisal_template_id = pa.appraisal_template_id
AND obj.appraisal_id = pa.appraisal_id
AND obj.business_group_id = pa.business_group_id
AND NVL (pat.business_group_id, obj.business_group_id) =
obj.business_group_id) a
WHERE papf.person_id = a.appraisee_person_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.business_group_id = a.business_group_id
AND ppmp.plan_id = a.plan_id
AND paaf.person_id = papf.person_id
AND paaf.assignment_type = ‘E’
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND ppmp.plan_id = :P_PLAN_ID
AND appraisee_person_id = NVL(:P_PERSON_ID,appraisee_person_id)
;
