How to find applied patches for R12 EBS Oracle Applications

One key task for Oracle Application EBS Database Administrators is to perform patching on regular basis for maintenance pack upgrades and bug fixes. However, with busy work schedules, keeping track of applied patches can be a challenge.

Oracle R12 Applications uses the following tables under the APPS schema to track the status for bug fixes and patches with adpatch:

ad_applied_patches
ad_bugs


SQL> desc ad_applied_patches
Name Null? Type
----------------------------------------- -------- ----------------------------
APPLIED_PATCH_ID NOT NULL NUMBER
RAPID_INSTALLED_FLAG VARCHAR2(1)
PATCH_NAME NOT NULL VARCHAR2(120)
PATCH_TYPE NOT NULL VARCHAR2(30)
MAINT_PACK_LEVEL VARCHAR2(30)
SOURCE_CODE NOT NULL VARCHAR2(3)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
IMPORTED_FLAG VARCHAR2(1)
IMPORTED_FROM_DB VARCHAR2(30)
IMPORTED_ID NUMBER
MERGE_DATE DATE
DATA_MODEL_DONE_FLAG VARCHAR2(1)


A useful query to track status of applied patches for the E-Business Suite with Oracle EBS is the following:

SQL> select applied_patch_id, last_update_date
from ad_applied_patches
order by last_update_date;


APPLIED_PATCH_ID LAST_UPDATE_DAT
---------------- ---------------
41444 03-NOV-09
41445 03-NOV-09
42444 09-NOV-09
42445 09-NOV-09
43444 18-NOV-09
44444 24-NOV-09
45444 30-NOV-09
45445 30-NOV-09
45446 30-NOV-09
45447 30-NOV-09
45448 03-DEC-09

APPLIED_PATCH_ID LAST_UPDATE_DAT
---------------- ---------------
45449 03-DEC-09
45450 03-DEC-09
45451 03-DEC-09

Hope this quick tip helps fellow Oracle Apps DBAs.

Cheers,
Ben