ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4728D51A9E05362FB09050512Y 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 SSP SSPRPSSP_XML US SSP Statutory Sick Pay Imported from BI Publisher Description: Statutory Sick Pay Report Application: SSP Source: Statutory Sick Pay Report (XML) Short Name: SSPRPSSP_XML DB package: SSP_SSPRPSSP_XMLP_PKG BI Publisher :c_business_group_name :c_payroll_name :c_person_name :c_report_subtitle :p_business_group_id :p_conc_request_id :p_date_from :p_date_to :p_payroll_id :p_person_id :p_session_date 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Business Group ID 2 10 :p_date_from Date Y US Start Date Start Date 3 20 :p_date_to Date Y US End Date End Date 4 30 :p_payroll_id LOV Oracle PAY_SRS_PAYROLL Y select pay.payroll_id id, pay.payroll_name value, null description from pay_payrolls_f pay,fnd_sessions fnd where pay.business_group_id= fnd_global.per_business_group_id and fnd.session_id=userenv('sessionid') and fnd.effective_date between pay.effective_start_date and pay.effective_end_date order by pay.payroll_name US Payroll Name Payroll Name 5 40 :p_session_date Date select effective_date from fnd_sessions where session_id = userenv('SESSIONID') Y US PER_DATES_STANDARD Session Date 6 50 :p_person_id LOV Oracle SSP_ALL_PEOPLE Y select person_id id, full_name value, null description from per_people_f p where (p.business_group_id= fnd_global.per_business_group_id and ((to_date(:$flex$.per_dates_standard, 'YYYY/MM/DD HH24:MI:SS') between p.effective_start_date and p.effective_end_date) or (to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') >p.effective_end_date and p.effective_end_date= (select max(p1.effective_end_date) from per_all_people_f p1 where p1.person_id=p.person_id)) or (to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') <p.effective_start_date and p.effective_start_date=p.start_date))) order by p.full_name US Person Name Person Name