2 Replies Latest reply on Jul 2, 2020 8:29 PM by mdtaylor

    Request for employee and Position hierarchy SQL

    A.T

      HI Team,

       

      I have requirement to extract position hierarchy query along with subordinate and subordinate employee name, here i am unable to pull  subordinate employee name and his approval limit . all come out in different form can you please help me out in SQL Query Extract.

       

      Thanks in Advance .

        • 1. Re: Request for employee and Position hierarchy SQL
          A.T

          Hi Team,

           

          Can I get any help on my requirement,please

          • 2. Re: Request for employee and Position hierarchy SQL
            mdtaylor

            Subordinate hierarchy assumes HR is maintaining that data correctly.  Start with your top person, like CEO.

             

            select (select full_name from per_all_people_f where person_id=a1.person_id and effective_end_date > syadate) Employee_name,

            (select full_name from per_all_people_f where person_id=a1.supervisor_id and effective_end_date > syadate) Supervisor_name,

            PATH,

            LEVEL

            from

            (

            SELECT paf.person_id, paf.supervisor_id

            , LPAD (' ', 2 * LEVEL - 1)

            || SYS_CONNECT_BY_PATH (paf.person_id, '/') PATH

            , LEVEL

            FROM per_all_assignments_f paf

            START WITH paf.person_id = <:person_id>

            AND paf.primary_flag = 'Y'

            AND paf.assignment_type = 'E'

            AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date

            AND assignment_status_type_id = 1

            CONNECT BY PRIOR paf.person_id = paf.supervisor_id

            AND paf.primary_flag = 'Y'

            AND paf.assignment_type = 'E'

            AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date

            AND assignment_status_type_id = 1) a1

            order by LEVEL

             

             

            SELECT NAME PATH_NAME,PARENT_NAME FROM

            (SELECT     pps.position_structure_id,pps.NAME, LPAD (' ', 5 * LEVEL) || has.NAME HIERARCHY,

                          has.position_id, LEVEL rep_level, hap.NAME parent_name,

                          pse.parent_position_id, has.NAME child_name,

                          pse.subordinate_position_id

                     FROM (SELECT NAME, position_id

                             FROM hr_all_positions_f_tl

                            WHERE LANGUAGE = USERENV ('LANG')) hap,

                          (SELECT NAME, position_id

                             FROM hr_all_positions_f_tl

                            WHERE LANGUAGE = USERENV ('LANG')) has,

                          per_pos_structure_elements pse,

                          per_pos_structure_versions pve,

                          per_position_structures pps

                    WHERE pse.business_group_id = 0

                      AND pve.position_structure_id = pps.position_structure_id

                      AND pse.pos_structure_version_id = pve.pos_structure_version_id

                      AND SYSDATE BETWEEN pve.date_from AND NVL (pve.date_to, SYSDATE)

                      AND hap.position_id = pse.parent_position_id

                      AND has.position_id = pse.subordinate_position_id

               START WITH pse.parent_position_id =

                             (SELECT parent_position_id

                                FROM per_pos_structure_elements a

                               WHERE a.pos_structure_version_id = pse.pos_structure_version_id

                                 AND a.pos_structure_element_id =

                                        (SELECT MIN (pos_structure_element_id)

                                           FROM per_pos_structure_elements b

                                          WHERE b.pos_structure_version_id =

                                                                a.pos_structure_version_id))

               CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id

                      AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id

                      AND PRIOR pse.business_group_id = pse.business_group_id)

            WHERE position_structure_id=        

            (Select approval_path_id From  po_action_history pha where object_id= :po_header_id and sequence_num=    

            (Select max(sequence_num) From  po_action_history pha where object_id= :po_header_id) And ROWNUM=1)

            GROUP BY NAME,PARENT_NAME;