SELECT distinct paa.assignment_action_id,
pei.pei_information1,
pds.actual_termination_date,
PAY_PAYKRHCL_XMLP_PKG.cf_sequence_counterformula(:CF_SEQUENCE_COUNTER) CF_SEQUENCE_COUNTER,
PAY_PAYKRHCL_XMLP_PKG.cf_get_dataformula(paa.assignment_action_id, pds.actual_termination_date) CF_GET_DATA,
PAY_PAYKRHCL_XMLP_PKG.CP_HIA_BUSINESS_PLACE_CODE_p CP_HIA_BUSINESS_PLACE_CODE,
PAY_PAYKRHCL_XMLP_PKG.CP_HIA_HI_NUMBER_p CP_HIA_HI_NUMBER,
PAY_PAYKRHCL_XMLP_PKG.CP_HIA_EMPLOYEE_NAME_p CP_HIA_EMPLOYEE_NAME,
PAY_PAYKRHCL_XMLP_PKG.CP_HIA_REGISTRATION_NUMBER_p CP_HIA_REGISTRATION_NUMBER,
PAY_PAYKRHCL_XMLP_PKG.CP_HIA_QUALIFIED_DATE_p CP_HIA_QUALIFIED_DATE,
PAY_PAYKRHCL_XMLP_PKG.CP_HI_PREM_EE_WO_ADJ_ASG_YTD_p CP_HI_PREM_EE_WO_ADJ_ASG_YTD,
PAY_PAYKRHCL_XMLP_PKG.CP_EARNINGS_SUBJ_HI_ASG_YTD_p CP_EARNINGS_SUBJ_HI_ASG_YTD,
PAY_PAYKRHCL_XMLP_PKG.CP_HI_WORKING_MONTHS_p CP_HI_WORKING_MONTHS,
PAY_PAYKRHCL_XMLP_PKG.CP_NUMBER_OF_MONTHS_PAID_p CP_NUMBER_OF_MONTHS_PAID,
PAY_PAYKRHCL_XMLP_PKG.CP_TERMINATION_DATE_p CP_TERMINATION_DATE
FROM per_people_extra_info pei,
per_assignments_f pa,
pay_assignment_actions paa,
per_periods_of_service pds
WHERE paa.payroll_action_id = :lp_payroll_action_id
AND pa.assignment_id = paa.assignment_id
AND pei.person_id(+) = pa.person_id
AND pei.information_type(+) = 'PER_KR_HEALTH_INSURANCE_INFO'
AND pds.person_id = pa.person_id
AND nvl(pds.actual_termination_date,:lp_reported_date +1) > :lp_reported_date
AND pds.date_start <= :lp_reported_date AND :lp_reported_date between pa.effective_start_date and pa.effective_end_date
ORDER BY to_number(pei.pei_information1) |