SSP Open Sickness Absences

Description
Categories: BI Publisher, Human Resources
Application: SSP
Source: Open Sickness Absences Report (XML)
Short Name: SSPRPOAR_XML
DB package: SSP_SSPRPOAR_XMLP_PKG
Select  distinct 
            rtrim(ppef.Full_name)                 Emp_Name,
            rtrim(ppef.Employee_number)     Emp_number ,
            pabs.Sickness_start_date   Start_date ,
            pabs.sickness_start_date order_date,
            paat.Name                           Sickness_type ,
            nvl(ppay.Payroll_name,Null)       Payroll
from
       Per_absence_attendances          pabs ,
       Per_absence_attendance_types paat,
       Per_people_f                            ppef,
       Per_assignments_f                   pass ,
       Pay_payrolls_f                         ppay
where
       pass.payroll_id = ppay.payroll_id (+)
       and ppef.person_id = pass.person_id
       and pabs.sickness_start_date between pass.effective_start_date
                                                              and pass.effective_end_date
       and pass.primary_flag  =  'Y'
       and ppef.business_group_id  =  :P_bus_grp
       and pabs.person_id  =  ppef.person_id
       and pabs.sickness_start_date between ppef.effective_start_date
                                                              and ppef.effective_end_date
       and pabs.absence_attendance_type_id = paat.absence_attendance_type_id
       and paat.Absence_category = 'S'
       and pabs.sickness_start_date is not null
       and pabs.sickness_end_date is null
       and (   (nvl(:P_payroll,-1) = -1 or pass.payroll_id = :P_payroll)
                                      or pass.payroll_id is null      )
Order by
       5, pabs.Sickness_start_date,
       rtrim(ppef.full_name)
Parameter Name SQL text Validation
Business Group
 
Number
Session Date
 
Date
Payroll Name
 
LOV Oracle