SELECT PAB.ROWID , PAA.ASSIGNMENT_ID ASSIGNMENT_ID , PTP.TIME_PERIOD_ID TIME_PERIOD_ID , NVL(PET.REPORTING_NAME , PET.ELEMENT_NAME) ELEMENT_REPORTING_NAME , PAB.DATE_START START_DATE1 , PAB.DATE_END END_DATE1 , DECODE(PAT.HOURS_OR_DAYS , 'H' , PAB.ABSENCE_HOURS , PAB.ABSENCE_DAYS) ABSENCE_HOURS , /*BUG3887983*/ TO_NUMBER(PRV_I.RESULT_VALUE) PAYMENT FROM PER_TIME_PERIODS PTP , PER_ALL_ASSIGNMENTS_F PAA , /* ADDED FOR BUG 4731967 , 4731967 */ PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAC , PAY_RUN_RESULTS PRR , PAY_ELEMENT_TYPES_F PET , PAY_INPUT_VALUES_F PIV , PAY_RUN_RESULTS PRR_I , PAY_RUN_RESULT_VALUES PRV_I , PAY_INPUT_VALUES_F PIV_I , PAY_INPUT_VALUES_F PIV_I3 , PER_ABSENCE_ATTENDANCE_TYPES PAT , PER_ABSENCE_ATTENDANCES PAB , PAY_ELEMENT_ENTRIES_F PEE , PAY_ELEMENT_ENTRY_VALUES_F PEV , PAY_ELEMENT_ENTRY_VALUES_F PEEV1 WHERE PTP.PAYROLL_ID = PPA.PAYROLL_ID AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID AND PAC.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PAC.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID /* ADDED FOR BUG#2665475 */ AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /* ADDED FOR BUG 4731967 */ AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /* ADDED FOR BUG 4731967 */ AND PEE.EFFECTIVE_START_DATE BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE /* ADDED FOR BUG 4731967 */ AND PEE.EFFECTIVE_END_DATE BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE /* ADDED FOR BUG 4731967 */ AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID AND PAB.ABSENCE_ATTENDANCE_ID = PEE.CREATOR_ID AND PRR.SOURCE_ID = PEE.ELEMENT_ENTRY_ID AND PEE.CREATOR_TYPE = 'A' AND PIV.INPUT_VALUE_ID = PAT.INPUT_VALUE_ID AND PAT.ABSENCE_ATTENDANCE_TYPE_ID = PAB.ABSENCE_ATTENDANCE_TYPE_ID AND NOT EXISTS (SELECT /*+UNNEST*/ NULL FROM PAY_INPUT_VALUES_F PIV_PV WHERE PIV_PV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PIV_PV.NAME = 'PAY VALUE') AND PRR_I.SOURCE_ID = PRR.SOURCE_ID AND PRR_I.SOURCE_TYPE = 'I' AND PRR_I.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PRR_I.RUN_RESULT_ID = PRV_I.RUN_RESULT_ID AND NOT EXISTS (SELECT /*+UNNEST*/ NULL FROM PAY_ELEMENT_TYPES_F PET1 WHERE ELEMENT_NAME = 'ADVANCE OUTSTANDING' AND PET1.ELEMENT_TYPE_ID = PRR_I.ELEMENT_TYPE_ID /* MDOFIED FOR BUG 4731967 */ AND PPA.EFFECTIVE_DATE BETWEEN PET1.EFFECTIVE_START_DATE AND PET1.EFFECTIVE_END_DATE) AND PIV_I.INPUT_VALUE_ID = PRV_I.INPUT_VALUE_ID AND PIV_I.NAME = 'PAY VALUE' AND NOT EXISTS /* BUG#4688702 */ ( SELECT /*+UNNEST*/ NULL FROM PAY_ELEMENT_CLASSIFICATIONS PEC , PAY_SUB_CLASSIFICATION_RULES_F PSCR WHERE PSCR.ELEMENT_TYPE_ID = PIV_I.ELEMENT_TYPE_ID AND PSCR.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID AND PEC.CLASSIFICATION_NAME = 'LEAVE LOADING' AND PPA.EFFECTIVE_DATE BETWEEN PSCR.EFFECTIVE_START_DATE AND PSCR.EFFECTIVE_END_DATE ) AND PEE.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID AND PIV.INPUT_VALUE_ID = PEV.INPUT_VALUE_ID AND PET.PROCESSING_TYPE <> 'R' AND PIV_I3.NAME(+) = 'PAY DATE' AND PIV_I3.ELEMENT_TYPE_ID(+) = PET.ELEMENT_TYPE_ID AND PEE.ELEMENT_ENTRY_ID = PEEV1.ELEMENT_ENTRY_ID AND ( ( PEEV1.INPUT_VALUE_ID = PIV_I3.INPUT_VALUE_ID AND NVL(TO_DATE(PEEV1.SCREEN_ENTRY_VALUE , 'YYYY/MM/DD HH24:MI:SS') , PTP.START_DATE) BETWEEN PTP.START_DATE AND PTP.END_DATE) OR (PIV_I3.INPUT_VALUE_ID IS NULL))