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