SSP Statutory Maternity Pay

Description
Categories: BI Publisher, Human Resources
Application: SSP
Source: Statutory Maternity Pay Report (XML)
Short Name: SSPRPSMP_XML
DB package: SSP_SSPRPSMP_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_SSPRPSMP_XMLP_PKG.c_smpformula(mat.due_date, per.person_id) C_SMP,
	SSP_SSPRPSMP_XMLP_PKG.C_EWC_p C_EWC,
	SSP_SSPRPSMP_XMLP_PKG.C_WQ_p C_WQ,
	SSP_SSPRPSMP_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 = 'Statutory Maternity Pay'
	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 is null or
mat.leave_type='MA'
    )
Parameter Name SQL text Validation
Business Group
 
Number
Person Name
 
LOV Oracle
PER_DATES_STANDARD
 
Date
Payroll Name
 
LOV Oracle
End Date
 
Date
Start Date
 
Date