SQL Field Search

This is a Query to find references to a particular field.  Just replace the bind variables.  Use your imagination in modifying this query to find Profile attributes.  The objects searched for are those relevant to most Implementations and do not search more specialized features (such as tree applets for instance).  If those features are used, you will need to add another Union to the query.  Also, because of the limitations in searching Oracle LONG data types, this query does not search Script.  I keep a minimized repository search window open in my Tools session with all the script objects selected so that I can quickly drag the window up and paste in what I am searching for.

var :attr_val = "CMI Failure Id";             -- Field Name or string to search for

var :buscomp = "CMI Quote Simple";    -- Business Component of the relevant object if applicable
var :wc = "Y";                                            -- Trailing Wildcard.  N indicates an exact match

select attr_type, obj_name, attr_name, attr_val from (
select 'Applet List Column' attr_type, a.name obj_name, lc.name attr_name, lc.field_name attr_val from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r
where lc.field_name like :attr_val||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' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Control' attr_type, a.name obj_name, c.name attr_name, c.field_name attr_val from siebel.s_control c, siebel.s_applet a, siebel.s_repository r
where c.field_name like :attr_val||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' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet User Prop' attr_type, a.name obj_name, up.name attr_name, up.Value attr_val from siebel.S_APPLET_UPROP up, siebel.s_applet a, siebel.s_repository r
where up.Value like '%'||:attr_val||'%' and up.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Toggle' attr_type, a.name obj_name, t.name attr_name, t.AUTO_TOG_FLD_NAME attr_val from siebel.S_APPLET_TOGGLE t, siebel.s_applet a, siebel.s_repository r
where t.AUTO_TOG_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and t.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and a.inactive_flg = 'N' and t.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Drilldown - Source' attr_type, a.name obj_name, d.name attr_name, d.SRC_FIELD_NAME attr_val from siebel.S_DDOWN_OBJECT d, siebel.s_applet a, siebel.s_repository r
where d.SRC_FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and d.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and d.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Dynamic Drilldown' attr_type, a.name obj_name, d.name attr_name, dd.FIELD_NAME attr_val from siebel.S_DDOWN_DYNDEST dd, siebel.S_DDOWN_OBJECT d, siebel.s_applet a, siebel.s_repository r
where dd.FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and dd.DDOWN_OBJECT_ID = d.row_id
and d.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository'
and dd.inactive_flg = 'N' and d.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Join Spec' attr_type, bc.name obj_name, j.name attr_name, js.SRC_FLD_NAME attr_val from siebel.S_JOIN_SPEC js, siebel.S_JOIN j, siebel.s_buscomp bc, siebel.s_repository r
where js.SRC_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and js.JOIN_ID = j.row_id and j.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and j.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Predefault - Same' attr_type, bc.name obj_name, f.name attr_name, f.PREDEFVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.PREDEFVAL like '%'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Predefault - Other' attr_type, bc.name obj_name, f.name attr_name, f.PREDEFVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.PREDEFVAL like '%'||:buscomp||'.'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N'

union all

select 'Calculated Field - Same' attr_type, bc.name obj_name, f.name attr_name, f.CALCVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.CALCVAL like '%'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Calculated Field - Other' attr_type, bc.name obj_name, f.name attr_name, f.CALCVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.CALCVAL like '%ParentFieldValue%'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N'

union all

select 'BC User Prop' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.VALUE like '%'||:attr_val||'%' and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'BC User Prop' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.NAME like '%'||:attr_val||decode(:wc,'Y','%','') and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'BC User Prop - Child' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.NAME like 'Parent%' and up.VALUE like '%'||:buscomp||'%'||:attr_val||'%' and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name <> :buscomp

union all

select 'BC User Prop - Child' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.NAME like 'Parent%'||:buscomp and up.VALUE like :attr_val||decode(:wc,'Y','%','') and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name <> :buscomp

union all

select 'Pick Map - Same' attr_type, bc.name obj_name, f.name attr_name, pm.NAME attr_val from siebel.s_pickmap pm, siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where pm.FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and pm.FIELD_ID = f.row_id and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N' and pm.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Pick Map - Other' attr_type, bc.name obj_name, f.name attr_name, pm.NAME attr_val from siebel.s_pickmap pm, siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r, siebel.S_PICKLIST pl
where pm.PICK_FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and pm.FIELD_ID = f.row_id and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and r.row_id = pl.repository_id
and f.inactive_flg = 'N' and pm.inactive_flg = 'N' and f.PICKLIST_NAME = pl.name and pl.BUSCOMP_NAME = :buscomp

union all

select 'MVL Primary Id Field' attr_type, bc.name obj_name, mvl.name attr_name, mvl.PRIMEID_FLD_NAME attr_val from siebel.S_MVLINK mvl, siebel.s_buscomp bc, siebel.s_repository r
where mvl.PRIMEID_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and mvl.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and mvl.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'MVL Source Id Field' attr_type, bc.name obj_name, mvl.name attr_name, mvl.SRC_FLD_NAME attr_val from siebel.S_MVLINK mvl, siebel.s_buscomp bc, siebel.s_repository r
where mvl.SRC_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and mvl.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and mvl.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'MVF Destination Field' attr_type, bc.name obj_name, f.name attr_name, mvl.DEST_BC_NAME||'.'||f.DEST_FLD_NAME attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r, siebel.S_MVLINK mvl
where f.DEST_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and f.BUSCOMP_ID = bc.row_id and mvl.NAME = f.MVLINK_NAME and mvl.BUSCOMP_ID = bc.row_id and mvl.DEST_BC_NAME = :buscomp
and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N'

union all

select 'Link - Destination' attr_type, l.name obj_name, '' attr_name, l.DST_FLD_NAME attr_val from siebel.s_link l, siebel.s_repository r
where l.DST_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and l.CHILD_BC_NAME = :buscomp and l.repository_id = r.row_id and r.name = 'Siebel Repository' and l.inactive_flg = 'N'

union all

select 'Link - Search Spec' attr_type, l.name obj_name, '' attr_name, l.SRCHSPEC attr_val from siebel.s_link l, siebel.s_repository r
where l.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and l.CHILD_BC_NAME = :buscomp and l.repository_id = r.row_id and r.name = 'Siebel Repository' and l.inactive_flg = 'N'

union all

select 'Applet - Search Spec' attr_type, a.name obj_name, '' attr_name, a.SRCHSPEC attr_val from siebel.s_applet a, siebel.s_repository r
where a.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and a.BUSCOMP_NAME = :buscomp and a.repository_id = r.row_id and r.name = 'Siebel Repository' and a.inactive_flg = 'N'

union all

select 'Picklist - Search Spec' attr_type, p.name obj_name, '' attr_name, p.SRCHSPEC attr_val from siebel.S_PICKLIST p, siebel.s_repository r
where p.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and p.BUSCOMP_NAME = :buscomp and p.repository_id = r.row_id and r.name = 'Siebel Repository' and p.inactive_flg = 'N'

union all

select 'Bus Comp - Search Spec' attr_type, bc.name obj_name, '' attr_name, bc.SRCHSPEC attr_val from siebel.s_buscomp bc, siebel.s_repository r
where bc.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and bc.NAME = :buscomp and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and bc.inactive_flg = 'N'

union all

select 'Action Set - Conditional Expression' attr_type, cas.name obj_name, ca.name attr_name, ca.COND_EXPR attr_val from siebel.S_CT_EVENT rt, siebel.s_ct_action ca, siebel.s_ct_action_set cas
where rt.CT_ACTN_SET_ID = cas.row_id and ca.CT_ACTN_SET_ID = cas.row_id and ca.COND_EXPR like '%'||:attr_val||decode(:wc,'Y','%',']%') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Action Set - Attribute Set' attr_type, cas.name obj_name, ca.name attr_name, ca.SET_RHS_EXPR attr_val from siebel.S_CT_EVENT rt, siebel.s_ct_action ca, siebel.s_ct_action_set cas
where rt.CT_ACTN_SET_ID = cas.row_id and ca.CT_ACTN_SET_ID = cas.row_id and ca.SET_RHS_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Run Time Event - Conditional Expression' attr_type, rt.OBJ_NAME obj_name, rt.EVT_NAME attr_name, rt.ACTN_COND_EXPR attr_val from siebel.S_CT_EVENT rt
where rt.ACTN_COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Run Time Event - Sub Event' attr_type, rt.OBJ_NAME obj_name, rt.EVT_NAME attr_name, rt.EVT_SUB_NAME attr_val from siebel.S_CT_EVENT rt
where rt.EVT_SUB_NAME like :attr_val||decode(:wc,'Y','%','') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Integration Component Field' attr_type, io.name obj_name, ic.name attr_name, icf.NAME attr_val from siebel.S_INT_FIELD icf, siebel.S_INT_COMP ic, siebel.S_INT_OBJ io, siebel.s_repository r
where icf.EXT_NAME like :attr_val||decode(:wc,'Y','%','') and icf.INT_COMP_ID = ic.row_id and ic.INT_OBJ_ID = io.row_id and io.repository_id = r.row_id and r.name = 'Siebel Repository'
and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and ic.EXT_NAME = :buscomp

union all

select 'IO Maps - Source Fields' attr_type, o.name obj_name, C.name attr_name, F.SRC_EXPR attr_val from siebel.S_INT_FLDMAP F, siebel.S_INT_COMPMAP C, siebel.S_INT_OBJMAP O, siebel.S_INT_OBJ IO,
siebel.S_INT_COMP IC, siebel.S_INT_FIELD ICF, siebel.s_repository r
where C.int_obj_map_id = O.row_id and F.int_comp_map_id = C.row_id and F.SRC_EXPR like '%'||ICF.NAME||'%' and IC.EXT_NAME like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and O.src_int_obj_name = IO.NAME and c.src_int_comp_name = IC.NAME
and ICF.ext_name = :attr_val||decode(:wc,'Y','%','') and IO.repository_id = r.row_id and r.name = 'Siebel Repository' and ICF.INT_COMP_ID = IC.row_id
and IC.INT_OBJ_ID = IO.row_id and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and (O.COMMENTS is null or O.COMMENTS not like 'ARCHIVE%')

union all

select 'IO Maps - Destination Fields' attr_type, o.name obj_name, C.name attr_name, F.dst_int_fld_name attr_val from siebel.S_INT_FLDMAP F, siebel.S_INT_COMPMAP C, siebel.S_INT_OBJMAP O, siebel.S_INT_OBJ IO,
siebel.S_INT_COMP IC, siebel.S_INT_FIELD ICF, siebel.s_repository r
where C.int_obj_map_id = O.row_id and F.int_comp_map_id = C.row_id and F.dst_int_fld_name = ICF.NAME and IC.EXT_NAME like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and O.dst_int_obj_name = IO.NAME and c.dst_int_comp_name = IC.NAME
and ICF.ext_name = :attr_val||decode(:wc,'Y','%','') and IO.repository_id = r.row_id and r.name = 'Siebel Repository' and ICF.INT_COMP_ID = IC.row_id
and IC.INT_OBJ_ID = IO.row_id and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and (O.COMMENTS is null or O.COMMENTS not like 'ARCHIVE%')

union all

select 'Data Maps - Source Fields' attr_type, o.name obj_name, C.name attr_name, f.src_fld_name attr_val from siebel.S_FIELD_DMAP F, siebel.S_BUSCOMP_DMAP C, siebel.S_BUSOBJ_DMAP O
where C.BUSOBJ_DMAP_ID = O.row_id and F.BUSCOMP_DMAP_ID = C.row_id and F.SRC_FLD_NAME like '%'||:attr_val||'%' and c.SRC_BUSCOMP_NAME = :buscomp

union all

select 'Data Maps - Destination Fields' attr_type, o.name obj_name, C.name attr_name, f.DST_FLD_NAME attr_val from siebel.S_FIELD_DMAP F, siebel.S_BUSCOMP_DMAP C, siebel.S_BUSOBJ_DMAP O
where C.BUSOBJ_DMAP_ID = O.row_id and F.BUSCOMP_DMAP_ID = C.row_id and F.DST_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and c.DST_BUSCOMP_NAME = :buscomp

union all

select 'WF Branch Criteria - Field' attr_type, wf.name obj_name, s.name attr_name, b.name attr_val from siebel.S_WFR_COND_CRIT cc, siebel.S_WFR_STP_BRNCH b, siebel.S_WFR_STP s, siebel.S_WFR_PROC wf, siebel.s_repository r
where cc.BUSCOMP_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and cc.BRANCH_ID = b.row_id and b.STEP_ID = s.row_id and s.PROCESS_ID = wf.row_id and wf.repository_id = r.row_id and r.name = 'Siebel Repository'
and wf.STATUS_CD = 'COMPLETED' and cc.BUSCOMP_NAME = :buscomp

union all

select 'WF Branch Criteria - Expression' attr_type, wf.name obj_name, s.name attr_name, b.name attr_val from siebel.S_WFR_COND_VAL cv, siebel.S_WFR_COND_CRIT cc, siebel.S_WFR_STP_BRNCH b, siebel.S_WFR_STP s, siebel.S_WFR_PROC wf, siebel.s_repository r
where cv.LO_CHAR5 like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and cv.COND_CRIT_ID = cc.row_id and cc.BRANCH_ID = b.row_id and b.STEP_ID = s.row_id and s.PROCESS_ID = wf.row_id and wf.repository_id = r.row_id and r.name = 'Siebel Repository'
and wf.STATUS_CD = 'COMPLETED' and cc.BUSCOMP_NAME = :buscomp

union all

select 'WF Branch Step - Argument' attr_type, wf.name obj_name, s.name attr_name, a.name attr_val from siebel.S_WFR_STP_ARG a, siebel.S_WFR_STP s, siebel.S_WFR_PROC wf, siebel.s_repository r
where a.BUSCOMP_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and a.STEP_ID = s.row_id and s.PROCESS_ID = wf.row_id and wf.repository_id = r.row_id and r.name = 'Siebel Repository'
and wf.STATUS_CD = 'COMPLETED' and a.BUSCOMP_NAME = :buscomp

union all

select 'DVM - Rule' attr_type, rs.name obj_name, r.name attr_name, r.RULE_EXPR attr_val from siebel.S_VALDN_RULE R, siebel.S_VALDN_RL_SET RS
where R.RULE_SET_ID = rs.row_id and r.RULE_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and r.BUSCOMP_NAME = :buscomp and rs.status_cd = 'Active'

union all

select 'Personalization - Applet Condition' attr_type, a.APPLET_NAME obj_name, a.APPLET_NAME attr_name, A.VIS_COND_EXPR attr_val from siebel.S_CT_APPLET a, siebel.S_APPLET ra, siebel.s_repository r
where a.APPLET_NAME = ra.name and a.VIS_COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and r.name = 'Siebel Repository' and ra.repository_id = r.row_id and ra.inactive_flg = 'N'
and ra.buscomp_name like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and ra.INACTIVE_FLG = 'N' 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)

union all

select 'Personalization - Applet Rule Set' attr_type, a.APPLET_NAME obj_name, to_char(ar.SEQ_NUM) attr_name, ar.COND_EXPR attr_val from siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository r
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and ar.COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and r.name = 'Siebel Repository' and ra.inactive_flg = 'N' and ra.repository_id = r.row_id
and ra.buscomp_name like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and ra.INACTIVE_FLG = 'N' 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)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Personalization - Rule Condition' attr_type, rs.name obj_name, r.Name attr_name, r.COND_EXPR attr_val from siebel.S_CT_RULE R, siebel.S_CT_RULE_SET RS, siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository rep
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and rep.name = 'Siebel Repository' and ra.repository_id = rep.row_id and ra.inactive_flg = 'N' and rs.row_id = ar.ct_rule_set_id
and R.CT_RULE_SET_ID = rs.row_id and r.COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%')
and ra.BUSCOMP_NAME = :buscomp and rs.ACTIVE_FLG = 'Y' and r.ACTIVE_FLG = 'Y' and ra.INACTIVE_FLG = 'N' 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)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Personalization - Rule Include Condition' attr_type, rs.name obj_name, r.Name attr_name, r.INCL_RULE_EXPR attr_val from siebel.S_CT_RULE R, siebel.S_CT_RULE_SET RS, siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository rep
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and rep.name = 'Siebel Repository' and ra.repository_id = rep.row_id and ra.inactive_flg = 'N' and rs.row_id = ar.ct_rule_set_id
and R.CT_RULE_SET_ID = rs.row_id and r.INCL_RULE_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%')
and ra.BUSCOMP_NAME = :buscomp and rs.ACTIVE_FLG = 'Y' and r.ACTIVE_FLG = 'Y' and ra.INACTIVE_FLG = 'N' 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)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Personalization - Rule Exclude Condition' attr_type, rs.name obj_name, r.Name attr_name, r.EXCL_RULE_EXPR attr_val from siebel.S_CT_RULE R, siebel.S_CT_RULE_SET RS, siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository rep
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and rep.name = 'Siebel Repository' and ra.repository_id = rep.row_id and ra.inactive_flg = 'N' and rs.row_id = ar.ct_rule_set_id
and R.CT_RULE_SET_ID = rs.row_id and r.EXCL_RULE_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%')
and ra.BUSCOMP_NAME = :buscomp and rs.ACTIVE_FLG = 'Y' and r.ACTIVE_FLG = 'Y' and ra.INACTIVE_FLG = 'N' 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)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Batch Job - Search' attr_type, a.DISPLAY_NAME obj_name, wf.NAME attr_name, ps.VALUE attr_val
from siebel.S_SRM_REQUEST q, siebel.S_SRM_ACTION a, siebel.S_SRM_REQ_PARAM ps, siebel.S_SRM_ACT_PARAM psp, siebel.S_SRM_REQ_PARAM pw, siebel.S_SRM_ACT_PARAM pwp, siebel.S_WFR_PROC wf, siebel.s_repository r, siebel.S_BUSOBJ bo
where ps.REQ_ID = q.row_id and ps.VALUE like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and q.STATUS = 'ACTIVE' AND q.REQ_TYPE_CD = 'RPT_PARENT' and ps.ACTPARAM_ID = psp.ROW_ID
and psp.NAME = 'Search Specification' and pw.REQ_ID = q.row_id and pw.ACTPARAM_ID = pwp.ROW_ID and pwp.NAME = 'Workflow Process Name' and a.row_id = q.action_id and pw.value = wf.PROC_NAME and wf.REPOSITORY_ID = r.row_id
and r.name = 'Siebel Repository' and bo.repository_id = r.row_id and wf.STATUS_CD = 'COMPLETED' and wf.BUSOBJ_NAME = bo.name and bo.PR_BUSCOMP_NAME like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','')
);