SELECT DISTINCT PER.ROWID ROW_ID , PER.PERSON_ID , NULL SALARY , NULL PAY_BASIS , JBT.NAME JOB_TITLE , PER.FULL_NAME , PAA.ASSIGNMENT_ACTION_ID , ASF.ASSIGNMENT_ID , ASF.ASSIGNMENT_NUMBER , ASF.LOCATION_ID , ASF.INTERNAL_ADDRESS_LINE , PER.NATIONAL_IDENTIFIER NI_NUMBER , PER.EXPENSE_CHECK_SEND_TO_ADDRESS FROM PER_JOBS_TL JBT , PER_ALL_ASSIGNMENTS_F ASF , PER_PEOPLE_F PER , PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA WHERE JBT.JOB_ID(+) = ASF.JOB_ID AND JBT.LANGUAGE (+) = USERENV('LANG') AND PER.PERSON_ID = ASF.PERSON_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('R' , 'Q' , 'P' , 'U' , 'V') AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND NOT EXISTS ( SELECT NULL FROM PAY_ELEMENT_ENTRY_VALUES_F EEV , PAY_ELEMENT_ENTRIES_F EE , PER_PAY_BASES PPB /* EXCLUDE ASSIGNMENTS WITH SALARY ADMIN - PICKED IN UNION */ WHERE NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN EEV.EFFECTIVE_START_DATE AND EEV.EFFECTIVE_END_DATE AND PPB.PAY_BASIS_ID = ASF.PAY_BASIS_ID AND EEV.INPUT_VALUE_ID+0 = PPB.INPUT_VALUE_ID AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN EE.EFFECTIVE_START_DATE AND EE.EFFECTIVE_END_DATE AND EE.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND EE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID ) UNION SELECT DISTINCT PER.ROWID ROW_ID , PER.PERSON_ID , EEV.SCREEN_ENTRY_VALUE SALARY , INITCAP(PPB.PAY_BASIS) PAY_BASIS , JBT.NAME JOB_TITLE , PER.FULL_NAME , PAI.LOCKING_ACTION_ID , ASF.ASSIGNMENT_ID , ASF.ASSIGNMENT_NUMBER , ASF.LOCATION_ID , ASF.INTERNAL_ADDRESS_LINE , PER.NATIONAL_IDENTIFIER NI_NUMBER , PER.EXPENSE_CHECK_SEND_TO_ADDRESS FROM PER_JOBS_TL JBT , PER_ALL_ASSIGNMENTS_F ASF , PER_PEOPLE_F PER , PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA , PER_PAY_BASES PPB , PAY_ACTION_INTERLOCKS PAI , PAY_PAYROLL_ACTIONS PPA1 , PAY_ASSIGNMENT_ACTIONS PAA1 , PAY_ELEMENT_ENTRY_VALUES_F EEV , PAY_ELEMENT_ENTRIES_F EE WHERE JBT.JOB_ID(+) = ASF.JOB_ID AND JBT.LANGUAGE (+) = USERENV('LANG') AND PER.PERSON_ID = ASF.PERSON_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('R' , 'Q' , 'V') AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND PPB.PAY_BASIS_ID = ASF.PAY_BASIS_ID AND PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA1.ASSIGNMENT_ACTION_ID = PAI.LOCKING_ACTION_ID AND PAA1.PAYROLL_ACTION_ID = PPA1.PAYROLL_ACTION_ID AND PPA1.ACTION_TYPE IN ('P' , 'U') AND EE.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND EE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID AND EEV.INPUT_VALUE_ID+0 = PPB.INPUT_VALUE_ID AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN EEV.EFFECTIVE_START_DATE AND EEV.EFFECTIVE_END_DATE AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN EE.EFFECTIVE_START_DATE AND EE.EFFECTIVE_END_DATE UNION SELECT DISTINCT PER.ROWID ROW_ID , PER.PERSON_ID , EEV.SCREEN_ENTRY_VALUE SALARY , INITCAP(PPB.PAY_BASIS) PAY_BASIS , JBT.NAME JOB_TITLE , PER.FULL_NAME , PAA.ASSIGNMENT_ACTION_ID , ASF.ASSIGNMENT_ID , ASF.ASSIGNMENT_NUMBER , ASF.LOCATION_ID , ASF.INTERNAL_ADDRESS_LINE , PER.NATIONAL_IDENTIFIER NI_NUMBER , PER.EXPENSE_CHECK_SEND_TO_ADDRESS FROM PER_JOBS_TL JBT , PER_ALL_ASSIGNMENTS_F ASF , PER_PEOPLE_F PER , PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA , PER_PAY_BASES PPB , PAY_ELEMENT_ENTRY_VALUES_F EEV , PAY_ELEMENT_ENTRIES_F EE WHERE JBT.JOB_ID(+) = ASF.JOB_ID AND JBT.LANGUAGE (+) = USERENV('LANG') AND PER.PERSON_ID = ASF.PERSON_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('R' , 'Q' , 'V') AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND PPB.PAY_BASIS_ID = ASF.PAY_BASIS_ID AND EEV.INPUT_VALUE_ID+0 = PPB.INPUT_VALUE_ID AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN EEV.EFFECTIVE_START_DATE AND EEV.EFFECTIVE_END_DATE AND NVL(PPA.DATE_EARNED , PPA.EFFECTIVE_DATE) BETWEEN EE.EFFECTIVE_START_DATE AND EE.EFFECTIVE_END_DATE AND EE.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND EE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID