SELECT ROWNUM             ROWNM, 
       TSGOV.dept_obs     DEPT_OBS, 
       TSGOV.res_mgr_id   RES_MGR_ID, 
       TSGOV.res_mgr_name RES_MGR_NAME, 
       TSGOV.mgr_id       MGR_ID, 
       TSGOV.res_id       RES_ID, 
       TSGOV.res_name     RES_NAME, 
       TSGOV.emp_type     EMP_TYPE, 
       TSGOV.contractor   CONTRACTOR, 
       TSGOV.prstart      PRSTART, 
       TSGOV.prfinish     PRFINISH, 
       TSGOV.status       STATUS, 
       TSGOV.status       P_STATUS, 
       TSGOV.prtimeperiod PRTIMEPERIOD, 
       TSGOV.tpid         TPID, 
       TSGOV.person_type  PERSON_TYPE 
FROM   (SELECT dept_obs, 
               res_mgr_id, 
               res_mgr_name, 
               mgr_id, 
               res_id, 
               srm_id, 
               res_name, 
               emp_type, 
               person_type, 
               contractor, 
               To_date(prstart) 
                      PRSTART, 
               To_date(prfinish) 
                      PRFINISH, 
               status, 
               tpid, 
               To_char(prstart, 'dd-Mon-yyyy') 
               ||' - ' 
               ||To_char(prfinish, 'dd-Mon-yyyy') 
                      PRTIMEPERIOD, 
               Row_number() 
                 over ( 
                   PARTITION BY res_id, res_name, To_date(prstart), To_date( 
                 prfinish), 
                 status 
                   ORDER BY res_id, res_name, To_date(prstart), To_date(prfinish 
                 ), 
                 status) AS 
               RN 
        FROM   (SELECT dept_obs, 
                       res_mgr_id, 
                       res_mgr_name, 
                       srm_id, 
                       mgr_id, 
                       res_id, 
                       res_name, 
                       emp_type, 
                       person_type, 
                       ( CASE 
                           WHEN emp_type = 'Contractor' THEN contractor 
                           ELSE '' 
                         END ) CONTRACTOR, 
                       prstart, 
                       prfinish, 
                       tpid, 
                       ( CASE 
                           WHEN Nvl(TS.prstatus, 9) = 9 THEN 'Not Created' 
                           WHEN ( Nvl(TS.prstatus, 9) = 0 ) THEN 'Not Submitted' 
                           WHEN Nvl(TS.prstatus, 9) IN ( 3, 4 ) 
                                AND ( Nvl(ATS.created_date, 
                                      To_date('01/01/99', 'MM/DD/YY')) 
                                      NOT 
                                      BETWEEN 
                                          prstart AND prfinish ) THEN 
                           'Not Attested' 
                           ELSE 'OTHER' 
                         END ) STATUS 
                FROM   (SELECT 
               OBS.path 
               DEPT_OBS, 
               MGR.unique_name 
               RES_MGR_ID, 
               MGR.full_name 
               RES_MGR_NAME, 
               MGR.user_id 
               MGR_ID, 
               S.unique_name 
               RES_ID, 
               S.full_name 
               RES_NAME, 
               S.id 
               SRM_ID, 
               S.person_type 
               PERSON_TYPE, 
               (SELECT name 
                FROM   cmn_lookups_v 
                WHERE  lookup_type = 'SRM_RESOURCE_TYPE' 
                       AND language_code = 'en' 
                       AND id = S.person_type) 
               EMP_TYPE, 
               (SELECT name 
                FROM   cmn_lookups_v 
                WHERE  lookup_type = 'TD_CONTRACTOR_CLASS' 
                       AND language_code = 'en' 
                       AND lookup_code = OCR.td_contractor_class) 
               CONTRACTOR 
               --,PTP.PRSTART  
               , 
               To_date(PTP.prstart) 
               PRSTART, 
               To_date(PTP.prfinish) - 1 
               PRFINISH, 
               PTP.prid                                           TPID, 
               S.id                                               RESID 
                        FROM   prtimeperiod PTP, 
                               srm_resources S, 
                               odf_ca_resource OCR, 
                               srm_resources MGR, 
                               prj_resources PR, 
                               (SELECT RES_POA.record_id RECORD_ID, 
                                       RES_UNITS.path    PATH 
                                FROM   (SELECT id 
                                               UNIT_ID, 
                                               type_id 
                                               TPID, 
                                               Sys_connect_by_path(name, '/') 
                                               PATH 
                                        FROM   prj_obs_units 
                                        START WITH parent_id IS NULL 
                                        CONNECT BY PRIOR id = parent_id) 
                                       RES_UNITS 
                                       join prj_obs_types RES_POT 
                                         ON RES_UNITS.tpid = RES_POT.id 
                                            AND Lower(RES_POT.unique_name) = 
                                                'td_department' 
                                       join prj_obs_associations RES_POA 
                                         ON RES_UNITS.unit_id = RES_POA.unit_id 
                                            AND RES_POA.table_name = 
                                                'SRM_RESOURCES' 
                                       join obs_units_flat_by_mode RES_FLAT 
                                         ON RES_FLAT.linked_unit_id = 
                                            RES_UNITS.unit_id 
                                WHERE  RES_FLAT.unit_mode = 
                                       'OBS_UNIT_AND_CHILDREN' 
                                       --AND (RES_FLAT.UNIT_ID = :PARAM_OBS) 
                                       AND ( :param_obs IS NULL 
                                              OR :param_obs = RES_FLAT.unit_id ) 
                               ) OBS 
                        WHERE  MGR.user_id = S.manager_id 
                               --AND S.IS_ACTIVE=1 
                               AND PR.prid = S.id 
                               AND PR.prisopen = 1 
                               AND prtrackmode = 2 
                               AND OCR.id = S.id 
                               AND OBS.record_id = S.id 
                               AND ( :param_p_res_status IS NULL 
                                      OR :param_p_res_status = S.is_active )) 
                       RES 
                       left outer join (SELECT PTS.prstatus, 
                                               S.id, 
                                               PTP.prid 
                                        FROM   prtimeperiod PTP, 
                                               prtimesheet PTS, 
                                               srm_resources S 
                                        WHERE  PTS.prresourceid = S.id 
                                               AND PTS.prtimeperiodid = PTP.prid 
                                               AND PTS.prstatus != 5) TS 
                                    ON RES.tpid = prid 
                                       AND RES.resid = TS.id 
                       left outer join (SELECT ATT.created_date, 
                                               (SELECT user_id 
                                                FROM   srm_resources 
                                                WHERE  id = td_res_mgr) 
                                               TD_RES_MGR, 
                                               MUL.id 
                                        FROM   odf_ca_td_rm_attest ATT, 
                                               (SELECT MUL.pk_id, 
                                                       S.id 
                                                FROM   odf_multi_valued_lookups 
                                                       MUL, 
                                                       srm_resources S 
                                                WHERE 
                                       MUL.attribute = 'td_res_name' 
                                       AND MUL.value = S.id) MUL 
                                        WHERE  MUL.pk_id = ATT.id) ATS 
                                    ON RES.resid = ATS.id 
               --AND RES.MGR_ID=ATS.TD_RES_MGR 
               ) 
        WHERE  status != 'OTHER' 
               AND ( mgr_id = mgr_id ) 
               AND ( tpid = tpid ))TSGOV 
WHERE  TSGOV.rn = 1 
       AND 1 = 1; 

AND 1 = 1; 

               Row_number() 
                 over ( 
                   PARTITION BY res_id, res_name, To_date(prstart), To_date( 
                 prfinish), 
                 status 
                   ORDER BY res_id, res_name, To_date(prstart), To_date(prfinish 
                 ), 
                 status) AS 
               RN 

To_date(prstart), 

trunc(prestart)