How to check if a patch is applied in Oracle EBS 12.2

In this article different methods of getting information about applied Oracle EBS 12.2 patches are reviewed.

Oracle Applications Manager

Login to Oracle EBS as a user that has access to Oracle Applications Manager. For purposes of this tutorial Enginatics EBS demo instance has been used.

Picture 1: Launching OAM Patching and Utilities feature

Populate the fields such as patch number, period of application and language and click ‘Go’ button. If the patch was applied you will get the results as on the screenshot below.

Picture 2: Checking applied patch

Database query

Another popular method of checking applied patches is querying Oracle EBS Database. As stated in MOS Doc ID 1963046.1 AD_BUGS table can not be used anymore for that purpose in release 12.2.x. It will still return information, but you can not rely on it. Some patches reported to be applied actually might be applied and then patching cycle was aborted before finalizing the application.
As an alternative AD_PATCH.IS_PATCH_APPLIED function is provided. You should login as APPS user to execute the statements below.

SQL>select
decode(ad_patch.is_patch_applied('&ebs_release',&appltop_id,&patch_num,'&lang'),'EXPLICIT','APPLIED','NOT_APPLIED','NOT APPLIED')
from dual;

While executing the above statement you will be prompted for required parameters. Example of executing the statement is presented on the picture below:

Picture 3: AD_PATCH.IS_PATCH_APPLIED function usage

Query parameters

First parameter

For EBS 12.2 please use ‘R12’ as the first parameter.

Second parameter

For single-node systems you can use appl_top_id=-1
To obtain the appl_top_id for multi-node installations use the following query which is a modified version of the query from Doc ID 1963046.1.
ad_appl_tops usually contains obsolete appl_tops as described in Doc ID 2075234.1
So I joined ad_appl_tops with fnd_appl_tops which doesn’t have obsolete records.
But fnd_appl_tops returns two dualfs appl_tops for EBS release 12.2. So I used distinct keyword to return only one of them.

SQL>select aat.name,aat.appl_top_id from applsys.ad_appl_tops aat, (select distinct fat.name from applsys.fnd_appl_tops fat) fat where aat.name=fat.name;

Third parameter

Specify patch number that you want to check

Fourth parameter

This parameter is optional. If it’s not provided ‘US’ value will be used. Specify language code if you want to check patch for specific NLS language.
You can use the following query to get the list of language codes of the installed languages in your EBS system.

SQL>select language_code,nls_language from fnd_languages where installed_flag='I';

Improving the query

Here’s another version of this query checking for patch application status on all application nodes for all installed languages with possibility to specify multiple patch numbers

SQL>select
adb.bug_number,
aat.name appl_top_name,
adb.language,
decode(ad_patch.is_patch_applied('R12',aat.appl_top_id,adb.bug_number,adb.language),'EXPLICIT','APPLIED','NOT_APPLIED','NOT APPLIED') status
from ad_bugs adb,
(
select
aat.name,aat.appl_top_id
from
applsys.ad_appl_tops aat,
(select distinct fat.name from applsys.fnd_appl_tops fat) fat
where
aat.name=fat.name
) aat
where adb.bug_number in (1013371,20034256)
order by adb.bug_number,aat.name,adb.language;

Blitz report

The final method reviewed in this post is Blitz report. It allows executing query directly from EBS forms interface without navigating to OAM page. Also you don’t need to login to the database to run the query. You can create your custom report using AD_PATCH.IS_PATCH_APPLIED function and save it for future quick access. Please refer to Blitz Report™ User Guide for required steps to create a custom report.

The screenshots below contain details of an example report “AD Applied Patches R12.2”.

Report has several parameters: Patch number, Appl Top Id, Language which correspond to the parameters of the function ad_patch.is_patch_applied.

An example of running the report AD Applied Patches R12.2 is shown on the screenshot below.
Report parameters use LOVs, so you can select each parameter from list.

After selecting all the parameters click ‘Run’ button

In seconds you will get perfectly formatted excel output with the query results

Older EBS versions

For Oracle EBS 11,12.0 and 12.1 you still can use ad_bugs table to query applied patches. You can use the following query:

(select bug_number,creation_date from ad_bugs where bug_number='&bug')
union
(select patch_name,creation_date from ad_applied_patches where patch_name ='&patch');

There are also associated reports AD Applied Patches 11i and AD Applied Patches

Conclusion

Each method reviewed in this post has its advantages.
While OAM is a convenient way to get the required information it has some disadvantages too. You may not have access to OAM or it is possibly not convenient to switch from the EBS forms interface to just query a patch.
The method of querying the database is widely used among DBAs. But to use it you need APPS user access and basic knowledge of SQL. Also you have to be able to connect to the database.
You can run Blitz report directly from EBS forms interface and quickly get results in the form of excel file. You also can use Blitz report to create any custom report.