Hello,
I got a requirement to create a report on "performance annual review" with the organization hierarchy. I am unable to get the employee and the manager calculated rating. When I link the
calculated rating, the data is being repeated. Please help me on how to achieve this.
Query :
--Performance annual review (PER_ALL_Supervisor_f link the manager name)-------------------------------------------------------
select distinct PPF.FULL_NAME,
PAPF.PERSON_NUMBER,
HAPL.NAME as position,
PD.NAME as department,
PGF.GRADE_CODE,
HVR.CALCULATED_RATING,
--HVL.NAME as "Document name",
HDTL.NAME as "Template name",
HDVL.NAME AS "Document name",
HVL.MANAGER_NAME,
PJX.NAME as job
from PER_PERSON_NAMES_F PPF,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_DEPARTMENTS PD,
PER_GRADES_F PGF,
HRA_OVERALL_RATINGS_VL HVL,
HRA_TMPL_DEFNS_VL HDTL,
HRA_DOC_TYPES_VL HDVL,
HRA_TMPL_PERIODS_VL HTPL,
PER_JOBS_X PJX,
HRA_EVAL_RATINGS HVR,
HR_ALL_POSITIONS_F_TL HAPL
WHERE PPF.PERSON_ID = PAPF.PERSON_ID
AND PAAM.JOB_ID = PJX.JOB_ID
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PAAM.PERSON_ID = PPF.PERSON_ID
AND PAAM.POSITION_ID = HAPL.POSITION_ID
AND PAAM.GRADE_ID = PGF.GRADE_ID
AND PAAM.ORGANIZATION_ID = PD.ORGANIZATION_ID
AND HVL.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID
AND HDTL.TEMPLATE_DEFN_ID = HTPL.TEMPLATE_DEFN_ID
AND HTPL.BUSINESS_GROUP_ID = PAAM.BUSINESS_GROUP_ID
AND HVL.DOC_TYPE_ID = HDTL.DOC_TYPE_ID
AND HDVL.DOC_TYPE_ID = HDTL.DOC_TYPE_ID
AND HDVL.DOC_TYPE_ID = HVL.DOC_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPF.effective_start_date)
AND TRUNC(PPF.effective_end_date)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.effective_start_date)
AND TRUNC(PAPF.effective_end_date)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAM.effective_start_date)
AND TRUNC(PAAM.effective_end_date)
AND PPF.NAME_TYPE like 'GLOBAL'
AND HAPL.LANGUAGE ='US'
order by PERSON_NUMBER
-------------------------------------------------------------------------------------
Regards,
Sunil