Miscellaneous SQL Repository Research

I frequently use SQL to track down potential issues in the repository.  Here are a couple of statements that help solve common problems:

Fields Inactivated in the BC but Active in the Integration Object

select io.name int_obj, ic.name int_comp, icf.NAME int_field from siebel.S_INT_FIELD icf, siebel.S_INT_COMP ic, siebel.S_INT_OBJ io, siebel.s_repository r, siebel.s_buscomp bc, siebel.s_field f
where 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 ic.EXT_NAME = bc.name and icf.EXT_NAME = f.name and f.buscomp_id = bc.row_id and bc.repository_id = r.row_id and f.inactive_flg = 'Y'
and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and f.last_upd > to_date('11/01/2012', 'MM/DD/YYYY');

 Identify potential causes of the Truncation or Null Fetch error

This can sometimes be caused by not compiling the Table object too.  The first query finds business component joined fields where the BC field length or data type does not match the column length.  The second does the same but only analyzes base table columns and adds a last updated parameter which could be added or removed from either.  The last updated date is useful because vanilla actually has numerous instances of this potential problem.  I say potential because any records returned need to be checked against the data.  The error will only occur if the data in the column is longer than the BC length specified.  The Third query is an example of how to check assuming for instance S_ORDER_ITEM.ATTRIB_40 is mapped to a DTYPE_ID BC field..

select bc.name Bus_comp, f.name field, f.join_name Join, j.DEST_TBL_NAME Tbl_name, f.col_name Col_name, f.type Field_Type, f.textlen Fld_Lgth, c.DATA_TYPE Col_type, c.LENGTH col_lgth
from siebel.s_field f, siebel.s_repository r, siebel.s_join j, siebel.s_buscomp bc, siebel.s_column c, siebel.s_table t
where bc.REPOSITORY_ID = r.row_id and r.name = 'Siebel Repository'
and bc.row_id = f.buscomp_id and j.name = f.join_name and j.buscomp_id = bc.row_id
and c.TBL_ID = t.row_id and t.NAME = j.DEST_TBL_NAME and c.name = f.col_name
and r.row_id = t.REPOSITORY_ID and r.row_id = f.REPOSITORY_ID and r.row_id = c.REPOSITORY_ID
and t.INACTIVE_FLG = 'N' and c.INACTIVE_FLG = 'N' and bc.INACTIVE_FLG = 'N' and f.INACTIVE_FLG = 'N'
and ((f.type = 'DTYPE_ID' and c.length > 15)
or (f.type = 'DTYPE_PHONE' and c.length > 40)
or (f.type = 'DTYPE_BOOL' and c.length > 1)
or (f.type = 'DTYPE_TEXT' and c.DATA_TYPE like 'Date%'))
order by j.DEST_TBL_NAME, f.col_name;

select bc.name Bus_comp, f.name field, t.NAME tbl_name, f.col_name Col_name, f.type Field_Type, f.textlen Fld_Lgth, c.DATA_TYPE Col_type, c.LENGTH col_lgth
from siebel.s_field f, siebel.s_repository r, siebel.s_buscomp bc, siebel.s_column c, siebel.s_table t
where bc.REPOSITORY_ID = r.row_id and r.name = 'Siebel Repository'
and bc.row_id = f.buscomp_id
and c.TBL_ID = t.row_id and t.NAME = bc.TABLE_NAME and c.name = f.col_name
and r.row_id = t.REPOSITORY_ID and r.row_id = f.REPOSITORY_ID and r.row_id = c.REPOSITORY_ID
and t.INACTIVE_FLG = 'N' and c.INACTIVE_FLG = 'N' and bc.INACTIVE_FLG = 'N' and f.INACTIVE_FLG = 'N'
and ((f.type = 'DTYPE_ID' and c.length > 15)
or (f.type = 'DTYPE_PHONE' and c.length > 40)
or (f.type = 'DTYPE_BOOL' and c.length > 1)
or (f.type = 'DTYPE_TEXT' and c.DATA_TYPE like 'Date%'))
and f.last_upd > to_date('06/01/2010', 'MM/DD/YYYY')
order by t.NAME, f.col_name;

select ATTRIB_40 from siebel.S_ORDER_ITEM_XM where length(ATTRIB_40) > 15;

WF Deployed but not activated:

select wpr.proc_name, wpr.version from siebel.S_WFR_PROC wpr, siebel.s_repository r
where r.row_id = wpr.REPOSITORY_ID and r.name = 'Siebel Repository' and wpr.STATUS_CD = 'COMPLETED'
and wpr.name like 'PPT%'
and exists (select 'x' from siebel.S_WFA_DPLOY_DEF wpd where wpd.NAME = wpr.proc_name and wpd.TYPE_CD = 'PROCESS' and wpd.DEPLOY_STATUS_CD = 'ACTIVE'
            and wpd.REPOSITORY_VERSION <> wpr.VERSION)
and wpr.version = (select max(wpr1.version) from siebel.S_WFR_PROC wpr1
                  where r.row_id = wpr1.REPOSITORY_ID and wpr1.STATUS_CD = 'COMPLETED' and wpr1.proc_name = wpr.proc_name)
order by wpr.proc_name;

BC Fields not configured as boolean that should be

select bc.name, f.name, f.type, f.CALCVAL from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository'
and (f.CALCVAL like '%,%Y%,%N%)' OR f.CALCVAL like '%,%N%,%Y%)')
and f.TYPE <> 'DTYPE_BOOL'
and f.inactive_flg = 'N' and bc.name like 'PPT%' and bc.inactive_flg = 'N'
order by bc.name, f.name;

select bc.name, f.name, f.type, f.CALCVAL from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository'
and f.TEXTLEN = 1
and f.TYPE <> 'DTYPE_BOOL'
and f.inactive_flg = 'N' and bc.name like 'PPT%' and bc.inactive_flg = 'N'
order by bc.name, f.name;

Applet controls where checkbox not configured correctly

select a.name, lc.name, lc.field_name, lc.HTML_TYPE, lc.HTML_ICON_MAP, f.type from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r, siebel.s_field f, siebel.s_buscomp bc
where 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 (lc.HTML_ICON_MAP is null or lc.HTML_TYPE <> 'CheckBox')
and f.name = lc.FIELD_NAME and f.BUSCOMP_ID = bc.row_id and bc.name = a.BUSCOMP_NAME and f.TYPE = 'DTYPE_BOOL' and a.name like 'PPT%';

Integration Object Maps that have changed since a prior release:

select release, Map_Name, change_level, Field_Upd, Field_User, Comp_Upd, Comp_User, Obj_Upd, Obj_user, Comp_Name, src_expr, dst_int_fld_name, Obj_Comments
from ( -- Object level
select o.x_release release, o.name Map_Name, '1-Object' change_level, null Field_Upd, null Field_User, null Comp_Upd, null Comp_User, o.last_upd Obj_Upd, ou.login Obj_user, null Comp_Name, null src_expr, null dst_int_fld_name, o.comments Obj_Comments
from siebel.S_INT_OBJMAP o, siebel.s_user ou
where o.last_upd_by = ou.row_id and o.last_upd >= to_date('04/23/2019', 'MM/DD/YYYY')
UNION ALL -- Component level
select o.x_release release, o.name Map_Name, '2-Component' change_level, null Field_Upd, null Field_User, c.last_upd Comp_Upd, cu.login Comp_User, null Obj_Upd, null Obj_user, c.name Comp_Name, c.SRC_SRCHSPEC src_expr, null dst_int_fld_name, o.comments Obj_Comments
from siebel.S_INT_OBJMAP o, siebel.S_INT_COMPMAP c, siebel.s_user cu, siebel.s_user ou
where c.int_obj_map_id = o.row_id and c.last_upd_by = cu.row_id and o.last_upd_by = ou.row_id --and o.x_release = '1.27.12'
and c.last_upd >= to_date('4/23/2019', 'MM/DD/YYYY')
UNION ALL -- Field level
select o.x_release release, o.name Map_Name, '3-Field' change_level, f.last_upd Field_Upd, fu.login Field_User, null Comp_Upd, null Comp_User, null Obj_Upd, null Obj_user, c.name Comp_Name, f.src_expr, f.dst_int_fld_name, o.comments Obj_Comments
from siebel.S_INT_OBJMAP o, siebel.S_INT_COMPMAP c, siebel.S_INT_FLDMAP f, siebel.s_user fu, siebel.s_user cu, siebel.s_user ou
where f.int_comp_map_id = c.row_id and c.int_obj_map_id = o.row_id and f.last_upd_by = fu.row_id and c.last_upd_by = cu.row_id and o.last_upd_by = ou.row_id --and o.x_release = '1.27.12'
and (f.last_upd >= to_date('4/23/2019', 'MM/DD/YYYY'))
) where release <> '1.27.13'
order by Map_Name, change_level;

Repository Search by SQL

Tools offers a Repository Search feature which is quite robust but it has some significant drawbacks:

1. Tedious to specify only those items which might have the object name you are looking for
2. Time consuming to do a full search or a search of certain objects with high record counts
3. Does not search UI admin objects where a field might be referenced

There have been times when I wanted to inactivate objects in order to streamline the application and have had difficulty identifying all the references.  Or if you see an error in the Siebel log, it is sometimes hard to forensically determine its source.

I have written a series of SQL statements to search for the items directly against the server tools DB.  This SQL looks at both repository tables where a particular object type might be referenced as well as UI administrative objects.  It is not guaranteed to be 100% comprehensive and should be modified to the particular needs of how a client has used the application.  For instance, if Variable Maps are not modified from vanilla, it is probably not necessary to query the tables that store them.

Fields
Applets

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)
)

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','%','')
);

My BI Quick Reference

I know there are a bunch of cheat sheets out there, but I frequently don't find everything I am looking for in one place so figured I would just start building my own.

Siebel Functions

Include statement

<?namespace:psfn=http://www.oracle.com/XSL/Transform/java/com.siebel.xmlpublisher.reports.XSLFunctions?>

Date Conversion

<?psfn:totext(OrderDate,"MM/dd/yyyy","MM/dd/yyyy hh:mm:ss")?>

Loops:

Basic Loop where QuoteItem is the Integration Component/XML Group

<?for-each:QuoteItem?>

Add a where clause to constrain rows in the loop.  Multiple constraints can be added back to back with each bracketed section representing an AND.  An OR would need to be done inside a single bracketed expression.  the .// is an xpath expression to determine the XML group of the field

<?for-each:QuoteItem[.//LineType='Sales']?>
<?for-each:QuoteItem[.//LineType='Sales'][.//LineNumber<100]?>
<?for-each:QuoteItem[.//LineType='Sales' or .//LineType='Service']?>

Groupings 

Loop that groups by the column LineType and sorts by the grouping

<?for-each-group:QuoteItem;./LineType?><?sort:current-group()/LineType;'ascending';data-type='text'?>

A nested loop showing the sub group records for the loop above:

<?for-each:current-group()?><?sort:Product;'ascending';data-type='text'?>

Loop that groups records together by a particular column and makes each grouping a column in a table

<?for-each-group@column:QuoteItem;./LineType?>

Similar to grouping by column is to group by section.  In this case each grouping creates a heading when the report breaks across multiple pages.

<?for-each@section:G_CUSTOMER?>

Group Expressions

This expression is used to sum a column from series of records outside the context of a loop.  The expression in the bracket specifies those records to include, in this case only non null values.  This is an XPath expression.

<?sum(.//ItemExtendedPriceTotal[.!=''])?>

Conditionals:

If/else

<?if:Comment!=''?>
<?Comment?>
<?end if?>

Keep in mind that Carriage returns outside the expression will still appear so consider this when judging where to put the donditional

Switch/Case/Select/Choose:

<?choose:?> <?when:MY_FIELD='value tested'?> <?call:template?> <?end when?> <?when:MY_FIELD_2='value tested 2'?> <?call:template_2?> <?end when?> <?otherwise:?> <?call:template_other?> <?end otherwise?> <?end choose?> 

Embedding a 64 Bit Image

For this to work, there must be a 64 Bit attachment embedded in the Integration Object being sent to BI. In this example,QuoteAttachment is the element name of the IC Field of type DTYPE_ATTACHMENT containing the 64 Bit inline image.

<fo:instream-foreign-object content-type="image/jpg"><?QuoteAttachment?></fo:instream-foreign-object>

Additional attributes for the fo:instream-foreign-object tag in addition to content-type can resize the image: Specify in pixels as follows:

<fo:instream-foreign-object content type="image/jpg" height="300 px" width="4 px">

... or in centimeters:

<fo:instream-foreign-object content type="image/jpg" height="3 cm" width="4 cm">

... or as a percentage of the original dimensions:

<fo:instream-foreign-object content type="image/jpg" height="300%" width="300%"> ...