CREATE TABLE EMPLOYEES
(
  EMPNO   NUMBER(4),
  ENAME   VARCHAR2(8 BYTE),
  INIT    VARCHAR2(5 BYTE),
  JOB     VARCHAR2(8 BYTE),
  MGR     NUMBER(4),
  BDATE   DATE,
  MSAL    NUMBER(6,2),
  COMM    NUMBER(6,2),
  DEPTNO  NUMBER(2)
)

Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (1, 'Jason', 'N', 'TRAINER', 2, 
    TO_DATE('12/18/1965 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 800, NULL, 10);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (2, 'Jerry', 'J', 'SALESREP', 3, 
    TO_DATE('11/19/1966 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1600, 300, 10);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (3, 'Jord', 'T', 'SALESREP', 5, 
    TO_DATE('10/21/1967 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1700, 500, 20);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (4, 'Mary', 'J', 'MANAGER', 5, 
    TO_DATE('09/22/1968 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1800, NULL, 20);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (5, 'Joe', 'P', 'SALESREP', 10, 
    TO_DATE('08/23/1969 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1900, 1400, 30);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (6, 'Black', 'R', 'MANAGER', 2, 
    TO_DATE('07/24/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2000, NULL, 30);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (7, 'Red', 'A', 'MANAGER', 4, 
    TO_DATE('06/25/1971 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2100, NULL, 40);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (8, 'White', 'S', 'TRAINER', 4, 
    TO_DATE('05/26/1972 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2200, NULL, 40);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (9, 'Yellow', 'C', 'DIRECTOR', 10, 
    TO_DATE('04/27/1973 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2300, NULL, 20);
Insert into EMPLOYEES
   (EMPNO, ENAME, INIT, JOB, MGR, 
    BDATE, MSAL, COMM, DEPTNO)
 Values
   (10, 'Pink', 'J', 'SALESREP', NULL, 
    TO_DATE('03/28/1974 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2400, 0, 30);
COMMIT;

SELECT  ename, empno
  FROM employees
  WHERE (:REPORT_TYPE =1 AND empno=:Var_EMP_NO) OR 
      (:REPORT_TYPE =2 AND mgr=:Var_EMP_NO) OR 
      (:REPORT_TYPE =3 ) START WITH mgr = :Var_EMP_NO
                       CONNECT BY PRIOR empno = mgr

SELECT ename, empno
  FROM employees
 WHERE (:report_type = 1 AND empno = :var_emp_no)
    OR (:report_type = 2 AND mgr = :var_emp_no)
    OR ((:report_type = 3) AND (empno IN (SELECT     empno
                                                FROM employees
                                          START WITH mgr = :var_emp_no
                                          CONNECT BY PRIOR empno = mgr)))