Retrieve patch information for a specific module

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'