VIEW: APPS.PA_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 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)

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 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)