WITH CTE AS (
    SELECT P_WBS.getWbsName(Wbs.WBS_ID, VGuiUserOcPoi.POI_ID) AS NAME,
           VGuiUserOcPoi.POI_ID,
           Wbs.WBS_ID,
           Wbs.REF_NUMBER,
           NVL (WBS.IS_PROJECT, 'N') IS_PROJECT,
           wbs.poi_tin,
           ROW_NUMBER() OVER (PARTITION BY WBS.name, VGuiUserOcPoi.POI_ID, Wbs.REF_NUMBER ORDER BY nvl (WBS.is_project, 'N')) as row_num,
           VGuiUserOcPoi.short_name as poi_short_name
    FROM V_GUI_USER_OC_POI VGuiUserOcPoi ,WBS Wbs
    where VGuiUserOcPoi.POI_ID = Wbs.POI_ID(+)
    and VGuiUserOcPoi.OC_POI_STATUS_ID = 0
    and nvl(Wbs.WBS_STATUS_ID,0) = 0
    and nvl (VGuiUserOcPoi.is_home_poi_category, 'N') = 'N'
    and VGuiUserOcPoi.oc_poi_category_id = nvl(:oc_poi_category_id, VGuiUserOcPoi.oc_poi_category_id)
    and (Wbs.WBS_LEVEL=1 or (Wbs.WBS_LEVEL=0 and not exists (select w.wbs_id from WBS w where nvl(w.wbs_status_id, 0) = 0 and w.wbs_level=1 and w.poi_id=Wbs.poi_id)))
    and VGuiUserOcPoi.GUI_USER_ID = :gui_user_id
    )
SELECT name, wbs_id, POI_ID, REF_NUMBER, poi_tin, is_project, poi_short_name
FROM CTE
WHERE row_num = 1
ORDER BY name, poi_id, ref_number;