Oracle Transactional Business Intelligence

Products Banner

Fetching last manager name and number for the terminated employees in sql

Received Response


Fetching last manager name and number for the terminated employees in sql




Anyone know how to fetch the manager name for the terminated employees? For terminated or resigned employees manager name and number wont  be there in application. But our customer requirement is to fetch the manager details for them. They want to capture his/her manager name before he resigned. How to fetch that in sql?


Thanks in advance. Need your inputs.

Nirmal kumar


  • select distinct per.person_number ter_person_number,
          pos.actual_termination_date ter_actual_termination_date,
          s.manager_id ter_manager_id,
          mgr_num.person_number ter_manager_number,
     per.person_id ter_person_id,
     mgr_name.DISPLAY_NAME ter_manager_name,
     mgr_adid.attribute3 ter_mgr_adid
    from per_periods_of_service pos
    ,    per_assignment_supervisors_f s
    ,    per_all_people_f mgr_num
    ,    per_all_people_f per
    , PER_PERSON_NAMES_F mgr_name
    , per_persons mgr_adid
    where pos.actual_termination_date is not null
    and   pos.person_id = s.person_id
    and   pos.actual_termination_date between s.effective_start_date and s.effective_end_date
    and   s.manager_id = mgr_num.person_id
    and   s.manager_type = 'LINE_MANAGER'
    and   pos.actual_termination_date between mgr_num.effective_start_date and mgr_num.effective_end_date
    and   per.person_id = pos.person_id
    and   pos.actual_termination_date between per.effective_start_date and per.effective_end_date
    and   s.manager_id = mgr_name.person_id
    AND mgr_adid.person_id      = mgr_name.person_id(+)