Query for Extracting Employee Information:

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