BIP – Query to extract worker manager job name

Below SQL query below shows how a job name can be pulled for a worker’s manager. The SQL can be easily modified to include further employment related attributes for worker’s line manager.

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
, pjft.name mgr_job_name
, 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_jobs_f_tl pjft
, 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
AND paam_mgr.job_id = pjft.job_id
AND pjft.language = 'US'
AND trunc(sysdate) BETWEEN pjft.effective_start_date AND pjft.effective_end_date