View: HRFV_PERSONAL_DETAILS

Product:	PER - Human Resources
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.HRFV_PERSONAL_DETAILS

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.TITLE TITLE
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG) REGISTERED_DISABLED
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, DECODE(PEO.MARITAL_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS)) MARITAL_STATUS
, DECODE(PEO.NATIONALITY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY)) NATIONALITY
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, DECODE(PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('HOME_OFFICE'
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS)) MAIL_DESTINATION
, DECODE(PEO.SECOND_PASSPORT_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.SECOND_PASSPORT_EXISTS)) SECOND_PASSPORT
, PEO.OFFICE_NUMBER OFFICE_NUMBER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, PEO.WORK_SCHEDULE WORK_SCHEDULE
, PEO.FTE_CAPACITY FTE_CAPACITY
, DECODE(PEO.RESUME_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.RESUME_EXISTS)) RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED RESUME_LAST_UPDATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) CURRENT_EMPLOYEE_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_DEATH DATE_OF_DEATH
, DECODE(PEO.FAST_PATH_EMPLOYEE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE)) FAST_PATH_EMPLOYEE
, PEO.HOLD_APPLICANT_DATE_UNTIL HOLD_APPLICATION_UNTIL
, PEO.HONORS HONORS
, DECODE(PEO.ON_MILITARY_SERVICE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.ON_MILITARY_SERVICE)) ON_MILITARY_SERVICE
, PEO.PROJECTED_START_DATE PROJECTED_START_DATE
, DECODE(PEO.STUDENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS)) STUDENT_STATUS
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_FLAG_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS EXPENSE_CHECK_SEND_ADDR_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_EXISTS_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, PPTT_EMP.USER_PERSON_TYPE EMPLOYEE_PERSON_TYPE
, TO_CHAR(NULL) APPLICANT_PERSON_TYPE
, PP.PHONE_NUMBER WORK_TELEPHONE_NUMBER
, LANG.NLS_LANGUAGE CORRESPONDENCE_LANGUAGE
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, '_DF:PER:PER_PEOPLE:PEO'
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, PPT.PERSON_TYPE_ID PERSON_TYPE_ID
FROM FND_LANGUAGES LANG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTT
, PER_PERSON_TYPES PPT_EMP
, PER_PERSON_TYPES_TL PPTT_EMP
, PER_PERSON_TYPE_USAGES_F PTU
, PER_PHONES PP
, PER_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
WHERE PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = USERENV('LANG')
AND PEO.PERSON_ID = PTU.PERSON_ID
AND PEO.EFFECTIVE_START_DATE BETWEEN PTU.EFFECTIVE_START_DATE
AND PTU.EFFECTIVE_END_DATE
AND PTU.PERSON_TYPE_ID = PPT_EMP.PERSON_TYPE_ID
AND PPT_EMP.SYSTEM_PERSON_TYPE = 'EMP'
AND PPT_EMP.PERSON_TYPE_ID = PPTT_EMP.PERSON_TYPE_ID
AND PPTT_EMP.LANGUAGE = USERENV('LANG')
AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+) = 'W1'
AND PEO.PERSON_ID = PP.PARENT_ID(+)
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PP.DATE_FROM(+)
AND PP.DATE_TO(+)
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND ((PEO.EMPLOYEE_NUMBER IS NULL) OR (PEO.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PEO.PERSON_ID
AND PPS1.DATE_START <= PEO.EFFECTIVE_END_DATE)) )
AND PEO.CORRESPONDENCE_LANGUAGE = LANG.LANGUAGE_CODE(+)
AND PEO.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.TITLE TITLE
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG) REGISTERED_DISABLED
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, DECODE(PEO.MARITAL_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS)) MARITAL_STATUS
, DECODE(PEO.NATIONALITY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY)) NATIONALITY
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, DECODE(PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('HOME_OFFICE'
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS)) MAIL_DESTINATION
, DECODE(PEO.SECOND_PASSPORT_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.SECOND_PASSPORT_EXISTS)) SECOND_PASSPORT
, PEO.OFFICE_NUMBER OFFICE_NUMBER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, PEO.WORK_SCHEDULE WORK_SCHEDULE
, PEO.FTE_CAPACITY FTE_CAPACITY
, DECODE(PEO.RESUME_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.RESUME_EXISTS)) RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED RESUME_LAST_UPDATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) CURRENT_EMPLOYEE_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_DEATH DATE_OF_DEATH
, DECODE(PEO.FAST_PATH_EMPLOYEE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE)) FAST_PATH_EMPLOYEE
, PEO.HOLD_APPLICANT_DATE_UNTIL HOLD_APPLICATION_UNTIL
, PEO.HONORS HONORS
, DECODE(PEO.ON_MILITARY_SERVICE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.ON_MILITARY_SERVICE)) ON_MILITARY_SERVICE
, PEO.PROJECTED_START_DATE PROJECTED_START_DATE
, DECODE(PEO.STUDENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS)) STUDENT_STATUS
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_FLAG_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS EXPENSE_CHECK_SEND_ADDR_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_EXISTS_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, TO_CHAR(NULL) EMPLOYEE_PERSON_TYPE
, PPTT_APP.USER_PERSON_TYPE APPLICANT_PERSON_TYPE
, PP.PHONE_NUMBER WORK_TELEPHONE_NUMBER
, LANG.NLS_LANGUAGE CORRESPONDENCE_LANGUAGE
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, '_DF:PER:PER_PEOPLE:PEO'
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, PPT.PERSON_TYPE_ID PERSON_TYPE_ID
FROM FND_LANGUAGES LANG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTT
, PER_PERSON_TYPES PPT_APP
, PER_PERSON_TYPES_TL PPTT_APP
, PER_PERSON_TYPE_USAGES_F PTU
, PER_PHONES PP
, PER_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
WHERE PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'APL'
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = USERENV('LANG')
AND PEO.PERSON_ID = PTU.PERSON_ID
AND PEO.EFFECTIVE_START_DATE BETWEEN PTU.EFFECTIVE_START_DATE
AND PTU.EFFECTIVE_END_DATE
AND PTU.PERSON_TYPE_ID = PPT_APP.PERSON_TYPE_ID
AND PPT_APP.SYSTEM_PERSON_TYPE = 'APL'
AND PPT_APP.PERSON_TYPE_ID = PPTT_APP.PERSON_TYPE_ID
AND PPTT_APP.LANGUAGE = USERENV('LANG')
AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+) = 'W1'
AND PEO.PERSON_ID = PP.PARENT_ID(+)
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PP.DATE_FROM(+)
AND PP.DATE_TO(+)
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND ((PEO.EMPLOYEE_NUMBER IS NULL) OR (PEO.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PEO.PERSON_ID
AND PPS1.DATE_START <= PEO.EFFECTIVE_END_DATE)) )
AND PEO.CORRESPONDENCE_LANGUAGE = LANG.LANGUAGE_CODE(+)
AND PEO.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.TITLE TITLE
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, DECODE(PEO.REGISTERED_DISABLED_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG)) REGISTERED_DISABLED
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, DECODE(PEO.MARITAL_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS)) MARITAL_STATUS
, DECODE(PEO.NATIONALITY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY)) NATIONALITY
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, DECODE(PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('HOME_OFFICE'
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS)) MAIL_DESTINATION
, DECODE(PEO.SECOND_PASSPORT_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.SECOND_PASSPORT_EXISTS)) SECOND_PASSPORT
, PEO.OFFICE_NUMBER OFFICE_NUMBER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, PEO.WORK_SCHEDULE WORK_SCHEDULE
, PEO.FTE_CAPACITY FTE_CAPACITY
, DECODE(PEO.RESUME_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.RESUME_EXISTS)) RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED RESUME_LAST_UPDATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) CURRENT_EMPLOYEE_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_DEATH DATE_OF_DEATH
, DECODE(PEO.FAST_PATH_EMPLOYEE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE)) FAST_PATH_EMPLOYEE
, PEO.HOLD_APPLICANT_DATE_UNTIL HOLD_APPLICATION_UNTIL
, PEO.HONORS HONORS
, DECODE(PEO.ON_MILITARY_SERVICE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.ON_MILITARY_SERVICE)) ON_MILITARY_SERVICE
, PEO.PROJECTED_START_DATE PROJECTED_START_DATE
, DECODE(PEO.STUDENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS)) STUDENT_STATUS
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_FLAG_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS EXPENSE_CHECK_SEND_ADDR_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_EXISTS_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, PPTT_EMP.USER_PERSON_TYPE EMPLOYEE_PERSON_TYPE
, PPTT_APP.USER_PERSON_TYPE APPLICANT_PERSON_TYPE
, PP.PHONE_NUMBER WORK_TELEPHONE_NUMBER
, LANG.NLS_LANGUAGE CORRESPONDENCE_LANGUAGE
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, '_DF:PER:PER_PEOPLE:PEO'
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, PPT.PERSON_TYPE_ID PERSON_TYPE_ID
FROM FND_LANGUAGES LANG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES PPT_EMP
, PER_PERSON_TYPES PPT_APP
, PER_PERSON_TYPES_TL PPTT
, PER_PERSON_TYPES_TL PPTT_EMP
, PER_PERSON_TYPES_TL PPTT_APP
, PER_PERSON_TYPE_USAGES_F PTU_EMP
, PER_PERSON_TYPE_USAGES_F PTU_APP
, PER_PHONES PP
, PER_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
WHERE PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP_APL'
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = USERENV('LANG')
AND PEO.PERSON_ID = PTU_EMP.PERSON_ID
AND PEO.EFFECTIVE_START_DATE BETWEEN PTU_EMP.EFFECTIVE_START_DATE
AND PTU_EMP.EFFECTIVE_END_DATE
AND PTU_EMP.PERSON_TYPE_ID = PPT_EMP.PERSON_TYPE_ID
AND PPT_EMP.SYSTEM_PERSON_TYPE = 'EMP'
AND PPT_EMP.PERSON_TYPE_ID = PPTT_EMP.PERSON_TYPE_ID
AND PPTT_EMP.LANGUAGE = USERENV('LANG')
AND PEO.PERSON_ID = PTU_APP.PERSON_ID
AND PEO.EFFECTIVE_START_DATE BETWEEN PTU_APP.EFFECTIVE_START_DATE
AND PTU_APP.EFFECTIVE_END_DATE
AND PTU_APP.PERSON_TYPE_ID = PPT_APP.PERSON_TYPE_ID
AND PPT_APP.SYSTEM_PERSON_TYPE = 'APL'
AND PPT_APP.PERSON_TYPE_ID = PPTT_APP.PERSON_TYPE_ID
AND PPTT_APP.LANGUAGE = USERENV('LANG')
AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+) = 'W1'
AND PEO.PERSON_ID = PP.PARENT_ID(+)
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PP.DATE_FROM(+)
AND PP.DATE_TO(+)
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND ((PEO.EMPLOYEE_NUMBER IS NULL) OR (PEO.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PEO.PERSON_ID
AND PPS1.DATE_START <= PEO.EFFECTIVE_END_DATE)) )
AND PEO.CORRESPONDENCE_LANGUAGE = LANG.LANGUAGE_CODE(+)
AND PEO.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.TITLE TITLE
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, DECODE(PEO.REGISTERED_DISABLED_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG)) REGISTERED_DISABLED
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, DECODE(PEO.MARITAL_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS)) MARITAL_STATUS
, DECODE(PEO.NATIONALITY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY)) NATIONALITY
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, DECODE(PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('HOME_OFFICE'
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS)) MAIL_DESTINATION
, DECODE(PEO.SECOND_PASSPORT_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.SECOND_PASSPORT_EXISTS)) SECOND_PASSPORT
, PEO.OFFICE_NUMBER OFFICE_NUMBER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, PEO.WORK_SCHEDULE WORK_SCHEDULE
, PEO.FTE_CAPACITY FTE_CAPACITY
, DECODE(PEO.RESUME_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.RESUME_EXISTS)) RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED RESUME_LAST_UPDATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) CURRENT_EMPLOYEE_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_DEATH DATE_OF_DEATH
, DECODE(PEO.FAST_PATH_EMPLOYEE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE)) FAST_PATH_EMPLOYEE
, PEO.HOLD_APPLICANT_DATE_UNTIL HOLD_APPLICATION_UNTIL
, PEO.HONORS HONORS
, DECODE(PEO.ON_MILITARY_SERVICE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.ON_MILITARY_SERVICE)) ON_MILITARY_SERVICE
, PEO.PROJECTED_START_DATE PROJECTED_START_DATE
, DECODE(PEO.STUDENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS)) STUDENT_STATUS
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_FLAG_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS EXPENSE_CHECK_SEND_ADDR_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_EXISTS_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, TO_CHAR(NULL) EMPLOYEE_PERSON_TYPE
, TO_CHAR(NULL) APPLICANT_PERSON_TYPE
, PP.PHONE_NUMBER WORK_TELEPHONE_NUMBER
, LANG.NLS_LANGUAGE CORRESPONDENCE_LANGUAGE
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, '_DF:PER:PER_PEOPLE:PEO'
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, PPT.PERSON_TYPE_ID PERSON_TYPE_ID
FROM FND_LANGUAGES LANG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTT
, PER_PHONES PP
, PER_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
WHERE PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE NOT IN ('APL'
, 'EMP'
, 'EMP_APL')
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = USERENV('LANG')
AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+) = 'W1'
AND PEO.PERSON_ID = PP.PARENT_ID(+)
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PP.DATE_FROM(+)
AND PP.DATE_TO(+)
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND ((PEO.EMPLOYEE_NUMBER IS NULL) OR (PEO.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PEO.PERSON_ID
AND PPS1.DATE_START <= PEO.EFFECTIVE_END_DATE)) )
AND PEO.CORRESPONDENCE_LANGUAGE = LANG.LANGUAGE_CODE(+)
AND PEO.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID) WITH READ ONLY

Columns