eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_KR_SEP_HI_ADJ_V

Source

View Text - Preformatted


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')

View Text - HTML Formatted

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')