VIEW: APPS.PER_PEOPLE_V2

Source

View Text - Preformatted

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 ,DECODE(sys_context('HR_SESSION_DATA','LEG_CODE'),'AE', (SELECT F14.MEANING FROM HR_LOOKUPS F14 WHERE F14.LOOKUP_CODE = PP.PER_INFORMATION18 AND F14.LOOKUP_TYPE = 'AE_NATIONALITY' ) ,FL1.MEANING) NATIONALITY_FLAG , PP.NATIONAL_IDENTIFIER , PP.PREVIOUS_LAST_NAME , FL4.MEANING 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, HR_LOOKUPS FL4, 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') AND FL4.LOOKUP_TYPE (+) = 'REGISTERED_DISABLED' AND FL4.LOOKUP_CODE (+) = PP.REGISTERED_DISABLED_FLAG

View Text - HTML Formatted

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
, DECODE(SYS_CONTEXT('HR_SESSION_DATA'
, 'LEG_CODE')
, 'AE'
, (SELECT F14.MEANING
FROM HR_LOOKUPS F14
WHERE F14.LOOKUP_CODE = PP.PER_INFORMATION18
AND F14.LOOKUP_TYPE = 'AE_NATIONALITY' )
, FL1.MEANING) NATIONALITY_FLAG
, PP.NATIONAL_IDENTIFIER
, PP.PREVIOUS_LAST_NAME
, FL4.MEANING 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
, HR_LOOKUPS FL4
, 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')
AND FL4.LOOKUP_TYPE (+) = 'REGISTERED_DISABLED'
AND FL4.LOOKUP_CODE (+) = PP.REGISTERED_DISABLED_FLAG