select distinct v.vehicle_id, v.vin1||v.vin2 vin, DECODE(v.request_code,'R','Refund','P','Replacement') type,c.comment_type, v.added_date upload_date,
       a.param_value_descr affiliation, max(text_seq), max(c.added_date) last_update_date, v.comment_id, v.user_id user_id
  from rav_comment_texts c,
      rav_vehicles v,
      rav_co_param_values a
 where v.comment_id = c.comment_id and c.comment_type in ('REF','REPL') and v.check_date is NULL and v.affiliation_ind = a.param_value
   and a.client_code= 'RAV' and a.program_code='RAV' and a.PARAM_CODE = 'AFFILIATION_CODE'
group by v.vehicle_id, v.vin1, v.vin2,v.request_code, c.comment_type, v.comment_id, c.comment_id, v.added_date, v.AFFILIATION_IND, a.param_value_descr, v.user_id

execute_query;