How to find out Patch Information and Duration in Oracle Applications 11i

Use following SQL Query to find out Installed Patch Information and duration.

SELECT AAP.PATCH_NAME "Patch Name",
NVL
(DECODE
(MERGED_DRIVER_FLAG,
'Y', AD_PA_VALIDATE_CRITERIASET.GET_CONCAT_MERGEPATCHES
(APD.PATCH_DRIVER_ID), '' ), 'Single Patch' ) "Merged Patches",
APR.START_DATE "Start Date", APR.END_DATE "End Date",
APD.DRIVER_FILE_NAME "Driver File",
APR.PATCH_ACTION_OPTIONS "Patch Options",
DECODE (APR.SERVER_TYPE_ADMIN_FLAG,
'Y', 'Admin,',
NULL
)
|| DECODE (APR.SERVER_TYPE_FORMS_FLAG, 'Y', 'Forms,', NULL)
|| DECODE (APR.SERVER_TYPE_NODE_FLAG, 'Y', 'Node,', NULL)
|| DECODE (APR.SERVER_TYPE_WEB_FLAG, 'Y', 'Web,', NULL)
"Server Type",
APD.PLATFORM "Platform",

SUBSTR (APR.PATCH_TOP, 1, 20) "Patch Top",
AD_CORE.GET_FORMATTED_ELAPSED_TIME
((APR.END_DATE - APR.START_DATE), 2 ) "Elapsed Time"
FROM AD_PATCH_RUNS APR,
AD_PATCH_DRIVER_LANGS APDL,
AD_PATCH_DRIVERS APD,
AD_APPLIED_PATCHES AAP
WHERE APD.PATCH_DRIVER_ID = APR.PATCH_DRIVER_ID
AND APD.PATCH_DRIVER_ID = APDL.PATCH_DRIVER_ID
AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
AND AAP.PATCH_NAME = &PATCH_NAME
ORDER BY END_DATE DESC;






Use following SQL query to find out Patch Job Details and Timings.


SELECT PROGRAM "Program", AAP.PATCH_NAME "Patch Name",
APRT.JOB_NAME "Job Name", APRT.PHASE_NAME "Phase",
APRT.START_TIME "Job Start Time",
APRT.END_TIME "Job End Time",
ROUND ((NVL
(APRT.END_TIME, SYSDATE) - APRT.START_TIME)
* 60 * 24, 2) "Elapsed Time"
FROM AD_PROGRAM_RUN_TASK_JOBS APRT,
AD_PATCH_RUNS APR,
AD_PATCH_DRIVERS APD,
AD_APPLIED_PATCHES AAP
WHERE APRT.SESSION_ID = APR.SESSION_ID
AND APR.PATCH_DRIVER_ID = APD.PATCH_DRIVER_ID
AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
AND AAP.PATCH_NAME = &PATCH_NAME
ORDER BY "Elapsed Time" DESC