VIEW: APPS.PER_PEOPLE_V

Source

View Text - Preformatted

SELECT PER.ROWID , PER.PERSON_ID , PER.EFFECTIVE_START_DATE , PER.EFFECTIVE_END_DATE , PER.BUSINESS_GROUP_ID + 0 , PER.PERSON_TYPE_ID , PER.LAST_NAME , PER.START_DATE , PER.APPLICANT_NUMBER , PER.COMMENT_ID , PER.CURRENT_APPLICANT_FLAG , PER.CURRENT_EMP_OR_APL_FLAG , PER.CURRENT_EMPLOYEE_FLAG , PER.DATE_EMPLOYEE_DATA_VERIFIED , PER.DATE_OF_BIRTH , PER.DATE_OF_DEATH , PER.EMAIL_ADDRESS , PER.EMPLOYEE_NUMBER , PER.EXPENSE_CHECK_SEND_TO_ADDRESS , PER.FIRST_NAME , PER.FULL_NAME , PER.ORDER_NAME , PER.KNOWN_AS , PER.MARITAL_STATUS , PER.MIDDLE_NAMES , PER.NATIONALITY , PER.NATIONAL_IDENTIFIER , PER.PREVIOUS_LAST_NAME , PER.REGISTERED_DISABLED_FLAG , PER.SEX , PER.TITLE , PER.SUFFIX , PER.VENDOR_ID , HR_GENERAL.GET_WORK_PHONE(PER.PERSON_ID) WORK_TELEPHONE , PER.REQUEST_ID , PER.PROGRAM_APPLICATION_ID , PER.PROGRAM_ID , PER.PROGRAM_UPDATE_DATE , PER.ATTRIBUTE_CATEGORY , PER.ATTRIBUTE1 , PER.ATTRIBUTE2 , PER.ATTRIBUTE3 , PER.ATTRIBUTE4 , PER.ATTRIBUTE5 , PER.ATTRIBUTE6 , PER.ATTRIBUTE7 , PER.ATTRIBUTE8 , PER.ATTRIBUTE9 , PER.ATTRIBUTE10 , PER.ATTRIBUTE11 , PER.ATTRIBUTE12 , PER.ATTRIBUTE13 , PER.ATTRIBUTE14 , PER.ATTRIBUTE15 , PER.ATTRIBUTE16 , PER.ATTRIBUTE17 , PER.ATTRIBUTE18 , PER.ATTRIBUTE19 , PER.ATTRIBUTE20 , PER.ATTRIBUTE21 , PER.ATTRIBUTE22 , PER.ATTRIBUTE23 , PER.ATTRIBUTE24 , PER.ATTRIBUTE25 , PER.ATTRIBUTE26 , PER.ATTRIBUTE27 , PER.ATTRIBUTE28 , PER.ATTRIBUTE29 , PER.ATTRIBUTE30 , PER.LAST_UPDATE_DATE , PER.LAST_UPDATED_BY , PER.LAST_UPDATE_LOGIN , PER.CREATED_BY , PER.CREATION_DATE , PER.PER_INFORMATION_CATEGORY , PER.PER_INFORMATION1 , PER.PER_INFORMATION2 , PER.PER_INFORMATION3 , PER.PER_INFORMATION4 , PER.PER_INFORMATION5 , PER.PER_INFORMATION6 , PER.PER_INFORMATION7 , PER.PER_INFORMATION8 , PER.PER_INFORMATION9 , PER.PER_INFORMATION10 , PER.PER_INFORMATION11 , PER.PER_INFORMATION12 , PER.PER_INFORMATION13 , PER.PER_INFORMATION14 , PER.PER_INFORMATION15 , PER.PER_INFORMATION16 , PER.PER_INFORMATION17 , PER.PER_INFORMATION18 , PER.PER_INFORMATION19 , PER.PER_INFORMATION20 , PER.PER_INFORMATION21 , PER.PER_INFORMATION22 , PER.PER_INFORMATION23 , PER.PER_INFORMATION24 , PER.PER_INFORMATION25 , PER.PER_INFORMATION26 , PER.PER_INFORMATION27 , PER.PER_INFORMATION28 , PER.PER_INFORMATION29 , PER.PER_INFORMATION30 , PER.WORK_SCHEDULE , PER.CORRESPONDENCE_LANGUAGE , PER.STUDENT_STATUS , PER.FTE_CAPACITY , PER.ON_MILITARY_SERVICE , PER.SECOND_PASSPORT_EXISTS , PER.BACKGROUND_CHECK_STATUS , PER.BACKGROUND_DATE_CHECK , PER.BLOOD_TYPE , PER.LAST_MEDICAL_TEST_DATE , PER.LAST_MEDICAL_TEST_BY , PER.REHIRE_RECOMMENDATION , PER.REHIRE_AUTHORIZOR , PER.REHIRE_REASON , PER.RESUME_EXISTS , PER.RESUME_LAST_UPDATED , PER.OFFICE_NUMBER , PER.INTERNAL_LOCATION , PER.MAILSTOP , PER.PROJECTED_START_DATE , PER.HONORS , PER.PRE_NAME_ADJUNCT , PER.HOLD_APPLICANT_DATE_UNTIL /* , PPTTL.USER_PERSON_TYPE */ , hr_person_type_usage_info.get_user_person_type(ses.effective_date,per.person_id) , PPT.SYSTEM_PERSON_TYPE , HL1.MEANING , HL2.MEANING , HL3.MEANING , HL4.MEANING , HL5.MEANING , HL6.MEANING , HL7.MEANING , FL.DESCRIPTION , HL8.MEANING , HL9.MEANING , HL10.MEANING , DECODE(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START, DECODE(PER.CURRENT_NPW_FLAG,'Y',PPP.DATE_START, NULL)) , PPS.PERIOD_OF_SERVICE_ID , PER.COORD_BEN_MED_PLN_NO , PER.COORD_BEN_NO_CVG_FLAG , PER. DPDNT_ADOPTION_DATE , PER.DPDNT_VLNTRY_SVCE_FLAG , PER.RECEIPT_OF_DEATH_CERT_DATE , PER.USES_TOBACCO_FLAG , HL11.MEANING , PER.BENEFIT_GROUP_ID , BEN.NAME , PPS.ADJUSTED_SVC_DATE , PER.ORIGINAL_DATE_OF_HIRE , PER.TOWN_OF_BIRTH , PER.REGION_OF_BIRTH , PER.COUNTRY_OF_BIRTH , FTV.TERRITORY_SHORT_NAME , PER.GLOBAL_PERSON_ID , PER.COORD_BEN_MED_EXT_ER , PER.COORD_BEN_MED_PL_NAME , PER.COORD_BEN_MED_INSR_CRR_NAME , PER.COORD_BEN_MED_INSR_CRR_IDENT , PER.COORD_BEN_MED_CVG_STRT_DT , PER.COORD_BEN_MED_CVG_END_DT , PER.NPW_NUMBER , PER.CURRENT_NPW_FLAG ,PER.GLOBAL_NAME ,PER.LOCAL_NAME ,PER.LIST_NAME FROM HR_LOOKUPS HL1 ,HR_LOOKUPS HL2 ,HR_LOOKUPS HL3 ,HR_LOOKUPS HL4 ,HR_LOOKUPS HL5 ,HR_LOOKUPS HL6 ,HR_LOOKUPS HL7 ,HR_LOOKUPS HL8 ,HR_LOOKUPS HL9 ,HR_LOOKUPS HL10 ,HR_LOOKUPS HL11 ,FND_LANGUAGES_VL FL ,PER_PERSON_TYPES PPT ,PER_PERSON_TYPES_TL PPTTL ,PER_PERIODS_OF_SERVICE PPS ,PER_PERIODS_OF_PLACEMENT PPP ,PER_PEOPLE_F PER ,BEN_BENFTS_GRP BEN ,FND_TERRITORIES_VL FTV ,FND_SESSIONS SES WHERE HL1.LOOKUP_TYPE (+) = 'TITLE' AND HL1.LOOKUP_CODE (+) = PER.TITLE AND HL2.LOOKUP_TYPE (+) = 'SEX' AND HL2.LOOKUP_CODE (+) = PER.SEX AND HL3.LOOKUP_TYPE (+) = 'HOME_OFFICE' AND HL3.LOOKUP_CODE (+) = PER.EXPENSE_CHECK_SEND_TO_ADDRESS AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS' AND HL4.LOOKUP_CODE (+) = PER.MARITAL_STATUS AND HL5.LOOKUP_TYPE (+) = 'NATIONALITY' AND HL5.LOOKUP_CODE (+) = PER.NATIONALITY AND HL6.LOOKUP_TYPE (+) = 'STUDENT_STATUS' AND HL6.LOOKUP_CODE (+) = PER.STUDENT_STATUS AND HL7.LOOKUP_TYPE (+) = 'WORK_SCHEDULE' AND HL7.LOOKUP_CODE (+) = PER.WORK_SCHEDULE AND HL8.LOOKUP_TYPE (+) = 'BLOOD_TYPE' AND HL8.LOOKUP_CODE (+) = PER.BLOOD_TYPE AND HL9.LOOKUP_TYPE (+) = 'REGISTERED_DISABLED' AND HL9.LOOKUP_CODE (+) = PER.REGISTERED_DISABLED_FLAG AND HL10.LOOKUP_TYPE (+) = 'MILITARY_SERVICE' AND HL10.LOOKUP_CODE (+) = PER.ON_MILITARY_SERVICE AND HL11.LOOKUP_TYPE(+) = 'TOBACCO_USER' AND HL11.LOOKUP_CODE(+) = PER.USES_TOBACCO_FLAG AND FL.LANGUAGE_CODE (+) = PER.CORRESPONDENCE_LANGUAGE AND PPT.PERSON_TYPE_ID = PER.PERSON_TYPE_ID AND PPS.PERSON_ID (+) = PER.PERSON_ID AND PPP.PERSON_ID (+) = PER.PERSON_ID AND BEN.BENFTS_GRP_ID (+) = PER.BENEFIT_GROUP_ID AND FTV.TERRITORY_CODE (+) = PER.COUNTRY_OF_BIRTH AND ((PER.EMPLOYEE_NUMBER IS NULL) OR (PER.EMPLOYEE_NUMBER IS NOT NULL AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID = PER.PERSON_ID AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND ((PER.NPW_NUMBER IS NULL) OR (PER.NPW_NUMBER IS NOT NULL AND PPP.DATE_START = (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND PPP1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND SES.SESSION_ID=USERENV('sessionid') AND PER.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID=USERENV('sessionid')) AND PER.EFFECTIVE_END_DATE >= (SELECT SE.EFFECTIVE_DATE FROM FND_SESSIONS SE WHERE SE.SESSION_ID=USERENV('sessionid')) AND PPT.PERSON_TYPE_ID = PPTTL.PERSON_TYPE_ID AND PPTTL.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

SELECT PER.ROWID
, PER.PERSON_ID
, PER.EFFECTIVE_START_DATE
, PER.EFFECTIVE_END_DATE
, PER.BUSINESS_GROUP_ID + 0
, PER.PERSON_TYPE_ID
, PER.LAST_NAME
, PER.START_DATE
, PER.APPLICANT_NUMBER
, PER.COMMENT_ID
, PER.CURRENT_APPLICANT_FLAG
, PER.CURRENT_EMP_OR_APL_FLAG
, PER.CURRENT_EMPLOYEE_FLAG
, PER.DATE_EMPLOYEE_DATA_VERIFIED
, PER.DATE_OF_BIRTH
, PER.DATE_OF_DEATH
, PER.EMAIL_ADDRESS
, PER.EMPLOYEE_NUMBER
, PER.EXPENSE_CHECK_SEND_TO_ADDRESS
, PER.FIRST_NAME
, PER.FULL_NAME
, PER.ORDER_NAME
, PER.KNOWN_AS
, PER.MARITAL_STATUS
, PER.MIDDLE_NAMES
, PER.NATIONALITY
, PER.NATIONAL_IDENTIFIER
, PER.PREVIOUS_LAST_NAME
, PER.REGISTERED_DISABLED_FLAG
, PER.SEX
, PER.TITLE
, PER.SUFFIX
, PER.VENDOR_ID
, HR_GENERAL.GET_WORK_PHONE(PER.PERSON_ID) WORK_TELEPHONE
, PER.REQUEST_ID
, PER.PROGRAM_APPLICATION_ID
, PER.PROGRAM_ID
, PER.PROGRAM_UPDATE_DATE
, PER.ATTRIBUTE_CATEGORY
, PER.ATTRIBUTE1
, PER.ATTRIBUTE2
, PER.ATTRIBUTE3
, PER.ATTRIBUTE4
, PER.ATTRIBUTE5
, PER.ATTRIBUTE6
, PER.ATTRIBUTE7
, PER.ATTRIBUTE8
, PER.ATTRIBUTE9
, PER.ATTRIBUTE10
, PER.ATTRIBUTE11
, PER.ATTRIBUTE12
, PER.ATTRIBUTE13
, PER.ATTRIBUTE14
, PER.ATTRIBUTE15
, PER.ATTRIBUTE16
, PER.ATTRIBUTE17
, PER.ATTRIBUTE18
, PER.ATTRIBUTE19
, PER.ATTRIBUTE20
, PER.ATTRIBUTE21
, PER.ATTRIBUTE22
, PER.ATTRIBUTE23
, PER.ATTRIBUTE24
, PER.ATTRIBUTE25
, PER.ATTRIBUTE26
, PER.ATTRIBUTE27
, PER.ATTRIBUTE28
, PER.ATTRIBUTE29
, PER.ATTRIBUTE30
, PER.LAST_UPDATE_DATE
, PER.LAST_UPDATED_BY
, PER.LAST_UPDATE_LOGIN
, PER.CREATED_BY
, PER.CREATION_DATE
, PER.PER_INFORMATION_CATEGORY
, PER.PER_INFORMATION1
, PER.PER_INFORMATION2
, PER.PER_INFORMATION3
, PER.PER_INFORMATION4
, PER.PER_INFORMATION5
, PER.PER_INFORMATION6
, PER.PER_INFORMATION7
, PER.PER_INFORMATION8
, PER.PER_INFORMATION9
, PER.PER_INFORMATION10
, PER.PER_INFORMATION11
, PER.PER_INFORMATION12
, PER.PER_INFORMATION13
, PER.PER_INFORMATION14
, PER.PER_INFORMATION15
, PER.PER_INFORMATION16
, PER.PER_INFORMATION17
, PER.PER_INFORMATION18
, PER.PER_INFORMATION19
, PER.PER_INFORMATION20
, PER.PER_INFORMATION21
, PER.PER_INFORMATION22
, PER.PER_INFORMATION23
, PER.PER_INFORMATION24
, PER.PER_INFORMATION25
, PER.PER_INFORMATION26
, PER.PER_INFORMATION27
, PER.PER_INFORMATION28
, PER.PER_INFORMATION29
, PER.PER_INFORMATION30
, PER.WORK_SCHEDULE
, PER.CORRESPONDENCE_LANGUAGE
, PER.STUDENT_STATUS
, PER.FTE_CAPACITY
, PER.ON_MILITARY_SERVICE
, PER.SECOND_PASSPORT_EXISTS
, PER.BACKGROUND_CHECK_STATUS
, PER.BACKGROUND_DATE_CHECK
, PER.BLOOD_TYPE
, PER.LAST_MEDICAL_TEST_DATE
, PER.LAST_MEDICAL_TEST_BY
, PER.REHIRE_RECOMMENDATION
, PER.REHIRE_AUTHORIZOR
, PER.REHIRE_REASON
, PER.RESUME_EXISTS
, PER.RESUME_LAST_UPDATED
, PER.OFFICE_NUMBER
, PER.INTERNAL_LOCATION
, PER.MAILSTOP
, PER.PROJECTED_START_DATE
, PER.HONORS
, PER.PRE_NAME_ADJUNCT
, PER.HOLD_APPLICANT_DATE_UNTIL /*
, PPTTL.USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SES.EFFECTIVE_DATE
, PER.PERSON_ID)
, PPT.SYSTEM_PERSON_TYPE
, HL1.MEANING
, HL2.MEANING
, HL3.MEANING
, HL4.MEANING
, HL5.MEANING
, HL6.MEANING
, HL7.MEANING
, FL.DESCRIPTION
, HL8.MEANING
, HL9.MEANING
, HL10.MEANING
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, DECODE(PER.CURRENT_NPW_FLAG
, 'Y'
, PPP.DATE_START
, NULL))
, PPS.PERIOD_OF_SERVICE_ID
, PER.COORD_BEN_MED_PLN_NO
, PER.COORD_BEN_NO_CVG_FLAG
, PER. DPDNT_ADOPTION_DATE
, PER.DPDNT_VLNTRY_SVCE_FLAG
, PER.RECEIPT_OF_DEATH_CERT_DATE
, PER.USES_TOBACCO_FLAG
, HL11.MEANING
, PER.BENEFIT_GROUP_ID
, BEN.NAME
, PPS.ADJUSTED_SVC_DATE
, PER.ORIGINAL_DATE_OF_HIRE
, PER.TOWN_OF_BIRTH
, PER.REGION_OF_BIRTH
, PER.COUNTRY_OF_BIRTH
, FTV.TERRITORY_SHORT_NAME
, PER.GLOBAL_PERSON_ID
, PER.COORD_BEN_MED_EXT_ER
, PER.COORD_BEN_MED_PL_NAME
, PER.COORD_BEN_MED_INSR_CRR_NAME
, PER.COORD_BEN_MED_INSR_CRR_IDENT
, PER.COORD_BEN_MED_CVG_STRT_DT
, PER.COORD_BEN_MED_CVG_END_DT
, PER.NPW_NUMBER
, PER.CURRENT_NPW_FLAG
, PER.GLOBAL_NAME
, PER.LOCAL_NAME
, PER.LIST_NAME
FROM HR_LOOKUPS HL1
, HR_LOOKUPS HL2
, HR_LOOKUPS HL3
, HR_LOOKUPS HL4
, HR_LOOKUPS HL5
, HR_LOOKUPS HL6
, HR_LOOKUPS HL7
, HR_LOOKUPS HL8
, HR_LOOKUPS HL9
, HR_LOOKUPS HL10
, HR_LOOKUPS HL11
, FND_LANGUAGES_VL FL
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTTL
, PER_PERIODS_OF_SERVICE PPS
, PER_PERIODS_OF_PLACEMENT PPP
, PER_PEOPLE_F PER
, BEN_BENFTS_GRP BEN
, FND_TERRITORIES_VL FTV
, FND_SESSIONS SES
WHERE HL1.LOOKUP_TYPE (+) = 'TITLE'
AND HL1.LOOKUP_CODE (+) = PER.TITLE
AND HL2.LOOKUP_TYPE (+) = 'SEX'
AND HL2.LOOKUP_CODE (+) = PER.SEX
AND HL3.LOOKUP_TYPE (+) = 'HOME_OFFICE'
AND HL3.LOOKUP_CODE (+) = PER.EXPENSE_CHECK_SEND_TO_ADDRESS
AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
AND HL4.LOOKUP_CODE (+) = PER.MARITAL_STATUS
AND HL5.LOOKUP_TYPE (+) = 'NATIONALITY'
AND HL5.LOOKUP_CODE (+) = PER.NATIONALITY
AND HL6.LOOKUP_TYPE (+) = 'STUDENT_STATUS'
AND HL6.LOOKUP_CODE (+) = PER.STUDENT_STATUS
AND HL7.LOOKUP_TYPE (+) = 'WORK_SCHEDULE'
AND HL7.LOOKUP_CODE (+) = PER.WORK_SCHEDULE
AND HL8.LOOKUP_TYPE (+) = 'BLOOD_TYPE'
AND HL8.LOOKUP_CODE (+) = PER.BLOOD_TYPE
AND HL9.LOOKUP_TYPE (+) = 'REGISTERED_DISABLED'
AND HL9.LOOKUP_CODE (+) = PER.REGISTERED_DISABLED_FLAG
AND HL10.LOOKUP_TYPE (+) = 'MILITARY_SERVICE'
AND HL10.LOOKUP_CODE (+) = PER.ON_MILITARY_SERVICE
AND HL11.LOOKUP_TYPE(+) = 'TOBACCO_USER'
AND HL11.LOOKUP_CODE(+) = PER.USES_TOBACCO_FLAG
AND FL.LANGUAGE_CODE (+) = PER.CORRESPONDENCE_LANGUAGE
AND PPT.PERSON_TYPE_ID = PER.PERSON_TYPE_ID
AND PPS.PERSON_ID (+) = PER.PERSON_ID
AND PPP.PERSON_ID (+) = PER.PERSON_ID
AND BEN.BENFTS_GRP_ID (+) = PER.BENEFIT_GROUP_ID
AND FTV.TERRITORY_CODE (+) = PER.COUNTRY_OF_BIRTH
AND ((PER.EMPLOYEE_NUMBER IS NULL) OR (PER.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PER.PERSON_ID
AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE)))
AND ((PER.NPW_NUMBER IS NULL) OR (PER.NPW_NUMBER IS NOT NULL
AND PPP.DATE_START = (SELECT MAX(PPP1.DATE_START)
FROM PER_PERIODS_OF_PLACEMENT PPP1
WHERE PPP1.PERSON_ID = PER.PERSON_ID
AND PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)))
AND SES.SESSION_ID=USERENV('SESSIONID')
AND PER.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID=USERENV('SESSIONID'))
AND PER.EFFECTIVE_END_DATE >= (SELECT SE.EFFECTIVE_DATE
FROM FND_SESSIONS SE
WHERE SE.SESSION_ID=USERENV('SESSIONID'))
AND PPT.PERSON_TYPE_ID = PPTTL.PERSON_TYPE_ID
AND PPTTL.LANGUAGE = USERENV('LANG')