SSP Statutory Shared Parental Adoption Pay

SELECT 	pay.payroll_id								M_PAYROLL_ID,
	per.person_id								    M_PERSON_ID,
	asg.assignment_id								M_ASSIGNMENT_ID,
	mat.maternity_id								M_MATERNITY_ID,
	per.full_name							        M_EMPLOYEE_NAME,
	per.employee_number							    M_EMPLOYEE_NUMBER,
	per.national_identifier							M_NI_NUMBER,
	mat.mpp_start_date								M_START_DATE,
 	next_day(mat.mpp_start_date + ele_info.maximum_mpp  , to_char(to_date('18/06/2005', 'DD/MM/RRRR'), 'DY'))				M_END_DATE,
	mat.PLACEMENT_date						    M_BIRTH_DATE,
	mat.pay_smp_as_lump_sum 			 	 	    M_LUMP_SUM,
    mat.due_date                                    M_DUE_DATE   ,
 mat.matching_date M_MATCHING_DATE, 
	SSP_SHARED_ADOPTION_PAY.c_sppaformula(mat.due_date, mat.matching_date, per.person_id) C_SMP,
	SSP_SHARED_ADOPTION_PAY.C_EWC_p C_EWC,
	SSP_SHARED_ADOPTION_PAY.C_WQ_p C_WQ,
	SSP_SHARED_ADOPTION_PAY.C_AVG_EARNINGS_p C_AVG_EARNINGS
FROM	per_people_f			per,
    	ssp_maternities			mat,
    	per_assignments_f			asg,
    	pay_payrolls_f			pay,
(
 	select	to_number (ele1.element_information4) * 7 MAXIMUM_MPP
	from	pay_element_types_f ele1
	where	ele1.element_name = :C_ELEMENT_NAME
 	and	:p_session_date between ele1.effective_start_date
					and ele1.effective_end_date
) ele_info 
WHERE	per.person_id			= asg.person_id
AND	asg.payroll_id			    = pay.payroll_id
AND mat.person_id               = asg.person_id 
AND	per.business_group_id		= :P_BUSINESS_GROUP_ID
AND	pay.payroll_id  			= nvl(:P_PAYROLL_ID, pay.payroll_id)
AND	per.person_id   			= nvl(:P_PERSON_ID, per.person_id)
AND	asg.primary_flag            = 'Y' 
AND	:P_SESSION_DATE between per.effective_start_date and per.effective_end_date
AND	:P_SESSION_DATE between asg.effective_start_date and asg.effective_end_date
AND	:P_SESSION_DATE between pay.effective_start_date and pay.effective_end_date
AND (
	 mat.spl_start_date BETWEEN :P_DATE_FROM and :P_DATE_TO
OR
     mat.spl_end_date BETWEEN :P_DATE_FROM and :P_DATE_TO
)
and mat.leave_type=:C_LEAVE_TYPE