View: PA_EMPLOYEES

Product:	PA - Projects
Description:	Brief Description : View to display the current PER_PEOPLE_F record for employees. Comprehensive Description : PA_EMPLOYEES is a view that selects only the employee records from the PER_PEOPLE_F table as of the current date. This view is u
Implementation/DBA Data:	APPS.PA_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 MIN(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 IN ( (DECODE('Y'
, P.CURRENT_EMPLOYEE_FLAG
, 'EMP'
, P.CURRENT_NPW_FLAG
, '-1'
, 'EX_EMP')) )
AND PTU2.EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND PTU.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND PTU.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
AND TYP.SYSTEM_PERSON_TYPE = (DECODE('Y'
, P.CURRENT_EMPLOYEE_FLAG
, 'EMP'
, P.CURRENT_NPW_FLAG
, '-1'
, 'EX_EMP'))
AND P.EFFECTIVE_START_DATE = (SELECT MIN(PP.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = P.PERSON_ID
AND PP.EFFECTIVE_END_DATE >= TRUNC(SYSDATE) )
AND P.EFFECTIVE_END_DATE >= TRUNC(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 MIN(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 IN ( (DECODE('Y'
, P.CURRENT_EMPLOYEE_FLAG
, '-1'
, P.CURRENT_NPW_FLAG
, 'CWK'
, 'EX_CWK')) )
AND PTU2.EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND PTU.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND PTU.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
AND TYP.SYSTEM_PERSON_TYPE = (DECODE('Y'
, P.CURRENT_EMPLOYEE_FLAG
, '-1'
, P.CURRENT_NPW_FLAG
, 'CWK'
, 'EX_CWK'))
AND P.EFFECTIVE_START_DATE = (SELECT MIN(PP.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = P.PERSON_ID
AND PP.EFFECTIVE_END_DATE >= TRUNC(SYSDATE) )
AND P.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)

Columns