Query for Employee Information: Oracle Apps Employee Data Extract :
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