It sometimes happens that you quickly want to get an overview of patches that are applied for a specific application. When you found a bug in the system and want to know wether an advised patch is already applied for example.
From the front-end you can do that of course through OAM or in OA Framework pages through the About This Page link. Sometimes I’ll also use a short SQL statement to quickly get an extract. Use the below statement in you SQL tool like Toad, SQL Developer, SQL*Plus or whatever tool you use to connect to the database. It will generate a list of patches applied with the bug numbers. You can also see wether the patch was applied succesfully or not. Filter on a specific application by using the APPLICATION_SHORT_NAME column from the AD_PATCH_RUN_BUGS table. Include an additional AND statement in the WHERE clause to include an ORIG_BUG_NUMBER filter.
SELECT
apr.PATCH_RUN_ID PATCH_RUN_ID
, apr.PATCH_TOP PATCH_TOP
, apr.START_DATE START_DATE_PATCH
, apr.END_DATE END_DATE_PATCH
, aprb.PATCH_RUN_BUG_ID PATCH_RUN_BUG_ID
, aprb.ORIG_BUG_NUMBER ORIG_BUG_NUMBER
, aprb.APPLIED_FLAG APPLIED_FLAG
, aprb.SUCCESS_FLAG SUCCESS_FLAG
, af.FILENAME FILENAME
, afv.VERSION FILE_VERSION
FROM
AD_PATCH_RUNS apr
, AD_PATCH_RUN_BUGS aprb
, AD_PATCH_RUN_BUG_ACTIONS aprba
, AD_FILES af
, AD_FILE_VERSIONS afv
WHERE
apr.PATCH_RUN_ID = aprb.PATCH_RUN_ID
AND aprb.PATCH_RUN_BUG_ID = aprba.PATCH_RUN_BUG_ID
AND aprba.FILE_ID = af.FILE_ID
AND af.FILE_ID = afv.FILE_ID
AND aprba.PATCH_FILE_VERSION_ID = afv.FILE_VERSION_ID
AND aprb.APPLICATION_SHORT_NAME = 'PA'
