0
0
Select distinct
papf.person_number,
ppnf.FULL_NAME Employee_name,
ppnfm.display_name appraiser_name,
--,(REGEXP_REPLACE(EvalRatingOS.COMMENTS),'<.+?>') as EMPLOYEE_OVERALL_COMMENTS
--,(REGEXP_REPLACE(EvalRatingMC.COMMENTS),'<.+?>') as MANAGER_OVERALL_COMMENTS
REGEXP_REPLACE (EvalRatingOS.COMMENTS, '<[^>]*>| |<span style="font-fami') as EMPLOYEE_OVERALL_COMMENTS,
REGEXP_REPLACE (EvalRatingMC.COMMENTS, '<[^>]*>| |<span style="font-fami') as MANAGER_OVERALL_COMMENTS,
HTP.CUSTOMARY_NAME,
HE.EVALUATION_ID,
to_char(HRLV2.RATING_DESCRIPTION) as Appraiser_Rating
From
per_all_people_f papf,
per_person_names_f ppnf,
per_person_names_f ppnfm,
per_all_assignments_m paam,
--,per_periods_of_service ppos
hrt_profiles_b hpbp,
--,hrt_profile_items hpip
HRA_EVALUATIONS HE,
HRA_EVAL_SECTIONS EvalSectionOS,
HRA_EVAL_RATINGS EvalRatingOS,
HRA_EVAL_PARTICIPANTS EvalParticipantWOS,
HRA_EVAL_RATINGS EvalRatingMC,
HRA_EVAL_PARTICIPANTS EvalParticipantMC,
HRA_EVAL_SECTIONS EvalSectionMC,
PER_ALL_PEOPLE_F papf1,
HRA_TMPL_PERIODS_VL HTP,
HRT_REVIEW_PERIODS_TL HRP,
HRA_OVERALL_RATINGS_VL HORV1,
HRA_OVERALL_RATINGS_VL HORV2,
HRT_RATING_LEVELS_VL HRLV1,
HRT_RATING_LEVELS_VL HRLV2,
HRA_EVALUATIONS HEM
Where
1 = 1
and papf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and papf.person_id = paam.person_id
and paam.primary_assignment_flag = 'Y'
and paam.assignment_status_type = 'ACTIVE' --and paam.assignment_type = 'E'
--and paam.ASSIGNMENT_STATUS_TYPE_ID = '1'
--and paam.period_of_service_id = ppos.period_of_service_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and paam.person_id = hpbp.person_id( + )
--and hpbp.profile_id = hpip.profile_id(+)
--and hpip.attribute_category(+) like '%PREV_EMPLOY_%'
--and hpip.CONTENT_TYPE_ID(+) ='129'
/*****Worker details******/
and he.worker_id = papf.person_id
and HE.EVALUATION_ID = EvalSectionOS.EVALUATION_ID
and EvalSectionOS.SECTION_TYPE_CODE = 'OS'
and EvalSectionOS.EVAL_SECTION_ID = EvalRatingOS.REFERENCE_ID
and EvalRatingOS.REFERENCE_TYPE = 'SECTION'
and HE.EVALUATION_ID = EvalParticipantWOS.EVALUATION_ID
and HE.WORKER_ID = EvalParticipantWOS.person_id
and EvalRatingOS.EVAL_PARTICIPANT_ID = EvalParticipantWOS.EVAL_PARTICIPANT_ID
/*****Manager details******/
and papf1.person_id = hem.manager_id
and hem.worker_id = papf.person_id
and trunc(SYSDATE) BETWEEN papf1.EFFECTIVE_START_DATE AND papf1.EFFECTIVE_END_DATE
AND HTP.TMPL_PERIOD_ID = HEm.TMPL_PERIOD_ID
AND HTP.CUSTOMARY_NAME IN ('Year End Performance 2022')
AND HTP.REVIEW_PERIOD_ID = HRP.REVIEW_PERIOD_ID
AND HORV2.EVALUATION_ID ( + ) = HEM.EVALUATION_ID
AND HORV2.ROLE_TYPE_CODE ( + ) = 'MANAGER'
AND HRLV1.RATING_LEVEL_ID ( + ) = HORV1.OVERALL_RATING
AND HRLV2.RATING_LEVEL_ID ( + ) = HORV2.OVERALL_RATING
and HEM.EVALUATION_ID = EvalSectionMC.EVALUATION_ID
and EvalSectionMC.SECTION_TYPE_CODE = 'OS'
and EvalSectionMC.EVAL_SECTION_ID = EvalRatingMC.REFERENCE_ID
and EvalRatingMC.REFERENCE_TYPE = 'SECTION'
AND HEM.EVALUATION_ID = EvalParticipantMC.EVALUATION_ID
AND HEM.manager_id = EvalParticipantMC.person_id
AND EvalRatingMC.EVAL_PARTICIPANT_ID = EvalParticipantMC.EVAL_PARTICIPANT_ID
and papf1.person_id = ppnfm.person_id
and ppnfm.name_type = 'GLOBAL'
and trunc(sysdate) between ppnfm.effective_start_date and ppnfm.effective_end_date
--and papf.person_number IN ('12345')
and papf.person_id = :Employee
order by
papf.person_number
I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.