APPS.AD_POST_PATCH SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 26

  select snapshot_id
  into v_snapshot_id
  from ad_snapshots
  where appl_top_id = p_appltop_id
  and snapshot_name = 'CURRENT_VIEW'
  and snapshot_type = 'C';

Line: 39

  v_sql_stmt := 'insert into ad_processed_files_temp' ||
                '(' ||
                'product_short_name,' ||
                'subdir,' ||
                'file_base,' ||
                'file_extension,' ||
                'file_id,' ||
                'file_version_id,' ||
                'version,' ||
                'date_applied,' ||
                'adpatch_flag' ||
  ')' ||
  ' select f.app_short_name,' ||
  '       f.subdir,' ||
  '       substr(f.filename,0,instr(f.filename,''.'')-1),' ||
  '       substr(f.filename,instr(f.filename,''.'')+1),' ||
  '       sf.file_id, ' ||
  '       sf.file_version_id, ' ||
  '       adfv.version, ' ||
  '       sf.last_patched_date, ' ||
  '       ''Y'' ' ||
  ' from ad_files f, ' ||
  '     ad_file_versions adfv, ' ||
  '     ad_snapshot_files sf ' ||
  ' where f.file_id = adfv.file_id ' ||
  ' and   sf.file_id = f.file_id ' ||
  ' and   sf.file_version_id = adfv.file_version_id ' ||
  ' and   substr(f.filename,instr(f.filename,''.'')+1) in ' || p_file_extension_list ||
  ' and   sf.snapshot_id = ' || v_snapshot_id ||
  ' and   sf.irep_gathered_flag = ''N''';

Line: 104

  select snapshot_id
  into v_snapshot_id
  from ad_snapshots
  where appl_top_id = p_appltop_id
  and snapshot_name = 'CURRENT_VIEW'
  and snapshot_type = 'C';

Line: 119

  ' select f.app_short_name,' ||
  '       f.subdir,' ||
  '       substr(f.filename,0,instr(f.filename,''.'')-1) file_base,' ||
  '       substr(f.filename,instr(f.filename,''.'')+1) file_extension,' ||
  '       sf.file_id, ' ||
  '       sf.file_version_id, ' ||
  '       adfv.version, ' ||
  '       sf.last_patched_date ' ||
  ' from ad_files f, ' ||
  '     ad_file_versions adfv, ' ||
  '     ad_snapshot_files sf ' ||
  ' where f.file_id = adfv.file_id ' ||
  ' and   sf.file_id = f.file_id ' ||
  ' and   sf.file_version_id = adfv.file_version_id ' ||
  ' and   substr(f.filename,instr(f.filename,''.'')+1) = ''ildt''' ||
  ' and   sf.snapshot_id = ' || v_snapshot_id ||
  ' and   sf.last_patched_date >= ''' || to_date(p_start_date,'DD-MM-YYYY') || '''' ||
  ' ) S' ||
  ' ON ( apft.product_short_name=S.app_short_name and' ||
  '     apft.subdir=S.subdir and' ||
  '     apft.file_base=S.file_base and' ||
  '     apft.file_extension=S.file_extension )' ||
  ' WHEN MATCHED THEN UPDATE SET ' ||
  '     apft.file_version_id=S.file_version_id,' ||
  '     apft.version=S.version,' ||
  '     apft.date_applied=S.last_patched_date,' ||
  '     apft.adpatch_flag=''Y''' ||
  ' WHEN NOT MATCHED THEN INSERT ' ||
  ' (product_short_name,subdir,file_base,' ||
  ' file_extension,file_id,file_version_id,' ||
  ' version,date_applied,adpatch_flag)' ||
  ' VALUES ' ||
  '    (S.app_short_name ,S.subdir ,S.file_base,' ||
  '     S.file_extension, S.file_id, S.file_version_id,' ||
  '     S.version, S.last_patched_date,''Y'')';

Line: 185

  select snapshot_id
  into v_snapshot_id
  from ad_snapshots
  where appl_top_id = p_appltop_id
  and snapshot_name = 'CURRENT_VIEW'
  and snapshot_type = 'C';

Line: 201

  ' select f.app_short_name,' ||
  '       f.subdir,' ||
  '       substr(f.filename,0,instr(f.filename,''.'')-1) file_base,' ||
  '       substr(f.filename,instr(f.filename,''.'')+1) file_extension,' ||
  '       sf.file_id, ' ||
  '       sf.file_version_id, ' ||
  '       adfv.version, ' ||
  '       sf.last_patched_date ' ||
  ' from ad_files f, ' ||
  '     ad_file_versions adfv, ' ||
  '     ad_snapshot_files sf ' ||
  ' where f.file_id = adfv.file_id ' ||
  ' and   sf.file_id = f.file_id ' ||
  ' and   sf.file_version_id = adfv.file_version_id ' ||
  ' and   substr(f.filename,instr(f.filename,''.'')+1) = ''ildt''' ||
  ' and   sf.snapshot_id = ' || v_snapshot_id ||
  ' ) S' ||
  ' ON ( apft.product_short_name=S.app_short_name and' ||
  '     apft.subdir=S.subdir and' ||
  '     apft.file_base=S.file_base and' ||
  '     apft.file_extension=S.file_extension )' ||
  ' WHEN MATCHED THEN UPDATE SET ' ||
  '     apft.file_version_id=S.file_version_id,' ||
  '     apft.version=S.version,' ||
  '     apft.date_applied=S.last_patched_date,' ||
  '     apft.adpatch_flag=''Y''' ||
  ' WHEN NOT MATCHED THEN INSERT ' ||
  ' (product_short_name,subdir,file_base,' ||
  ' file_extension,file_id,file_version_id,' ||
  ' version,date_applied,adpatch_flag)' ||
  ' VALUES ' ||
  '    (S.app_short_name ,S.subdir ,S.file_base,' ||
  '     S.file_extension, S.file_id, S.file_version_id,' ||
  '     S.version, S.last_patched_date,''Y'')';

Line: 272

  select snapshot_id
  into v_snapshot_id
  from ad_snapshots
  where appl_top_id = p_appltop_id
  and snapshot_name = 'CURRENT_VIEW'
  and snapshot_type = 'C';

Line: 288

  ' select f.app_short_name,' ||
  '       f.subdir,' ||
  '       substr(f.filename,0,instr(f.filename,''.'')-1) file_base,' ||
  '       substr(f.filename,instr(f.filename,''.'')+1) file_extension,' ||
  '       sf.file_id, ' ||
  '       sf.file_version_id, ' ||
  '       adfv.version, ' ||
  '       sf.last_patched_date ' ||
  ' from ad_files f, ' ||
  '     ad_file_versions adfv, ' ||
  '     ad_snapshot_files sf ' ||
  ' where f.file_id = adfv.file_id ' ||
  ' and   sf.file_id = f.file_id ' ||
  ' and   sf.file_version_id = adfv.file_version_id ' ||
  ' and   substr(f.filename,instr(f.filename,''.'')+1) in '||
  p_file_extension_list||
  ' and   sf.snapshot_id = ' || v_snapshot_id ||
  ' and   sf.last_patched_date >= ''' || to_date(p_start_date,'DD-MM-YYYY') ||'''' ||
  ' ) S' ||
  ' ON ( apft.product_short_name=S.app_short_name and' ||
  '     apft.subdir=S.subdir and' ||
  '     apft.file_base=S.file_base and' ||
  '     apft.file_extension=S.file_extension )' ||
  ' WHEN MATCHED THEN UPDATE SET ' ||
  '     apft.file_version_id=S.file_version_id,' ||
  '     apft.version=S.version,' ||
  '     apft.date_applied=S.last_patched_date,' ||
  '     apft.adpatch_flag=''Y''' ||
  ' WHEN NOT MATCHED THEN INSERT ' ||
  ' (product_short_name,subdir,file_base,' ||
  ' file_extension,file_id,file_version_id,' ||
  ' version,date_applied,adpatch_flag)' ||
  ' VALUES ' ||
  '    (S.app_short_name ,S.subdir ,S.file_base,' ||
  '     S.file_extension, S.file_id, S.file_version_id,' ||
  '     S.version, S.last_patched_date,''Y'')';

Line: 359

  select snapshot_id
  into v_snapshot_id
  from ad_snapshots
  where appl_top_id = p_appltop_id
  and snapshot_name = 'CURRENT_VIEW'
  and snapshot_type = 'C';

Line: 375

  v_sql_stmt := 'update ad_snapshot_files sf' ||
  ' set sf.irep_gathered_flag=''Y''' ||
  ' where sf.snapshot_id = ' || v_snapshot_id ||
  ' and sf.irep_gathered_flag = ''N''' ||
  ' and sf.file_id in ' ||
  '         (select t.file_id from ad_processed_files_temp t ' ||
  '          where  sf.file_version_id   = t.file_version_id ' ||
  '          and    sf.file_id           = t.file_id ' ||
  '          and    sf.last_patched_date = t.date_applied ' ||
  '          and    t.file_extension = ''ildt''' || ') ';