How to check if a patch is applied in Oracle E Business Suite 11i, R12.1.x and R12.2.x

In Oracle EBS R12.2.x :

In Oracle E Business Suite (ebs erp) R12.2.x you cannot query the AD_BUGS table to check if patches have been applied..
The AD_BUGS table may have entries for patches that were applied but later the patching cycle was aborted (not really applied).


The way to check whether a patch is really applied is to use the AD_PATCH.IS_PATCH_APPLIED PL/SQL function.

Usage:

select AD_PATCH.IS_PATCH_APPLIED(\'$release\',\'$appltop_id\',\'$patch_no\',\'$language\') 

from dual;

Example sql:

SELECT adb.bug_number,ad_patch.is_patch_applied('11i', 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (20034256);

or for single app tier installations:

select ad_patch.is_patch_applied('R12',-1,20034256) from dual;

Expected results:

EXPLICIT = applied
NOT APPLIED = not applied / aborted

Note: If you are sure patch is applied, but showing as not applied then do the following workaround.

1. Start adadmin after source the RUN FS env.
2. Select "2. Maintain Applications Files menu" in "AD Administration Main Menu".
3. In "Maintain Applications Files", select "4. Maintain snapshot information".
4. Select "2. Update current view snapshot" in the "Maintain Snapshot Information".
5. Select "1. Update Complete APPL_TOP" in the "Maintain Current View Snapshot Information".

In EBS ERP 11i and R12.1.x:

Below Queries can be used to check if patch is applied:

select * from ad_bugs 
where bug_number = '&bug_number'; 

select * from ad_applied_patches 
where patch_name = '&bug_number'; 

SELECT DISTINCT a.bug_number, e.patch_name, c.end_date, b.applied_flag 
FROM ad_bugs a, 
  ad_patch_run_bugs b, 
  ad_patch_runs c, 
  ad_patch_drivers d, 
  ad_applied_patches e 
WHERE a.bug_id = b.bug_id 
AND b.patch_run_id = c.patch_run_id 
AND c.patch_driver_id = d.patch_driver_id 
AND d.applied_patch_id = e.applied_patch_id 
AND a.bug_number LIKE '&bug_number' 
ORDER BY 1 DESC ;

Reference:

Useful Scripts for E-Business Suite Applications Analysts (Doc ID 887438.1)
Tips and Queries for Troubleshooting Advanced Topologies (Doc ID 364439.1)
How To Check if a Patch is Applied in 12.2.x? (Doc ID 1963046.1)