3g

SELECT basekey ,description FROM (
   SELECT
      'BSC Area-' || bsc_area.BSC_BK ||', BS-' || bsc_area.BS_BK || ', BCF_ID :' || bs.bts_site_manager_bcf_id || ' not unique. '
     -- || ST.DESCRIPTION || ' '
      --|| FU_RLM_NUMBER_FROM_BS_BK(bsc_area.BS_BK) -- TTC 64810 DESCRIPTION STATUS 06/14/17
      AS basekey ,
      st.description
      , ROW_NUMBER() over (PARTITION BY bsc_area.bsc_bk, bs.bts_site_manager_bcf_id ORDER BY bs.basekey) rn
   FROM
      vi_o2_all_bs_bsc_toc_hi bsc_area
      inner join node_details  nd ON nd.object_id = bsc_area.BS_OID
      left join PA_PROJECT_REC ppr ON  nd.object_id = REPLACE(ppr.OBJECT_ID_1, '''','')
      left join status st ON  st.status_id = nd.status
      , bs_rec bs
   WHERE
      bs.object_id = bsc_area.bs_oid
    --  and fu_hierarchy_up( bsc_area.bsc_oid, tpid) = cr.n_id
)
WHERE
   rn > 1
GROUP BY
basekey,
description
 ;