Product: | PAY - Payroll |
---|---|
Description: | This view returns contact data based on assignment_id and session_date. |
Implementation/DBA Data: | APPS.PAY_KR_CONTACT_V |
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'