VIEW: APPS.PAY_KR_CONTACT_V

Source

View Text - Preformatted

SELECT pcr.rowid ROW_ID, pcr.contact_relationship_id, pa.assignment_id, pa.assignment_number, pa.person_id, pcr.contact_person_id, pcr.contact_type, substr(HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type),1,80) d_contact_type, pcr_pp.full_name, pcr_pp.national_identifier, pay_kr_entries_pkg.calc_age(pcr_pp.national_identifier,pcr_pp.date_of_birth,fnd.effective_date) age, pcr.cont_information_category, pcr.cont_information1, pcr.cont_information2, pcr.cont_information3, pcr.cont_information4, pcr.cont_information5, pcr.cont_information7, pcr.cont_information8, pcr.cont_information11, pcr.cont_information10, pcr.cont_information12, pcr.cont_information13, pcr.cont_information14, pcr.object_version_number FROM per_contact_relationships pcr, per_assignments_f pa, fnd_sessions fnd, per_people_f pcr_pp WHERE fnd.session_id = userenv('sessionid') AND fnd.effective_date between pa.effective_start_date and pa.effective_end_date AND pa.assignment_type ='E' AND pcr.person_id = pa.person_id AND fnd.effective_date between nvl(pcr.date_start,fnd.effective_date) and decode(pcr.cont_information9,'D',trunc(add_months(nvl(pcr.date_end,fnd.effective_date),12),'YYYY')-1,nvl(pcr.date_end,fnd.effective_date)) AND pcr_pp.person_id = pcr.contact_person_id AND fnd.effective_date between pcr_pp.effective_start_date and pcr_pp.effective_end_date AND pcr.cont_information_category = 'KR' AND pcr.cont_information1 = 'Y'

View Text - HTML Formatted

SELECT PCR.ROWID ROW_ID
, PCR.CONTACT_RELATIONSHIP_ID
, PA.ASSIGNMENT_ID
, PA.ASSIGNMENT_NUMBER
, PA.PERSON_ID
, PCR.CONTACT_PERSON_ID
, PCR.CONTACT_TYPE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CONTACT'
, PCR.CONTACT_TYPE)
, 1
, 80) D_CONTACT_TYPE
, PCR_PP.FULL_NAME
, PCR_PP.NATIONAL_IDENTIFIER
, PAY_KR_ENTRIES_PKG.CALC_AGE(PCR_PP.NATIONAL_IDENTIFIER
, PCR_PP.DATE_OF_BIRTH
, FND.EFFECTIVE_DATE) AGE
, PCR.CONT_INFORMATION_CATEGORY
, PCR.CONT_INFORMATION1
, PCR.CONT_INFORMATION2
, PCR.CONT_INFORMATION3
, PCR.CONT_INFORMATION4
, PCR.CONT_INFORMATION5
, PCR.CONT_INFORMATION7
, PCR.CONT_INFORMATION8
, PCR.CONT_INFORMATION11
, PCR.CONT_INFORMATION10
, PCR.CONT_INFORMATION12
, PCR.CONT_INFORMATION13
, PCR.CONT_INFORMATION14
, PCR.OBJECT_VERSION_NUMBER
FROM PER_CONTACT_RELATIONSHIPS PCR
, PER_ASSIGNMENTS_F PA
, FND_SESSIONS FND
, PER_PEOPLE_F PCR_PP
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PA.ASSIGNMENT_TYPE ='E'
AND PCR.PERSON_ID = PA.PERSON_ID
AND FND.EFFECTIVE_DATE BETWEEN NVL(PCR.DATE_START
, FND.EFFECTIVE_DATE)
AND DECODE(PCR.CONT_INFORMATION9
, 'D'
, TRUNC(ADD_MONTHS(NVL(PCR.DATE_END
, FND.EFFECTIVE_DATE)
, 12)
, 'YYYY')-1
, NVL(PCR.DATE_END
, FND.EFFECTIVE_DATE))
AND PCR_PP.PERSON_ID = PCR.CONTACT_PERSON_ID
AND FND.EFFECTIVE_DATE BETWEEN PCR_PP.EFFECTIVE_START_DATE
AND PCR_PP.EFFECTIVE_END_DATE
AND PCR.CONT_INFORMATION_CATEGORY = 'KR'
AND PCR.CONT_INFORMATION1 = 'Y'