SELECT cat_id, cat_nm, prnt_cat_id
     , CASE WHEN LEVEL >= LEAD(LEVEL, 1, LEVEL) OVER(ORDER BY ROWNUM)
            THEN 'N' ELSE 'Y' END is_leaf
  FROM (
        SELECT cat_id, cat_nm, prnt_cat_id
          FROM tblps_category_info
         WHERE work_no = '201206000176'
           AND work_rev_no = '1'
           AND gosi_no = '2012-905'
        )
 WHERE prnt_cat_id = 0
 START WITH prnt_cat_id = 0
 CONNECT BY PRIOR cat_id = prnt_cat_id
 ORDER BY cat_nm
;