To fetch Manager rating of performance document and the run result value for the particular document. (Annual bonus letter)

 /* Name : Annual leave Bonus v3 DATE: 23-03-2020

CREATED BY : PARTHA

This report is to get the employee's performance rating and their annual bonus amount (annual bonus =element)

This report won't return the result if the run result doesn't exist. Commented 2019 hardcoded at the review period.

*/

SELECT

PAPF.PERSON_NUMBER,

PPF1.FULL_NAME AS "US NAME",

PPF2.FULL_NAME AS "AR NAME",

HVL2.CUSTOMARY_NAME AS "DOCUMENT NAME",

HVL2.DOCUMENT_TYPE_NAME "DOCUMENT TYPE",

HRTL.REVIEW_PERIOD_NAME,

ROUND(HVL2.CALCULATED_OVERALL_RATING)AS "CALCULATED_Manager_Rating",

-----------------------------------------MANAGER_OVER_ALL_RATING------------------------------------------------

(SELECT HRL.RATING_LEVEL_CODE MGR_RATING

FROM hra_eval_ratings HER, HRT_RATING_LEVELS_B HRL, hra_eval_sections HES,hra_evaluations HEVAL

WHERE 1=1

AND HES.EVALUATION_ID = HEVAL.EVALUATION_ID

AND HES.section_type_code = 'OS'

AND HER.evaluation_id = HES.EVALUATION_ID

and HER.role_type_code = 'MANAGER'

and HER.reference_id = HES.eval_section_id

AND HRL.rating_level_id=HER.PERFORMANCE_RATING_ID

AND HEVAL.TMPL_PERIOD_ID = HTPV.TMPL_PERIOD_ID

AND HEVAL.TMPL_PERIOD_ID = HVL2.TMPL_PERIOD_ID

AND HEVAL.ASSIGNMENT_ID=PAAM.ASSIGNMENT_ID

AND HEVAL.ASSIGNMENT_ID = HVL2.ASSIGNMENT_ID) AS "Manager Overall Rating",

----------------------------------------------Annual Bonus Amount-------------------------------------------

(SELECT

PRRV.RESULT_VALUE AS "Result"

FROM 

PAY_ELEMENT_TYPES_VL ELE,

PAY_RUN_RESULTS PRR, 

PAY_RUN_RESULT_VALUES PRRV,

PAY_INPUT_VALUES_VL PIV,

PAY_PAYROLL_REL_ACTIONS PPRA,

PAY_PAY_RELATIONSHIPS_DN PREL,

PAY_PAYROLL_ACTIONS PPA,

PAY_ALL_PAYROLLS_F PAP,

PAY_TIME_PERIODS PTP,

PAY_ELE_CLASSIFICATIONS PEC 

WHERE 

PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID

AND PPA.PAYROLL_ID = PAP.PAYROLL_ID

AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID

AND PPRA.RUN_TYPE_ID IS NOT NULL

AND PRR.ELEMENT_TYPE_ID = ELE.ELEMENT_TYPE_ID

AND PRRV.RUN_RESULT_ID(+) = PRR.RUN_RESULT_ID

AND PIV.INPUT_VALUE_ID(+) = PRRV.INPUT_VALUE_ID

AND PREL.PAYROLL_RELATIONSHIP_ID = PPRA.PAYROLL_RELATIONSHIP_ID

AND PREL.PERSON_ID = PPF1.PERSON_ID

AND PREL.PERSON_ID = PPF2.PERSON_ID

AND PAPF.PERSON_ID = PPF1.PERSON_ID

AND PAPF.PERSON_ID = PPF2.PERSON_ID

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

AND PTP.STATUS='O'

AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE

AND PTP.STATUS='O'

AND  PPA.DATE_EARNED BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

AND PAP.PAYROLL_NAME IN (:PAYROLL)

AND PPA.EFFECTIVE_DATE IN (:Payroll_Process_Date)

AND  ELE.ELEMENT_NAME IN (:ELEMENT_NAME)

AND PIV.NAME= 'Pay Value' 

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PPRA.ACTION_STATUS = 'C'

AND  PPA.ACTION_STATUS IN ('C', 'E')

AND PPRA.RETRO_COMPONENT_ID IS NULL) as "Annual Bonus",

------------------------------------------------DESCRIPTION----------------------------------------------------

/*

CASE WHEN (HVL2.OVERALL_RATING IS NULL) 

THEN  

(SELECT 

HRVL.rating_description 

FROM 

HRT_RATING_LEVELS_VL HRVL,

HRA_OVERALL_RATINGS_VL HVL

WHERE 

HVL.assignment_id=PAAM.ASSIGNMENT_ID

AND HVL.ROLE_TYPE_CODE='MANAGER'

AND HVL.TMPL_PERIOD_ID=HTPV.TMPL_PERIOD_ID

AND HRVL.RATING_MODEL_ID='300000068287301'

AND ROUND(HVL.CALCULATED_OVERALL_RATING,0)=HRVL.NUMERIC_RATING)

ELSE

(SELECT HRLL.RATING_DESCRIPTION FROM HRT_RATING_LEVELS_VL HRLL, hra_eval_ratings HER, HRT_RATING_LEVELS_B HRL, hra_eval_sections HES,hra_evaluations HEVAL

WHERE HRLL.rating_level_id = HRL.rating_level_id

AND HES.EVALUATION_ID = HEVAL.EVALUATION_ID

AND HES.section_type_code = 'OS'

AND HER.evaluation_id = HES.EVALUATION_ID

and HER.role_type_code = 'MANAGER'

and HER.reference_id = HES.eval_section_id

AND HRL.rating_level_id=HER.PERFORMANCE_RATING_ID

AND HEVAL.TMPL_PERIOD_ID = HTPV.TMPL_PERIOD_ID

AND HEVAL.TMPL_PERIOD_ID = HVL2.TMPL_PERIOD_ID

AND HEVAL.ASSIGNMENT_ID=PAAM.ASSIGNMENT_ID

AND HEVAL.ASSIGNMENT_ID = HVL2.ASSIGNMENT_ID)

END AS "DESCRIPTION"

*/

-----------------------------------------------MAIN FROM------------------------------------------------------

FROM 

PER_ALL_PEOPLE_F PAPF,

PER_PERSON_NAMES_F PPF1,

PER_PERSON_NAMES_F PPF2,

PER_ALL_ASSIGNMENTS_M PAAM,

HRA_OVERALL_RATINGS_VL HVL2,

HRT_REVIEW_PERIODS_TL HRTL,

HRA_TMPL_PERIODS_VL HTPV

-------------------------------------------------MAIN WHERE-----------------------------------------------------

WHERE 

PAPF.PERSON_ID = PPF1.PERSON_ID

AND PAPF.PERSON_ID = PPF2.PERSON_ID

AND PAPF.PERSON_ID = PAAM.PERSON_ID

AND PPF1.PERSON_ID = PAAM.PERSON_ID

AND PPF2.PERSON_ID = PAAM.PERSON_ID

AND PPF1.PERSON_ID = PPF2.PERSON_ID

AND HVL2.WORKER_ID = PAPF.PERSON_ID

AND HVL2.WORKER_ID = PPF1.PERSON_ID

AND HVL2.WORKER_ID = PPF2.PERSON_ID

AND HVL2.WORKER_ID = PAAM.PERSON_ID

AND HVL2.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID

AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE AND PPF1.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE AND PPF2.EFFECTIVE_END_DATE

AND HRTL.REVIEW_PERIOD_ID = HTPV.REVIEW_PERIOD_ID

AND HVL2.TMPL_PERIOD_ID = HTPV.TMPL_PERIOD_ID

AND PPF1.CHAR_SET_CONTEXT = 'US'

AND PPF2.CHAR_SET_CONTEXT = 'AR'

AND HVL2.ROLE_TYPE_CODE = 'MANAGER'

AND PAAM.ASSIGNMENT_TYPE = 'E'

AND HRTL.LANGUAGE = 'US'

AND (SELECT

PRRV.RESULT_VALUE AS "Result"

FROM 

PAY_ELEMENT_TYPES_VL ELE,

PAY_RUN_RESULTS PRR, 

PAY_RUN_RESULT_VALUES PRRV,

PAY_INPUT_VALUES_VL PIV,

PAY_PAYROLL_REL_ACTIONS PPRA,

PAY_PAY_RELATIONSHIPS_DN PREL,

PAY_PAYROLL_ACTIONS PPA,

PAY_ALL_PAYROLLS_F PAP,

PAY_TIME_PERIODS PTP,

PAY_ELE_CLASSIFICATIONS PEC 

WHERE 

PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID

AND PPA.PAYROLL_ID = PAP.PAYROLL_ID

AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID

AND PPRA.RUN_TYPE_ID IS NOT NULL

AND PRR.ELEMENT_TYPE_ID = ELE.ELEMENT_TYPE_ID

AND PRRV.RUN_RESULT_ID(+) = PRR.RUN_RESULT_ID

AND PIV.INPUT_VALUE_ID(+) = PRRV.INPUT_VALUE_ID

AND PREL.PAYROLL_RELATIONSHIP_ID = PPRA.PAYROLL_RELATIONSHIP_ID

AND PREL.PERSON_ID = PPF1.PERSON_ID

AND PREL.PERSON_ID = PPF2.PERSON_ID

AND PAPF.PERSON_ID = PPF1.PERSON_ID

AND PAPF.PERSON_ID = PPF2.PERSON_ID

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

AND PTP.STATUS='O'

AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE

AND PTP.STATUS='O'

AND  PPA.DATE_EARNED BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

AND PAP.PAYROLL_NAME IN (:PAYROLL)

AND PPA.EFFECTIVE_DATE IN (:Payroll_Process_Date)

AND  ELE.ELEMENT_NAME IN (:ELEMENT_NAME)

AND PIV.NAME= 'Pay Value' 

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PPRA.ACTION_STATUS = 'C'

AND  PPA.ACTION_STATUS IN ('C', 'E')

AND PPRA.RETRO_COMPONENT_ID IS NULL) IS NOT NULL

------------------------------------------------PARAMETER-----------------------------------------------

AND PAPF.PERSON_NUMBER = NVL(:PERSON_NUMBER,PAPF.PERSON_NUMBER)

AND HRTL.REVIEW_PERIOD_NAME IN (:REVIEW_PERIOD)

AND HVL2.DOCUMENT_TYPE_NAME IN (:Reference_Rating_Document_Type)