VIEW: APPS.PAY_P45_V1

Source

View Text - Preformatted

SELECT A.BUSINESS_GROUP_ID , A.ASSIGNMENT_ID , A.PERSON_ID , SERV.ACTUAL_TERMINATION_DATE , P.LAST_NAME , P.TITLE , P.FIRST_NAME , P.NATIONAL_IDENTIFIER , SERV.LAST_STANDARD_PROCESS_DATE LAST_PROCESS_DATE , DECODE(SERV.LEAVING_REASON, 'D', 'D', NULL) DECEASED_FLAG , A.ASSIGNMENT_NUMBER , A.PAYROLL_ID , ORG.ORGANIZATION_ID , ORGTL.NAME ORG_NAME , P.EMPLOYEE_NUMBER FROM PER_PEOPLE P, PER_ASSIGNMENTS_F A, PER_PERIODS_OF_SERVICE SERV, HR_ALL_ORGANIZATION_UNITS_TL ORGTL, HR_ALL_ORGANIZATION_UNITS ORG WHERE ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID AND ORGTL.LANGUAGE = USERENV('LANG') AND ACTUAL_TERMINATION_DATE IS NOT NULl AND A.PERIOD_OF_SERVICE_ID = SERV.PERIOD_OF_SERVICE_ID AND NVL(LAST_STANDARD_PROCESS_DATE,ACTUAL_TERMINATION_DATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE AND A.PERSON_ID = P.PERSON_ID AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND NOT EXISTS (SELECT NULL FROM PER_ASSIGNMENT_EXTRA_INFO EXT WHERE EXT.ASSIGNMENT_ID = A.ASSIGNMENT_ID AND EXT.INFORMATION_TYPE = 'GB_P45' AND NVL(EXT.AEI_INFORMATION2,'N') != 'Y' AND EXT.AEI_INFORMATION1 = 'Y') UNION SELECT A.BUSINESS_GROUP_ID, A.ASSIGNMENT_ID, A.PERSON_ID, A.EFFECTIVE_END_DATE ACTUAL_TERMINATION_DATE, P.LAST_NAME, P.TITLE, P.FIRST_NAME, P.NATIONAL_IDENTIFIER, A.EFFECTIVE_END_DATE LAST_PROCESS_DATE, NULL DECEASED_FLAG, A.ASSIGNMENT_NUMBER, A.PAYROLL_ID, ORG.ORGANIZATION_ID, ORG.NAME ORG_NAME, P.EMPLOYEE_NUMBER FROM PER_PEOPLE P, PER_ASSIGNMENTS_F A, HR_ALL_ORGANIZATION_UNITS_TL ORGTL, HR_ALL_ORGANIZATION_UNITS ORG WHERE ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID AND ORGTL.LANGUAGE = USERENV('LANG') AND EXISTS ( SELECT NULL FROM PER_ASSIGNMENT_EXTRA_INFO AEI WHERE AEI.ASSIGNMENT_ID = A.ASSIGNMENT_ID AND AEI.INFORMATION_TYPE = 'GB_P45' AND AEI.AEI_INFORMATION2 = 'Y' ) AND A.EFFECTIVE_END_DATE = ( SELECT MAX(ASG2.EFFECTIVE_END_DATE) FROM PER_ASSIGNMENTS_F ASG2 WHERE ASG2.ASSIGNMENT_ID = A.ASSIGNMENT_ID AND ASG2.EFFECTIVE_END_DATE != TO_DATE('31/12/4712','DD/MM/YYYY')) AND A.PERSON_ID = P.PERSON_ID AND NOT EXISTS ( SELECT NULL FROM PER_PERIODS_OF_SERVICE POS WHERE POS.ACTUAL_TERMINATION_DATE IS NOT NULL AND POS.PERIOD_OF_SERVICE_ID = A.PERIOD_OF_SERVICE_ID ) AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID

View Text - HTML Formatted

SELECT A.BUSINESS_GROUP_ID
, A.ASSIGNMENT_ID
, A.PERSON_ID
, SERV.ACTUAL_TERMINATION_DATE
, P.LAST_NAME
, P.TITLE
, P.FIRST_NAME
, P.NATIONAL_IDENTIFIER
, SERV.LAST_STANDARD_PROCESS_DATE LAST_PROCESS_DATE
, DECODE(SERV.LEAVING_REASON
, 'D'
, 'D'
, NULL) DECEASED_FLAG
, A.ASSIGNMENT_NUMBER
, A.PAYROLL_ID
, ORG.ORGANIZATION_ID
, ORGTL.NAME ORG_NAME
, P.EMPLOYEE_NUMBER
FROM PER_PEOPLE P
, PER_ASSIGNMENTS_F A
, PER_PERIODS_OF_SERVICE SERV
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, HR_ALL_ORGANIZATION_UNITS ORG
WHERE ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID
AND ORGTL.LANGUAGE = USERENV('LANG')
AND ACTUAL_TERMINATION_DATE IS NOT NULL
AND A.PERIOD_OF_SERVICE_ID = SERV.PERIOD_OF_SERVICE_ID
AND NVL(LAST_STANDARD_PROCESS_DATE
, ACTUAL_TERMINATION_DATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND A.PERSON_ID = P.PERSON_ID
AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND NOT EXISTS (SELECT NULL
FROM PER_ASSIGNMENT_EXTRA_INFO EXT
WHERE EXT.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND EXT.INFORMATION_TYPE = 'GB_P45'
AND NVL(EXT.AEI_INFORMATION2
, 'N') != 'Y'
AND EXT.AEI_INFORMATION1 = 'Y') UNION SELECT A.BUSINESS_GROUP_ID
, A.ASSIGNMENT_ID
, A.PERSON_ID
, A.EFFECTIVE_END_DATE ACTUAL_TERMINATION_DATE
, P.LAST_NAME
, P.TITLE
, P.FIRST_NAME
, P.NATIONAL_IDENTIFIER
, A.EFFECTIVE_END_DATE LAST_PROCESS_DATE
, NULL DECEASED_FLAG
, A.ASSIGNMENT_NUMBER
, A.PAYROLL_ID
, ORG.ORGANIZATION_ID
, ORG.NAME ORG_NAME
, P.EMPLOYEE_NUMBER
FROM PER_PEOPLE P
, PER_ASSIGNMENTS_F A
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, HR_ALL_ORGANIZATION_UNITS ORG
WHERE ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID
AND ORGTL.LANGUAGE = USERENV('LANG')
AND EXISTS ( SELECT NULL
FROM PER_ASSIGNMENT_EXTRA_INFO AEI
WHERE AEI.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND AEI.INFORMATION_TYPE = 'GB_P45'
AND AEI.AEI_INFORMATION2 = 'Y' )
AND A.EFFECTIVE_END_DATE = ( SELECT MAX(ASG2.EFFECTIVE_END_DATE)
FROM PER_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND ASG2.EFFECTIVE_END_DATE != TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND A.PERSON_ID = P.PERSON_ID
AND NOT EXISTS ( SELECT NULL
FROM PER_PERIODS_OF_SERVICE POS
WHERE POS.ACTUAL_TERMINATION_DATE IS NOT NULL
AND POS.PERIOD_OF_SERVICE_ID = A.PERIOD_OF_SERVICE_ID )
AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID