View: PAY_P45_V1

Product:	PAY - Payroll
Description:	PAY_P45_V1 is a UK specific view based on PER_PEOPLE_F that selects employees for the P45 report.
Implementation/DBA Data:	APPS.PAY_P45_V1

View Text

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

Columns