SSP Statutory Paternity Birth Pay

Description
Categories: BI Publisher, Human Resources
Application: SSP
Source: Statutory Paternity Birth Pay Report (XML)
Short Name: SSPRPSPPB_XML
DB package: SSP_SSPRPSPB_XMLP_PKG
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.actual_birth_date						    M_BIRTH_DATE,
	mat.pay_smp_as_lump_sum 			 	 	    M_LUMP_SUM,
    mat.due_date                                    M_DUE_DATE, 
	SSP_SSPRPSPB_XMLP_PKG.c_sppbformula(mat.due_date, per.person_id) C_SMP,
	SSP_SSPRPSPB_XMLP_PKG.C_EWC_p C_EWC,
	SSP_SSPRPSPB_XMLP_PKG.C_WQ_p C_WQ,
	SSP_SSPRPSPB_XMLP_PKG.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.mpp_start_date		between :P_DATE_FROM and :P_DATE_TO)
	OR (mat.mpp_start_date		<             :P_DATE_FROM
                         AND  next_day(mat.mpp_start_date +  ele_info.maximum_mpp,to_char(to_date('18/06/2005', 'DD/MM/RRRR'), 'DY')) > :P_DATE_FROM)
                  )
and mat.leave_type= :C_LEAVE_TYPE
Parameter Name SQL text Validation
Business Group
 
Number
Report Type
 
LOV Oracle
Person Name
 
LOV Oracle
PER_DATES_STANDARD
 
Date
Payroll Name
 
LOV Oracle
End Date
 
Date
Start Date
 
Date