PAY Superannuation Contribution

select per.full_name,
	floor((:P_END_DATE-per.date_of_birth)/365) age,
	paa.assignment_id,
	paa.assignment_number,
                  pcak.concatenated_segments cost_centre, 
	&cp_sgc_rate_1 CF_sgc_rate,
	PAY_PYAUSGC_XMLP_PKG.CP_sgc_rate_1_p CP_sgc_rate_1, 
	PAY_PYAUSGC_XMLP_PKG.cf_payausgc_pkbformula(paa.assignment_id,floor((:P_END_DATE-per.date_of_birth)/365)) CF_payausgc_pkb,
	PAY_PYAUSGC_XMLP_PKG.CP_start_date_p CP_start_date,
	PAY_PYAUSGC_XMLP_PKG.CP_end_date_p CP_end_date,
	PAY_PYAUSGC_XMLP_PKG.CP_super_sal_p CP_super_sal,
	PAY_PYAUSGC_XMLP_PKG.CP_compliance_mesg_p CP_compliance_mesg,
	PAY_PYAUSGC_XMLP_PKG.CP_warning_mesg_p CP_warning_mesg,
	PAY_PYAUSGC_XMLP_PKG.CP_sgc_contr_p CP_sgc_contr
from
    per_people_f per,
    per_assignments_f paa,
    per_periods_of_service_v ppos,
    pay_cost_allocation_keyflex pcak,
    pay_cost_allocations_f pca
 where
    per.person_id=paa.person_id
	and paa.period_of_service_id = ppos.period_of_service_id
    and exists 
	       (select paa.assignment_id 
		from
 			pay_run_results prr,
 			pay_assignment_actions paa1,
   			pay_element_types_f pet,
			pay_payroll_actions ppa,
			per_time_periods ptp
		where
 			prr.element_type_id=pet.element_type_id and
 			paa1.assignment_action_id=prr.assignment_action_id and
 			pet.element_name='Superannuation Contribution' and
			ppa.payroll_action_id=paa1.payroll_action_id and
			ppa.payroll_id = ptp.payroll_id and
			ppa.time_period_id = ptp.time_period_id and
			ppa.action_status='C' and
			ppa.action_type in ('R','Q') and
			ptp.end_date between :P_START_DATE and :P_END_DATE and
			ppa.effective_date between :P_START_DATE and :P_END_DATE and
	                                paa.assignment_id =paa1.assignment_id and
                                                paa1.tax_unit_id = :P_REGISTERED_EMPLOYER) 
    and per.person_id=ppos.person_id
    and (ppos.actual_termination_date >= :P_START_DATE  OR
            ppos.actual_termination_date is null)
    and nvl(ppos.actual_termination_date,:P_END_DATE)
            between per.effective_start_date and per.effective_end_Date 
    and pcak.cost_allocation_keyflex_id(+)=pca.cost_allocation_keyflex_id
    and pca.assignment_id(+) = paa.assignment_id
    and nvl(ppos.actual_termination_date,:P_END_DATE)  between paa.effective_start_date and paa.effective_end_date
    and (:P_START_DATE  between pca.effective_start_date and pca.effective_end_date or pca.effective_start_date is null) 
    and (:P_ALL_EXCEPTION = 'ALL'  OR
           (:P_ALL_EXCEPTION <> 'ALL' 
and exists (
           select 1 from dual where pay_au_sgc_pkg.compliance_mesg(paa.assignment_id,
           floor((sysdate-per.date_of_birth)/365),:P_END_DATE,:P_SGC_RATE,:P_BUSINESS_GROUP_ID,:P_REGISTERED_EMPLOYER,'AU') is                 not null  )))
 and (pcak.cost_allocation_keyflex_id =:p_cost_centre or
                 :p_cost_centre is null)
                  and (per.employee_number=:P_EMPLOYEE_NUMBER OR
                 :P_EMPLOYEE_NUMBER is null)
order by nvl(per.order_name,per.full_name)