SELECT /*+ ORDERED INDEX(SES FND_SESSIONS_U1) INDEX(PET PAY_ELEMENT_TYPES_F_UK2) INDEX(PRR PAY_RUN_RESULTS_N50) INDEX(PIV PAY_INPUT_VALUES_F_N50) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) */ PRR.ASSIGNMENT_ACTION_ID , MAX (DECODE (PIV.NAME , 'CONTRIBUTION CLASS' , PRRV.RESULT_VALUE , NULL)) CONTRIBUTION_CLASS , MAX (DECODE (PIV.NAME , 'SUBCLASS' , PRRV.RESULT_VALUE , NULL)) OVERRIDDEN_SUBCLASS FROM FND_SESSIONS SES , PAY_ELEMENT_TYPES_F PET , PAY_RUN_RESULTS PRR , PAY_INPUT_VALUES_F PIV , PAY_RUN_RESULT_VALUES PRRV WHERE PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID AND PET.ELEMENT_NAME = 'IE PRSI DETAIL' AND PET.LEGISLATION_CODE = 'IE' AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID AND PIV.NAME IN ('CONTRIBUTION CLASS' , 'SUBCLASS' ) AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND SES.SESSION_ID = USERENV ('SESSIONID') AND SES.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND SES.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE GROUP BY PRR.ASSIGNMENT_ACTION_ID