To Find Applied Patch and Patch Levels

To find quicker way with the following Queries:
SELECT *
FROM ad_bugs
WHERE bug_number='XXXXXX';

SELECT *
FROM ad_applied_patches
WHERE patch_name='YYYYYY';

Note: Please enter Patch number in place of and , e.g '3453499'


To check patch level:
SELECT patch_level,
               application_name
FROM fnd_product_installations fpi,
            fnd_application_tl fat
WHERE patch_level IS NOT NULL AND
               fpi.application_id = fat.application_id
ORDER BY application_name;

Oracle Applications Current Patchset Comparison Utility - patchsets.sh [ID 139684.1]

As per ID 443761.1 there are Several Methods to check for a certain patch:

Method 1:
Check Patches applied from Oracle Applications Manager (OAM).
Connect to OAM:
Go to Site Map >> Maintenance >> Applied Patches - here search for specific patch

Method 2:
Use 'adphrept' utility documented in Metalink Note:181665.1:
Example: To get the complete patch details for patches applied in Dec 2011:

On UNIX:
$ cd $AD_TOP/patch/115/sql
$ sqlplus /
SQL> @adphrept.sql 3 ALL ALL 12/01/11 12/31/11 ALL ALL ALL ALL ALL N N N N N

On NT:
C:\> cd %AD_TOP%\patch\115\sql
C:\> sqlplus
SQL> @adphrept.sql 3 ALL ALL 12/01/11 12/31/11 ALL ALL ALL ALL ALL N N N N N

Method 3:
Use the following query, however methods 1 and 2 are more reliable.
sqlplus apps/
SQL>SELECT DISTINCT
                         RPAD (a.bug_number, 11)
                       || RPAD (e.patch_name, 11)
                       || RPAD (TRUNC (c.end_date), 12)
                       || RPAD (b.applied_flag, 4)    BUG_APPLIED
           FROM ad_bugs a,
                       ad_patch_run_bugs b,
                       ad_patch_runs c,
                       ad_patch_drivers d,
                       ad_applied_patches e
           WHERE a.bug_id = b.bug_id
                AND b.patch_run_id = c.patch_run_id
                AND c.patch_driver_id = d.patch_driver_id
                AND d.applied_patch_id = e.applied_patch_id
                AND a.bug_number IN  ('','')
          ORDER BY 1 DESC;
Note: Please enter Patch number in place of and , e.g '3453499'

Method 4:
In multi-node environment you are advised to use Patch Query from section "Checking the Patch Requirements on each Appl_Top" of the Metalink Note:364439.1. There are also good Tips and Queries for Troubleshooting Advanced Topologies.

Method 5:
Use the following query, to find Applied Patches in EBS 12.2
sqlplus apps/
SQL>SELECT adop_session_id,
         bug_number,
         session_type,
         DECODE (status,
                 'N', 'Applied on other nodes',
                 'R', 'Running',
                 'H', 'Failed (Hard)',
                 'F', 'Failed (Jobs Skipped)',
                 'S', 'Success (Jobs Skipped)',
                 'Y', 'Success',
                 'C', 'Clone Complete')
             status,
         applied_file_system_base,
         patch_file_system_base,
         node_name,
         start_date,
         end_date,
         ROUND ((end_date - start_date) * 24 * 60, 2)
             exec_time,
         adpatch_options,
         autoconfig_status,
         driver_file_name
    FROM ad_adop_session_patches
   WHERE session_type IN ('ADPATCH',
                          'HOTPATCH',
                          'DOWNTIME',
                          'ONLINE')
ORDER BY adop_session_id, start_date, end_date;

Session type column was restricted to the following values:

§  ADPATCH: Patches applied using the traditional adpatch tool. It is used right after installing 12.2 and before online patching is enabled by applying patch 13543062.

§  HOTPATCH: Patches applied in hotpatch mode (aka. no online patching cycle is involved). It should be used only when instructed by Oracle.

§  DOWNTIME: Patches applied when all application services are down. Only used when instructed by Oracle, such as when applying 12.2.6 RUP.

§  ONLINE: Patches applied in a normal online patching cycle. This is the recommended method.