SSP Statutory Sick Pay

SELECT distinct	asg.payroll_id							M_PAYROLL_ID,
		per.person_id							M_PERSON_ID,
		abs.absence_attendance_id					M_ABSENCE_ID,
		asg.assignment_id						M_ASSIGNMENT_ID,
		per.full_name							M_EMPLOYEE_NAME,
		per.employee_number						M_EMPLOYEE_NUMBER,
		per.national_identifier						M_NI_NUMBER,
		abs.sickness_start_date						M_LINKED_PIW_START,
		abs.sickness_start_date						M_SICKNESS_START_DATE,
		ecl.average_earnings_amount					M_AVG_EARNINGS,
		pos.prior_employment_ssp_weeks					M_SSP1L_WEEKS,
		pos.prior_employment_ssp_paid_to				M_SSP1L_DATE
FROM		per_people_f			per,
		per_absence_attendances		abs,
		per_absence_attendance_types	abt,
		ssp_earnings_calculations	ecl,
		per_periods_of_service		pos,
		per_assignments_f		asg
WHERE		per.business_group_id		= :P_BUSINESS_GROUP_ID
AND		abt.absence_category		= 'S'
AND		abs.person_id                   = per.person_id			
AND		abs.absence_attendance_type_id	= abt.absence_attendance_type_id
AND		per.person_id			= ecl.person_id
AND             ecl.effective_date              = abs.sickness_start_date 
AND		per.person_id			= pos.person_id
AND		per.person_id			= asg.person_id
AND		asg.payroll_id			= nvl(:P_PAYROLL_ID, asg.payroll_id)
AND		per.person_id			= nvl(:P_PERSON_ID, per.person_id)
AND             asg.period_of_service_id=pos.period_of_service_id 
and             asg.assignment_type='E'
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 per.effective_start_date and per.effective_end_date
AND		:P_SESSION_DATE			
between asg.effective_start_date and asg.effective_end_date
AND	(
	    (abs.linked_absence_id		is null
	     AND      (
		      (abs.sickness_start_date 		between :P_DATE_FROM and :P_DATE_TO)
	              OR	  
		      (abs.sickness_end_date		between :P_DATE_FROM and :P_DATE_TO)
		      )
	    )
             OR	exists (
			SELECT	abs2.linked_absence_id
			FROM	per_absence_attendances abs2
			WHERE	abs2.linked_absence_id = abs.absence_attendance_id
			AND	(
				  (abs2.sickness_start_date 	between :P_DATE_FROM and :P_DATE_TO)
			      OR  (abs2.sickness_end_date	between :P_DATE_FROM and :P_DATE_TO)
				)
			)
	)
ORDER BY	asg.payroll_id,
		per.full_name,
		abs.sickness_start_date,per.person_id,abs.absence_attendance_id,asg.assignment_id,per.employee_number,per.national_identifier,abs.sickness_start_date,ecl.average_earnings_amount,pos.prior_employment_ssp_weeks,pos.prior_employment_ssp_paid_to