create or replace force view bkup.bkup_available_snap_tape_vw 
(database, start_time, source, end_time, status, backup_type, snapshot_name, remote_end, os, server)
AS
   select sid as database,
          to_char (to_date (start_date || start_time, 'YYMMDDHH24:MI'),'MM/DD/YYYY HH:MI AM') start_time,
          case
             when start_date >= to_char(sysdate - snap_retension_days, 'YYMMDD')
               and start_date >= to_char(sysdate - tape_retension_days, 'YYMMDD')
                then 'Snap/Tape'
             when start_date >= to_char(sysdate - snap_retension_days, 'YYMMDD')
                then 'Snap'
             when start_date >= to_char(sysdate - tape_retension_days, 'YYMMDD')
                then 'Tape'
          end source,
          end_time as end_time,
          case
             when status = 'Normal'
                then 'Completed'
             else status
          end as status,
          replace(replace(replace(bkup_type, 'COLD R', 'COLD TAPE:R'),'HOT R','HOT TAPE:R'),'RTAPE','REMOTE-TAPE') as backup_type,
          snapshot as snapshot_name,
          decode(rend_date, null, null, to_char(to_date(rend_date || ' ' || rend_time,'YYMMDD HH24:MI'),'MM/DD/YYYY HH:MI AM')) as remote_end,
          os, host as server
     from bkup.bkup_log
    where start_date > to_char(sysdate - 35, 'YYMMDD')
      and bkup_type <> 'HOT MIRROR'
      and (   start_date >= to_char(sysdate - snap_retension_days, 'YYMMDD')
           or start_date >= to_char(sysdate - tape_retension_days, 'YYMMDD')
          );