WITH master AS
(
SELECT 
    EMP_CODE,
    EMP_LAST_NAME,
  REPLACE(EMP_LAST_NAME,' ','') AS EMP_LAST_NAME_R,
  EMP_FIRST_NAME,
  REPLACE(EMP_FIRST_NAME,' ','') AS EMP_FIRST_NAME_R,
  REPLACE(EMP_LAST_NAME,' ','') || REPLACE(EMP_FIRST_NAME,' ','') AS EMP_ALL_NAME_R
FROM
  EMP
WHERE
    VALID_FLG = '1'
)
,cnt_master AS
(
SELECT
    EMP_ALL_NAME_R,
    COUNT(*) OVER(PARTITION BY EMP_ALL_NAME_R) AS cnt_all
FROM
    master
)
,make_dup AS
(
SELECT DISTINCT
    *
FROM
    cnt_master
WHERE
    cnt_all > 1
)
SELECT
  EMP_CODE AS CODE,
  EMP_LAST_NAME AS SURNAME,
--   EMP_LAST_NAME_R,
  EMP_FIRST_NAME AS NAME
--   EMP_FIRST_NAME_R,
--   EMP_ALL_NAME_R
FROM
  master 
WHERE EXISTS 
    (SELECT
    *
FROM
    make_dup
WHERE
    make_dup.EMP_ALL_NAME_R = master.EMP_ALL_NAME_R)
ORDER BY
  EMP_ALL_NAME_R,
  EMP_CODE