Employee Termination Report

SELECT ppf.employee_number
       , ppf.full_name, ppf.email_address, pps.date_start "Start Date",
       pps.actual_termination_date
                                      ,
       (SELECT l.meaning
          FROM hr_lookups l
         WHERE l.lookup_type(+) = 'LEAV_REAS'
               AND l.lookup_code(+) = pps.leaving_reason) leaving_reason
                                                                       
       ,
       pj.NAME job, houa.NAME organization_name,
       fu.user_name "Oracle User Name", fu.start_date "Oracle Start Date",
       fu.end_date "Oracle End Date",
       (SELECT MAX (start_time)
          FROM fnd_logins
         WHERE user_id = fu.user_id) "Last Logon Date",
       pps.last_update_date "Terminated On"              
                                           ,
       NVL2 (fu.end_date,
             fu.end_date - pps.actual_termination_date,
             NULL
            ) "Oracle end date after term"        
                                          ,
       NVL2 ((SELECT MAX (start_time)
                FROM fnd_logins
               WHERE user_id = fu.user_id),
             (SELECT MAX (start_time)
                FROM fnd_logins
               WHERE user_id = fu.user_id) - pps.actual_termination_date,
             NULL
            ) "Logon after term"                  
  FROM per_people_f ppf,
       per_people_f ppfs,
       per_assignments_f paf,
       per_assignment_status_types past,
       per_grades pg,
       per_jobs pj,
       per_job_groups pjg,
       per_pay_bases ppb,
       per_person_types ppt,
       pay_people_groups ppg,
       pay_payrolls_f pay,
       per_periods_of_service pps,
       hr_locations_all hl,
       hr_all_organization_units houg,
       hr_organization_units hougtl,
       hr_organization_units houa,
       hr_organization_units houb,
       hr_soft_coding_keyflex hsck,
       per_positions pap,
       per_person_type_usages_f pptu,
       fnd_user fu
 WHERE ppf.person_id = paf.person_id
   AND paf.business_group_id + 0 = houb.organization_id
   AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id(+)
   AND paf.organization_id = houa.organization_id(+)
   AND paf.job_id = pj.job_id(+)
   AND paf.grade_id = pg.grade_id(+)
   AND paf.people_group_id = ppg.people_group_id(+)
   AND paf.pay_basis_id = ppb.pay_basis_id(+)
   AND paf.payroll_id = pay.payroll_id(+)
   AND paf.period_of_service_id = pps.period_of_service_id(+)
   AND paf.assignment_status_type_id = past.assignment_status_type_id
   AND paf.supervisor_id = ppfs.person_id(+)
   AND paf.position_id = pap.position_id(+)
   AND pptu.person_type_id = ppt.person_type_id
   AND pptu.person_id = ppf.person_id
   AND pj.job_group_id = pjg.job_group_id(+)
   AND ppt.system_person_type IN
                        ('EMP', 'EMP_APL', 'EX_EMP', 'EX_EMP_APL', 'RETIREE')
   AND hsck.segment1 = TO_CHAR (houg.organization_id(+))
   AND houg.organization_id = hougtl.organization_id(+)
   AND paf.location_id = hl.location_id(+)
   AND paf.organization_id = houa.organization_id(+)
   AND paf.effective_end_date BETWEEN NVL (ppfs.effective_start_date,
                                           paf.effective_end_date
                                          )
                                  AND NVL (ppfs.effective_end_date,
                                           paf.effective_end_date
                                          )
   AND paf.effective_end_date BETWEEN NVL (pay.effective_start_date,
                                           paf.effective_end_date
                                          )
                                  AND NVL (pay.effective_end_date,
                                           paf.effective_end_date
                                          )
   AND pps.actual_termination_date BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date
   AND pps.actual_termination_date BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date
   AND NVL (pps.actual_termination_date, SYSDATE) >=
               NVL (NVL (:p_start_date, pps.actual_termination_date), SYSDATE)
   AND NVL (pps.actual_termination_date, SYSDATE) <=
                 NVL (NVL (:p_end_date, pps.actual_termination_date), SYSDATE)
   AND NVL (ppf.current_employee_flag, 'N') = 'Y'
   AND NVL (paf.primary_flag, 'N') = 'Y'
   AND paf.assignment_type = 'E'
   AND pps.actual_termination_date BETWEEN pptu.effective_start_date
                                       AND pptu.effective_end_date
   AND system_person_type = 'EMP'
   AND ppf.person_id = fu.employee_id(+)
   AND (SELECT person_type_id
          FROM (SELECT   person_id, object_version_number, person_type_id
                    FROM per_people_f
                ORDER BY person_id, object_version_number DESC) x
         WHERE ROWNUM = 1 AND person_id = ppf.person_id) NOT IN (
                                              SELECT person_type_id
                                                FROM per_person_types
                                               WHERE system_person_type =
                                                                         'EMP');