Product: | PER - Human Resources |
---|---|
Description: | Used to support user-interface. |
Implementation/DBA Data: | APPS.PER_CONTACT_RELATIONSHIPS_V2 |
SELECT CON.ROWID
, CON.CONTACT_RELATIONSHIP_ID
, CON.BUSINESS_GROUP_ID + 0
, CON.PERSON_ID
, CON.CONTACT_PERSON_ID
, CON.CONTACT_PERSON_ID
, CON.PERSON_ID
, CON.CONTACT_TYPE
, C.MEANING
, CON.COMMENTS
, CON.DEPENDENT_FLAG
, CON.BENEFICIARY_FLAG
, CON.THIRD_PARTY_PAY_FLAG
, CON.BONDHOLDER_FLAG
, CON.PRIMARY_CONTACT_FLAG
, CON.DATE_START
, BEN1.NAME
, CON.START_LIFE_REASON_ID
, CON.DATE_END
, BEN2.NAME
, CON.END_LIFE_REASON_ID
, CON.RLTD_PER_RSDS_W_DSGNTR_FLAG
, CON.PERSONAL_FLAG
, CON.SEQUENCE_NUMBER
, CON.REQUEST_ID
, CON.PROGRAM_APPLICATION_ID
, CON.PROGRAM_ID
, CON.PROGRAM_UPDATE_DATE
, CON.CONT_ATTRIBUTE_CATEGORY
, CON.CONT_ATTRIBUTE1
, CON.CONT_ATTRIBUTE2
, CON.CONT_ATTRIBUTE3
, CON.CONT_ATTRIBUTE4
, CON.CONT_ATTRIBUTE5
, CON.CONT_ATTRIBUTE6
, CON.CONT_ATTRIBUTE7
, CON.CONT_ATTRIBUTE8
, CON.CONT_ATTRIBUTE9
, CON.CONT_ATTRIBUTE10
, CON.CONT_ATTRIBUTE11
, CON.CONT_ATTRIBUTE12
, CON.CONT_ATTRIBUTE13
, CON.CONT_ATTRIBUTE14
, CON.CONT_ATTRIBUTE15
, CON.CONT_ATTRIBUTE16
, CON.CONT_ATTRIBUTE17
, CON.CONT_ATTRIBUTE18
, CON.CONT_ATTRIBUTE19
, CON.CONT_ATTRIBUTE20
, CON.CONT_INFORMATION_CATEGORY
, CON.CONT_INFORMATION1
, CON.CONT_INFORMATION2
, CON.CONT_INFORMATION3
, CON.CONT_INFORMATION4
, CON.CONT_INFORMATION5
, CON.CONT_INFORMATION6
, CON.CONT_INFORMATION7
, CON.CONT_INFORMATION8
, CON.CONT_INFORMATION9
, CON.CONT_INFORMATION10
, CON.CONT_INFORMATION11
, CON.CONT_INFORMATION12
, CON.CONT_INFORMATION13
, CON.CONT_INFORMATION14
, CON.CONT_INFORMATION15
, CON.CONT_INFORMATION16
, CON.CONT_INFORMATION17
, CON.CONT_INFORMATION18
, CON.CONT_INFORMATION19
, CON.CONT_INFORMATION20
, CON.LAST_UPDATE_DATE
, CON.LAST_UPDATED_BY
, CON.LAST_UPDATE_LOGIN
, CON.CREATED_BY
, CON.CREATION_DATE
FROM HR_LOOKUPS C
, PER_CONTACT_RELATIONSHIPS CON
, FND_SESSIONS FND
, BEN_LER_F_VL BEN1
, BEN_LER_F_VL BEN2
WHERE C.LOOKUP_TYPE = 'CONTACT'
AND C.LOOKUP_CODE = CON.CONTACT_TYPE
AND FND.SESSION_ID = USERENV('SESSIONID')
AND BEN1.LER_ID(+) = CON.START_LIFE_REASON_ID
AND FND.EFFECTIVE_DATE BETWEEN NVL(BEN1.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(BEN1.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND BEN2.LER_ID(+)= CON.END_LIFE_REASON_ID
AND FND.EFFECTIVE_DATE BETWEEN NVL(BEN2.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(BEN2.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)