Query to find the patch status what we will apply to fix some bugs in the applications.

----Give the patch_name here to find whether patch has been applied in the instance or not.
select
faav.application_short_name,
faav.application_name,
aap.patch_name,
aap.patch_type,
aap.source_code,
aap.creation_date,
aap.created_by,
aap.last_update_date,
aap.last_updated_by
from
AD_APPLIED_PATCHES aap,
apps.fnd_application_all_view faav
where
aap.applied_patch_id=faav.application_id and
aap.patch_name='5117525';----give the patch name or number here as per your request.

Query to Find Patches which are loaded in oracle apps instance based on the Application Name level

Hi All,

Based on my friend's request i am adding the updated query with the following requirement. 

This will help you to fetch the details of the patch based on the application level. Thanks my friend for your request.

SELECT   SUBSTR (a.application_name, 1, 60) "Application Name",
         SUBSTR (i.product_version, 1, 4) "Version",
         i.patch_level "Patch Level", i.application_id "Application ID",
         i.last_update_date "Last Update"
    FROM apps.fnd_product_installations i, apps.fnd_application_all_view a
   WHERE i.application_id = a.application_id
   and a.application_name='Payables'  ----- Add the application name here as per your request
ORDER BY a.application_name;

Regards

Prasad