PeopleSoft : Direct and Indirect reportees in PeopleSoft

 Below is the syntax to get direct and indirect reportees in peoplesoft:

SELECT… CONNECT BY

Select data with a hierarchical (parent/child) relationship

Syntax:

   SELECT…

   [START WITH initial_condition]

   CONNECT BY [nocycle] PRIOR recurse_condition

   [ORDER SIBLINGS BY order_by_clause]

Key:

    START WITH        : The row(s) to be used as the root of the hierarchy

    CONNECT BY        : Condition that identifies the relationship between

                        parent and child rows of the hierarchy

    NOCYCLE           : Do not circle around loops (where the current row has

                        a child which is also its ancestor.)

    ORDER SIBLINGS BY : Preserve ordering of the hierarchical query

                        then apply the order_by_clause to the sibling rows

Example:

Select level-1

,PEOPLE_ID

, SUPERVISOR_ID

FROM PS_JOB_VW

start with SUPERVISOR_ID = 'xxxxx'

CONNECT BY PRIOR PEOPLE_ID = SUPERVISOR_ID;