Uses : Connect to Apps User

set pagesize 50;
set lines 200;
col bug_number format a10
col lvl format a3
col minipks format a7
col patch_name format a10
col patch_type format a10
col driver_file_name format a16
col servertype format a12
col patch_top format a28

prompt "Enter the patch number to query (sample: 1883538)"

select
b.bug_number
, ap.patch_name
, ap.patch_type
, ad_patch_hist_reps.get_level_if_one(ap.applied_pat ch_id) lvl
, ad_patch_hist_reps.get_concat_minipks(pd.patch_dri ver_id) minipks
, decode (pr.server_type_admin_flag,'Y','Admin' ,null)||
decode (pr.server_type_forms_flag, 'Y',',Forms',null)||
decode (pr.server_type_node_flag ,'Y',',Node' ,null)||
decode (pr.server_type_web_flag,'Y',',Web',null) servertype
, to_char(pr.start_date,'mm/dd/rr hh24:mi') Start_date
, decode(pd.driver_type_c_flag, 'Y', 'Copy', null)||
decode(pd.driver_type_d_flag, 'Y',
decode(pd.driver_type_c_flag, 'Y', ',DB', 'DB'), null)||
decode(pd.driver_type_g_flag, 'Y',
decode(pd.driver_type_c_flag, 'Y', ',Gen',
decode(pd.driver_type_d_flag, 'Y', ',Gen', 'Gen')
), null
) driver_type
, pd.driver_file_name
, pr.patch_top
, to_char(pr.end_date , 'mm/dd/rr hh24:mi') End_date
from ad_applied_patches ap
,ad_patch_driver_langs l
,ad_patch_drivers pd
,ad_appl_tops at
,ad_patch_runs pr
,ad_patch_run_bugs prb
,ad_bugs b
where pr.appl_top_id = at.appl_top_id
and pr.patch_driver_id = pd.patch_driver_id
and pd.applied_patch_id = ap.applied_patch_id
and pd.patch_driver_id = l.patch_driver_id
and b.bug_id = prb.bug_id
and prb.patch_run_id = pr.patch_run_id
and b.bug_number ='&patch_no'
order by at.name , pr.end_date;