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

How to Find out File version in Oracle Applications 11i E-Business Suite

     
    Some times you need to find out the file version in oracle application for some TAR or for some development work. You can use SQL command to find out file version, package version or you can use OS commands to find out the version.

Use following SQL command to find out file version, patch number.

SELECT af.app_short_name "Application", af.filename "File Name",
afv.VERSION "File Version",
NVL (aap.patch_name, 'Default Installation') "Patch Number",
'$' || fa.basepath || '/' || af.subdir "Location"
FROM apps.ad_files af,
apps.ad_patch_run_bug_actions apa,
apps.ad_patch_run_bugs aprb,
apps.ad_patch_runs apr,
apps.ad_patch_drivers apd,
apps.ad_applied_patches aap,
apps.ad_file_versions afv,
apps.fnd_application fa
WHERE af.filename = 'IGSEN010.fmb'
AND af.file_id = apa.file_id(+)
AND apa.common_action_id(+) = 4042
AND apa.patch_run_bug_id = aprb.patch_run_bug_id(+)
AND aprb.patch_run_id = apr.patch_run_id(+)
AND apr.patch_driver_id = apd.patch_driver_id(+)
AND apd.applied_patch_id = aap.applied_patch_id(+)
AND af.file_id = afv.file_id
AND af.app_short_name = fa.application_short_name
GROUP BY aap.patch_name,
af.filename,
afv.VERSION,
af.app_short_name,
af.file_id,
af.subdir,
fa.basepath



At OS level go to $Product_TOP/patch/115/Object_Type

Example
$pwd
/u01/oracle/TESTORCL/testorclappl/igs/11.5.0/forms/US


$ident IGSEN010.fmx | grep IGSEN010.fmb | grep Header
$Header: IGSEN010.fmb 115.24 2003/10/06 06:32 svanukur ship


If you know DB object name you can use following SQL Statement to find out Package,Procedure or Function version.

select text from dba_source where name='FND_OAM_DSCRAM_DMLS_PKG' and line=2