BIP – Query to extract Supervisor Details

Select papfemp.person_number
     , ppnfemp.first_name                                    person_first_name
     , ppnfemp.last_name                                     person_last_name
     , paamemp.assignment_number                              person_assignment
     , to_char(pasf.effective_start_date, 'YYYY-MM-DD')        effective_start_date
     , to_char(pasf.effective_end_date, 'YYYY-MM-DD')          effective_end_date
     , papf_mgr.person_number                                  manager_number
     , ppnf_mgr.first_name                                    manager_first_name
     , ppnf_mgr.last_name                                     manager_last_name
     , paam_mgr.assignment_number                              manager_assignment
     , pasf.manager_type
  From per_all_people_f                       papfemp
     , per_person_names_f                     ppnfemp
     , per_all_assignments_m                  paamemp
     , per_all_people_f                       papf_mgr
     , per_person_names_f                     ppnf_mgr
     , per_all_assignments_m                  paam_mgr
     , per_assignment_supervisors_f           pasf
 Where ppnfemp.person_id              = papfemp.person_id
   And ppnfemp.name_type              = 'GLOBAL'
   And paamemp.person_id              = papfemp.person_id
   And paamemp.assignment_type            In ('E', 'C', 'N')
   And paamemp.effective_latest_change     = 'Y'
   And paamemp.effective_start_date  Between papfemp.effective_start_date     And papfemp.effective_end_date
   And paamemp.effective_start_date  Between ppnfemp.effective_start_date     And ppnfemp.effective_end_date
   And TRUNC(SYSDATE)  Between paamemp.effective_start_date                      And paamemp.effective_end_date
   And paamemp.effective_start_date  Between pasf.effective_start_date     And pasf.effective_end_date
   And ppnf_mgr.person_id           = papf_mgr.person_id
   And ppnf_mgr.name_type              = 'GLOBAL'
   And paam_mgr.person_id                   = papf_mgr.person_id
   And paam_mgr.assignment_type            In ('E', 'C', 'N')
   And paam_mgr.effective_latest_change     = 'Y'
   And paam_mgr.effective_start_date  Between papf_mgr.effective_start_date     And papf_mgr.effective_end_date
   And paam_mgr.effective_start_date  Between ppnf_mgr.effective_start_date     And ppnf_mgr.effective_end_date
   And paamemp.effective_start_date   Between paam_mgr.effective_start_date     And paam_mgr.effective_end_date

   And pasf.person_id                       = papfemp.person_id
   And pasf.assignment_id                   = paamemp.assignment_id
   And pasf.manager_id                      = papf_mgr.person_id
   And pasf.manager_assignment_id           = paam_mgr.assignment_id