View: PA_TERM_EMPLOYEES

Product:	PA - Projects
Description:	Brief Description : View to display ex-employees ,including contingent workers. Comprehensive Description : PA_TERM_EMPLOYEES is a view that selects the employee records in PER_ALL_PEOPLE_F as of current date minus PA_TERMINATED_EMPLOYEE_DA
Implementation/DBA Data:	APPS.PA_TERM_EMPLOYEES

View Text

SELECT P.PERSON_ID
, P.FULL_NAME
, P.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, P.LAST_NAME
, P.FIRST_NAME
, P.MIDDLE_NAMES
, P.EFFECTIVE_START_DATE
, P.EFFECTIVE_END_DATE
, P.EXPENSE_CHECK_SEND_TO_ADDRESS
, P.BUSINESS_GROUP_ID
, P.VENDOR_ID
, DECODE(P.CURRENT_EMPLOYEE_FLAG
, 'Y'
, '*'
, P.CURRENT_EMPLOYEE_FLAG) "ACTIVE"
, TYP.SYSTEM_PERSON_TYPE PERSON_TYPE
FROM PER_PEOPLE_F P
, PER_PERSON_TYPES TYP
, PER_PERSON_TYPE_USAGES_F PTU
WHERE P.PERSON_ID = PTU.PERSON_ID
AND PTU.EFFECTIVE_START_DATE = (SELECT MAX(PTU2.EFFECTIVE_START_DATE)
FROM PER_PERSON_TYPE_USAGES_F PTU2
, PER_PERSON_TYPES TYP2
WHERE PTU.PERSON_ID = PTU2.PERSON_ID
AND PTU2.PERSON_TYPE_ID = TYP2.PERSON_TYPE_ID
AND TYP2.SYSTEM_PERSON_TYPE = ('EMP')
AND PTU2.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE))
AND PTU.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE)
AND PTU.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
AND TYP.SYSTEM_PERSON_TYPE = ('EMP')
AND P.EFFECTIVE_START_DATE = (SELECT MAX(PP.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = P.PERSON_ID
AND PP.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE)
AND CURRENT_NPW_FLAG IS NULL)
AND P.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE) UNION ALL SELECT P.PERSON_ID
, P.FULL_NAME
, P.NPW_NUMBER EMPLOYEE_NUMBER
, P.LAST_NAME
, P.FIRST_NAME
, P.MIDDLE_NAMES
, P.EFFECTIVE_START_DATE
, P.EFFECTIVE_END_DATE
, P.EXPENSE_CHECK_SEND_TO_ADDRESS
, P.BUSINESS_GROUP_ID
, P.VENDOR_ID
, DECODE(P.CURRENT_NPW_FLAG
, 'Y'
, '*'
, P.CURRENT_NPW_FLAG) "ACTIVE"
, TYP.SYSTEM_PERSON_TYPE PERSON_TYPE
FROM PER_PEOPLE_F P
, PER_PERSON_TYPES TYP
, PER_PERSON_TYPE_USAGES_F PTU
WHERE P.PERSON_ID = PTU.PERSON_ID
AND PTU.EFFECTIVE_START_DATE = (SELECT MAX(PTU2.EFFECTIVE_START_DATE)
FROM PER_PERSON_TYPE_USAGES_F PTU2
, PER_PERSON_TYPES TYP2
WHERE PTU.PERSON_ID = PTU2.PERSON_ID
AND PTU2.PERSON_TYPE_ID = TYP2.PERSON_TYPE_ID
AND TYP2.SYSTEM_PERSON_TYPE = ('CWK')
AND PTU2.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE))
AND PTU.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE)
AND PTU.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
AND TYP.SYSTEM_PERSON_TYPE = ('CWK')
AND P.EFFECTIVE_START_DATE = (SELECT MAX(PP.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = P.PERSON_ID
AND PP.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE)
AND CURRENT_EMPLOYEE_FLAG IS NULL)
AND P.EFFECTIVE_END_DATE BETWEEN TRUNC(SYSDATE - TO_NUMBER(NVL(FND_PROFILE.VALUE('PA_TERMINATED_EMPLOYEE_DAYS')
, 0)))
AND (SYSDATE)

Columns