How To Check if a Patch is Applied in 12.2.x using SQL*PLUS 

Applies To:

Oracle Trade Management - Version 12.2 to 12.2.7 [Release 12.2]
Oracle Application Object Library - Version 12.2 to 12.2.7 [Release 12.2]
Oracle Applications DBA - Version 12.2 to 12.2.7 [Release 12.2Cloud to 12.2]
Oracle E-Business Suite Technology Stack - Version 12.2 to 2.2.7 [Release 12.2]
Oracle Applications Manager - Version 12.2 to 12.2.7 [Release 12.2Cloud to 12.2]
Information in this document applies to any platform.

Goal:

The purpose of this document is to explain how to check if a Patch is applied on an environment in Release 12.2.x.

Solution:

In eBusiness Suite (EBS) 12.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).

To check whether a patch is really applied use the AD_PATCH.IS_PATCH_APPLIED pl/sql function.

Using this API is an alternative method for users without access to Oracle Applications Manager's "Patching and Utilities" feature to determine if a certain patch is applied.

Usage:

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

Example sql using the APPL_TOP ID for a specific node in a multinode environment (1045 is the APPL_TOP ID):

SELECT adb.bug_number,ad_patch.is_patch_applied('R12', 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.

The cause is the snapshot is not updated, now it is on development's working in the following bug:

Bug: 23059082 - current view snapshot is not being updated during patch application.

The workaround is to updated the snapshot manually with the steps below:

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".

Reference metalink Doc ID 1963046.1