This is just another attempt to unpack Siebel Audit Trail CLOB value through PL/SQL. This time it is with a "classic" select statement.

It is definitely not the most performant solution but useful if you want to quickly look through a handful of S_AUDIT_ITEM records without a need to open Siebel views or install SQL packages.

Might give you an idea of how to leverage a recursive SQL for parsing purposes.

with user_select as (
    -- free form S_AUDIT_ITEM select should include at least ROW_ID, AUDIT_LOG, BUSCOMP_NAME, TBL_NAME columns
    select *
    from siebel.S_AUDIT_ITEM
    where AUDIT_LOG is not null and rownum <= 5
    order by row_id desc
),
-- split audit_log value into tokens
tokens (ROW_ID, LVL, AUDIT_LOG, BUSCOMP_NAME, TBL_NAME, REMAIN, TOKEN_LEN, TOKEN, NEW_POS) as (
    -- maybe it is worth fetching first token here?
    select ROW_ID, 0 LVL, AUDIT_LOG, BUSCOMP_NAME, TBL_NAME,
        AUDIT_LOG REMAIN,
        0 TOKEN_LEN,
        null TOKEN,
        1 NEW_POS
    from user_select
    union all
    select i.ROW_ID, p.LVL + 1 LVL, i.AUDIT_LOG, i.BUSCOMP_NAME, i.TBL_NAME,
        SUBSTR(p.AUDIT_LOG, p.NEW_POS) REMAIN,  -- remaining AUDIT_LOG value after cutting processed tokens (for debugging)
        TO_NUMBER(REGEXP_SUBSTR(i.AUDIT_LOG, '(\d*)\*', p.NEW_POS, 1, '', 1)) TOKEN_LEN, -- length of the following token (for debugging)
         -- token lenght comes before * and then a comes a token of that lenght
        REGEXP_SUBSTR(p.AUDIT_LOG, '\*(.{' || REGEXP_SUBSTR(i.AUDIT_LOG, '(\d*)\*', p.NEW_POS, 1, '', 1) || '})', p.NEW_POS, 1, '', 1) TOKEN,
        -- position at which starts next token length (current position + token lenght + lenght of token lenght + 1(* sign))
        p.NEW_POS + TO_NUMBER(REGEXP_SUBSTR(i.AUDIT_LOG, '(\d*)\*', p.NEW_POS, 1, '', 1)) + length(REGEXP_SUBSTR(i.AUDIT_LOG, '(\d*)\*', p.NEW_POS, 1, '', 1)) + 1 NEW_POS
    from user_select i
        join tokens p on p.ROW_ID = i.ROW_ID and p.NEW_POS < length(i.AUDIT_LOG) -- recursive join until we reach the end of AUDIT_LOG value
),
-- group tokens into C,N,O,J,L,K arrays and number rows
blocks (ROW_ID, LVL, AUDIT_LOG, BUSCOMP_NAME, TBL_NAME, TOKEN, TYPE, ROWCNT, NUM) as (
    select ROW_ID, LVL, AUDIT_LOG, BUSCOMP_NAME, TBL_NAME, TOKEN, 
        TO_CHAR(SUBSTR(TOKEN, 1, 1)) TYPE, -- first token indicates the meaning of following tokens (C, N, O, J, L, K)
        TO_NUMBER(SUBSTR(TOKEN, 2)) ROWCNT, -- and a count of following tokens
        0 NUM -- mark first(technical) token to filter it out later
    from tokens
    where lvl = 1
    union all
    select t.ROW_ID, t.LVL, t.AUDIT_LOG, t.BUSCOMP_NAME, t.TBL_NAME, t.TOKEN, 
        CASE WHEN p.ROWCNT = p.NUM  -- fetch next technical token after reached the end of token array
            THEN TO_CHAR(SUBSTR(t.TOKEN, 1, 1))
            ELSE p.TYPE
        END TYPE,
        CASE WHEN p.ROWCNT = p.NUM
            THEN TO_NUMBER(SUBSTR(t.TOKEN, 2))
            ELSE p.ROWCNT
        END ROWCNT,
        CASE WHEN p.ROWCNT = p.NUM -- also reset a row number
            THEN 0
            ELSE p.NUM + 1
        END NUM
    from tokens t
        join blocks p on t.row_id = p.row_id and t.lvl = p.lvl + 1
),
-- combine arrays into records and map columns into fields
recs as (
    -- J(oin) entries are recorded with field names
    select j.*, to_char(j.token) FIELD_NAME, o.token OLD_VAL, n.token NEW_VAL
    from blocks j
        join blocks n on n.row_id = j.row_id and n.num = j.num and n.type = 'L'
        join blocks o on o.row_id = j.row_id and o.num = j.num and o.type = 'K'
    where j.num > 0 and j.type = 'J'
    union all
    -- C(olumn) entries need to be mapped to field names through [Administration - Audit Trail]
    select c.*, NVL(a.field_name, to_char(c.token)) FIELD_NAME, o.token OLD_VAL, n.token NEW_VAL
    from blocks c
        join blocks n on n.row_id = c.row_id and n.num = c.num and n.type = 'N'
        join blocks o on o.row_id = c.row_id and o.num = c.num and o.type = 'O'
        left join (
            select b.buscomp_name, f.tbl_name, f.col_name, f.field_name,
                ROW_NUMBER() OVER (PARTITION BY b.buscomp_name, f.tbl_name, f.col_name ORDER BY f.created desc) row_num
            from siebel.S_AUDIT_BUSCOMP b
                join siebel.S_AUDIT_FIELD f on f.audit_bc_id = b.row_id
        ) a on a.col_name = to_char(c.token) and a.buscomp_name = c.buscomp_name and a.tbl_name = c.tbl_name and row_num = 1 -- fetching only last created field entry
    where c.num > 0 and c.type = 'C'
)
select ROW_ID, NUM, FIELD_NAME, NEW_VAL, OLD_VAL 
from recs 
    -- here you can join original S_AUDIT_LOG records by ROW_ID if you need more info
order by ROW_ID desc, TYPE desc, NUM asc;

And it is also on GitHub.

Today I want to share a couple of SQLs which helps me to leverage workspaced repository tables.

Modified objects

The first one is a simple flat query of workspace affected objects. Same data you see when clicking workspace in Siebel Tools.
Filter by the object name/type to get an idea of who is modifying/modified the object.

select w.name WS_NAME, w.status_cd WS_STATUS, v.VERSION_NUM WS_VER, pw.name BASE_WS_NAME,
  u.fst_name WS_AUTHOR, o.OBJ_TYPE , o.OBJ_NAME, o.operation_cd, o.last_upd
from siebel.S_REPOSITORY r -- repository
  join siebel.S_WORKSPACE pw on r.ROW_ID = pw.REPOSITORY_ID -- baseline workspace
  join siebel.S_WORKSPACE w on w.PAR_WS_ID = pw.ROW_ID -- child / dev workspace with changes
  join siebel.S_WS_VERSION v on v.ws_id = w.row_id -- versions of the workspace
  join siebel.S_CONTACT u on u.ROW_ID = w.CREATED_BY -- ws author
  join siebel.S_RTC_MOD_OBJ o on o.WS_VER_ID = v.ROW_ID and r.ROW_ID = o.REPOSITORY_ID -- modified objects
where r.name = 'Siebel Repository'
    and w.NAME = 'dev_vbabkin_d412_1' -- filter by dev branch name
    --and pw.NAME = 'int_dev' -- or baseline/parent branch name
    --and o.obj_type = 'Business Component' and o.obj_name = 'Account' -- or by affected object
order by o.last_upd desc;

Keep in mind that SQL only lists top-level objects (Applet, BusComp, Picklist etc) without any specific details.

Complete object modification history

The second query gives full information about object modifications in delivered workspaces including each attribute across all sub-objects.
When you deliver a workspace Siebel logs the history of all modifications done to the objects in your workspace. Below SQL just puts all the logs tables together.

select v.VERSION_NUM WS_VER, dw.name WS_NAME, u.fst_name AUTHOR, m.last_upd, w.name BASE_WS_NAME,
    c.TOP_PARENT_TYPE TOP_OBJ_TYPE, c.TOP_PARENT_NAME TOP_OBJ_NAME,
    c.obj_type, c.object_name, 
    a.attribute, NVL(a.old_cust_value, a.old_std_value) OLD_VAL, a.new_std_value NEW_VAL,
    c.OBJ_NAME, c.STATUS
from siebel.S_REPOSITORY r -- repository
  join siebel.S_WORKSPACE w on r.ROW_ID = w.REPOSITORY_ID -- baseline workspace
  join siebel.S_WS_VERSION v on w.ROW_ID = v.WS_ID and r.ROW_ID = v.REPOSITORY_ID -- versions of baseline workspace
  join siebel.S_WS_MERGE_LOG m on m.RESULT_WS_VER_ID = v.ROW_ID -- workspace merge info
  join siebel.S_WS_VERSION dv on dv.ROW_ID = m.FROM_WS_VER_ID -- versions of delivered workspace
  join siebel.S_WORKSPACE dw on dw.ROW_ID = dv.WS_ID -- delivered workspace (child branches)
  join siebel.S_CONTACT u on u.ROW_ID = dw.CREATED_BY -- ws author
  left join siebel.S_WS_MG_LOGOBJ c on c.WS_MERGE_LOG_ID = m.row_id -- modified child objects3445rg
  left join siebel.S_WS_MG_LOGATTR a on a.ws_merge_log_id = m.row_id and a.ws_mg_logobj_id = c.row_id -- modified attributes
where r.name = 'Siebel Repository'
  and w.NAME = 'int_dev' --filter by parent/destination/baseline workspace name
  --and dw.name like 'dev_vbabkin_d412_1' -- by delivered workspace name
  and c.TOP_PARENT_TYPE = 'Business Component' and c.TOP_PARENT_NAME = 'Account' -- by top level object
  --and c.obj_type='Business Component User Prop' and c.object_name like 'Named Method%' -- by child object (could be same as top if you are looking for attrib changes)
  --and a.attribute = 'Inactive' and a.new_std_value = 'Y' -- by attribute
order by v.VERSION_NUM desc, c.TOP_PARENT_NAME, c.object_name;

Unlike the first SQL it doesn't give you info about in-progress workspaces.

Workspace delivery in progress

Finally, as a bonus below is a SQL to check if there is a workspace delivery in progress. Run it before delivering your workspace to avoid annoying "Another workspace delivery/rebase is in-progress. Please try after sometime." error with a failed workspace to be resubmitted.

DECLARE
    wsname varchar(30) := 'int_dev'; -- baseline/parent workspace where you delivering to
BEGIN
    SELECT name into wsname FROM siebel.S_WORKSPACE w WHERE w.name = wsname FOR UPDATE NOWAIT;
    ROLLBACK;
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -54 THEN
            raise_application_error(-20001, 'Another delivery in progress'); 
        ELSE
            ROLLBACK;
        END IF;
END;

To be continued...

This is one of my favourite repository SQL statements. It connects all layers of Siebel application into one flat structure:

Here are just a few of the questions it can answer:

• Where a particular column (field or BC) is exposed in UI?
• Under which caption a particular column/field is exposed in UI?
• Is my field or column is already used in the application?
• What fields or columns are exposed on a certain view (applet or application)?
• What views are available in my application?

I'll share the most full version of the SQL, but feel free to tune it up to your purpose.

SELECT distinct
  t.NAME "TABLE", col.NAME "COLUMN",
  b.NAME "BUSCOMP", f.NAME "FIELD", 
  nvl(ci.CAPTION, li.DISPLAY_NAME) "CAPTION", nvl(c.NAME, lc.NAME) "CONTROL", a.NAME "APPLET",
  s.NAME "SCREEN", v.NAME "VIEW", ap.NAME "APPLICATION"
FROM siebel.S_REPOSITORY r
  -- workspace
  join siebel.S_WORKSPACE ws on ws.REPOSITORY_ID = r.ROW_ID
  -- application
  join siebel.S_APPLICATION ap on ap.REPOSITORY_ID = r.ROW_ID and ws.ROW_ID = ap.WS_ID
  join siebel.S_PAGE_TAB aps on aps.APPLICATION_ID = ap.WS_SRC_ID and aps.INACTIVE_FLG = 'N' and ws.ROW_ID = aps.WS_ID
  -- screen
  join siebel.S_SCREEN s on s.name = aps.SCREEN_NAME and s.REPOSITORY_ID = r.ROW_ID AND s.INACTIVE_FLG = 'N' and ws.ROW_ID = s.WS_ID
  join siebel.S_SCREEN_VIEW sv on sv.screen_id = s.WS_SRC_ID and sv.INACTIVE_FLG = 'N' and ws.ROW_ID = sv.WS_ID
  -- view
  join siebel.S_VIEW v on v.REPOSITORY_ID = r.ROW_ID and sv.VIEW_NAME = v.name and ws.ROW_ID = v.WS_ID
  join siebel.S_VIEW_WEB_TMPL vt on vt.VIEW_ID = v.WS_SRC_ID and vt.INACTIVE_FLG = 'N' and ws.ROW_ID = vt.WS_ID
  join siebel.S_VIEW_WTMPL_IT vti on vti.VIEW_WEB_TMPL_ID = vt.WS_SRC_ID and vti.INACTIVE_FLG = 'N' and ws.ROW_ID = vti.WS_ID
  -- applet (only form, list applets)
  join siebel.S_APPLET a on a.REPOSITORY_ID = r.ROW_ID and a.name = vti.APPLET_NAME and ws.ROW_ID = a.WS_ID
  join siebel.S_APPL_WEB_TMPL w on w.applet_id = a.WS_SRC_ID and w.TYPE = vti.APPLET_MODE_CD and w.INACTIVE_FLG ='N' and ws.ROW_ID = w.WS_ID
  join siebel.S_APPL_WTMPL_IT wi on wi.APPL_WEB_TMPL_ID = w.WS_SRC_ID and wi.INACTIVE_FLG = 'N' and ws.ROW_ID = wi.WS_ID
  -- control
  left join siebel.S_CONTROL c on c.APPLET_ID = a.WS_SRC_ID and wi.CTRL_NAME = c.name and c.INACTIVE_FLG = 'N' and ws.ROW_ID = c.WS_ID
  left join siebel.S_LIST l on l.APPLET_ID = a.WS_SRC_ID and ws.ROW_ID = l.WS_ID
  left join siebel.S_LIST_COLUMN lc on lc.LIST_ID = l.WS_SRC_ID and wi.CTRL_NAME = lc.name and ws.ROW_ID = lc.WS_ID
  -- control caption (only overrides!)
  left join siebel.S_CONTROL_INTL ci on ci.CONTROL_ID = c.WS_SRC_ID and ws.ROW_ID = ci.WS_ID
  left join siebel.S_LIST_COL_INTL li on li.LIST_COLUMN_ID = lc.WS_SRC_ID and ws.ROW_ID = li.WS_ID
  -- buscomp
  join SIEBEL.S_BUSCOMP b on b.name = a.BUSCOMP_NAME and b.REPOSITORY_ID = r.ROW_ID and ws.ROW_ID = b.WS_ID
  -- fields exposed either through control or list column
  join siebel.S_FIELD f on f.BUSCOMP_ID = b.WS_SRC_ID
    and (lc.FIELD_NAME = f.name or c.FIELD_NAME = f.name) and ws.ROW_ID = f.WS_ID
  -- join
  left join siebel.S_JOIN j on j.name = f.JOIN_NAME and j.BUSCOMP_ID = f.BUSCOMP_ID and ws.ROW_ID = j.WS_ID
  -- table
  join siebel.S_TABLE t on t.REPOSITORY_ID = r.ROW_ID
    and (t.name = b.TABLE_NAME and f.join_name is null  -- base table
    or t.name = j.DEST_TBL_NAME and j.row_id is not null  -- explicit joins
    or t.name = f.JOIN_NAME and j.row_id is null) -- implicit joins
  -- column
  join siebel.S_COLUMN col on col.TBL_ID = t.ROW_ID and f.COL_NAME = col.name and ws.ROW_ID = c.WS_ID
WHERE r.name = 'Siebel Repository'
  and ws.name = 'MAIN'
  and ap.name LIKE 'Siebel Financial Services'
ORDER BY 1, 2, 3;

Keep in mind that the SQL:

• is not optimised and could take time to return results
• doesn't query popup/assoc/pick applets and task views
• supports only caption overrides
• only works for root workspace (MAIN)

Here is also a version for old Siebel repositories (non-workspace): Snippet

SELECT distinct
  t.NAME "TABLE", col.NAME "COLUMN",
  b.NAME "BUSCOMP", f.NAME "FIELD", 
  nvl(ci.CAPTION, li.DISPLAY_NAME) "CAPTION", nvl(c.NAME, lc.NAME) "CONTROL", a.NAME "APPLET",
  s.NAME "SCREEN", v.NAME "VIEW", ap.NAME "APPLICATION"
FROM siebel.S_REPOSITORY r
  -- application
  join siebel.S_APPLICATION ap on ap.REPOSITORY_ID = r.ROW_ID
  join siebel.S_PAGE_TAB aps on aps.APPLICATION_ID = ap.ROW_ID and aps.INACTIVE_FLG = 'N'
  -- screen
  join siebel.S_SCREEN s on s.name = aps.SCREEN_NAME and s.REPOSITORY_ID = r.ROW_ID AND s.INACTIVE_FLG = 'N'
  join siebel.S_SCREEN_VIEW sv on sv.screen_id = s.ROW_ID and sv.INACTIVE_FLG = 'N'
  -- view
  join siebel.S_VIEW v on v.REPOSITORY_ID = r.ROW_ID and sv.VIEW_NAME = v.name
  join siebel.S_VIEW_WEB_TMPL vt on vt.VIEW_ID = v.ROW_ID and vt.INACTIVE_FLG = 'N'
  join siebel.S_VIEW_WTMPL_IT vti on vti.VIEW_WEB_TMPL_ID = vt.ROW_ID and vti.INACTIVE_FLG = 'N'
  -- applet (only form, list applets)
  join siebel.S_APPLET a on a.REPOSITORY_ID = r.ROW_ID and a.name = vti.APPLET_NAME
  join siebel.S_APPL_WEB_TMPL w on w.applet_id = a.ROW_ID and w.TYPE = vti.APPLET_MODE_CD and w.INACTIVE_FLG ='N'
  join siebel.S_APPL_WTMPL_IT wi on wi.APPL_WEB_TMPL_ID = w.ROW_ID and wi.INACTIVE_FLG = 'N'
  -- control
  left join siebel.S_CONTROL c on c.APPLET_ID = a.ROW_ID and wi.CTRL_NAME = c.name and c.INACTIVE_FLG = 'N'
  left join siebel.S_LIST l on l.APPLET_ID = a.ROW_ID
  left join siebel.S_LIST_COLUMN lc on lc.LIST_ID = l.ROW_ID and wi.CTRL_NAME = lc.name
  -- control caption (only overrides!)
  left join siebel.S_CONTROL_INTL ci on ci.CONTROL_ID = c.ROW_ID
  left join siebel.S_LIST_COL_INTL li on li.LIST_COLUMN_ID = lc.ROW_ID
  -- buscomp
  join SIEBEL.S_BUSCOMP b on b.name = a.BUSCOMP_NAME and b.REPOSITORY_ID = r.ROW_ID
  -- fields exposed either through control or list column
  join siebel.S_FIELD f on f.BUSCOMP_ID = b.ROW_ID 
    and (lc.FIELD_NAME = f.name or c.FIELD_NAME = f.name)
  -- join
  left join siebel.S_JOIN j on j.name = f.JOIN_NAME and j.BUSCOMP_ID = f.BUSCOMP_ID
  -- table
  join siebel.S_TABLE t on t.REPOSITORY_ID = r.ROW_ID 
    and (t.name = b.TABLE_NAME and f.join_name is null  -- base table
    or t.name = j.DEST_TBL_NAME and j.row_id is not null  -- explicit joins
    or t.name = f.JOIN_NAME and j.row_id is null) -- implicit joins
  -- column
  join siebel.S_COLUMN col on col.TBL_ID = t.ROW_ID and f.COL_NAME = col.name
WHERE r.name = 'Siebel Repository'
  and ap.name LIKE 'Siebel Financial Services'
ORDER BY 1, 2, 3;

Starting a series of SQLs to check the consistency of Siebel repository object.

And the first SQL is to identify Integration Component fields referencing non-existing BusComp fields:

with io as (  -- IO/IC/Field
  SELECT io.repository_id, io.name IO, ic.name IC, ic.ext_name BC, nvl(ifu.value, iff.ext_name) FIELD
  FROM siebel.S_INT_OBJ io
       join siebel.S_INT_COMP ic on io.row_id = ic.int_obj_id
            and ic.inactive_flg = 'N'
       join siebel.S_INT_FIELD iff on iff.int_comp_id = ic.row_id 
            and iff.inactive_flg = 'N' 
            and iff.field_type_cd = 'Data'
       left join siebel.S_INTFLD_UPROP ifu on ifu.int_field_id = iff.row_id  -- MVF
            and ifu.inactive_flg = 'N' 
            and ifu.name in ('MVGFieldName', 'AssocFieldName')
  where io.base_obj_type = 'Siebel Business Object'
        and io.inactive_flg = 'N'), 
        
bc as ( -- BC/Field
  select bc.repository_id, bc.name BC, f.name FIELD 
  from siebel.S_BUSCOMP bc 
      join siebel.S_FIELD f on f.buscomp_id = bc.row_id 
        and f.inactive_flg = 'N')
  
select io.io, io.ic, io.field, io.bc 
from io 
  join siebel.S_REPOSITORY r on r.row_id = io.repository_id
where r.name = 'Siebel Repository'
    and io.field not in ('Id','Conflict Id','Created','Created By','Mod Id','Updated','Updated By', 'SSA Primary Field', 'IsPrimaryMVG') -- excluding system fields
    and (bc, field) not in (select bc, field from bc where repository_id = r.row_id)
and io.io like 'AMS%'; -- to filter by your project preffix

Do you remember a pain when calculated expression splits across dozens calculated fields?

That SQL will help you to visualise complex dependencies of calculated fields:

SELECT level,
  SYS_CONNECT_BY_PATH(t.NAME, '/') PATH,
  t.*
FROM
  (SELECT f.NAME, f.CALCVAL
   FROM siebel.S_FIELD f
     JOIN siebel.S_BUSCOMP b ON b.row_id  = f.BUSCOMP_ID
   WHERE b.name = 'Order Entry - Orders' -- bus comp name
  ) t
START WITH name = 'Payment Order Total' -- field name
CONNECT BY prior CALCVAL LIKE '%[' || NAME || ']%'
ORDER BY 2;

It will show you all subsequent fields for vanilla calculated field [Payment Order Total] (BusComp = Order Entry - Orders) in recursion:

Here is a full version of SQL with more details

with t as (
  select f.NAME, f.CALCVAL,
    DECODE(f.MULTI_VALUED, 'Y', f.MVLINK_NAME || '.' || f.DEST_FLD_NAME, 
      DECODE(f.CALCULATED, 'Y', f.CALCVAL, 
        DECODE(f.JOIN_NAME, null, b.TABLE_NAME || '.' || f.COL_NAME, 
          DECODE(j.DEST_TBL_NAME, null, f.JOIN_NAME || '.' || f.COL_NAME,
            j.DEST_TBL_NAME || '.' || f.COL_NAME)))) VAL
  from siebel.S_FIELD f
    join siebel.S_BUSCOMP b on b.row_id = f.BUSCOMP_ID
    left join siebel.S_JOIN j on j.NAME = f.JOIN_NAME AND j.BUSCOMP_ID = f.BUSCOMP_ID
  where b.name = 'Order Entry - Orders')
select distinct level, SYS_CONNECT_BY_PATH(t.NAME, '/') PATH, t.NAME, t.VAL from t
start with name = 'Payment Order Total'
connect by prior CALCVAL LIKE '%[' || NAME || ']%'
order by 2;