View: PER_JP_SI_DEPENDENT_TRANSFER_V

Product:	PER - Human Resources
Description:	This view is used by Japanese statutory report (Notification of Health Insurance Dependents / National Pension Type 3 Insured)
Implementation/DBA Data:	APPS.PER_JP_SI_DEPENDENT_TRANSFER_V

View Text

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

Columns