Scripts: PPA

Check database name, version, rel_level for an instance?

select i.instance_name, i.host_name, f.release_name release, i.version from v$instance i,fnd_product_groups f
where upper(substr(i.instance_name,1,4)) =
upper(substr(f.applications_system_name,1,4));


-- How to find the ATG RUP details?

SELECT (bug_number),
DECODE ((bug_number),
'4231215', 'SCM_PF.J.2',
'4042499', 'SCM_PF.J.1',
'3384350', 'SCM_PF.J',
'2700001', 'PRC_PF.I',
'2320032', 'PRC_PF.H',
'2141229', 'PRC_PF.G',
'1891381', 'PRC_PF.F',
'1745369', 'PRC_PF.E',
'1554100', 'PRC_PF.D',
'2123967', '11.5.7 11i.PRC_PF.G',
'2293243', '11.5.8 11i.PRC_PF.H',
'2669606', '11.5.9 11i.PRC_PF.I',
'3126422', '11.5.9.1',
'3171663', '11.5.9.2',
'3384350', '11.5.10 SCP J - Sourcing',
'3140000', '11.5.10 Base Install',
'3240000', '11.5.10.1 E-Businees Suite',
'3460000', '11.5.10.2 E-Businees Suite',
'6505228', '11.5.10 PO Rollup 3',
'6778767', '11.5.10 PO Rollup 4',
'7168377', '11.5.10 PO Rollup 5',
'7291444', '11.5.10 PO Rollup 6',
'7433314', '11.5.10 PO Rollup 7',
'7600643', '11.5.10 PO Rollup 8',
'4017300', '11.5 ATG CU 1',
'4125550', '11.5 ATG CU 2',
'4334965', '11.5 ATG CU 3',
'4676589', '11.5 ATG CU 4',
'5473858', '11.5 ATG CU 5',
'5903765', '11.5 ATG CU 6',
'6241631', '11.5 ATG CU 7',
'5082400', '12.0 RUP 1',
'6014659', '12.0 RUP 2',
'6272715', '12.0 RUP 3',
'6510214', '12.0 RUP 4',
'6728000', '12.0 RUP 6',
'7015582', '12.0 PO 5',
'7218243', '12.0 PO - Jul 2008',
'7291462', '12.0 PO - Aug 2008',
'7355145', '12.0 PO - Sep 2008',
'7433336', '12.0 PO - Oct 2008',
'7505241', '12.0 PO - Nov 2008',
'7600636', '12.0 PO - Dec 2008',
'7691702', '12.0 PO - Jan 2009',
'4066783', '11.5 Sourcing I-Help: 4066783',
'4028294', '11.5 Sourcing Rollup J: 4028294',
'4214649', '11.5 FPJ Sourcing Help: 4214649',
'4460597', '11.5 Sourcing J Delimiter: 4460597',
'5150810', '11.5 Sourcing J Rollup 2: 5150810',
'5197918', '11.5 Sourcing J Rollup 2 Help: 5197918',
'5739724', '11.5 INV/RCV Rollup Patch 4',
'6449139', '11.5 INV/RCV Rollup Patch 5',
'6461517', '11.5 INV/RCV Rollup Patch 6',
'6461519', '11.5 INV/RCV Rollup Patch 7',
'6461522', '11.5 INV/RCV Rollup Patch 8',
'6870030', '11.5 INV/RCV Rollup Patch 9',
'7258620', '11.5 INV/RCV Rollup Patch 10',
'7258624', '11.5 INV/RCV Rollup Patch 11',
'7258629', '11.5 INV/RCV Rollup Patch 12',
'7425810', '12.0 AP Sept Critical Update',
'7379106', '12.0 Sub-Ledger Sept Update',
'7376003', '12.0 Payments Critical Sept Update',
'7113915', '12.0 EB Tax September Update',
'Other'
)
FROM ad_bugs
WHERE bug_number IN
('4066783','4028294','4214649','5739724',
'4460597','5150810','5197918','7218243',
'3384350','2700001','2320032','7291462',
'2141229','1891381','1745369','7291444',
'1554100','2123967','3126422','3171663',
'3140000','3240000','3460000','2293243',
'4042499','4231215','4676589','2669606',
'4334965','4125550','4017300','7168377',
'5082400','6014659','6272715','7015582',
'6510214','4676589','5903765','6505228',
'6778767','5739724','6449139','6461517',
'6461519','6461522','6870030','7258620',
'7258624','7258629','7355145','6241631',
'7505241','7433336','7433314','7600643',
'7600636','7691702','6728000','7113915',
'7425810','7379106','7376003')
ORDER BY 2 ASC;

/* Query to find out if any patch except localisation 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 = ''





/* 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 = '')) 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