APPS patch history - a_phist.sql

Checks if APPS patch has been applied by querying ad_applied_patches with 
some other tables almost like adphrept.sql does ...

--
-- File:
--     a_phist.sql
--
--
-- Author:
--     VMOGILEV http://www.dbatoolz.com/
--
--
--
-- Purpose:
--     Checks if APPS patch has been applied by querying
--     ad_applied_patches with some other tables
--     almost like adphrept.sql does ...
--
-- Usage:
--     a_phist.sql
--
-- Example:
--     a_phist.sql
--
--
-- History:
--     03-MAR-2003     VMOGILEV     Created
--     04-JAN-2006     VMOGILEV     adjusted formating columns width
--
--


set lines 80
set trims on

col patch_run_id format 99999 heading "PR Id"
col name format a7 trunc heading "APPLTop"
col language format a4 heading "Lang"
col patch_name format a10 heading "Patch Name"
col driver_file_name format a12 heading "Drv Name"
col start_t format a17 heading "Start Time"
col end_t format a17 heading "End Time"

select
   pr.patch_run_id
,  at.name
,  l.language
,  ap.patch_name
,  pd.driver_file_name
,  to_char(pr.start_date,'RRRR-MON-DD hh24:mi') start_t
,  to_char(pr.end_date,'RRRR-MON-DD hh24:mi') end_t
FROM ad_applied_patches ap
,    ad_patch_driver_langs l
,    ad_patch_drivers pd
,    ad_appl_tops at
,    ad_patch_runs pr
where ap.patch_name like '&patch_number_to_check'
and pr.appl_top_id = at.appl_top_id
and pr.patch_driver_id = pd.patch_driver_id
and pd.applied_patch_id = ap.applied_patch_id
and pd.patch_driver_id = l.patch_driver_id
ORDER BY at.name,l.language,pr.end_date desc
/

You are running script: a_phist.sql
Enter value for patch_number_to_check: 4507588
old  14: where ap.patch_name like '&patch_number_to_check'
new  14: where ap.patch_name like '4507588'

 PR Id APPLTop Lang Patch Name Drv Name     Start Time        End Time
------ ------- ---- ---------- ------------ ----------------- -----------------
  1736 mdbox1  US   4507588    u4507588.drv 2007-SEP-05 17:27 2007-SEP-05 17:27
  1735 box1    US   4507588    u4507588.drv 2007-SEP-05 17:23 2007-SEP-05 17:24

2 rows selected.

13:53:04 APPS@PROD:box1>