SELECT p.full_name,
p.employee_number employee_number,
p.last_name last_name,
p.first_name first_name,
p.original_date_of_hire hire_date,
p.date_of_birth dob,
p.sex gender,
p.email_address email_address,
(SELECT name
FROM apps.hr_all_organization_units
WHERE organization_id = p.business_group_id)
organization,
(SELECT location_code
FROM apps.hr_locations_all_tl
WHERE location_id = a.location_id AND language = USERENV ('LANG'))
location_name,
'A' status_flag,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv
WHERE code_combination_id = a.default_code_comb_id)
expense_account,
(SELECT papf1.full_name supervisor_name
FROM apps.per_all_people_f papf,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf1
WHERE papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.supervisor_id = papf1.person_id
AND papf1.current_employee_flag = 'Y'
AND papf.business_group_id = paaf.business_group_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND papf.employee_number = p.employee_number
AND papf.person_id = p.person_id)
supervisor
FROM apps.per_all_people_f p,
apps.per_all_assignments_f a,
apps.pay_people_groups ppg,
apps.hr_all_positions_f hap
WHERE p.person_id = a.person_id
AND SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
AND a.people_group_id = ppg.people_group_id
AND hap.position_id(+) = a.position_id
No comments:
Post a Comment