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