View: BEN_PEOPLE_RSLT_V

Product:	BEN - Advanced Benefits
Description:	Used to support user interface
Implementation/DBA Data:	APPS.BEN_PEOPLE_RSLT_V

View Text

SELECT DISTINCT PPF.PERSON_ID
, PPF.EFFECTIVE_START_DATE
, PPF.EFFECTIVE_END_DATE
, PPF.BUSINESS_GROUP_ID + 0
, PPF.PERSON_TYPE_ID
, PPF.LAST_NAME
, PPF.START_DATE
, PPF.APPLICANT_NUMBER
, PPF.COMMENT_ID
, PPF.CURRENT_APPLICANT_FLAG
, PPF.CURRENT_EMP_OR_APL_FLAG
, PPF.CURRENT_EMPLOYEE_FLAG
, PPF.DATE_EMPLOYEE_DATA_VERIFIED
, PPF.DATE_OF_BIRTH
, PPF.DATE_OF_DEATH
, PPF.EMAIL_ADDRESS
, PPF.EMPLOYEE_NUMBER
, PPF.EXPENSE_CHECK_SEND_TO_ADDRESS
, PPF.FIRST_NAME
, PPF.FULL_NAME
, PPF.ORDER_NAME
, PPF.KNOWN_AS
, PPF.MARITAL_STATUS
, PPF.MIDDLE_NAMES
, PPF.NATIONALITY
, PPF.NATIONAL_IDENTIFIER
, PPF.PREVIOUS_LAST_NAME
, PPF.REGISTERED_DISABLED_FLAG
, PPF.SEX
, PPF.TITLE
, PPF.SUFFIX
, PPF.VENDOR_ID
, HR_GENERAL.GET_WORK_PHONE(PPF.PERSON_ID) WORK_TELEPHONE
, PPF.REQUEST_ID
, PPF.PROGRAM_APPLICATION_ID
, PPF.PROGRAM_ID
, PPF.PROGRAM_UPDATE_DATE
, PPF.ATTRIBUTE_CATEGORY
, PPF.ATTRIBUTE1
, PPF.ATTRIBUTE2
, PPF.ATTRIBUTE3
, PPF.ATTRIBUTE4
, PPF.ATTRIBUTE5
, PPF.ATTRIBUTE6
, PPF.ATTRIBUTE7
, PPF.ATTRIBUTE8
, PPF.ATTRIBUTE9
, PPF.ATTRIBUTE10
, PPF.ATTRIBUTE11
, PPF.ATTRIBUTE12
, PPF.ATTRIBUTE13
, PPF.ATTRIBUTE14
, PPF.ATTRIBUTE15
, PPF.ATTRIBUTE16
, PPF.ATTRIBUTE17
, PPF.ATTRIBUTE18
, PPF.ATTRIBUTE19
, PPF.ATTRIBUTE20
, PPF.ATTRIBUTE21
, PPF.ATTRIBUTE22
, PPF.ATTRIBUTE23
, PPF.ATTRIBUTE24
, PPF.ATTRIBUTE25
, PPF.ATTRIBUTE26
, PPF.ATTRIBUTE27
, PPF.ATTRIBUTE28
, PPF.ATTRIBUTE29
, PPF.ATTRIBUTE30
, PPF.LAST_UPDATE_DATE
, PPF.LAST_UPDATED_BY
, PPF.LAST_UPDATE_LOGIN
, PPF.CREATED_BY
, PPF.CREATION_DATE
, PPF.PER_INFORMATION_CATEGORY
, PPF.PER_INFORMATION1
, PPF.PER_INFORMATION2
, PPF.PER_INFORMATION3
, PPF.PER_INFORMATION4
, PPF.PER_INFORMATION5
, PPF.PER_INFORMATION6
, PPF.PER_INFORMATION7
, PPF.PER_INFORMATION8
, PPF.PER_INFORMATION9
, PPF.PER_INFORMATION10
, PPF.PER_INFORMATION11
, PPF.PER_INFORMATION12
, PPF.PER_INFORMATION13
, PPF.PER_INFORMATION14
, PPF.PER_INFORMATION15
, PPF.PER_INFORMATION16
, PPF.PER_INFORMATION17
, PPF.PER_INFORMATION18
, PPF.PER_INFORMATION19
, PPF.PER_INFORMATION20
, PPF.WORK_SCHEDULE
, PPF.CORRESPONDENCE_LANGUAGE
, PPF.STUDENT_STATUS
, PPF.FTE_CAPACITY
, PPF.ON_MILITARY_SERVICE
, PPF.SECOND_PASSPORT_EXISTS
, PPF.BACKGROUND_CHECK_STATUS
, PPF.BACKGROUND_DATE_CHECK
, PPF.BLOOD_TYPE
, PPF.LAST_MEDICAL_TEST_DATE
, PPF.LAST_MEDICAL_TEST_BY
, PPF.REHIRE_RECOMMENDATION
, PPF.REHIRE_AUTHORIZOR
, PPF.REHIRE_REASON
, PPF.RESUME_EXISTS
, PPF.RESUME_LAST_UPDATED
, PPF.OFFICE_NUMBER
, PPF.INTERNAL_LOCATION
, PPF.MAILSTOP
, PPF.PROJECTED_START_DATE
, PPF.HONORS
, PPF.PRE_NAME_ADJUNCT
, PPF.HOLD_APPLICANT_DATE_UNTIL
, PPTTL.USER_PERSON_TYPE
, 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(PPF.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL)
, PPS.PERIOD_OF_SERVICE_ID
, PPF.COORD_BEN_MED_PLN_NO
, PPF.COORD_BEN_NO_CVG_FLAG
, PPF. DPDNT_ADOPTION_DATE
, PPF.DPDNT_VLNTRY_SVCE_FLAG
, PPF.RECEIPT_OF_DEATH_CERT_DATE
, PPF.USES_TOBACCO_FLAG
, HL11.MEANING
, PPF.BENEFIT_GROUP_ID
, BNG.NAME
, PPS.ADJUSTED_SVC_DATE
, PPF.ORIGINAL_DATE_OF_HIRE
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_PEOPLE PPF
, BEN_BENFTS_GRP BNG
, BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PRTT_PREM_F PREM
WHERE HL1.LOOKUP_TYPE (+) = 'TITLE'
AND HL1.LOOKUP_CODE (+) = PPF.TITLE
AND HL2.LOOKUP_TYPE (+) = 'SEX'
AND HL2.LOOKUP_CODE (+) = PPF.SEX
AND HL3.LOOKUP_TYPE (+) = 'HOME_OFFICE'
AND HL3.LOOKUP_CODE (+) = PPF.EXPENSE_CHECK_SEND_TO_ADDRESS
AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
AND HL4.LOOKUP_CODE (+) = PPF.MARITAL_STATUS
AND HL5.LOOKUP_TYPE (+) = 'NATIONALITY'
AND HL5.LOOKUP_CODE (+) = PPF.NATIONALITY
AND HL6.LOOKUP_TYPE (+) = 'STUDENT_STATUS'
AND HL6.LOOKUP_CODE (+) = PPF.STUDENT_STATUS
AND HL7.LOOKUP_TYPE (+) = 'WORK_SCHEDULE'
AND HL7.LOOKUP_CODE (+) = PPF.WORK_SCHEDULE
AND HL8.LOOKUP_TYPE (+) = 'BLOOD_TYPE'
AND HL8.LOOKUP_CODE (+) = PPF.BLOOD_TYPE
AND HL9.LOOKUP_TYPE (+) = 'REGISTERED_DISABLED'
AND HL9.LOOKUP_CODE (+) = PPF.REGISTERED_DISABLED_FLAG
AND HL10.LOOKUP_TYPE (+) = 'MILITARY_SERVICE'
AND HL10.LOOKUP_CODE (+) = PPF.ON_MILITARY_SERVICE
AND HL11.LOOKUP_TYPE(+) = 'TOBACCO_USER'
AND HL11.LOOKUP_CODE(+) = PPF.USES_TOBACCO_FLAG
AND FL.LANGUAGE_CODE (+) = PPF.CORRESPONDENCE_LANGUAGE
AND PPT.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
AND PPS.PERSON_ID (+) = PPF.PERSON_ID
AND PEN.PERSON_ID = PPF.PERSON_ID
AND PEN.PRTT_ENRT_RSLT_ID = PREM.PRTT_ENRT_RSLT_ID
AND BNG.BENFTS_GRP_ID (+) = PPF.BENEFIT_GROUP_ID
AND ((PPF.EMPLOYEE_NUMBER IS NULL) OR (PPF.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PPF.PERSON_ID
AND PPS1.DATE_START <= PPF.EFFECTIVE_END_DATE)))
AND PPF.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PPF.EFFECTIVE_END_DATE >= (SELECT SE.EFFECTIVE_DATE
FROM FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID'))
AND PEN.EFFECTIVE_START_DATE <= (SELECT SE.EFFECTIVE_DATE
FROM FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID'))
AND PEN.EFFECTIVE_END_DATE >= (SELECT SE.EFFECTIVE_DATE
FROM FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID'))
AND PREM.EFFECTIVE_START_DATE <= (SELECT SE.EFFECTIVE_DATE
FROM FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID'))
AND PREM.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')

Columns