Archive for February, 2008

SQL Select Responsibilities, Views, BO, BCs

-- Siebel 7.8
-- Gives you a list of responsibilities with count of assigned users to that responsibility
SELECT   resp.row_id, resp.NAME, COUNT (*) AS users_count
    FROM siebel.s_per_resp per_resp, siebel.s_resp resp
   WHERE resp.row_id = per_resp.resp_id(+)
GROUP BY resp.row_id, per_resp.resp_id, resp.NAME
ORDER BY COUNT (*) DESC



-- Siebel 7.8
-- gives you list of responsibilities and their views - corresponding Business object, applets within view with BC
-- assigned_users is count of users assigned to particular responsibility
SELECT   resp.NAME AS resp_name, resp.assigned_users, vie.NAME AS view_name,
         vie.busobj_name, wtmpa.applet_name AS applet_name,
         appl.buscomp_name bc_name
    FROM siebel.s_app_view apw,
         siebel.s_view vie,
         siebel.s_app_view_resp apwre,
         (SELECT   resp.NAME, resp.row_id, COUNT (*) assigned_users
              FROM siebel.s_resp resp, siebel.s_per_resp per_resp
             WHERE resp.row_id = per_resp.resp_id(+)
          GROUP BY resp.NAME, resp.row_id) resp,
         siebel.s_view_web_tmpl wtmp,
         siebel.s_view_wtmpl_it wtmpa,
         siebel.s_applet appl,
         siebel.s_repository repos
   WHERE repos.NAME = 'Siebel Repository'
     AND apw.NAME = vie.NAME(+)
     AND apw.row_id = apwre.view_id
     AND resp.row_id = apwre.resp_id
     AND vie.row_id = wtmp.view_id
     AND wtmp.row_id = wtmpa.view_web_tmpl_id
     AND vie.repository_id = repos.row_id
     AND appl.NAME = wtmpa.applet_name
     AND appl.repository_id = vie.repository_id
     AND (wtmpa.inactive_flg IS NULL OR wtmpa.inactive_flg = 'N')
     AND resp.row_id LIKE '1-%'
ORDER BY resp.assigned_users DESC, resp_name, view_name

-- Siebel 7.8
-- gives you list of views corresponding Business object, applets within view with BC
-- resp_count means in how many responsibilities is view listed in
-- max_assigned_users_to_resp is max count of users assigned to one of the responsibility
SELECT   COUNT (*) resp_count,
         MAX (resp.assigned_users) max_assigned_users_to_resp,
         vie.NAME AS view_name, vie.busobj_name,
         wtmpa.applet_name AS applet_name, appl.buscomp_name bc_name
    FROM siebel.s_app_view apw,
         siebel.s_view vie,
         siebel.s_app_view_resp apwre,
         (SELECT   resp.NAME, resp.row_id, COUNT (*) assigned_users
              FROM siebel.s_resp resp, siebel.s_per_resp per_resp
             WHERE resp.row_id = per_resp.resp_id(+)
          GROUP BY resp.NAME, resp.row_id) resp,
         siebel.s_view_web_tmpl wtmp,
         siebel.s_view_wtmpl_it wtmpa,
         siebel.s_applet appl,
         siebel.s_repository repos
   WHERE repos.NAME = 'Siebel Repository'
     AND apw.NAME = vie.NAME(+)
     AND apw.row_id = apwre.view_id
     AND resp.row_id = apwre.resp_id
     AND vie.row_id = wtmp.view_id
     AND wtmp.row_id = wtmpa.view_web_tmpl_id
     AND vie.repository_id = repos.row_id
     AND appl.NAME = wtmpa.applet_name
     AND appl.repository_id = vie.repository_id
     AND (wtmpa.inactive_flg IS NULL OR wtmpa.inactive_flg = 'N')
     AND resp.row_id LIKE '1-%'                       -- resps created by user
GROUP BY vie.NAME, vie.busobj_name, wtmpa.applet_name, appl.buscomp_name
ORDER BY COUNT (*) DESC, view_name

Useful selects

Oracle database version: select * from v$version

Oracle characterset: select value from v$nls_parameters where parameter=’NLS_CHARACTERSET’

Navigate to specific Siebel View

One of my first trouble I encountered using Siebel was how to navigate to specific Siebel View without knowing its label in Sitemap.

Here is the little trick: just fill the view name in a “View Name” text box a press Submit and then just copy and paste URL parameters into your browser with Siebel client.

Example: http://hostname/eCommunications/start.swe?SWECmd=GotoView&SW EView=This+can+be+your+View+Name

View name:
Url parameters: