To fetch the payroll run result value for the particular element. ( Appreciation Bonus Letterv2)

/* Name: Appreciation Bonus Letterv2 Creation DATE: 30-06-2020

created by : Partha */

SELECT 

rownum,

PAP.PAYROLL_NAME,

PAPF.PERSON_NUMBER,

PPNF.FULL_NAME,

ELE.ELEMENT_NAME,

PEC.BASE_CLASSIFICATION_NAME,

PIV.NAME INPUT_VALUE_NAME,

--PPA.DATE_EARNED,

TO_CHAR(PPA.DATE_EARNED,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "DATE EARNED",

TO_CHAR(PPA.EFFECTIVE_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "EFFECTIVE_DATE",

PTP.PERIOD_NAME ,

PRRV.RESULT_VALUE,

PPA.PAYROLL_ACTION_ID

---------------------------------------------FROM----------------------------------

FROM 

PAY_ELEMENT_TYPES_VL ELE,

PAY_RUN_RESULTS PRR, 

PAY_RUN_RESULT_VALUES PRRV,

PAY_INPUT_VALUES_VL PIV,

PER_PERSON_NAMES_F PPNF,

PER_PERSON_SECURED_LIST_V   PAPF,

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 STATEMENT--------------------------------------

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 = PPNF.PERSON_ID

AND PAPF.PERSON_ID = PPNF.PERSON_ID

--AND PPNF.NAME_TYPE= 'GLOBAL'

--AND PPNF.CHAR_SET_CONTEXT = 'AR'

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

AND PPA.EFFECTIVE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE

AND  PPA.EFFECTIVE_DATE  BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND  PPA.EFFECTIVE_DATE  BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND PTP.STATUS='O'

AND  PPA.EFFECTIVE_DATE BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE

AND  PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE

AND  PPA.EFFECTIVE_DATE BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

AND PIV.NAME= 'Pay Value'

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PIV.UOM = 'M'

--AND PJL.LANGUAGE = 'US'

--AND  PPRA.ACTION_STATUS = 'C'

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

AND PPRA.RETRO_COMPONENT_ID IS NULL

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

AND  PPA.EFFECTIVE_DATE  BETWEEN :START_DATE AND :END_DATE

AND PAPF.PERSON_NUMBER  = nvl(:P_PERSON_NUMBER,PAPF.PERSON_NUMBER)

AND ELE.ELEMENT_NAME = :ELEMENT_NAME

ORDER BY ROWNUM DESC