VIEW: APPS.BEN_PEOPLE_RSLT_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')