SQL Script to fetch Employee Supervisor Details- HCM Cloud

SELECT

    emp.person_number employee_number,

    mgr.person_number supervisor_emp_num,

	--mgr.effective_start_date supervisor_Start_dt

	paam_emp.effective_start_date Emp_Asgnmt_start_dt,

	paam_emp.effective_end_date Emp_Asgnmt_end_dt,

NVL(paam_emp.ACTION_CODE,'') ACTION_CODE,

NVL(paam_emp.REASON_CODE,'') REASON_CODE,	

NVL(PLE.NAME,' ') LEGAL_EMPLOYER_NAME

FROM

    per_all_people_f               emp,

    per_all_assignments_m          paam_emp,

    per_assignment_supervisors_f   pasf,

    per_all_people_f               mgr,

    per_all_assignments_m          paam_mgr,

	PER_LEGAL_EMPLOYERS PLE

    --per_person_names_f             emp_name

WHERE

    emp.person_id = paam_emp.person_id

    AND paam_emp.primary_assignment_flag = 'Y'

    AND paam_emp.assignment_type = 'E'

    AND paam_emp.effective_latest_change = 'Y'

    AND trunc(SYSDATE) BETWEEN emp.effective_start_date AND emp.effective_end_date

    AND trunc(SYSDATE) BETWEEN paam_emp.effective_start_date AND paam_emp.effective_end_date

    AND emp.person_id = pasf.person_id

    AND pasf.manager_type = 'LINE_MANAGER'

    AND mgr.person_id = pasf.manager_id

    AND mgr.person_id = paam_mgr.person_id

    AND paam_mgr.primary_assignment_flag = 'Y'

    AND paam_mgr.assignment_type = 'E'

    AND paam_mgr.effective_latest_change = 'Y'

    AND trunc(SYSDATE) BETWEEN mgr.effective_start_date AND mgr.effective_end_date

    AND trunc(SYSDATE) BETWEEN paam_mgr.effective_start_date AND paam_mgr.effective_end_date

	AND  paam_emp.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID(+)

	AND PLE.STATUS='A'

	--and emp.person_id=:p_person_id

	AND emp.person_number=:p_person_number

	AND trunc(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date

    /*AND emp.person_id = emp_name.person_id 

    AND emp_name.name_type = 'GLOBAL'

    AND trunc(SYSDATE) BETWEEN emp_name.effective_start_date AND emp_name.effective_end_date

    AND upper(emp_name.full_name) =''*/

    ORDER BY TO_NUMBER(emp.person_number)

Hope this Helps!