VIEW: APPS.PER_PEOPLE_V4

Source

View Text - Preformatted

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, /* EXCLUDE ASSIGNMENTS WITH SALARY ADMIN - PICKED IN UNION */ PAY_ELEMENT_ENTRIES_F EE, PER_PAY_BASES PPB 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, 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, PAY_ELEMENT_ENTRY_VALUES_F EEV, PAY_ELEMENT_ENTRIES_F EE, PER_PAY_BASES PPB 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 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

View Text - HTML Formatted

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
, /* EXCLUDE ASSIGNMENTS WITH SALARY ADMIN - PICKED IN UNION */ PAY_ELEMENT_ENTRIES_F EE
, PER_PAY_BASES PPB
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
, 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
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_ELEMENT_ENTRIES_F EE
, PER_PAY_BASES PPB
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 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