HOWTO (eBiz) - Tracking file changes after applying patches

We normally use adident utility to find what is the current version of a particular file in EBS, however in case you want to see home many patches changed the same file there is no tools that provides this information.

The query below will help you to identify not only the current file version, but the history of changes on the file after being modified by a particular patch.

/* 

-- Filename: check_file_version_with_patch_number.sql

-- Description: file change history tracking 

-- Author: Christian Ricci

-- Created on: 09/Mar/2011

-- Modified on: 20110309 - CR - Initial version

*/
col APP_SHORT_NAME for a8
col filename for a25
col subdir for a30
col version for a40
set lin 300 pages 1000 trim on trimspool on verify off
set colsep '|'
--
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select instance_name from v$instance;
--
SELECT
  ab.bug_number         ,
  f.app_short_name      ,
  f.subdir              ,
  f.filename            ,
  v.file_id             ,
  v.creation_date       ,
  v.last_update_date    ,
  v.version
FROM applsys.AD_FILE_VERSIONS v,
  applsys.ad_files f,
  applsys.AD_PATCH_RUN_BUGS pr,
  applsys.AD_PATCH_RUN_BUG_ACTIONS pra,
  applsys.ad_bugs ab
WHERE
    v.file_id=f.file_id
and pra.file_id=v.file_id
and pra.patch_file_version_id=v.file_version_id
and pr.patch_run_bug_id=pra.patch_run_bug_id
and ab.bug_id=pr.bug_id
and f.filename='&1'
ORDER BY
  v.last_update_date DESC,
  f.filename             ,
  v.creation_date;

This below query will show the files modified by a particular patch.

/* 

-- Filename: check_file_on_patch_number.sql

-- Description: Files changes by a particular patch

-- Author: Christian Ricci

-- Created on: 09/Mar/2011

-- Modified on: 20110309 - CR - Initial version

*/col APP_SHORT_NAME for a8
col filename for a25
col subdir for a30
col version for a40
set lin 300 pages 1000 trim on trimspool on verify off
set colsep '|'
--
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select instance_name from v$instance;
--
SELECT
  ab.bug_number         ,
  f.app_short_name      ,
  f.subdir              ,
  f.filename            ,
  v.file_id             ,
  v.creation_date       ,
  v.last_update_date    ,
  v.version
FROM applsys.AD_FILE_VERSIONS v,
  applsys.ad_files f,
  applsys.AD_PATCH_RUN_BUGS pr,
  applsys.AD_PATCH_RUN_BUG_ACTIONS pra,
  applsys.ad_bugs ab
WHERE
    v.file_id=f.file_id
and pra.file_id=v.file_id
and pra.patch_file_version_id=v.file_version_id
and pr.patch_run_bug_id=pra.patch_run_bug_id
and ab.bug_id=pr.bug_id
and ab.bug_number='&1'
ORDER BY
  v.last_update_date DESC,
  f.filename             ,
  v.creation_date;