Employee data extract based on business group

SELECT

     hou.name                business_unit,

     papf1.first_name,

     papf1.middle_names,

     papf1.last_name,

     papf1.full_name         emp_full_name,

     pj.name                 employeee_job_desc,

     pj.attribute16          wd_job,

     papf1.employee_number   employee_number,

     papf1.email_address     employee_email_hr,

     fu.email_address        employee_email_fnd,

     fu.user_name            user_name,

     papf2.first_name        manager_first_name,

     papf2.last_name         manager_last_name,

     papf2.middle_names      manager_middle_name,

     papf2.full_name         manager_full_name,

     papf2.employee_number   manager_empno,

     papf2.email_address     manager_email

 FROM

     apps.per_people_x papf1,

     apps.per_people_x papf2,

     apps.hr_organization_units_v hou,

     apps.per_all_assignments_f paaf1,

     apps.per_jobs pj,

     apps.fnd_user fu

 WHERE

     1 = 1

     AND hou.organization_id = papf1.business_group_id

     AND papf1.person_id = paaf1.person_id

     AND paaf1.job_id = pj.job_id (+)

     AND paaf1.supervisor_id = papf2.person_id (+)

     AND papf1.person_id = fu.employee_id (+)

     AND hou.type = 'BG'

     AND papf1.business_group_id = p_business_group_id

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