How to Check Applied Patches and Patch Level in Oracle EBS: SQL Generator

SELECT release_name
FROM   apps.fnd_product_groups;

SELECT b.bug_number,
       b.application_short_name,
       b.language,
       b.creation_date
FROM   ad_bugs b
WHERE  b.bug_number IN ('12345678','23456789')
ORDER  BY b.creation_date DESC;

SELECT *
FROM  (SELECT ap.patch_name,
              ap.patch_type,
              pd.driver_file_name,
              pr.start_date,
              pr.end_date
       FROM   ad_applied_patches ap
       JOIN   ad_patch_drivers pd ON pd.applied_patch_id = ap.applied_patch_id
       JOIN   ad_patch_runs pr    ON pr.patch_driver_id  = pd.patch_driver_id
       ORDER  BY pr.end_date DESC)
WHERE  ROWNUM <= 50;

SELECT adop_session_id, prepare_status, apply_status, finalize_status,
       cutover_status, cleanup_status, abort_status, status, node_name
FROM   ad_adop_sessions
ORDER  BY adop_session_id DESC;

SELECT adop_session_id, bug_number, status, applied_file_system_base,
       patch_file_system_base, adpatch_options, node_name, end_date
FROM   ad_adop_session_patches
ORDER  BY end_date DESC;

SELECT abbreviation,
       codelevel
FROM   ad_trackable_entities
WHERE  abbreviation IN ('ad','txk','fnd','atg_pf')
ORDER  BY abbreviation;

SELECT fa.application_short_name,
       fpi.patch_level,
       DECODE(fpi.status, 'I', 'Installed', 'S', 'Shared', 'N', 'Not installed', fpi.status) AS install_status,
       fpi.product_version
FROM   fnd_product_installations fpi
JOIN   fnd_application fa ON fa.application_id = fpi.application_id
WHERE  fa.application_short_name IN ('AD','FND','ONT','INV','PO','SQLAP','SQLGL','AR')
ORDER  BY fa.application_short_name;

-- Returns EXPLICIT, IMPLICIT or NOT_APPLIED; -1 checks across all APPL_TOPs
SELECT ad_patch.is_patch_applied('R12', -1, 12345678) AS patch_status
FROM   dual;