VIEW: APPS.PAY_KR_CONT_DETAILS_V

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)