-- 샘플데이터와 원하는값이랑 상이해서 수정했습니다.
WITH RELATION(BSCODE, UPBSCODE, BSNAME, ISBONBU) AS 
(
SELECT 'aaaaa'  ,'aaaa'   ,'안전팀' , 'N' FROM DUAL UNION ALL
SELECT 'aaaa'    ,'aaa'   ,'안전팀처' , 'N' FROM DUAL UNION ALL
SELECT 'aaa'      ,'aa'   , '시설부' , 'N' FROM DUAL UNION ALL
SELECT 'aa'        ,'a'   , 'A본부' , 'Y' FROM DUAL UNION ALL
SELECT 'a'        ,'k_a'   , 'A본사' , 'N' FROM DUAL UNION ALL
SELECT 'bbbb'    ,'bbb'   ,'위험팀' , 'N' FROM DUAL UNION ALL
SELECT 'bbb'      ,'bb'   , '위험팀처' , 'N' FROM DUAL UNION ALL
SELECT 'bb'        ,'b'   , 'B본부' , 'Y' FROM DUAL UNION ALL
SELECT 'b'        ,'k_b'   , 'B본사' , 'N' FROM DUAL 
), INSA (NAME, BSCODE, AMT) AS (
    SELECT 'Kevin', 'aaa', '1000' FROM DUAL UNION ALL 
    SELECT 'Michael', 'bbb', '2000' FROM DUAL UNION ALL
    SELECT 'Uncle', 'aaaaa', '4000' FROM DUAL
)


SELECT B.NAME 
     , A.PATH_NAME
     , REGEXP_SUBSTR( A.PATH_NAME , '[^/]+' , 1 ,2 ) AS "본부"
     , REGEXP_SUBSTR( A.PATH_CODE , '[^/]+' , 1 ,2 ) AS "본부코드"
     , A.BSNAME
     , A.BSCODE
     , B.AMT
  FROM ( SELECT LEVEL LV 
              , SYS_CONNECT_BY_PATH ( AA.BSNAME , '/' ) PATH_NAME
              , SYS_CONNECT_BY_PATH ( AA.BSCODE , '/' ) PATH_CODE
              , AA.BSNAME 
              , AA.BSCODE
           FROM RELATION AA
          START WITH AA.UPBSCODE IN ( 'k_a' , 'k_b') 
        CONNECT BY PRIOR AA.BSCODE = AA.UPBSCODE
       ) A
     , INSA B
 WHERE A.BSCODE = B.BSCODE
 ORDER BY B.NAME