PAY KR Health Insurance Premium Adjustment Annual Earnings

Description
Categories: BI Publisher
Columns: Assignment Action Id, Pei Information1, Actual Termination Date, Cf Sequence Counter, Cf Get Data, Cp Hia Business Place Code, Cp Hia Hi Number, Cp Hia Employee Name, Cp Hia Registration Number, Cp Hia Qualified Date ...
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)