Annual Bonus Report (To fetch the payroll run result value of an element)

 

SELECT 

--PAP.PAYROLL_NAME,

PAPF.PERSON_NUMBER,

PPNF.FULL_NAME AS "ENGLISH NAME",

PPNF1.FULL_NAME AS "ARABIC NAME",

PRRV.RESULT_VALUE

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_NAMES_F PPNF1,

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 

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 PREL.PERSON_ID = PPNF1.PERSON_ID

AND PAPF.PERSON_ID = PPNF1.PERSON_ID

AND PPNF.PERSON_ID = PPNF1.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL'

AND PPNF.CHAR_SET_CONTEXT = 'US'

AND PPNF1.CHAR_SET_CONTEXT = 'AR'

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

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

--PTP.CUT_OFF_DATE

AND  PPA.DATE_EARNED  BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED  BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED  BETWEEN PPNF1.EFFECTIVE_START_DATE AND PPNF1.EFFECTIVE_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 PIV.NAME= 'Pay Value'

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PIV.UOM = 'M'

AND  PPRA.ACTION_STATUS = 'C'

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

AND PAP.PAYROLL_NAME IN (:PAYROLL)

AND PPA.EFFECTIVE_DATE IN (:Payroll_Process_Date)

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

AND ELE.ELEMENT_NAME IN (:ELEMENT_NAME)

AND PPRA.RETRO_COMPONENT_ID IS NULL

--AND PAPF.PERSON_NUMBER = '16265'

--AND PPA.EFFECTIVE_DATE = :P_RUN_DATE

--AND PAPF.PERSON_NUMBER  = :P_PERSON_NUMBER

--AND PTP.PERIOD_NAME =:P_PERIOD_NAME