It’s not a secret that 11i extended support starts November 2010 and the cost has been waived for the 1st year of the extended support. A month ago Oracle released the mandatory requirements for 11i systems to qualify for the extended support in Note 883202.1. The note contains a huge list of required patches for each module. As I still work with quite many 11i systems I transformed the note into several SQL queries that help identify the requirements that are not present, otherwise it’s a huge pain to walk through the note each time.

It’s up to you to check if the note has been updated or not as the queries will remain without updates…

So here’s the 1st query checking the major requirements:

SELECT 'Application code level must be 11.5.10 CU2' component,
 actual ,
 needed,
 CASE WHEN actual>=needed THEN 'OK' ELSE 'NOT OK' END status
FROM
 (SELECT *
 FROM
 (SELECT major_version
 ||'.'
 ||minor_version
 ||'.'
 ||tape_version actual,
 '11.5.10.2' needed
 FROM ad_releases a
 ORDER BY to_number(major_version) DESC,
 to_number(minor_version) DESC,
 to_number(tape_version)DESC
 )
 WHERE rownum<2
 )
UNION ALL
SELECT 'ATG Rollup Patchset 6 (11i.ATG_PF.H.delta.6, Patch 5903765)',
 NVL(bug_number,0),
 needed,
 DECODE(NVL(bug_number,0),needed,'OK','NOT OK')
FROM ad_bugs, (select '5903765' needed from dual)
WHERE bug_number(+)=needed
UNION ALL
SELECT '10.2.0.4 Database or 11.1.0.7 Database ',
 version actual,
 '10.2.0.4 or 11.1.0.7' needed,
 CASE
 WHEN version LIKE '11%'
 AND version >='11.1.0.7'
 THEN 'OK '
 WHEN version LIKE '10%'
 AND version>='10.2.0.4'
 THEN 'OK'
 ELSE 'NOT OK'
 END status
FROM v$instance
union all
SELECT 'Forms6i Patchset 19 (version 6.0.8.28, Patch 6194129) (checked by interoperability patch)',
 NVL(bug_number,0),
 needed,
 DECODE(NVL(bug_number,0),needed,'OK','NOT OK')
FROM ad_bugs, (select '9410366' needed from dual union all select '8758105' needed from dual union all select '7343387' needed from dual)
WHERE bug_number(+)=needed
union all
SELECT 'Oracle HTTP Server MLR Patchset 4393827 (checked by interoperability patch)',
 NVL(bug_number,0),
 needed,
 DECODE(NVL(bug_number,0),needed,'OK','NOT OK')
FROM ad_bugs, (select '4104924' needed from dual)
WHERE bug_number(+)=needed
UNION ALL
SELECT 'Sun Java Runtime Engine (JRE) 1.6.0_03 or higher',
 actual ,
 needed,
 CASE
 WHEN actual>=needed
 THEN 'OK'
 ELSE 'NOT OK'
 END status
FROM
 (SELECT TO_CHAR(SUBSTR(text,instr(text,'<jinit_ver_comma oa_var=&quot;s_jinit_ver_comma&quot;>')+44,8)) actual,
 '1.6.0_03' needed
 FROM
 (SELECT *
 FROM fnd_oam_context_files c,
 fnd_nodes n
 WHERE n.support_forms='Y'
 AND c.name LIKE '%_'
 ||n.host ESCAPE ''
 ORDER BY last_synchronized DESC
 )
 WHERE rownum<2
 );

and the output is like this:

Extended support verification output 1

Extended support verification output 1

Note that the output has 3 rows for “Forms6i Patchset 19” verification, it’s because there have been 3 different interoperability patches that all are supported, so at least one of these 3 rows has to have “OK”. If the major components have been verified, you can move on to product requirements:

select fav.application_name, req.product_code, req.nonp1_issues, req.p1_issues, req.additional_info
from fnd_application_vl fav, fnd_product_installations fpi,
(select 'Common Applications Calendar' product ,'CAC' product_code,'6770055' nonP1_issues,'6770055' P1_issues,'' additional_info from dual union all
select 'Core Contracts','OKC','4028184','4028184','' from dual union all
select 'Procurement Contacts','OKP','4028184','4028184','' from dual union all
select 'Project Contracts','OKE','5345550','5345550','' from dual union all
select 'Sales Contracts','OKO','4028184','4028184','' from dual union all
select 'Service Contracts','OKS','5310177 and its pre-reqs  (3653484, 5101634, and 5339353)','5310177 and its pre-reqs  (3653484, 5101634, and 5339353)','' from dual union all
select 'Customers Online','IMC','4017594','4017594','' from dual union all
select 'Trading Community','HZ','3618299','3618299','' from dual union all
select 'Assets','FA','3653484 plus 6317575 and 7044061','3653484 plus 6317575 and 7044061','' from dual union all
select 'IAssets','IA','3653484','3653484','' from dual union all
select 'Advanced Collections','IEX','3653484 plus 6500218','3653484 plus 6500218','' from dual union all
select 'Bill Presentment Architecture','BPA','3653484','3653484','' from dual union all
select 'Cash Management','CE','3653484','3653484','' from dual union all
select 'Credit Management','ARC','3653484','3653484','' from dual union all
select 'E-Business Tax','ZX','3653484','3653484','' from dual union all
select 'Financials for the Americas','JL','3653484','3653484','' from dual union all
select 'Financials for Asia/Pacific','JA','3653484','3653484','' from dual union all
select 'Financials Common Country','JG','3653484','3653484','' from dual union all
select 'Financials for EMEA','JE','3653484','3653484','' from dual union all
select 'Financials for India','JAI','3653484 plus 5498551 and 6167056','3653484 plus 5498551 and 6167056','' from dual union all
select 'Internet Expenses','OIE','3653484 plus 4165000 and 7469766','3653484 plus 4165000 and 7469766','' from dual union all
select 'Lease Management','OKL','3981693 plus 4487651','3981693 plus 4487651','It is recommended that customers be on 4551977 plus 5350898.' from dual union all
select 'Loans','LNS','3653484 plus 6861822','3653484 plus 6861822','' from dual union all
select 'Payables','AP','3653484','3653484','' from dual union all
select 'Payments','IBY','3653484','3653484','' from dual union all
select 'Property Manager','PN','5591144','5591144','' from dual union all
select 'Public Sector Budgeting','PSB','3653484','3653484','' from dual union all
select 'Public Sector Financials','n/a','3653484','3653484','' from dual union all
select 'Public Sector Financials (International)','IGI','3653484','3653484','' from dual union all
select 'Receivables','AR','3653484','3653484','' from dual union all
select 'iReceivables','OIR','3653484 plus 7357444','3653484 plus 7357444','' from dual union all
select 'Treasury','XTR','3653484','3653484','' from dual union all
select 'U.S. Federal Financials','FV','3653484','3653484','' from dual union all
select 'General Ledger','GL','3653484','3653484','' from dual union all
select 'Advanced Benefits','BEN','6699770','6699770','' from dual union all
select 'Approvals Management','AME','4428060','4428060',' For HR Customers using AME, 6699770 will supersede 4428060' from dual union all
select 'HR Intelligence','HRI','4001448','4001448','' from dual union all
select 'Human Resources','PER','6699770','6699770',' For Shared HR customers, 6699770 is required' from dual union all
select 'Labor Distribution','PSP','6699770','6699770','' from dual union all
select 'Learning Management','OTA','7446888','7446888','' from dual union all
select 'Payroll (All Localizations)','PAY','7666111 and the latest Mid-Year HRMS Family Pack rollup',' 7666111 and the latest Mid-Year HRMS Family Pack rollup','The latest mid-year HRMS family pack or rollup must be applied as a prerequisite before processing the legislative Year End. For details on the latest Mid-Year patch, see Document 135266.1 Oracle HRMS Product Family - Release 11i and Release 12 Information.' from dual union all
select 'iRecruitment','IRC','6699770','6699770','' from dual union all
select 'Self-Service Human Resources','PER','6699770','6699770','' from dual union all
select 'Time and Labor','HXT','7226660','7226660','' from dual union all
select 'US Federal Human Resources','GHR','7666111 and the latest Mid-Year HRMS Family Pack rollup',' 7666111 and the latest Mid-Year HRMS Family Pack rollup','The latest mid-year HRMS family pack rollup must be applied as a prerequisite before processing the legislative Year End. For details on the latest Mid-Year patch, see Document 135266.1 Oracle HRMS Product Family - Release 11i and Release 12 Information.' from dual union all
select 'Balanced Scorecard','BSC','6617861','','' from dual union all
select 'E-Business Intelligence','BIS','6617445','','' from dual union all
select 'Customer Interaction History','JTH','4298654','','' from dual union all
select 'Inventory Management','INV','7666112','5739724','' from dual union all
select 'Warehouse Management','WMS','8337529','5855276','' from dual union all
select 'Enterprise Asset Management','EAM','5618585','5618585','' from dual union all
select 'Cost Management','CST','8362391','8362391','' from dual union all
select 'Quality','QA','8427903','11.5.10 CU2','' from dual union all
select 'Supply Chain Globalization','n/a','8657884','11.5.10 CU2','' from dual union all
select 'Work in Process','WIP','8515573','11.5.10 CU2','' from dual union all
select 'OPM Financials','GMF','7130576','11.5.10 CU2','' from dual union all
select 'OPM Inventory','GMI','7612852','11.5.10 CU2','' from dual union all
select 'OPM Logistics','GML','5747778','11.5.10 CU2','' from dual union all
select 'OPM Process Execution','GME','9065222','11.5.10 CU2','' from dual union all
select 'OPM Product Development','GMD','7157552 and 4536868','11.5.10 CU2','' from dual union all
select 'OPM Process Planning','GMP','7641335 and 6522434','11.5.10 CU2','' from dual union all
select 'Incentive Compensation','CN','4246441','4246441','' from dual union all
select 'Marketing','AMS','4449345','4449345','' from dual union all
select 'Partner Management','PV','6429776','6429776','' from dual union all
select 'Quoting','QOT','4252551','4252551','' from dual union all
select 'Sales','ASN','5329581','5329581','' from dual union all
select 'Sales for Handheld','ASP','4111409','4111409','' from dual union all
select 'Sales Offline','ASL','4401800','4401800','' from dual union all
select 'iStore','IBE','11.5.10 CU2','11.5.10 CU2','' from dual union all
select 'Territory Management','JTY','4005400','4005400','' from dual union all
select 'Trade Management','OZF','7340510','4897323','' from dual union all
select 'Advanced Pricing','QP','8391832','11.5.10 CU2','' from dual union all
select 'Configurator','CZ','7505626','7505626','' from dual union all
select 'Order Capture','ASO','4252536','4252536','' from dual union all
select 'Order Management','ONT','8639532','11.5.10 CU2 plus 4665900','' from dual union all
select 'iProcurement','ICX','8555411','11.5.10 CU2','Release Updates Packs (RUPs) are released every two months.  We strongly recommend that customers apply the latest RUP. For details and a RUP schedule, please refer to Document 602754.1 Procurement Suite Patching Strategy For One-Off Requests and RUPs.' from dual union all
select 'Purchasing','PO','8555411','11.5.10 CU2','Release Updates Packs (RUPs) are released every two months.  We strongly recommend that customers apply the latest RUP. For details and a RUP schedule, please refer to Document 602754.1 Procurement Suite Patching Strategy For One-Off Requests and RUPs.' from dual union all
select 'Sourcing','PON','8555411','11.5.10 CU2','Release Updates Packs (RUPs) are released every two months.  We strongly recommend that customers apply the latest RUP. For details and a RUP schedule, please refer to Document 602754.1 Procurement Suite Patching Strategy For One-Off Requests and RUPs.' from dual union all
select 'iSupplier Portal','POS','8555411','11.5.10 CU2','Release Updates Packs (RUPs) are released every two months.  We strongly recommend that customers apply the latest RUP. For details and a RUP schedule, please refer to Document 602754.1 Procurement Suite Patching Strategy For One-Off Requests and RUPs.' from dual union all
select 'Supplier Scheduling','CHV','8555411','11.5.10 CU2','Release Updates Packs (RUPs) are released every two months.  We strongly recommend that customers apply the latest RUP. For details and a RUP schedule, please refer to Document 602754.1 Procurement Suite Patching Strategy For One-Off Requests and RUPs.' from dual union all
select 'Advanced Product Catalog','EGO','7478311','4203793','' from dual union all
select 'Bills of Material','BOM','7478311 - PLM customers only','11.5.10 CU2','We recommend that customers apply the latest PLM.E rollups which are released every 6 to 8 months.  For information on the latest rollups, please refer to Document 414470.1 How To Determine the Product Lifecycle Management (PLM) Patchset and Rollup Version. IMPORTANT: Non-PLM customers should not apply PLM.E rollups.  To verify whether you are PLM.E customer or not, please refer to Document 414470.1 How To Determine the Product Lifecycle Management (PLM) Patchset and Rollup Version.' from dual union all
select 'Document Management and Collaboration','DOM','7478311','4203793','We recommend that customers apply the latest PLM.E rollups which are released every 6 to 8 months.  For information on the latest rollups, please refer to Document 414470.1 How To Determine the Product Lifecycle Management (PLM) Patchset and Rollup Version.' from dual union all
select 'Engineering','EN','7478311 - PLM customers only','11.5.10 CU2','We recommend that customers apply the latest PLM.E rollups which are released every 6 to 8 months.  For information on the latest rollups, please refer to Document 414470.1 How To Determine the Product Lifecycle Management (PLM) Patchset and Rollup Version. IMPORTANT: Non-PLM customers should not apply PLM.E rollups.  To verify whether you are PLM.E customer or not, please refer to Document 414470.1 How To Determine the Product Lifecycle Management (PLM) Patchset and Rollup Version.' from dual union all
select 'Item Master','ITM','7478311 - PLM customers only','11.5.10 CU2','We recommend that customers apply the latest PLM.E rollups which are released every 6 to 8 months.  For information on the latest rollups, please refer to Document 414470.1 How To Determine the Product Lifecycle Management (PLM) Patchset and Rollup Version. IMPORTANT: Non-PLM customers should not apply PLM.E rollups.  To verify whether you are PLM.E customer or not, please refer to Document 414470.1 How To Determine the Product Lifecycle Management (PLM) Patchset and Rollup Version.' from dual union all
select 'Grants Accounting','GMS','5644830','5644830','' from dual union all
select 'Project Billing','PJB','5644830','5644830','' from dual union all
select 'Project Collaboration','PJL','5644830','5644830','' from dual union all
select 'Project Costing','PJC','5644830','5644830','' from dual union all
select 'Project Portfolio Analysis','FPA','5644830','5644830','' from dual union all
select 'Project Foundation','PJF','5644830','5644830','' from dual union all
select 'Project Management','PJT','5644830','5644830','For project performance reporting features, customers should also aply 8497371. For MS Projects integration features, customers should also apply  9395663' from dual union all
select 'Project Resource Management','PJR','5644830','5644830','' from dual union all
select 'Projects','PA','5644830','5644830','' from dual union all
select 'Advanced Scheduler','CSR','5684770','5684770','' from dual union all
select 'Complex Maintenance, Repair Overhaul','AHL','4607538 and 5087894','4607538 and 5087894','We highly recommend that customers additionally apply 6174924 (with its pre-reqs) on top of 5087894.' from dual union all
select 'Customer Care','CSC','7691494','7691494','We highly recommend that customers apply 9288544.' from dual union all
select 'Depot Repair','CSD','7620639','7620639','' from dual union all
select 'Field Service','CSF','7172954','4231215','' from dual union all
select 'Mobile Field Service','CSM','7482077','7482077','' from dual union all
select 'iSupport','IBU','7668318','7668318','We highly recommend that customers apply 9405840.' from dual union all
select 'Teleservice','CS','7651923','7651923','We highly recommend that customers apply 9386488.' from dual union all
select 'Advanced Supply Chain Planning','MSC','9000516 and 8671960','9000516','Patches provided during the Extended Support period will follow the same guidelines that were used during the Premier Support period. We strongly recommend that customers apply the latest RUP patches mentioned in Document 223026.1. If Demantra is being used, integration 7644651 should also be applied.' from dual union all
select 'Collaborative Planning','MSC','7531850 and 8671960','7531850','Patches provided during the Extended Support period will follow the same guidelines that were used during the Premier Support period. We strongly recommend that customers apply the latest RUP patches mentioned in Document 223026.1. If Demantra is being used, integration 7644651 should also be applied.' from dual union all
select 'Data Collections','MSC','8671960','8671960','See Note 223026.1, Section IV for latest patches' from dual union all
select 'Demand Planning','MSD','8278480','8278480','See Note 223026.1, Section VIII for latest patches' from dual union all
select 'Global Order Promising','MSC','8686151 and 8671960','8686151','See Note 223026.1, Section II for latest patches' from dual union all
select 'Inventory Optimization','MSR','9000516 and 8671960','9000516','See Note 223026.1, Section II for latest patches' from dual union all
select 'Material Requirements Planning','MRP','7350793 and 7350872','7350793 and 7350872','See Note 353025.1 for latest MRP Rollup Patches. See Note 286755.1 for latest INV Supply/Demand rollup patches.' from dual union all
select 'Production Scheduling','MSS','One of the following: Linux32 bit server - 9033057 AIX64bit server - 9353666 HP64bit server - 9353669 SUN64bit server - 9353670 WIN32bit server - 9353718','One of the following: Linux32 bit server - 9033057 AIX64bit server - 9353666 HP64bit server - 9353669 SUN64bit server - 9353670 WIN32bit server - 9353718','Required Prerequisites: DESTINATION SIDE: 1. 8671960 2. 9000516 3. 8834044 4. 9210356 SOURCE SIDE: Step 1 : 8671960. The following patches are required only for OPM users: Step 2 : 3704948 Step 3 : 5220326 Step 4 : 7641335 Step 5 : 7718260 Step 6 : 7157552' from dual union all
select 'Strategic Network Optimization','MSN','One of the following: Linux32 bit server - 9033081 AIX64bit server - 9353739 HP64bit server - 9353743 SUN64bit server - 9353760 WIN32bit server - 9353766','One of the following: Linux32 bit server - 9033081 AIX64bit server - 9353739 HP64bit server - 9353743 SUN64bit server - 9353760 WIN32bit server - 9353766','Required Prerequisites: DESTINATION SIDE: 1. 8671960 2. 9000516 3. 8834044 4. 9210356 SOURCE SIDE: Step 1 : 8671960. The following patches are required only for OPM users: Step 2 : 3704948 Step 3 : 5220326 Step 4 : 7641335 Step 5 : 7718260 Step 6 : 7157552' from dual ) req
where fav.application_id = fpi.application_id and fav.application_short_name NOT like 'SYSADMIN' and fpi.status = 'I' and fav.application_short_name not like '%SHORT%' and req.product_code=replace(replace(fav.application_short_name,'OFA','FA'),'SQL','');

Sample output (after moving it to the Excel for readability):

Extended Support verification 2

Extended Support verification 2

As you see the query is quite huge, but that’s just because almost whole Note 883202.1 has been converted into it. The output contains only requirements for the products that are in “Installed” status (checked the same way patchsets.sh does) and contains following columns:

  • APPLICATION_NAME - name of the product
  • PRODUCT_CODE - short code of the product
  • P1_ISSUES - required patches/patchlevel to be supported for priority 1 issues.
  • NONP1_ISSUES - required patches/patchlevel to be supported for issues of other priority levels.
  • ADDITIONAL_INFO - comments from the note 883202.1

Please keep in mind the 2nd query does not verify if the patches have been installed already or not, that’s because it’s important to verify the situation manually, otherwise there’s a risk of skipping some important comment.