Wednesday, 26 September 2018

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)

2 comments: