EBS: Employee Performance Rank

To get the Employee Performance Rank.


/* Formatted on 01/12/2015 11:52:31 (QP5 v5.126.903.23003) */
SELECT   papf.employee_number,
         papf.full_name,
         DECODE (papf.sex,
                 'M',
                 'Male',
                 'F',
                 'Female')
            Gender,
         (SELECT   MAX (ppos.DATE_START)
            FROM   per_periods_of_service ppos
           WHERE   ppos.person_id = paaf.person_id)
            start_date,
         (SELECT   haou.name
            FROM   hr_all_organization_units haou
           WHERE   haou.organization_id = paaf.organization_id)
            Department,
         (SELECT   ppd.segment3 Ref_Ind
            FROM   apps.per_positions pp, apps.per_position_definitions ppd
           WHERE   ppd.position_definition_id = pp.position_definition_id
                   AND pp.position_id = paaf.position_id)
            Ref_Ind,
         (SELECT   ppg.group_name
            FROM   pay_people_groups ppg
           WHERE   ppg.people_group_id = paaf.people_group_id)
            group_name,
         (hr_general.DECODE_GRADE (paaf.grade_id)) SG,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2011'), 'YYYY'))
            Ranking_2011,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2012'), 'YYYY'))
            Ranking_2012,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2013'), 'YYYY'))
            Ranking_2013
  FROM   apps.per_all_people_f papf, apps.per_all_assignments_f paaf
 WHERE       papf.person_id = paaf.person_id
         AND paaf.primary_flag = 'Y'
         AND papf.current_employee_flag = 'Y'
         AND papf.business_group_id = :business_group_id
         AND SYSDATE BETWEEN paaf.effective_start_date
                         AND  paaf.effective_end_date
         AND SYSDATE BETWEEN papf.effective_start_date
                         AND  papf.effective_end_date;



Enjoy and Stay Well !! :)

EBS: HRMS Pay Element Details Query

To get the Pay Element details.

/* Formatted on 01/12/2015 11:45:57 (QP5 v5.126.903.23003) */
  SELECT   petf.ELEMENT_TYPE_ID "Element_ID",
           petf.element_name "Element Name",
           petf.reporting_name "Reporting Name",
           petf.description "Description",
           pec.classification_name "Classification",
           DECODE (petf.ADDITIONAL_ENTRY_ALLOWED_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Additional Entry Allowed",
           DECODE (petf.ADJUSTMENT_ONLY_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Adjustment Only",
           DECODE (petf.CLOSED_FOR_ENTRY_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Closed For Entry",
           DECODE (petf.MULTIPLE_ENTRIES_ALLOWED_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Multiple Entries",
           DECODE (petf.process_in_run_flag,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Process In Run",
           DECODE (petf.STANDARD_LINK_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Standard Link",
           DECODE (petf.processing_type,
                   'N',
                   'Non-Recurring',
                   'R',
                   'Recurring')
              "Rec Or Non Rec",
           DECODE (petf.post_termination_rule,
                   'A',
                   'Actual Termination',
                   'F',
                   'Final Close',
                   'L',
                   'Last Standard Process')
              "Termination Rule",
           petf.effective_start_date "Effective Start Date",
           petf.effective_end_date "Effective End Date",
           petf.processing_priority,
           TO_CHAR (petf.creation_date, 'MM/DD/YYYY') creation_date
    FROM   pay_element_types_f petf, pay_element_classifications pec
   WHERE   petf.classification_id = pec.classification_id
ORDER BY   1;



Enjoy and Stay Well !! :)

EBS: Payroll Query

To get the Payroll details .

/* Formatted on 01/12/2015 11:57:50 (QP5 v5.126.903.23003) */
  SELECT   employee_number,
           full_name,
           ptp.period_name,
           pec.classification_name,
           petf.element_name,
           petf.reporting_name,
           DECODE (petf.processing_type,
                   'N',
                   'Non-Recurring',
                   'R',
                   'Recurring')
              processing_type,
           pivf.NAME,
           prrv.result_value pay_value
    FROM   pay_run_result_values prrv,
           pay_run_results prr,
           pay_input_values_f pivf,
           pay_element_classifications pec,
           pay_element_types_f petf,
           per_all_people_f papf,
           per_all_assignments_f paaf,
           pay_assignment_actions paa,
           pay_payroll_actions ppa,
           pay_all_payrolls_f papfp,
           per_time_periods ptp
   WHERE       prrv.input_value_id = pivf.input_value_id
           AND prrv.run_result_id = prr.run_result_id
           AND pivf.NAME = 'Pay Value'
           AND ptp.end_date BETWEEN pivf.effective_start_date
                                AND  pivf.effective_end_date
           AND pivf.element_type_id = petf.element_type_id
           AND pec.classification_name =
                 NVL (:p_classification_name, pec.classification_name)
           AND pec.classification_id = petf.classification_id
           AND petf.element_name = NVL (:p_element_name, petf.element_name)
           AND ptp.end_date BETWEEN petf.effective_start_date
                                AND  petf.effective_end_date
           AND prr.element_type_id = petf.element_type_id
           AND prr.assignment_action_id = paa.assignment_action_id
           AND papf.employee_number =
                 NVL (:p_employee_number, papf.employee_number)
           AND ptp.end_date BETWEEN papf.effective_start_date
                                AND  papf.effective_end_date
           AND papf.person_id = paaf.person_id
           AND paaf.payroll_id = ppa.payroll_id
           AND ppa.effective_date BETWEEN paaf.effective_start_date
                                      AND  paaf.effective_end_date
           AND paaf.primary_flag = 'Y'
           AND paaf.business_group_id = ppa.business_group_id
           AND paaf.business_group_id = ppa.business_group_id
           AND paaf.assignment_id = paa.assignment_id
           AND paa.payroll_action_id = ppa.payroll_action_id
           AND ( (paa.source_action_id IS NULL AND ppa.action_type = 'R')
                OR (paa.source_action_id IS NOT NULL AND ppa.action_type = 'Q'))
           AND ppa.action_status = 'C'
           AND ppa.action_type IN ('Q', 'R')
           AND ppa.business_group_id = :p_business_group_id
           AND ppa.payroll_id = ptp.payroll_id
           AND ppa.time_period_id = ptp.time_period_id
           AND ptp.end_date BETWEEN papfp.effective_start_date
                                AND  papfp.effective_end_date
           AND papfp.payroll_name = NVL (:p_payroll_name, papfp.payroll_name)
           AND papfp.payroll_id = ptp.payroll_id
           AND ptp.period_name = NVL (:p_period_name, ptp.period_name)
           AND pec.classification_name IN ('Earnings', 'Voluntary Deductions')
ORDER BY   1,
           2,
           3,
           4,
           5;




Enjoy and Stay Well !! :)

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 !! :)

EBS: Learning Management Query

To get the Oracle Learning Management details.

/* Formatted on 01/12/2015 11:33:22 (QP5 v5.126.903.23003) */
  SELECT   oe.title event_name,
           TO_CHAR (oe.course_start_date, 'DD-Mon-RRRR') stdate,
           TO_CHAR (oe.course_end_date, 'DD-Mon-RRRR') enddate,
           oe.course_start_time stime,
           oe.course_end_time etime,
           papf.email_address emp_mail,
           papf.full_name emp_name,
           papf1.email_address sup_mail,
           papf1.full_name sup_name,
           oe.evt_information2 class_location
    FROM   ota_events oe,
           ota_delegate_bookings odb,
           per_all_people_f papf,
           per_all_assignments_f paaf,
           per_all_people_f papf1
   WHERE       1 = 1
           AND odb.event_id(+) = oe.event_id
           AND papf.person_id = odb.delegate_person_id
           AND odb.internal_booking_flag = 'Y'
           AND paaf.primary_flag = 'Y'
           AND papf.current_employee_flag = 'Y'
           AND paaf.person_id = papf.person_id
           AND papf1.person_id = paaf.supervisor_id
           AND papf1.current_employee_flag = 'Y'
           AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                   AND  paaf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                   AND  papf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date
                                   AND  papf1.effective_end_date
ORDER BY   1;



Enjoy and Stay Well !! :) 

EBS: Employee Performance Query

To get the Employee Performance Band.


/* Formatted on 01/12/2015 11:26:46 (QP5 v5.126.903.23003) */
  SELECT   DISTINCT papf.employee_number,
                    papf.full_name,
                    ppr.RATING_MEANING,
                    ppr.review_date,
                    hrv.name
    FROM   PER_ALL_PEOPLE_F papf,
           PER_all_ASSIGNMENTS_F paaf,
           PER_PERFORMANCE_REVIEWS_V ppr,
           PER_PERIODS_OF_SERVICE_V pps,
           HR_ORGANIZATION_UNITS_V hrv
   WHERE       1 = 1
           AND papf.person_id = ppr.person_id
           AND PPS.PERSON_ID = papf.person_id
           AND Paaf.PERSON_ID = papf.person_id
           AND HRV.ORGANIZATION_ID = paaf.ORGANIZATION_ID
           AND PPS.ACTUAL_TERMINATION_DATE IS NULL
           AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                   AND  papf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                   AND  paaf.effective_end_date
           AND TO_CHAR (ppr.review_date, 'YYYY') = '2014'
--and  to_number(ppr.RATING_MEANING) between 0 and  0.8    -
ORDER BY   papf.employee_number


Enjoy and Stay Well !! :)

EBS: New Employee Hire And Termination Query

1. To get the list of the employees who has joined in a specific month.


/* Formatted on 01/12/2015 11:16:54 (QP5 v5.126.903.23003) */
SELECT   DISTINCT
         papf.employee_number,
         papf.full_name,
         TO_CHAR (ppos.date_start, 'DD-MON-YYYY') date_start
  FROM   apps.per_all_people_f papf,
         per_all_assignments_f paaf,
         per_periods_of_service ppos
 WHERE   ppos.date_start BETWEEN TO_DATE ('01-OCT-2014', 'DD-MON-YYYY')
                             AND  TO_DATE ('31-OCT-2014', 'DD-MON-YYYY')
         AND papf.person_id = paaf.person_id
         AND papf.person_id = ppos.person_id


1. To get the list of the employees who has terminated in a specific month.

/* Formatted on 01/12/2015 11:17:55 (QP5 v5.126.903.23003) */
SELECT   DISTINCT
         papf.employee_number,
         papf.full_name,
         TO_CHAR (ppos.date_start, 'DD-MON-YYYY') date_start,
         TO_CHAR (ppos.actual_termination_date, 'DD-MON-YYYY')
            actual_termination_date
  FROM   apps.per_all_people_f papf,
         per_all_assignments_f paaf,
         per_periods_of_service ppos
 WHERE   ppos.actual_termination_date IS NOT NULL
         AND ppos.actual_termination_date BETWEEN TO_DATE ('01-OCT-2014',
                                                           'DD-MON-YYYY')
                                              AND  TO_DATE ('31-OCT-2014',
                                                            'DD-MON-YYYY')
         AND papf.person_id = paaf.person_id
         AND papf.person_id = ppos.person_id


Enjoy and Stay Well !! :)