Product: | PER - Human Resources |
---|---|
Description: | Used to support user interface |
Implementation/DBA Data: | APPS.PER_PEOPLE_V2 |
SELECT PP.PERSON_ID
, PP.EFFECTIVE_START_DATE
, PP.EFFECTIVE_END_DATE
, PP.BUSINESS_GROUP_ID /*
, PTTL.USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(FND.EFFECTIVE_DATE
, PP.PERSON_ID)
, PT.SYSTEM_PERSON_TYPE
, PT.PERSON_TYPE_ID
, PP.LAST_NAME
, PP.START_DATE
, PP.APPLICANT_NUMBER
, PP.CURRENT_APPLICANT_FLAG
, PP.CURRENT_EMP_OR_APL_FLAG
, PP.CURRENT_EMPLOYEE_FLAG
, PP.DATE_OF_BIRTH
, PP.EMAIL_ADDRESS
, PP.EMPLOYEE_NUMBER
, PP.FIRST_NAME
, PP.FULL_NAME
, PP.KNOWN_AS
, FL3.MEANING MARITAL_FLAG
, PP.MIDDLE_NAMES
, FL1.MEANING NATIONALITY_FLAG
, PP.NATIONAL_IDENTIFIER
, PP.PREVIOUS_LAST_NAME
, PP.REGISTERED_DISABLED_FLAG
, FL2.MEANING SEX_FLAG
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, PP.TITLE)
, HR_GENERAL.GET_WORK_PHONE (PP.PERSON_ID) WORK_TELEPHONE
, PP.LAST_UPDATE_DATE
, PP.LAST_UPDATED_BY
, PP.LAST_UPDATE_LOGIN
, PP.CREATED_BY
, PP.CREATION_DATE
, PP.ATTRIBUTE_CATEGORY
, PP.ATTRIBUTE1
, PP.ATTRIBUTE2
, PP.ATTRIBUTE3
, PP.ATTRIBUTE4
, PP.ATTRIBUTE5
, PP.ATTRIBUTE6
, PP.ATTRIBUTE7
, PP.ATTRIBUTE8
, PP.ATTRIBUTE9
, PP.ATTRIBUTE10
, PP.ATTRIBUTE11
, PP.ATTRIBUTE12
, PP.ATTRIBUTE13
, PP.ATTRIBUTE14
, PP.ATTRIBUTE15
, PP.ATTRIBUTE16
, PP.ATTRIBUTE17
, PP.ATTRIBUTE18
, PP.ATTRIBUTE19
, PP.ATTRIBUTE20
, PP.ATTRIBUTE21
, PP.ATTRIBUTE22
, PP.ATTRIBUTE23
, PP.ATTRIBUTE24
, PP.ATTRIBUTE25
, PP.ATTRIBUTE26
, PP.ATTRIBUTE27
, PP.ATTRIBUTE28
, PP.ATTRIBUTE29
, PP.ATTRIBUTE30
, PP.PER_INFORMATION_CATEGORY
, PP.PER_INFORMATION1
, PP.PER_INFORMATION2
, PP.PER_INFORMATION3
, PP.PER_INFORMATION4
, PP.PER_INFORMATION5
, PP.PER_INFORMATION6
, PP.PER_INFORMATION7
, PP.PER_INFORMATION8
, PP.PER_INFORMATION9
, PP.PER_INFORMATION10
, PP.PER_INFORMATION11
, PP.PER_INFORMATION12
, PP.PER_INFORMATION13
, PP.PER_INFORMATION14
, PP.PER_INFORMATION15
, PP.PER_INFORMATION16
, PP.PER_INFORMATION17
, PP.PER_INFORMATION18
, PP.PER_INFORMATION19
, PP.PER_INFORMATION20
, PP.WORK_SCHEDULE
, PP.CORRESPONDENCE_LANGUAGE
, PP.STUDENT_STATUS
, PP.FTE_CAPACITY
, PP.ON_MILITARY_SERVICE
, PP.SECOND_PASSPORT_EXISTS
, PP.RESUME_EXISTS
, PP.RESUME_LAST_UPDATED
, PP.OFFICE_NUMBER
, PP.INTERNAL_LOCATION
, PP.MAILSTOP
, PP.PROJECTED_START_DATE
, PP.HONORS
, PP.PRE_NAME_ADJUNCT
, PP.HOLD_APPLICANT_DATE_UNTIL
, PP.ORIGINAL_DATE_OF_HIRE
FROM PER_PERSON_TYPES PT
, PER_PERSON_TYPES_TL PTTL
, HR_LOOKUPS FL1
, HR_LOOKUPS FL2
, HR_LOOKUPS FL3
, PER_PEOPLE_F PP
, FND_SESSIONS FND
WHERE FND.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND FND.SESSION_ID = USERENV('SESSIONID')
AND PP.PERSON_TYPE_ID = PT.PERSON_TYPE_ID
AND FL1.LOOKUP_CODE (+) = PP.NATIONALITY
AND FL1.LOOKUP_TYPE (+) = 'NATIONALITY'
AND FL2.LOOKUP_TYPE (+) = 'SEX'
AND FL2.LOOKUP_CODE (+) = PP.SEX
AND FL3.LOOKUP_TYPE (+) = 'MAR_STATUS'
AND FL3.LOOKUP_CODE (+) = PP.MARITAL_STATUS
AND PT.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID
AND PTTL.LANGUAGE = USERENV('LANG')