Checking the File Versions with SQL Query

http://dbissues.blogspot.com/2016/02/checking-file-versions-with-sql-query.html

Many a times we need to check the file versions from our environment.
As functional consultants we generally don't have the database access.
Using the following query we can easily find the file versions without needing the help of a DBA


SELECT a.app_short_name, a.subdir, a.filename, b.version 
FROM   apps.ad_files a, 
       apps.ad_file_versions b 
WHERE  a.FILENAME like '%<filename>%' 
AND    a.file_id = b.file_id 
AND    a.app_short_name = 'HZ' --- e.g. : ASF, AMS.... 
AND    b.creation_date = (SELECT MAX (creation_date) 
                          FROM apps.ad_file_versions ver 
                          WHERE ver.file_id = a.file_id)