Patching queries (applied patches history)

query against ad_patch_drivers, ad_applied_patches and ad_patch_runs tables and provide applied patches history which help track how patches have been applied
 on Oracle E-Business Suite R12.2.

SELECT SUBSTR(a.session_id,1,10) session_id,

  SUBSTR(TO_CHAR(a.start_date, 'DD-MON-RR HH24:MI'),1,15) start_date,

  SUBSTR(TO_CHAR(a.end_date, 'DD-MON-RR HH24:MI'),1,15) end_date,

  SUBSTR(a.patch_name,1,10) patch_name,

  SUBSTR(a.orig_patch_name,1,20) orig_patch_name,

  SUBSTR(a.patch_type,1,15) patch_type,

  SUBSTR(TO_CHAR(a.creation_date, 'DD-MON-RR HH24:MI'),1,15) creation_date,

  SUBSTR(TO_CHAR(a.last_update_date, 'DD-MON-RR HH24:MI'),1,15) last_update_date,

  SUBSTR(a.patch_action_options,1,30) patch_action_options,

  a.driver_file_name,

  a.platform,

  a.merged_driver_flag,

  SUBSTR(a.appl_top_id,1,11) appl_top_id,

  a.server_type_admin_flag,

  a.server_type_forms_flag,

  a.server_type_node_flag,

  a.server_type_web_flag,

  a.success_flag,

  SUBSTR(a.failure_comments,1,30) failure_comments

FROM

  (SELECT pr.session_id,

    pr.start_date,

    pr.end_date,

    ap.patch_name,

    pd.orig_patch_name,

    ap.patch_type,

    pr.creation_date,

    pr.last_update_date,

    pr.patch_action_options,

    pd.driver_file_name,

    pd.platform,

    pd.merged_driver_flag,

    pr.appl_top_id,

    pr.server_type_admin_flag,

    pr.server_type_forms_flag,

    pr.server_type_node_flag,

    pr.server_type_web_flag,

    pr.success_flag,

    pr.failure_comments

  FROM AD_PATCH_DRIVERS pd,

    ad_applied_patches ap,

    AD_PATCH_RUNS pr

  WHERE pd.patch_driver_id= pr.patch_driver_id

  AND pd.applied_patch_id = ap.applied_patch_id

  ORDER BY SESSION_ID DESC

  ) A;