[Home] [Help]
View: PAY_GB_PEOPLE_SOE_V
View Text
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
, PRRV.RESULT_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_ENTRIES_F EE
, PER_PAY_BASES PPB
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
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 PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = 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 EE.EFFECTIVE_START_DATE
AND EE.EFFECTIVE_END_DATE
AND EE.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
AND EE.ELEMENT_ENTRY_ID = PRR.ELEMENT_ENTRY_ID
Columns
Name |
ROW_ID |
PERSON_ID |
SALARY |
PAY_BASIS |
JOB_TITLE |
FULL_NAME |
ASSIGNMENT_ACTION_ID |
ASSIGNMENT_ID |
ASSIGNMENT_NUMBER |
LOCATION_ID |
INTERNAL_ADDRESS_LINE |
NI_NUMBER |
EXPENSE_CHECK_SEND_TO_ADDRESS |