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)