with q as (
select level lvl,
empno
from wwv_demo_emp e
connect by prior empno = mgr
start with empno = nvl(:P14_ROOT,7839)
)
select SYS_CONNECT_BY_PATH(sub.ename,'/') path,
level,
LPAD(' ',((18*(level-1))+1),' ') ||
'<a href="f?p=4600:14:'||:app_session||':::14:P14_START_WITH:'||empno||'">'||wwv_flow_escape.html(sub.ename)||'</a>' ename,
job,
sal,
comm,
work_city,
work_country,
empno,
mgr,
(select ename from wwv_demo_emp x where x.empno = sub.mgr) manager,
(select count(*) from wwv_demo_emp x where x.mgr = sub.empno) directs,
nvl((select count(*) from wwv_demo_emp x connect by prior x.empno = x.mgr start with empno = sub.empno),1) -1 total_employees
from (
-- employees above start with employee
select e.*
from wwv_demo_emp e
connect by prior mgr=empno
start with empno = nvl(:P14_START_WITH, 7839)
union
-- employees who are are peers of the start with employee
select e.*
from wwv_demo_emp e
where level = (select lvl from q where empno = nvl(:P14_START_WITH,7839))
connect by prior empno = mgr
start with empno = nvl(:P14_ROOT,7839)
union
-- employees directly below start with employee
select e.*
from wwv_demo_emp e
where mgr = nvl(:P14_START_WITH,7839)
) sub
connect by prior empno = mgr
start with empno = nvl(:P14_ROOT,7839)