File Versions

-- ##############################################################################
--     FILE VERSIONS 1
-- ##############################################################################

-- basic file version

    SELECT f.app_short_name app
         , f.filename
         , afv.version
         , afv.creation_date
      FROM applsys.ad_file_versions afv
      JOIN applsys.ad_files f ON afv.file_id = f.file_id
     WHERE 1 = 1
--       AND afv.version = '120.194.12010000.98'
       AND f.filename LIKE 'IGIRMINS%'
--       AND afv.creation_date > '10-MAY-2016'
       AND 1 = 1;

-- ##############################################################################
--     FILE VERSIONS 2
-- ##############################################################################

      SELECT f.filename
           , DECODE(f.app_short_name, 'DUMMY', NULL, f.app_short_name) product
           , DECODE(f.subdir, 'DUMMY', NULL, f.subdir) directory
           , at.name appltop
           , afv.version file_version
           , to_char(afv.translation_level) trans_level
           , aap.patch_name patch_id
           , pr.end_date run_date
        FROM applsys.ad_appl_tops at
   LEFT JOIN applsys.ad_patch_runs pr              ON at.appl_top_id =             pr.appl_top_id
   LEFT JOIN applsys.ad_patch_drivers pd           ON pr.patch_driver_id =         pd.patch_driver_id
   LEFT JOIN applsys.ad_applied_patches aap        ON pd.applied_patch_id =        aap.applied_patch_id
   LEFT JOIN applsys.ad_patch_run_bugs prb         ON prb.patch_run_id =           pr.patch_run_id
   LEFT JOIN applsys.ad_patch_run_bug_actions prba ON prba.patch_run_bug_id =      prb.patch_run_bug_id
        JOIN applsys.ad_file_versions afv          ON prba.patch_file_version_id = afv.file_version_id
        JOIN applsys.ad_files f                    ON f.file_id =                  prba.file_id
       WHERE 1 = 1
    --     AND prba.executed_flag = 'Y'
         AND f.filename LIKE 'CEXCABMR%'
         AND 1 = 1;

-- ##############################################################################
--     FILE VERSIONS 3
-- ##############################################################################

-- http://apurva-oracleappscrm.blogspot.com/2013/04/checking-file-versions-with-sql-query.html

    SELECT af.app_short_name
         , fat.application_name
         , af.subdir
         , af.filename
         , afv.version
         , afv.creation_date
      FROM applsys.ad_files af
      JOIN applsys.ad_file_versions afv     ON af.file_id =         afv.file_id
 LEFT JOIN applsys.fnd_application fa       ON af.app_short_name =  fa.application_short_name
 LEFT JOIN applsys.fnd_application_tl fat   ON fa.application_id =  fat.application_id
     WHERE 1 = 1
       AND afv.creation_date = (SELECT MAX (creation_date) FROM apps.ad_file_versions ver WHERE ver.file_id = af.file_id)
       AND af.FILENAME like 'CSTPPCCB%' 
--       AND af.app_short_name = 'HZ'  
       AND 1 = 1;

-- ##############################################################################
--     FILE VERSIONS 4
-- ##############################################################################

    SELECT f.filename
         , v.version
         , v.creation_date
         , f.subdir 
      FROM applsys.ad_files f
         , applsys.ad_file_versions v 
     WHERE f.file_id = v.file_id 
       AND f.filename LIKE 'partra%' 
--       AND version like '120.%' 
  ORDER BY v.creation_date
         , v.version;