12.2 E-Business Suite Applications DBA Steps To Check if a Patch is Applied in 12.2.x using SQL*PLUS (Doc ID 1963046.1)

APPLIES TO:

Oracle E-Business Suite Technology Stack - Version 12.2 to 12.2.7 [Release 12.2]
Oracle Applications Manager - Version 12.2 to 12.2.7 [Release 12.2Cloud to 12.2]
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]
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\',\'$appl_top_id\',\'$patch_no\',\'$language\')
from dual;

To obtain the appl_top_id use the following select to list the appl_tops:

SELECT APPL_TOP_ID , NAME , DESCRIPTION , APPLICATIONS_SYSTEM_NAME , ACTIVE_FLAG FROM APPLSYS.AD_APPL_TOPS;

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 it is not showing as applied via the above steps, then update 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".