Query to get basic employee details in Oracle ERP fusion

--Query to get basic employee details in Oracle ERP fusion

--R12:
SELECT (SELECT name 

          FROM hr_all_organization_units 

         WHERE organization_id = paaf.business_group_id

       ) business_group
      ,papf1.employee_number
      ,papf1.first_name
      ,papf1.last_name
      ,papf1.full_name
      ,papf1.sex gender
      ,papf1.effective_start_date
      ,papf1.effective_end_date
      ,papf1.email_address
      ,(SELECT hloc.location_code 

          FROM hr_locations_all hloc 

         WHERE hloc.location_id = paaf.location_id 

       ) employee_location
      ,papf2.employee_number supervisor_employee_number
      ,papf2.full_name supervisor_name
      ,(SELECT pp.phone_number 

          FROM per_phones pp 

         WHERE papf1.person_id = pp.parent_id 

           AND pp.parent_table = 'PER_ALL_PEOPLE_F'
           AND pp.date_from = papf1.effective_start_date 

           AND NVL(pp.date_to, papf1.effective_end_date) 

              = papf1.effective_end_date

       ) work_telephone_number
      ,(SELECT name FROM per_jobs 

         WHERE job_id = paaf.job_id ) job_name
      ,(SELECT name FROM per_all_positions 

         WHERE position_id = paaf.position_id ) position_name
      ,(SELECT concatenated_segments FROM gl_code_combinations_kfv 

         WHERE code_combination_id = paaf.default_code_comb_id 

       ) default_charge_account

      ,(SELECT name FROM gl_ledgers 

         WHERE ledger_id = paaf.set_of_books_id ) ledger_name
      ,(SELECT pd.name FROM PER_DEPARTMENTS pd 

         WHERE organization_id = paaf.organization_id 

           AND trunc(sysdate) BETWEEN pd.effective_start_date 

                              AND nvl(pd.effective_end_date, sysdate+1) 

       )department
 FROM per_all_people_f papf1,
      per_all_people_f papf2,
      per_all_assignments_f paaf      
WHERE papf1.person_id(+) = paaf.person_id
  AND papf2.person_id(+) = paaf.supervisor_id
  AND paaf.primary_flag  = 'Y'
  AND papf1.current_employee_flag = 'Y'
  AND papf2.current_employee_flag = 'Y'
  
--Fusion
SELECT papf.person_number employee_number

     , pjft.name jobname

     , paam.assignment_number emp_assignment_number
     , gcc.segment1||'-'||gcc.segment2||'-'||

       gcc.segment3||'-'||gcc.segment4||'-'||

       gcc.segment5||'-'||gcc.segment6 exp_account

     , paam.effective_start_date assg_effective_start_date

     , pea.email_address,ppnf.full_name supervisor_name

     , papf2.person_number supervisor_number

     , hauft.name businessunit

     , papf.start_date hire_date

     , (SELECT pd.name FROM per_departments pd 

         WHERE organization_id = paaf.organization_id 

           AND TRUNC(SYSDATE) BETWEEN pd.effective_start_date 

                           AND NVL(pd.effective_end_date,SYSDATE+1) 

       ) department
  FROM per_all_people_f papf
     , per_all_people_f papf2
     , per_all_assignments_m paam
     , per_jobs_f pjf
     , per_jobs_f_tl pjft
     , gl_code_combinations gcc
     , per_email_addresses pea
     , per_assignment_supervisors_f pasf
     , per_person_names_f ppnf
     , hr_organization_units_f_tl hauft
 WHERE papf.person_id = paam.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date 

                          AND papf.effective_end_date
   AND paam.primary_assignment_flag = 'Y'
   AND paam.assignment_type = 'E'
   and paam.effective_latest_change = 'Y'
   and paam.DEFAULT_CODE_COMB_ID=gcc.code_combination_id
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date 

                          AND paam.effective_end_date
   AND paam.job_id = pjf.job_id
   AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date 

                          AND pjf.effective_end_date
   AND PEA.EMAIL_ADDRESS_ID=PAPF.PRIMARY_EMAIL_ID
   AND papf.person_id = pasf.person_id
   AND pasf.manager_type = 'LINE_MANAGER'
   AND ppnf.person_id = pasf.manager_id
   AND papf2.person_id = pasf.manager_id
   AND TRUNC(SYSDATE) BETWEEN papf2.effective_start_date 

                          AND papf2.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN pasf.effective_start_date 

                          AND pasf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date 

                          AND ppnf.effective_end_date
   AND pjf.job_id = pjft.job_id
   AND pjft.language = 'US'
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN pjft.effective_start_date 

                          AND pjft.effective_end_date
   AND hauft.organization_id = paam.business_unit_id
 ORDER BY papf.person_number