View: PAY_KR_CONT_DETAILS_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_KR_CONT_DETAILS_V

View Text

SELECT PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_NUMBER
, PAP.PERSON_ID
, PCR.CONTACT_TYPE
, HL.MEANING CONT_TYPE_MEANING
, PAP.FULL_NAME
, PAP.DATE_OF_BIRTH CONT_DOB
, PAP.NATIONAL_IDENTIFIER
, PAP.SEX CONT_SEX
, RTRIM(PKA.CITY_PROVINCE || ' ' || DISTRICT || ' ' || PKA.TOWN_VILLAGE) || ' ' || PA.ADDRESS_LINE2 CONT_ADDRESS
, PAP.EFFECTIVE_START_DATE CONT_START_DATE
, PAP.EFFECTIVE_END_DATE CONT_END_DATE
, PAA.EFFECTIVE_START_DATE EMP_START_DATE
, PAA.EFFECTIVE_END_DATE EMP_END_DATE
, PAY_KR_ENTRIES_PKG.CALC_AGE(PAP.NATIONAL_IDENTIFIER
, PAP.DATE_OF_BIRTH
, SYSDATE) AGE
, DECODE(SUBSTR(PAP.NATIONAL_IDENTIFIER
, 8
, 1)
, '5'
, '9'
, '6'
, '9'
, '7'
, '9'
, '8'
, '9'
, '1') CONT_NATIONALITY
, PCR.ROWID ROW_ID
, PCR.CONTACT_RELATIONSHIP_ID
, 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
, PA.DATE_FROM ADDRESS_START_DATE
, PA.DATE_TO ADDRESS_END_DATE
, NVL(PCR.CONT_INFORMATION11
, DECODE(PCR.CONTACT_TYPE
, 'P'
, '1'
, 'S'
, '3'
, 'A'
, '4'
, 'C'
, '4'
, 'R'
, '4'
, 'O'
, '4'
, 'T'
, '4'
, 'BROTHER'
, '5'
, 'SISTER'
, '5'
, '6')) REL_CODE
FROM PER_PEOPLE_F PAP
, PER_ASSIGNMENTS_F PAA
, PER_CONTACT_RELATIONSHIPS PCR
, PER_ADDRESSES PA
, HR_LEG_LOOKUPS HL
, PER_KR_ADDRESSES PKA
WHERE PAP.PERSON_ID = PCR.CONTACT_PERSON_ID
AND NVL(PCR.CONT_INFORMATION1
, 'N') = 'Y'
AND PCR.PERSON_ID = PAA.PERSON_ID
AND PAP.PERSON_ID = PA.PERSON_ID(+)
AND HL.LOOKUP_TYPE = 'CONTACT'
AND HL.LOOKUP_CODE = PCR.CONTACT_TYPE
AND PA.STYLE(+) = 'KR'
AND PA.PRIMARY_FLAG(+) = 'Y'
AND TO_NUMBER(PA.ADD_INFORMATION17) = PKA.POSTAL_CODE_ID(+)

Columns