Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

Query To find Employee Details in oracle apps

SELECT   ppt.user_person_type person_type, papf.full_name employee_name,
         papf.employee_number employee_number, pjt.NAME job, pgt.NAME grade,
         (SELECT full_name
            FROM anmig_per_all_people_f ppf
           WHERE ppf.person_id = paaf.supervisor_id
             AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                                     AND ppf.effective_end_date)
                                                              supervisor_name,
         papf.attribute1 "VACATION ACC DATE",    -- EMPLOYEE FORM DFF VALUES--
                                             papf.attribute2 "COMPANY LIMIT",
                                                 -- EMPLOYEE FORM DFF VALUES--
         papf.attribute3 "REGULAR EMPLOYMENT DATE",
                                                 -- EMPLOYEE FORM DFF VALUES--
                                                   paaf.probation_period,
         DECODE (paaf.probation_unit,
                 'M', 'MONTH',
                 'Y', 'YEARS',
                 'D', 'DAYS',
                 'W', 'WEEKS'
                ) probation_unit,
         paaf.date_probation_end, paaf.normal_hours working_hours,
         paaf.time_normal_start normal_time_start,
         paaf.time_normal_finish normal_time_finish
    FROM per_all_people_f papf,
         per_all_assignments_f paaf,
         per_jobs_tl pjt,
         per_grades_tl pgt,
         per_person_types_tl ppt
   WHERE papf.person_id = paaf.person_id
     AND paaf.primary_flag = 'Y'
     AND papf.current_employee_flag = 'Y'
     AND papf.business_group_id = paaf.business_group_id
     AND paaf.job_id = pjt.job_id
     AND paaf.grade_id = pgt.grade_id
     -- AND PAPF.EMPLOYEE_NUMBER = '30987'
     AND papf.person_type_id = ppt.person_type_id
     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 PAPF.FULL_NAME LIKE 'A%'
ORDER BY papf.full_name;


Share this article :

0 comments:

Post a Comment