VIEW: APPS.PER_JP_SI_DEPENDENT_TRANSFER_V

Source

View Text - Preformatted

SELECT /*+ ORDERED NO_MERGE(dpnttrf) USE_NL(papf_e paaf papf) INDEX(PAPF_E PER_PEOPLE_F_PK) INDEX(PAAF PER_ASSIGNMENTS_F_N12) INDEX(PAPF PER_PEOPLE_F_PK) */ papf_e.business_group_id business_group_id, dpnttrf.person_id person_id, papf_e.employee_number employee_number, papf_e.per_information18 last_name_kanji, papf_e.per_information19 first_name_kanji, paaf.assignment_id assignment_id, dpnttrf.contact_person_id contact_person_id, dpnttrf.dependent_type dependent_type, dpnttrf.transfer_type transfer_type, dpnttrf.transfer_date transfer_date, dpnttrf.transfer_reason_code transfer_reason_code, dpnttrf.transfer_reason_descrpition transfer_reason_descrpition, dpnttrf.type3_disqualified_notice type3_disqualified_notice from (select /*+ ORDERED USE_NL(pceif) INDEX(PCEIF PER_CONTACT_RELATIONSHIPS_N2) */ pcr.person_id person_id, pcr.contact_person_id contact_person_id, pcr.date_start pcr_sd, pcr.date_end pcr_ed, pceif.effective_start_date pceif_esd, pceif.effective_end_date pceif_eed, dpntt.dependent_type dependent_type, trft.transfer_type transfer_type, decode(trft.transfer_type, 'I',decode(dpntt.dependent_type, 'S',fnd_date.canonical_to_date(pceif.cei_information3), '3',fnd_date.canonical_to_date(pceif.cei_information13), 'D',fnd_date.canonical_to_date(pceif.cei_information1)), 'E',decode(dpntt.dependent_type, 'S',fnd_date.canonical_to_date(pceif.cei_information10)-1, '3',fnd_date.canonical_to_date(pceif.cei_information16)-1, 'D',fnd_date.canonical_to_date(pceif.cei_information6)-1)) transfer_date, substrb(decode(trft.transfer_type, 'I',decode(dpntt.dependent_type, 'S',pceif.cei_information4, '3',pceif.cei_information14, 'D',null), 'E',decode(dpntt.dependent_type, 'S',pceif.cei_information11, '3',pceif.cei_information17, 'D',null)),1,30) transfer_reason_code, decode(trft.transfer_type, 'I',decode(dpntt.dependent_type, 'S',pceif.cei_information5, '3',pceif.cei_information15, 'D',pceif.cei_information2), 'E',decode(dpntt.dependent_type, 'S',pceif.cei_information12, '3',pceif.cei_information18, 'D',pceif.cei_information7)) transfer_reason_descrpition, substrb(decode(trft.transfer_type, 'E',decode(dpntt.dependent_type, '3',pceif.cei_information19)),1,1) type3_disqualified_notice from per_contact_relationships pcr, per_contact_extra_info_f pceif, (select 'S' dependent_type from dual union select '3' dependent_type from dual union select 'D' dependent_type from dual) dpntt, (select 'I' transfer_type from dual union select 'E' transfer_type from dual) trft where pcr.cont_information1 = 'Y' and pcr.cont_information_category = 'JP' and pceif.contact_relationship_id = pcr.contact_relationship_id and ((dpntt.dependent_type <> 'D' and pcr.contact_type = 'S') or (dpntt.dependent_type = 'D' and pcr.contact_type <> 'S')) and pceif.information_type = decode(dpntt.dependent_type,'D','JP_HI_DEPENDENT','JP_HI_SPOUSE')) dpnttrf, per_all_people_f papf_e, per_all_assignments_f paaf, per_all_people_f papf where dpnttrf.transfer_date between dpnttrf.pceif_esd and dpnttrf.pceif_eed and dpnttrf.transfer_date between nvl(dpnttrf.pcr_sd,dpnttrf.transfer_date) and nvl(dpnttrf.pcr_ed,dpnttrf.transfer_date) and papf_e.person_id = dpnttrf.person_id and dpnttrf.transfer_date between papf_e.effective_start_date and papf_e.effective_end_date and paaf.person_id = papf_e.person_id and paaf.primary_flag = 'Y' and dpnttrf.transfer_date between paaf.effective_start_date and paaf.effective_end_date and papf.person_id = dpnttrf.contact_person_id and dpnttrf.transfer_date between papf.effective_start_date and papf.effective_end_date

View Text - HTML Formatted

SELECT /*+ ORDERED NO_MERGE(DPNTTRF) USE_NL(PAPF_E PAAF PAPF) INDEX(PAPF_E PER_PEOPLE_F_PK) INDEX(PAAF PER_ASSIGNMENTS_F_N12) INDEX(PAPF PER_PEOPLE_F_PK) */ PAPF_E.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, DPNTTRF.PERSON_ID PERSON_ID
, PAPF_E.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAPF_E.PER_INFORMATION18 LAST_NAME_KANJI
, PAPF_E.PER_INFORMATION19 FIRST_NAME_KANJI
, PAAF.ASSIGNMENT_ID ASSIGNMENT_ID
, DPNTTRF.CONTACT_PERSON_ID CONTACT_PERSON_ID
, DPNTTRF.DEPENDENT_TYPE DEPENDENT_TYPE
, DPNTTRF.TRANSFER_TYPE TRANSFER_TYPE
, DPNTTRF.TRANSFER_DATE TRANSFER_DATE
, DPNTTRF.TRANSFER_REASON_CODE TRANSFER_REASON_CODE
, DPNTTRF.TRANSFER_REASON_DESCRPITION TRANSFER_REASON_DESCRPITION
, DPNTTRF.TYPE3_DISQUALIFIED_NOTICE TYPE3_DISQUALIFIED_NOTICE
FROM (SELECT /*+ ORDERED USE_NL(PCEIF) INDEX(PCEIF PER_CONTACT_RELATIONSHIPS_N2) */ PCR.PERSON_ID PERSON_ID
, PCR.CONTACT_PERSON_ID CONTACT_PERSON_ID
, PCR.DATE_START PCR_SD
, PCR.DATE_END PCR_ED
, PCEIF.EFFECTIVE_START_DATE PCEIF_ESD
, PCEIF.EFFECTIVE_END_DATE PCEIF_EED
, DPNTT.DEPENDENT_TYPE DEPENDENT_TYPE
, TRFT.TRANSFER_TYPE TRANSFER_TYPE
, DECODE(TRFT.TRANSFER_TYPE
, 'I'
, DECODE(DPNTT.DEPENDENT_TYPE
, 'S'
, FND_DATE.CANONICAL_TO_DATE(PCEIF.CEI_INFORMATION3)
, '3'
, FND_DATE.CANONICAL_TO_DATE(PCEIF.CEI_INFORMATION13)
, 'D'
, FND_DATE.CANONICAL_TO_DATE(PCEIF.CEI_INFORMATION1))
, 'E'
, DECODE(DPNTT.DEPENDENT_TYPE
, 'S'
, FND_DATE.CANONICAL_TO_DATE(PCEIF.CEI_INFORMATION10)-1
, '3'
, FND_DATE.CANONICAL_TO_DATE(PCEIF.CEI_INFORMATION16)-1
, 'D'
, FND_DATE.CANONICAL_TO_DATE(PCEIF.CEI_INFORMATION6)-1)) TRANSFER_DATE
, SUBSTRB(DECODE(TRFT.TRANSFER_TYPE
, 'I'
, DECODE(DPNTT.DEPENDENT_TYPE
, 'S'
, PCEIF.CEI_INFORMATION4
, '3'
, PCEIF.CEI_INFORMATION14
, 'D'
, NULL)
, 'E'
, DECODE(DPNTT.DEPENDENT_TYPE
, 'S'
, PCEIF.CEI_INFORMATION11
, '3'
, PCEIF.CEI_INFORMATION17
, 'D'
, NULL))
, 1
, 30) TRANSFER_REASON_CODE
, DECODE(TRFT.TRANSFER_TYPE
, 'I'
, DECODE(DPNTT.DEPENDENT_TYPE
, 'S'
, PCEIF.CEI_INFORMATION5
, '3'
, PCEIF.CEI_INFORMATION15
, 'D'
, PCEIF.CEI_INFORMATION2)
, 'E'
, DECODE(DPNTT.DEPENDENT_TYPE
, 'S'
, PCEIF.CEI_INFORMATION12
, '3'
, PCEIF.CEI_INFORMATION18
, 'D'
, PCEIF.CEI_INFORMATION7)) TRANSFER_REASON_DESCRPITION
, SUBSTRB(DECODE(TRFT.TRANSFER_TYPE
, 'E'
, DECODE(DPNTT.DEPENDENT_TYPE
, '3'
, PCEIF.CEI_INFORMATION19))
, 1
, 1) TYPE3_DISQUALIFIED_NOTICE
FROM PER_CONTACT_RELATIONSHIPS PCR
, PER_CONTACT_EXTRA_INFO_F PCEIF
, (SELECT 'S' DEPENDENT_TYPE
FROM DUAL UNION SELECT '3' DEPENDENT_TYPE
FROM DUAL UNION SELECT 'D' DEPENDENT_TYPE
FROM DUAL) DPNTT
, (SELECT 'I' TRANSFER_TYPE
FROM DUAL UNION SELECT 'E' TRANSFER_TYPE
FROM DUAL) TRFT
WHERE PCR.CONT_INFORMATION1 = 'Y'
AND PCR.CONT_INFORMATION_CATEGORY = 'JP'
AND PCEIF.CONTACT_RELATIONSHIP_ID = PCR.CONTACT_RELATIONSHIP_ID
AND ((DPNTT.DEPENDENT_TYPE <> 'D'
AND PCR.CONTACT_TYPE = 'S') OR (DPNTT.DEPENDENT_TYPE = 'D'
AND PCR.CONTACT_TYPE <> 'S'))
AND PCEIF.INFORMATION_TYPE = DECODE(DPNTT.DEPENDENT_TYPE
, 'D'
, 'JP_HI_DEPENDENT'
, 'JP_HI_SPOUSE')) DPNTTRF
, PER_ALL_PEOPLE_F PAPF_E
, PER_ALL_ASSIGNMENTS_F PAAF
, PER_ALL_PEOPLE_F PAPF
WHERE DPNTTRF.TRANSFER_DATE BETWEEN DPNTTRF.PCEIF_ESD
AND DPNTTRF.PCEIF_EED
AND DPNTTRF.TRANSFER_DATE BETWEEN NVL(DPNTTRF.PCR_SD
, DPNTTRF.TRANSFER_DATE)
AND NVL(DPNTTRF.PCR_ED
, DPNTTRF.TRANSFER_DATE)
AND PAPF_E.PERSON_ID = DPNTTRF.PERSON_ID
AND DPNTTRF.TRANSFER_DATE BETWEEN PAPF_E.EFFECTIVE_START_DATE
AND PAPF_E.EFFECTIVE_END_DATE
AND PAAF.PERSON_ID = PAPF_E.PERSON_ID
AND PAAF.PRIMARY_FLAG = 'Y'
AND DPNTTRF.TRANSFER_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = DPNTTRF.CONTACT_PERSON_ID
AND DPNTTRF.TRANSFER_DATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE