Query for getting the Competences attached to an Appraisal:

SELECT
pc.name,
prl.step_value
FROM
per_appraisal_templates     pat,
per_assessments    pass,
per_competence_elements    pce,
per_competence_elements    pce1,
per_competences    pc,
per_rating_levels    prl
WHERE
pat.appraisal_template_id = <P_APPRAISAL_TEMPLATE_ID>
AND pat.assessment_type_id = pass.assessment_type_id
AND pass.appraisal_id = <P_APPRAISAL_ID>                        
AND pce.assessment_type_id = pat.assessment_type_id
AND UPPER(pce.type) = UPPER('ASSESSMENT_GROUP')
AND pce1.parent_competence_element_id = pce.competence_element_id
AND pce1.assessment_id = pass.assessment_id
AND UPPER(pce1.type) = UPPER('ASSESSMENT')
AND pc.competence_id = pce1.competence_id            
AND pce1.proficiency_level_id = prl.rating_level_id;