Scribd
Upload
96 views1 page

Competency Rating

The document contains a SQL query that retrieves performance evaluation data, including employee numbers, competency names, numeric ratings, and rating descriptions. It joins multiple tables related to evaluations, sections, ratings, participants, roles, and content items, applying various filters based on evaluation criteria and effective dates. The query is specifically designed to extract data for a particular evaluation template and manager role type.

Uploaded by

Marwan S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
96 views1 page

Competency Rating

The document contains a SQL query that retrieves performance evaluation data, including employee numbers, competency names, numeric ratings, and rating descriptions. It joins multiple tables related to evaluations, sections, ratings, participants, roles, and content items, applying various filters based on evaluation criteria and effective dates. The query is specifically designed to extract data for a particular evaluation template and manager role type.

Uploaded by

Marwan S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

SELECT PP.PERSON_NUMBER,b.

Name Competency_Name,
(SELECT RT_LVL.NUMERIC_RATING FROM HRT_RATING_LEVELS_VL RT_LVL WHERE
RT_LVL.BUSINESS_GROUP_ID=MgrEvalRatingEO.BUSINESS_GROUP_ID AND
RT_LVL.RATING_LEVEL_ID=MgrEvalRatingEO.PERFORMANCE_RATING_ID AND
( ( EvalSectionEO.USE_SECRTG_FOR_PERFRTG_FLAG='Y' AND
EvalSectionEO.SECTION_RATING_MODEL_ID=RT_LVL.RATING_MODEL_ID AND
RT_LVL.BUSINESS_GROUP_ID=EvalSectionEO.BUSINESS_GROUP_ID) OR
( RT_LVL.BUSINESS_GROUP_ID=MgrEvalItemVO.BUSINESS_GROUP_ID AND
MgrEvalItemVO.PERF_RATING_MODEL_ID =RT_LVL.RATING_MODEL_ID)) ) Numeric_Rating,
(SELECT RT_LVL.RATING_DESCRIPTION FROM HRT_RATING_LEVELS_VL RT_LVL WHERE
RT_LVL.BUSINESS_GROUP_ID=MgrEvalRatingEO.BUSINESS_GROUP_ID AND
RT_LVL.RATING_LEVEL_ID=MgrEvalRatingEO.PERFORMANCE_RATING_ID AND
( ( EvalSectionEO.USE_SECRTG_FOR_PERFRTG_FLAG='Y' AND
EvalSectionEO.SECTION_RATING_MODEL_ID=RT_LVL.RATING_MODEL_ID AND
RT_LVL.BUSINESS_GROUP_ID=EvalSectionEO.BUSINESS_GROUP_ID) OR
( RT_LVL.BUSINESS_GROUP_ID=MgrEvalItemVO.BUSINESS_GROUP_ID AND
MgrEvalItemVO.PERF_RATING_MODEL_ID =RT_LVL.RATING_MODEL_ID)) ) Rating_Desc
FROM HRA_EVALUATIONS EvaluationEO,
HRA_EVAL_SECTIONS EvalSectionEO,
HRA_EVAL_RATINGS MgrEvalRatingEO,
HRA_EVAL_ITEMS MgrEvalItemVO,
HRA_EVAL_PARTICIPANTS MgrEvalParticipantEO,
HRA_EVAL_ROLES MgrEvalRoleEO,
HRA_EVAL_ROLE_ACTIONS MgrEvalRoleActionEO,
PER_ALL_PEOPLE_F PP,
HRA_TMPL_DEFNS_TL TMPL,
HRT_CONTENT_ITEMS_VL b
WHERE EvaluationEO.EVALUATION_ID=EvalSectionEO.EVALUATION_ID AND
EvalSectionEO.SECTION_TYPE_CODE ='REG' AND
EvalSectionEO.EVAL_SECTION_ID=MgrEvalItemVO.EVAL_SECTION_ID AND
EvaluationEO.EVALUATION_ID=MgrEvalParticipantEO.EVALUATION_ID
AND
EvaluationEO.MANAGER_ID=MgrEvalParticipantEO.PERSON_ID AND
MgrEvalItemVO.EVAL_ITEM_ID=MgrEvalRatingEO.REFERENCE_ID(+) AND
MgrEvalRatingEO.REFERENCE_TYPE(+)='ITEM' AND

MgrEvalRatingEO.EVAL_PARTICIPANT_ID=MgrEvalParticipantEO.EVAL_PARTICIPANT_ID(+) AND
EvaluationEO.EVALUATION_ID=MgrEvalRoleEO.EVALUATION_ID AND
EvaluationEO.EVALUATION_ID=MgrEvalRoleActionEO.EVALUATION_ID
AND
MgrEvalRoleEO.EVAL_ROLE_ID=MgrEvalRoleActionEO.EVAL_ROLE_ID AND
MgrEvalRoleEO.EVAL_ROLE_ID=MgrEvalParticipantEO.EVAL_ROLE_ID
AND

EvalSectionEO.EVAL_SECTION_ID=mgrEvalRoleActionEO.EVAL_SECTION_ID
AND EvaluationEO.WORKER_ID = PP.PERSON_ID
AND UPPER(MgrEvalRatingEO.ROLE_TYPE_CODE) = 'MANAGER'
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND
PP.EFFECTIVE_END_DATE
--AND PP.PERSON_NUMBER = '10655'
AND TMPL.LANGUAGE = 'US'
AND EvaluationEO.TEMPLATE_DEFN_ID = TMPL.TEMPLATE_DEFN_ID
AND TMPL.TEMPLATE_DEFN_ID = 300000133930770
AND MgrEvalItemVO.REFERENCE_ITEM_ID = b.CONTENT_ITEM_ID

You might also like