VIEW: APPS.PER_JP_CONTACT_RELATIONSHIPS_V

Source

View Text - Preformatted

SELECT pcr.contact_relationship_id, pcr.person_id, pcr.contact_person_id, pcr.business_group_id, pbg.legislation_code, papf.last_name, papf.first_name, papf.per_information18, papf.per_information19, papf.full_name, papf.global_name, papf.local_name, pcr.contact_type, SUBSTRB(hr_general.decode_lookup('CONTACT',pcr.contact_type),1,80), papf.sex, SUBSTRB(hr_general.decode_lookup('SEX',papf.sex),1,80), papf.date_of_birth, TRUNC(MONTHS_BETWEEN(LEAST(NVL(papf.date_of_death,fs.effective_date),fs.effective_date),papf.date_of_birth)/12), papf.date_of_death, papf.effective_start_date, papf.effective_end_date, pcr.cont_attribute_category, pcr.cont_attribute1, pcr.cont_attribute2, pcr.cont_attribute3, pcr.cont_attribute4, pcr.cont_attribute5, pcr.cont_attribute6, pcr.cont_attribute7, pcr.cont_attribute8, pcr.cont_attribute9, pcr.cont_attribute10, pcr.cont_attribute11, pcr.cont_attribute12, pcr.cont_attribute13, pcr.cont_attribute14, pcr.cont_attribute15, pcr.cont_attribute16, pcr.cont_attribute17, pcr.cont_attribute18, pcr.cont_attribute19, pcr.cont_attribute20, pcr.last_update_date, pcr.last_updated_by, pcr.last_update_login, pcr.created_by, pcr.creation_date, pcr.cont_information_category, pcr.cont_information1, pcr.cont_information2, pcr.cont_information3, pcr.cont_information4, pcr.cont_information5, pcr.cont_information6, pcr.cont_information7, pcr.cont_information8, pcr.cont_information9, pcr.cont_information10, pcr.cont_information11, pcr.cont_information12, pcr.cont_information13, pcr.cont_information14, pcr.cont_information15, pcr.cont_information16, pcr.cont_information17, pcr.cont_information18, pcr.cont_information19, pcr.cont_information20, pcr.date_start, pcr.date_end FROM per_all_people_f papf, per_contact_relationships pcr, per_business_groups pbg, fnd_sessions fs WHERE papf.person_id = pcr.contact_person_id AND papf.business_group_id = pbg.business_group_id AND fs.session_id = USERENV('sessionid') AND fs.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date AND fs.effective_date BETWEEN NVL(pcr.date_start,papf.effective_start_date) AND NVL(pcr.date_end,papf.effective_end_date)

View Text - HTML Formatted

SELECT PCR.CONTACT_RELATIONSHIP_ID
, PCR.PERSON_ID
, PCR.CONTACT_PERSON_ID
, PCR.BUSINESS_GROUP_ID
, PBG.LEGISLATION_CODE
, PAPF.LAST_NAME
, PAPF.FIRST_NAME
, PAPF.PER_INFORMATION18
, PAPF.PER_INFORMATION19
, PAPF.FULL_NAME
, PAPF.GLOBAL_NAME
, PAPF.LOCAL_NAME
, PCR.CONTACT_TYPE
, SUBSTRB(HR_GENERAL.DECODE_LOOKUP('CONTACT'
, PCR.CONTACT_TYPE)
, 1
, 80)
, PAPF.SEX
, SUBSTRB(HR_GENERAL.DECODE_LOOKUP('SEX'
, PAPF.SEX)
, 1
, 80)
, PAPF.DATE_OF_BIRTH
, TRUNC(MONTHS_BETWEEN(LEAST(NVL(PAPF.DATE_OF_DEATH
, FS.EFFECTIVE_DATE)
, FS.EFFECTIVE_DATE)
, PAPF.DATE_OF_BIRTH)/12)
, PAPF.DATE_OF_DEATH
, PAPF.EFFECTIVE_START_DATE
, PAPF.EFFECTIVE_END_DATE
, PCR.CONT_ATTRIBUTE_CATEGORY
, PCR.CONT_ATTRIBUTE1
, PCR.CONT_ATTRIBUTE2
, PCR.CONT_ATTRIBUTE3
, PCR.CONT_ATTRIBUTE4
, PCR.CONT_ATTRIBUTE5
, PCR.CONT_ATTRIBUTE6
, PCR.CONT_ATTRIBUTE7
, PCR.CONT_ATTRIBUTE8
, PCR.CONT_ATTRIBUTE9
, PCR.CONT_ATTRIBUTE10
, PCR.CONT_ATTRIBUTE11
, PCR.CONT_ATTRIBUTE12
, PCR.CONT_ATTRIBUTE13
, PCR.CONT_ATTRIBUTE14
, PCR.CONT_ATTRIBUTE15
, PCR.CONT_ATTRIBUTE16
, PCR.CONT_ATTRIBUTE17
, PCR.CONT_ATTRIBUTE18
, PCR.CONT_ATTRIBUTE19
, PCR.CONT_ATTRIBUTE20
, PCR.LAST_UPDATE_DATE
, PCR.LAST_UPDATED_BY
, PCR.LAST_UPDATE_LOGIN
, PCR.CREATED_BY
, PCR.CREATION_DATE
, PCR.CONT_INFORMATION_CATEGORY
, PCR.CONT_INFORMATION1
, PCR.CONT_INFORMATION2
, PCR.CONT_INFORMATION3
, PCR.CONT_INFORMATION4
, PCR.CONT_INFORMATION5
, PCR.CONT_INFORMATION6
, PCR.CONT_INFORMATION7
, PCR.CONT_INFORMATION8
, PCR.CONT_INFORMATION9
, PCR.CONT_INFORMATION10
, PCR.CONT_INFORMATION11
, PCR.CONT_INFORMATION12
, PCR.CONT_INFORMATION13
, PCR.CONT_INFORMATION14
, PCR.CONT_INFORMATION15
, PCR.CONT_INFORMATION16
, PCR.CONT_INFORMATION17
, PCR.CONT_INFORMATION18
, PCR.CONT_INFORMATION19
, PCR.CONT_INFORMATION20
, PCR.DATE_START
, PCR.DATE_END
FROM PER_ALL_PEOPLE_F PAPF
, PER_CONTACT_RELATIONSHIPS PCR
, PER_BUSINESS_GROUPS PBG
, FND_SESSIONS FS
WHERE PAPF.PERSON_ID = PCR.CONTACT_PERSON_ID
AND PAPF.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
AND FS.SESSION_ID = USERENV('SESSIONID')
AND FS.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND FS.EFFECTIVE_DATE BETWEEN NVL(PCR.DATE_START
, PAPF.EFFECTIVE_START_DATE)
AND NVL(PCR.DATE_END
, PAPF.EFFECTIVE_END_DATE)