Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


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)
         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--
         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
ORDER BY papf.full_name;

Share this article :


  1. Thanks for sharing this great information on Oracle Fusion. Actually I was looking for the same information on internet for Oracle Fusion HCM Interview Questions and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more about Fusion financial by attending Oracle Fusion Financials Training.