VIEW: APPS.PER_CONTACT_RELATIONSHIPS_V1

Source

View Text - Preformatted

SELECT DISTINCT CON.PERSON_ID , CON.CONTACT_PERSON_ID , CON.CONTACT_PERSON_ID , CON.PERSON_ID , P.LAST_NAME , P.FIRST_NAME , P.MIDDLE_NAMES , P.TITLE , P.PRE_NAME_ADJUNCT , P.SUFFIX , P.SEX , P.DATE_OF_BIRTH , TRUNC(MONTHS_BETWEEN(FND.EFFECTIVE_DATE, P.DATE_OF_BIRTH)/12) , P.EMPLOYEE_NUMBER , P.APPLICANT_NUMBER , P.NATIONAL_IDENTIFIER , P.FULL_NAME , P.ORDER_NAME , T.MEANING , P.PERSON_TYPE_ID /* , PTTL.USER_PERSON_TYPE */ ,nvl(hr_person_type_usage_info.get_user_person_type(fnd.effective_date,p.person_id),hr_person_type_usage_info.get_user_person_type(p.effective_start_date,p.person_id)) , PT.SYSTEM_PERSON_TYPE , P.CURRENT_EMPLOYEE_FLAG , P.CURRENT_APPLICANT_FLAG , P.CURRENT_EMP_OR_APL_FLAG , P.REGISTERED_DISABLED_FLAG , P.ATTRIBUTE_CATEGORY , P.ATTRIBUTE1 , P.ATTRIBUTE2 , P.ATTRIBUTE3 , P.ATTRIBUTE4 , P.ATTRIBUTE5 , P.ATTRIBUTE6 , P.ATTRIBUTE7 , P.ATTRIBUTE8 , P.ATTRIBUTE9 , P.ATTRIBUTE10 , P.ATTRIBUTE11 , P.ATTRIBUTE12 , P.ATTRIBUTE13 , P.ATTRIBUTE14 , P.ATTRIBUTE15 , P.ATTRIBUTE16 , P.ATTRIBUTE17 , P.ATTRIBUTE18 , P.ATTRIBUTE19 , P.ATTRIBUTE20 , P.ATTRIBUTE21 , P.ATTRIBUTE22 , P.ATTRIBUTE23 , P.ATTRIBUTE24 , P.ATTRIBUTE25 , P.ATTRIBUTE26 , P.ATTRIBUTE27 , P.ATTRIBUTE28 , P.ATTRIBUTE29 , P.ATTRIBUTE30 , P.COMMENT_ID , P.PER_INFORMATION_CATEGORY , P.PER_INFORMATION1 , P.PER_INFORMATION2 , P.PER_INFORMATION3 , P.PER_INFORMATION4 , P.PER_INFORMATION5 , P.PER_INFORMATION6 , P.PER_INFORMATION7 , P.PER_INFORMATION8 , P.PER_INFORMATION9 , P.PER_INFORMATION10 , P.PER_INFORMATION11 , P.PER_INFORMATION12 , P.PER_INFORMATION13 , P.PER_INFORMATION14 , P.PER_INFORMATION15 , P.PER_INFORMATION16 , P.PER_INFORMATION17 , P.PER_INFORMATION18 , P.PER_INFORMATION19 , P.PER_INFORMATION20 , P.PER_INFORMATION21 , P.PER_INFORMATION22 , P.PER_INFORMATION23 , P.PER_INFORMATION24 , P.PER_INFORMATION25 , P.PER_INFORMATION26 , P.PER_INFORMATION27 , P.PER_INFORMATION28 , P.PER_INFORMATION29 , P.PER_INFORMATION30 , P.KNOWN_AS , P.DATE_OF_DEATH , P.CREATED_BY , P.CREATION_DATE , P.LAST_UPDATED_BY , P.LAST_UPDATE_DATE , P.LAST_UPDATE_LOGIN , P.NPW_NUMBER FROM HR_LOOKUPS T ,PER_PERSON_TYPES_TL PTTL ,PER_PERSON_TYPES PT ,PER_ALL_PEOPLE_F P ,PER_CONTACT_RELATIONSHIPS CON ,FND_SESSIONS FND WHERE T.LOOKUP_TYPE(+) = 'TITLE' AND T.LOOKUP_CODE(+) = P.TITLE AND CON.CONTACT_PERSON_ID = P.PERSON_ID AND P.PERSON_TYPE_ID = PT.PERSON_TYPE_ID AND PT.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID AND PTTL.LANGUAGE = userenv('LANG') AND FND.SESSION_ID = USERENV('sessionid') AND ((FND.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE) OR (NOT EXISTS (SELECT NULL FROM PER_ALL_PEOPLE_F P2 WHERE P2.PERSON_ID = P.PERSON_ID AND FND.EFFECTIVE_DATE BETWEEN P2.EFFECTIVE_START_DATE AND P2.EFFECTIVE_END_DATE) AND P.EFFECTIVE_START_DATE = P.START_DATE))

View Text - HTML Formatted

SELECT DISTINCT CON.PERSON_ID
, CON.CONTACT_PERSON_ID
, CON.CONTACT_PERSON_ID
, CON.PERSON_ID
, P.LAST_NAME
, P.FIRST_NAME
, P.MIDDLE_NAMES
, P.TITLE
, P.PRE_NAME_ADJUNCT
, P.SUFFIX
, P.SEX
, P.DATE_OF_BIRTH
, TRUNC(MONTHS_BETWEEN(FND.EFFECTIVE_DATE
, P.DATE_OF_BIRTH)/12)
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER
, P.NATIONAL_IDENTIFIER
, P.FULL_NAME
, P.ORDER_NAME
, T.MEANING
, P.PERSON_TYPE_ID /*
, PTTL.USER_PERSON_TYPE */
, NVL(HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(FND.EFFECTIVE_DATE
, P.PERSON_ID)
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(P.EFFECTIVE_START_DATE
, P.PERSON_ID))
, PT.SYSTEM_PERSON_TYPE
, P.CURRENT_EMPLOYEE_FLAG
, P.CURRENT_APPLICANT_FLAG
, P.CURRENT_EMP_OR_APL_FLAG
, P.REGISTERED_DISABLED_FLAG
, P.ATTRIBUTE_CATEGORY
, P.ATTRIBUTE1
, P.ATTRIBUTE2
, P.ATTRIBUTE3
, P.ATTRIBUTE4
, P.ATTRIBUTE5
, P.ATTRIBUTE6
, P.ATTRIBUTE7
, P.ATTRIBUTE8
, P.ATTRIBUTE9
, P.ATTRIBUTE10
, P.ATTRIBUTE11
, P.ATTRIBUTE12
, P.ATTRIBUTE13
, P.ATTRIBUTE14
, P.ATTRIBUTE15
, P.ATTRIBUTE16
, P.ATTRIBUTE17
, P.ATTRIBUTE18
, P.ATTRIBUTE19
, P.ATTRIBUTE20
, P.ATTRIBUTE21
, P.ATTRIBUTE22
, P.ATTRIBUTE23
, P.ATTRIBUTE24
, P.ATTRIBUTE25
, P.ATTRIBUTE26
, P.ATTRIBUTE27
, P.ATTRIBUTE28
, P.ATTRIBUTE29
, P.ATTRIBUTE30
, P.COMMENT_ID
, P.PER_INFORMATION_CATEGORY
, P.PER_INFORMATION1
, P.PER_INFORMATION2
, P.PER_INFORMATION3
, P.PER_INFORMATION4
, P.PER_INFORMATION5
, P.PER_INFORMATION6
, P.PER_INFORMATION7
, P.PER_INFORMATION8
, P.PER_INFORMATION9
, P.PER_INFORMATION10
, P.PER_INFORMATION11
, P.PER_INFORMATION12
, P.PER_INFORMATION13
, P.PER_INFORMATION14
, P.PER_INFORMATION15
, P.PER_INFORMATION16
, P.PER_INFORMATION17
, P.PER_INFORMATION18
, P.PER_INFORMATION19
, P.PER_INFORMATION20
, P.PER_INFORMATION21
, P.PER_INFORMATION22
, P.PER_INFORMATION23
, P.PER_INFORMATION24
, P.PER_INFORMATION25
, P.PER_INFORMATION26
, P.PER_INFORMATION27
, P.PER_INFORMATION28
, P.PER_INFORMATION29
, P.PER_INFORMATION30
, P.KNOWN_AS
, P.DATE_OF_DEATH
, P.CREATED_BY
, P.CREATION_DATE
, P.LAST_UPDATED_BY
, P.LAST_UPDATE_DATE
, P.LAST_UPDATE_LOGIN
, P.NPW_NUMBER
FROM HR_LOOKUPS T
, PER_PERSON_TYPES_TL PTTL
, PER_PERSON_TYPES PT
, PER_ALL_PEOPLE_F P
, PER_CONTACT_RELATIONSHIPS CON
, FND_SESSIONS FND
WHERE T.LOOKUP_TYPE(+) = 'TITLE'
AND T.LOOKUP_CODE(+) = P.TITLE
AND CON.CONTACT_PERSON_ID = P.PERSON_ID
AND P.PERSON_TYPE_ID = PT.PERSON_TYPE_ID
AND PT.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID
AND PTTL.LANGUAGE = USERENV('LANG')
AND FND.SESSION_ID = USERENV('SESSIONID')
AND ((FND.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE) OR (NOT EXISTS (SELECT NULL
FROM PER_ALL_PEOPLE_F P2
WHERE P2.PERSON_ID = P.PERSON_ID
AND FND.EFFECTIVE_DATE BETWEEN P2.EFFECTIVE_START_DATE
AND P2.EFFECTIVE_END_DATE)
AND P.EFFECTIVE_START_DATE = P.START_DATE))