SQL Applet Search

Here is the SQL to find Applet References.  You will notice there are two queries of Toggle Applets.  One rolls an applet all the way up to the view it appears in while the other shows toggle applets that do not appear in any view.  Therefore, in a toggle sequence, there may be one applet that appears twice.  The wc or wildcard bind variable adds a trailing wildcard

var :applet = "Quote Form Applet";     -- Applet Name
var :wc = "N";                               

select attr_type, screen, viewname, obj_name, attr_name, Applet_Mode from (
select 'View web template item' attr_type, s.name screen, v.name viewname, '' obj_name, vwti.APPLET_NAME attr_name, vwti.APPLET_MODE_CD Applet_Mode from siebel.s_screen s, siebel.S_SCREEN_VIEW sv, siebel.S_VIEW_WTMPL_IT vwti, siebel.S_VIEW_WEB_TMPL vwt, siebel.s_view v, siebel.s_repository r
where vwti.APPLET_NAME like :applet||decode(:wc,'Y','%','') and vwti.VIEW_WEB_TMPL_ID = vwt.row_id and vwt.VIEW_ID = v.row_id and v.name = sv.VIEW_NAME and sv.SCREEN_ID = s.row_id and s.repository_id = r.row_id
and v.repository_id = r.row_id and r.name = 'Siebel Repository' and vwti.inactive_flg = 'N' and vwt.inactive_flg = 'N' and sv.inactive_flg = 'N' and v.inactive_flg = 'N' and s.inactive_flg = 'N'

union all

select 'Form Applet Pick Applet' attr_type, '' screen, '' viewname, a.name obj_name, c.PICK_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_control c, siebel.s_applet a, siebel.s_repository r
where c.PICK_APPLET_NAME like :applet||decode(:wc,'Y','%','') and c.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and c.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'Form Applet MVG Applet' attr_type, '' screen, '' viewname, a.name obj_name, c.MVG_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_control c, siebel.s_applet a, siebel.s_repository r
where c.MVG_APPLET_NAME like :applet||decode(:wc,'Y','%','') and c.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and c.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'List Applet Pick Applet' attr_type, '' screen, '' viewname, a.name obj_name, lc.PICK_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r
where lc.PICK_APPLET_NAME like :applet||decode(:wc,'Y','%','') and lc.list_id = l.row_id and l.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and lc.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'List Applet MVG Applet' attr_type, '' screen, '' viewname, a.name obj_name, lc.MVG_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r
where lc.MVG_APPLET_NAME like :applet||decode(:wc,'Y','%','') and lc.list_id = l.row_id and l.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and lc.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'Associate Applet' attr_type, '' screen, '' viewname, a.name obj_name, a.ASSOC_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_applet a, siebel.s_repository r
where a.ASSOC_APPLET_NAME like :applet||decode(:wc,'Y','%','') and a.repository_id = r.row_id and r.name = 'Siebel Repository' and a.inactive_flg = 'N'
union all
select 'Applet Toggle' attr_type, '' screen, '' viewname, a.name obj_name, t.APPLET_NAME attr_name, '' Applet_Mode from siebel.S_APPLET_TOGGLE t, siebel.s_applet a, siebel.s_repository r
where t.APPLET_NAME like :applet||decode(:wc,'Y','%','') and t.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and t.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'Applet Toggle' attr_type, s.name screen, v.name viewname, a.name obj_name, t.APPLET_NAME attr_name, vwti.APPLET_MODE_CD Applet_Mode from siebel.s_screen s, siebel.S_SCREEN_VIEW sv, siebel.S_VIEW_WTMPL_IT vwti, siebel.S_VIEW_WEB_TMPL vwt, siebel.s_view v, siebel.S_APPLET_TOGGLE t, siebel.s_applet a, siebel.s_repository r
where t.APPLET_NAME like :applet||decode(:wc,'Y','%','') and vwti.APPLET_NAME = a.name and vwti.VIEW_WEB_TMPL_ID = vwt.row_id and vwt.VIEW_ID = v.row_id and v.name = sv.VIEW_NAME and sv.SCREEN_ID = s.row_id and t.applet_id = a.row_id
and a.repository_id = r.row_id and v.repository_id = r.row_id and s.repository_id = r.row_id and r.name = 'Siebel Repository'
and t.inactive_flg = 'N' and a.inactive_flg = 'N' and vwti.inactive_flg = 'N' and vwt.inactive_flg = 'N' and sv.inactive_flg = 'N' and v.inactive_flg = 'N'
union all
select 'Run Time Event - Object' attr_type, '' screen, '' viewname, rt.EVT_NAME obj_name, rt.OBJ_NAME attr_name, '' Applet_Mode from siebel.S_CT_EVENT rt
where rt.OBJ_NAME like decode(:wc,'Y','%','')||:applet||decode(:wc,'Y','%','') and rt.OBJ_TYPE_CD = 'Applet'

union all

select 'Personalization - Applet Condition' attr_type, '' screen, '' viewname, A.VIS_COND_EXPR obj_name, a.APPLET_NAME attr_name, '' Applet_Mode from siebel.S_CT_APPLET a
where a.APPLET_NAME like decode(:wc,'Y','%','')||:applet||decode(:wc,'Y','%','') and (a.EFF_END_DT is null or a.EFF_END_DT >= sysdate) and (a.EFF_START_DT is null or a.EFF_START_DT <= sysdate)
)