ID  SOURCE  CURR_FLAG       TYPE
1   IBM       Y               P
1   IBM       Y               OF
1   IBM       Y               P
2   IBM       Y               P
2   TCS       Y               P
3   IBM     NULL              P
3   CTS     NULL              P
3   TCS     NULL              P
4   IBM     NULL              OF
4   CTS     NULL              OF
4   TCS      Y                ON
5   CTS     NULL              OF
5   TCS      Y                ON

ID  SOURCE  CURR_FLAG       TYPE
2   IBM       Y               P
2   TCS       Y               P
4   IBM     NULL              OF
4   CTS     NULL              OF
4   TCS      Y                ON

select
    ID,
    SOURCE,
    CURR_FL,
   TYPE
from TABLE a where
  exists(select 1 from TABLE B where a.ID=B.ID and a.rowid<>B.rowid and B.source<>a.source)
  and exists(select 1 from TABLE C where a.ID=C.ID and C.source ='IBM')
and exists(select 1 from TABLE D where a.ID=D.ID and D.CURR_FL='Y') and
(TYPE,ID) IN (
select case  type when 1 then 'P' when 2 then 'OF' else 'ON' END TYPE,ID  from
(select ID,
     max(priority) keep (dense_rank first order by priority asc) as type
      from ( select ID,TYPE,
               case TYPE
                     when 'P' then 1
                     when 'OF' then 2
                     when 'ON' then 3
                end as priority
           from TABLE where ID
            in(select ID from TABLE where CURR_FL='Y') AND SOURCE='IBM'
                )
                group by ID))