View: PAY_KR_SEP_HI_ADJ_V

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

View Text

SELECT PAA.ESTABLISHMENT_ID ESTABLISHMENT_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PPEI.PEI_INFORMATION1 HI_NUMBER
, PAP.LAST_NAME||PAP.FIRST_NAME EMPLOYEE_NAME
, PAP.NATIONAL_IDENTIFIER REGISTRATION_NUMBER
, NVL(PPEI.PEI_INFORMATION4
, PPEI.PEI_INFORMATION2) QUALIFIED_DATE
, PPS.ACTUAL_TERMINATION_DATE SEPARATION_DATE
, PPEI.PEI_INFORMATION3 REASON_CODE
, 'P' PERIOD
, HOI1.ORG_INFORMATION1 BP_HI_NUMBER
, PAP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM PER_PEOPLE_F PAP
, PER_PEOPLE_EXTRA_INFO PPEI
, PER_ASSIGNMENTS_F PAA
, PER_PERIODS_OF_SERVICE PPS
, HR_ORGANIZATION_INFORMATION HOI1
WHERE PAP.PERSON_ID = PPEI.PERSON_ID
AND PPEI.INFORMATION_TYPE = 'PER_KR_HEALTH_INSURANCE_INFO'
AND PPEI.PEI_INFORMATION6 IS NULL
AND PAP.PERSON_ID = PAA.PERSON_ID
AND HOI1.ORGANIZATION_ID = PAA.ESTABLISHMENT_ID
AND HOI1.ORG_INFORMATION_CONTEXT = 'KR_HI_INFORMATION'
AND PPS.ACTUAL_TERMINATION_DATE BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND PPS.ACTUAL_TERMINATION_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PPS.PERSON_ID = PAP.PERSON_ID
AND PPS.BUSINESS_GROUP_ID = PAP.BUSINESS_GROUP_ID
AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
AND PPS.ACTUAL_TERMINATION_DATE BETWEEN TO_DATE('01-01-'||TO_CHAR(PPS.ACTUAL_TERMINATION_DATE
, 'RRRR')
, 'DD-MM-RRRR')
AND TO_DATE('31-12-'||TO_CHAR(PPS.ACTUAL_TERMINATION_DATE
, 'RRRR')
, 'DD-MM-RRRR') UNION ALL SELECT PAA.ESTABLISHMENT_ID ESTABLISHMENT_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PPEI.PEI_INFORMATION1 HI_NUMBER
, PAP.LAST_NAME||PAP.FIRST_NAME EMPLOYEE_NAME
, PAP.NATIONAL_IDENTIFIER REGISTRATION_NUMBER
, NVL(PPEI.PEI_INFORMATION4
, PPEI.PEI_INFORMATION2) QUALIFIED_DATE
, PPS.ACTUAL_TERMINATION_DATE SEPARATION_DATE
, PPEI.PEI_INFORMATION3 REASON_CODE
, 'C' PERIOD
, HOI1.ORG_INFORMATION1 BP_HI_NUMBER
, PAP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM PER_PEOPLE_F PAP
, PER_PEOPLE_EXTRA_INFO PPEI
, PER_ASSIGNMENTS_F PAA
, PER_PERIODS_OF_SERVICE PPS
, HR_ORGANIZATION_INFORMATION HOI1
WHERE PAP.PERSON_ID = PPEI.PERSON_ID
AND PPEI.INFORMATION_TYPE = 'PER_KR_HEALTH_INSURANCE_INFO'
AND PPEI.PEI_INFORMATION6 IS NULL
AND PAP.PERSON_ID = PAA.PERSON_ID
AND HOI1.ORGANIZATION_ID = PAA.ESTABLISHMENT_ID
AND HOI1.ORG_INFORMATION_CONTEXT = 'KR_HI_INFORMATION'
AND PPS.ACTUAL_TERMINATION_DATE BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND PPS.ACTUAL_TERMINATION_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PPS.PERSON_ID = PAP.PERSON_ID
AND PPS.BUSINESS_GROUP_ID = PAP.BUSINESS_GROUP_ID
AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
AND PPS.ACTUAL_TERMINATION_DATE >= TO_DATE('01-01-'||TO_CHAR(PPS.ACTUAL_TERMINATION_DATE
, 'RRRR')
, 'DD-MM-RRRR')
AND PPS.ACTUAL_TERMINATION_DATE < TO_DATE('01-03-'||TO_CHAR(PPS.ACTUAL_TERMINATION_DATE
, 'RRRR')
, 'DD-MM-RRRR')

Columns