View: PER_JP_CONTACT_RELATIONSHIPS_V

Product:	PER - Human Resources
Description:	This view is used by JP Contact List folder and JP Social Insurance and Income Tax on Dependent window.
Implementation/DBA Data:	APPS.PER_JP_CONTACT_RELATIONSHIPS_V

View Text

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)

Columns