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

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
/

Sample Output

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>