/* Query to find out if any patch except localization patch is applied or not, if applied, that what all drivers it contain and time of it’s application */
SELECT A.APPLIED_PATCH_ID,
A.PATCH_NAME,
A.PATCH_TYPE,
B.PATCH_DRVIER_ID,
B.DRIVER_FILE_NAME,
B.ORIG_PATCH_NAME,
B.CREATION_DATE,
B.PLATFORM,
B.SOURCE_CODE,
B.CREATIONG_DATE,
B.FILE_SIZE,
B.MERGED_DRIVER_FLAG,
B.MERGE_DATE
FROM AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B
WHERE A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID
AND A.PATCH_NAME = ‘<patch number>’;
/* To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id … patch run id */
SELECT D.PATCH_NAME,
B.APPLICATIONS_SYSTEM_NAME,
B.INSTANCE_NAME,
B.NAME,
C.DRIVER_FILE_NAME,
A.PATCH_DRIVER_ID,
A.PATCH_RUN_ID,
A.SESSION_ID,
A.PATCH_TOP,
A.START_DATE,
A.END_DATE,
A.SUCCESS_FLAG,
A.FAILURE_COMMENTS
FROM AD_PATCH_RUNS A,
AD_APPL_TOPS B,
AD_PATCH_DRVIERS C,
AD_APPLIED_PATCHES D
WHERE A.APPL_TOP_ID = B.APPL_TOP_ID
AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID
AND C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID
AND A.PATCH_DRIVER_ID IN
(SELECT PATCH_DRIVER_ID
FROM AD_PATCH_DRIVERS
WHERE APPLIED_PATCH_ID IN
(SELECT APPLIED_PATCH_ID
FROM AD_APPLIED_PATCHES
WHERE PATCH_NAME = ‘<patch number>’))
ORDER BY 3;
/* To find the latest application version */
SELECT ARU_RELEASE_NAME || ‘.’ || MINOR_VERSION || ‘.’ || TAPE_VERSION
version,
START_DATE_ACTIVE updated,
ROW_SOURCE_COMMENTS “how it is done”,
BASE_RELEASE_FLAG “Base version”
FROM AD_RELEASES
WHERE END_DATE_ACTIVE IS NULL;
/* to find the base application version */
SELECT ARU_RELEASE_NAME || ‘.’ || MINOR_VERSION || ‘.’ || TAPE_VERSION
version,
START_DATE_ACTIVE “when_UPDATED”,
ROW_SOURCE_COMMENTS “how it is done”
FROM AD_RELEASES
WHERE BASE_RELEASE_FLAG = ‘Y’;
/* To find all available application version */
SELECT ARU_RELEASE_NAME || ‘.’ || MINOR_VERSION || ‘.’ || TAPE_VERSION
version,
START_DATE_ACTIVE “when UPDATED”,
END_DATE_ACTIVE “when lasted”,
CASE
WHEN BASE_RELEASE_FLAG = ‘Y’ THEN ‘BASE VERSION’
ELSE ‘Upgrade’
END
“BASE/UPGRADE”,
ROW_SOURCE_COMMENTS “how it is done”
FROM AD_RELEASES;
/* To get file version of any application file which is changed through patch application */
SELECT A.FILE_ID,
A.APP_SHORT_NAME,
A.SUBDIR,
A.FILENAME,
MAX (B.VERSION)
FROM AD_FILES A, AD_FILE_VERSIONS B
WHERE A.FILE_ID = B.FILE_ID AND B.FILE_ID = 86291
GROUP BY A.FILE_ID,
A.APP_SHORT_NAME,
A.SUBDIR,
A.FILENAME;
/* To get information related to how many time driver file is applied for bugs */
SELECT *
FROM AD_PATCH_RUN_BUGS
WHERE BUG_ID IN (SELECT BUG_ID
FROM AD_BUGS
WHERE BUG_NUMBER = ‘&BUG_NUMBER’);
/* To find latest patchset level for module installed */
SELECT APP_SHORT_NAME, MAX (PATCH_LEVEL)
FROM AD_PATCH_DRIVER_MINIPKS
GROUP BY APP_SHORT_NAME;
/* To find what is being done by the patch */
SELECT A.BUG_NUMBER “Patch Number”,
B.PATCh_RUN_BUG_ID “Run Id”,
D.APP_SHORT_NAME appl_top,
D.SUBDIR,
D.FILENAME,
MAX (F.VERSION) latest,
E.ACTION_CODE action
FROM AD_BUGS A,
AD_PATCH_RUN_BUGS B,
AD_PATCH_RUN_BUG_ACTIONS C,
AD_FILES D,
AD_PATCH_COMMON_ACTIONS E,
AD_FILE_VERSIONS F
WHERE A.BUG_ID = B.BUG_ID
AND B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID
AND C.FILE_ID = D.FILE_ID
AND E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
AND D.FILE_ID = F.FILE_ID
AND A.BUG_NUMBER = ‘<patch number>’
AND B.PATCH_RUN_BUG_ID = ‘ < > ‘
AND C.EXECUTED_FLAG = ‘Y’
GROUP BY A.BUG_NUMBER,
B.PATCH_RUN_BUG_ID,
D.APP_SHORT_NAME,
D.SUBDIR,
D.FILENAME,
E.ACTION_CODE;
/* To find Merged patch Information from database in Oracle Applications*/
SELECT bug_number
FROM ad_bugs
WHERE bug_id IN (SELECT bug_id
FROM ad_comprising_patches
WHERE patch_driver_id = (SELECT patch_driver_id
FROM ad_patch_drivers
WHERE applied_patch_id = &n));
/* Second Query to know, what all has been done during application of PATCH */
SELECT J.PATCh_NAME,
H.APPLICATIONS_SYSTEM_NAME Instance_Name,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME appl_top,
D.SUBDIR,
D.FILENAME,
MAX (F.VERSION) latest,
E.ACTION_CODE action
FROM AD_BUGS A,
AD_PATCH_RUN_BUGS B,
AD_PATCH_RUN_BUG_ACTIONS C,
AD_FILES D,
AD_PATCH_COMMON_ACTIONS E,
AD_FILE_VERSIONS F,
AD_PATCH_RUNS G,
AD_APPL_TOPS H,
AD_PATCH_DRIVERS I,
AD_APPLIED_PATCHES J
WHERE A.BUG_ID = B.BUG_ID
AND B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID
AND C.FILE_ID = D.FILE_ID
AND E.COMMON_ACION_ID = C.COMMON_ACTION_ID
AND D.FILE_ID = F.FILE_ID
AND G.APPL_TOP_ID = H.APPL_TOP_ID
AND G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID
AND I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID
AND B.PATCH_RUN_ID = G.PATCH_RUN_ID
AND C.EXECUTED_FLAG = ‘Y’
AND G.PATCH_DRIVER_ID IN
(SELECT PATCH_DRIVER_ID
FROM AD_PATCH_DRIVERS
WHERE APPLIED_PATCH_ID IN
(SELECT APPLIED_PATCH_ID
FROM AD_APPLIED_PATCHES
WHERE PATCH_NAME = ‘<Patch Number>’))
GROUP BY J.PATCH_NAME,
H.APPLICATINS_SYSTEM_NAME,
H.NAME,
I.DRIVER_FILE_BNAME,
D.APP_SHORT_NAME,
D.SUBDIR,
D.FILENAME,
E.ACTION_CODE;
/* Script to find out Patch level of mini Pack */
SELECT product_version, patch_level
FROM FND_PROUDCT_INSTALLATIONS
WHERE patch_level LIKE ‘%&shortname%’;