Friday, October 28, 2016

HRMS Employee Supervisor Hierarchy Query in Oracle Applications

           FROM (SELECT     pax.supervisor_id, pax.person_id,
                            mgx.employee_number supervisor_staff_number,
                            REPLACE (ppx.full_name, '''', ' ') full_name,
                            REPLACE (mgx.full_name,
                                     ' '
                                    ) supervisor_full_name,
                            pax.position_id position_id, pax.assignment_id,
                            LEVEL l,
                            (SELECT paaf.ass_attribute1
                               FROM per_all_assignments_f paaf
                              WHERE paaf.person_id =
                                AND paaf.primary_flag = 'Y'
                                AND ass_attribute1 IS NOT NULL
                                AND TRUNC (SYSDATE)
                                       BETWEEN paaf.effective_start_date
                                           AND NVL (paaf.effective_end_date,
                                                    TRUNC (SYSDATE)
                                                   )) ass_attribute1
                       FROM per_assignments_x pax,
                            per_people_x ppx,
                            per_people_x mgx
                      WHERE ppx.person_id = pax.person_id
                        AND ppx.current_employee_flag = 'Y'
                        AND pax.primary_flag = 'Y'
                        AND SYSDATE BETWEEN ppx.effective_start_date
                                        AND NVL (ppx.effective_end_date,
                        AND SYSDATE BETWEEN mgx.effective_start_date
                                        AND NVL (mgx.effective_end_date,
                        AND SYSDATE BETWEEN pax.effective_start_date
                                        AND NVL (pax.effective_end_date,
                        AND mgx.person_id = pax.supervisor_id
                 START WITH ppx.person_id = (select employee_id from fnd_user
         where user_name = 'XXXXX')
                 CONNECT BY NOCYCLE PRIOR mgx.employee_number =

No comments:

Post a Comment