create table t2
(id number,
valid_flg char(1),
del_in char(1),
status char(1),
ins_dt date);

insert into t2 values (100,'N','N','I',sysdate);
insert into t2 values (100,'Y','Y','A',sysdate);
insert into t2 values (100,'Y','N','A',sysdate);
insert into t2 values (100,'N','Y','I',sysdate);
insert into t2 values (200,'N','Y','I',sysdate);
insert into t2 values (200,'Y','N','I',sysdate);

select id , count(*) from t2
    where  valid_flg ='Y' or del_in ='N' or status ='A'
    group by id;

SELECT t2.rowid rid, t2.*,
  rank() over (partition BY id order by
  CASE
    WHEN status = 'A'
    THEN 0
    ELSE 1
  END,
  CASE
    WHEN status = 'I'
    THEN 0
    ELSE
      CASE
        WHEN valid_flg = 'Y'
        THEN 0
        ELSE 1
      END
  END,
  CASE
    WHEN status = 'I'
    THEN 0
    ELSE
      CASE
        WHEN del_in = 'N'
        THEN 0
        ELSE 1
      END
  END) rnk
FROM t2;

WITH
    T2
    AS
        (SELECT 100 ID, 'N' VALID_FLG, 'N' DEL_IN, 'I' STATUS, TRUNC(SYSDATE) INS_DT FROM DUAL
         UNION ALL
         SELECT 100 ,'N' ,'N' ,'I' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 100 ,'Y' ,'N' ,'A' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 100 ,'N' ,'Y' ,'I' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 200 ,'N' ,'Y' ,'I' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 200 ,'Y' ,'N' ,'I' ,TRUNC(SYSDATE) FROM DUAL),
    WITH_FILTER
    AS
        (SELECT ID
               ,VALID_FLG
               ,DEL_IN
               ,STATUS
               ,INS_DT
               ,CASE
                    WHEN count(distinct STATUS) OVER(PARTITION BY ID ) = 1
                    THEN
                        'Y'
                    ELSE
                        VALID_FLG
                END
                    AS KEEP_ME
           FROM T2)
SELECT *
  FROM WITH_FILTER
 WHERE KEEP_ME = 'Y'

DELETE FROM t2 
WHERE id IN (SELECT id 
             FROM t2
             GROUP BY id
             HAVING COUNT(CASE WHEN valid_flg = 'N' 
                               OR del_in = 'Y' 
                               OR status = 'I' THEN 1 END) < COUNT(*)
            )
AND (valid_flg = 'N' 
     OR del_in = 'Y' 
     OR status = 'I');

SELECT t2.rowid rid, t2.*,
  rank() over (partition BY id order by
  CASE
    WHEN valid_flg ='N' OR del_in ='Y' OR status ='I'
    THEN 1
    ELSE 0
  END) rnk,
FROM t2;

delete from t2 
where t2.rowid in (select rid
                   from (SELECT inner.rowid rid,
                                rank() over (partition BY id 
                                             order by CASE WHEN valid_flg ='N' 
                                                           OR del_in ='Y' 
                                                           OR status ='I'
                                                           THEN 1
                                                           ELSE 0
                                                       END
                                            ) rnk,
                         FROM t2 inner
                        )
                    where rnk > 1
                   );

  select * from t1 tab1 where trunc(instance ) = '23-Aug-17'
  and (valid_flg = 'N' or (ststus ='I' or del_in='Y'))
  and id in ( select id from t1 tab2 where trunc(instance ) = '23-Aug-17'
  and (valid_flg ='Y' and (ststus ='A' and del_in ='N')));