VIEW: APPS.PA_TERM_EMPLOYEES

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)