Oracle APPS Query to Get File Version

/*

Using this Query to get Oracle APPS files version

 */

SELECT sub.filename, sub.version

  FROM (SELECT adf.filename filename,

               afv.version version,

               RANK ()

                   OVER (PARTITION BY adf.filename

                         ORDER BY afv.version_segment1 DESC,

                                  afv.version_segment2 DESC,

                                  afv.version_segment3 DESC,

                                  afv.version_segment4 DESC,

                                  afv.version_segment5 DESC,

                                  afv.version_segment6 DESC,

                                  afv.version_segment7 DESC,

                                  afv.version_segment8 DESC,

                                  afv.version_segment9 DESC,

                                  afv.version_segment10 DESC,

                                  afv.translation_level DESC)

                   AS rank1

          FROM ad_file_versions afv,

               (SELECT filename,

                       app_short_name,

                       subdir,

                       file_id

                  FROM ad_files

                 WHERE UPPER (filename) LIKE UPPER ('%&FileName%')) adf

         WHERE adf.file_id = afv.file_id) sub

 WHERE rank1 = 1

ORDER BY 1;



Hope This help you,