EBS: Employee Assignment Information Query

To get the Employee Assignment Information.


/* Formatted on 01/12/2015 11:44:04 (QP5 v5.126.903.23003) */
  SELECT   papf.employee_number,
           paaf.assignment_number,
           paaf.effective_start_date,
           paaf.effective_end_date,
           hr_general.decode_organization (paaf.organization_id)
              organization_name,
           hr_general.decode_job (paaf.job_id) job_name,
           hr_general.decode_position_current_name (paaf.position_id)
              position_name,
           hr_general.decode_grade (paaf.grade_id) grade_name,
           hr_general.decode_location (paaf.location_id) location_name,
           hr_general.decode_lookup ('YES_NO', ppg.segment3) Shift_Based,
           ppg.segment7 Work_Type,
           (SELECT   paat.user_status
              FROM   per_assignment_status_types paat
             WHERE   paat.assignment_status_type_id =
                        paaf.assignment_status_type_id)
              assignment_status,
           hr_general.decode_lookup ('EMP_CAT', paaf.employment_category)
              Assignment_Category,
           hr_general.decode_lookup ('EMPLOYEE_CATG', paaf.employment_category)
              Employee_category,
           hr_general.decode_pay_basis (paaf.pay_basis_id) salary_basis_name,
           DECODE (
              paaf.effective_end_date - TO_DATE ('31-DEC-4712'),
              0,
              (SELECT   papfs.employee_number
                 FROM   per_all_people_f papfs
                WHERE   TRUNC (:p_date) BETWEEN papfs.effective_start_date
                                            AND  papfs.effective_end_date
                        AND papfs.current_employee_flag = 'Y'
                        AND papfs.person_id = paaf.supervisor_id
                        AND papfs.business_group_id = paaf.business_group_id),
              NULL
           )
              Supervisor_Number,
           paaf.probation_period,
           hr_general.decode_lookup ('QUALIFYING_UNITS', paaf.probation_unit)
              probation_units,
           paaf.date_probation_end,
           paaf.notice_period,
           hr_general.decode_lookup ('QUALIFYING_UNITS',
                                     paaf.notice_period_uom)
              notice_period_units,
           paaf.normal_hours working_hours,
           paaf.frequency working_freq,
           paaf.time_normal_start,
           paaf.time_normal_finish,
           paaf.internal_address_line,
           hr_general.decode_lookup ('EMP_ASSIGN_REASON', paaf.change_reason)
              Assignment_Change_Reason,
           paaf.primary_flag,
           paaf.manager_flag,
           paaf.ass_attribute1 Group_Basic_Index,
           paaf.ass_attribute2 Base_Country,
           paaf.ass_attribute3 Colaf,
           paaf.ass_attribute4 Company,
           paaf.ass_attribute5 PBS,
           paaf.ass_attribute6 PBS_PIR,
           paaf.ass_attribute16 EBAS_Reduction,
           paaf.ass_attribute12 IBAS_Base_Country,
           paaf.ass_attribute13 IBAS_Host_Colaf,
           paaf.ass_attribute14 Work_Cycle,
           paaf.ass_attribute15 IBAS_Base_Colaf,
           paaf.ass_attribute7 Part_time_Percentage,
           paaf.ass_attribute9 Individual_Car_Ded_Percentage,
           paaf.ass_attribute10 Utilities_Allowance_Override,
           paaf.ass_attribute11 Fixed_Term_Contract_Staff_Ind,
           paaf.business_group_id,
           paaf.assignment_status_type_id,
           paaf.people_group_id,
           paaf.person_id,
           paaf.assignment_id
    FROM   pay_people_groups ppg,
           pay_all_payrolls_f papfp,
           per_all_assignments_f paaf,
           per_all_people_f papf
   WHERE   ppg.people_group_id(+) = paaf.people_group_id
           AND TRUNC (:p_date) BETWEEN papfp.effective_start_date(+)
                                   AND  papfp.effective_end_date(+)
           AND papfp.payroll_id(+) = paaf.payroll_id
           AND paaf.business_group_id = papf.business_group_id
           AND paaf.primary_flag = 'Y'
           AND paaf.person_id = papf.person_id
           AND TRUNC (:p_date) BETWEEN papf.effective_start_date
                                   AND  papf.effective_end_date
           AND papf.current_employee_flag = 'Y'
           AND papf.business_group_id = :p_business_group_id
ORDER BY   papf.employee_number, paaf.effective_start_date;



Enjoy and Stay Well !! :)