PAY KR Health Insurance Premium Adjustment Annual Earnings

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: KR Health Insurance Premium Adjustment Annual Earnings Report (XML)
Short Name: PAYKRHCL_XML
DB package: PAY_PAYKRHCL_XMLP_PKG
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)