Query To Find a Person is Manager or an Employee

SQL Query to Find Person is a Manager or an Employee

/* Formatted on 1/1/2015 4:25:53 PM (QP5 v5.215.12089.38647) */

SELECT PAPF.FULL_NAME,

       PAPF.PERSON_ID,

       (CASE

           WHEN PAPF.PERSON_ID IN

                   (SELECT PAAF.SUPERVISOR_ID

                      FROM PER_ALL_ASSIGNMENTS_F PAAF

                     WHERE     PAAF.PRIMARY_FLAG = 'Y'

                           AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE

                                                   AND PAAF.EFFECTIVE_END_DATE)

           THEN

              'MANAGER'

           ELSE

              'EMPLOYEE'

        END)

          "POSITION_INFORMATION"

  FROM PER_ALL_PEOPLE_F PAPF

 WHERE     TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE

                               AND PAPF.EFFECTIVE_END_DATE

       AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'

       AND PAPF.EMPLOYEE_NUMBER = NVL (:EMP_NO, PAPF.EMPLOYEE_NUMBER);