Siebel CRM little blog

VIEW

UPDATE siebel.s_view bc SET bc.obj_locked_flg = 'N', bc.obj_locked_date = NULL, bc.obj_locked_by = null, bc.obj_locked_lang = null

WHERE bc.row_id = '1-A4LQ-DX3R';

commit;

TABLE

UPDATE siebel.s_table tab SET tab.obj_locked_flg = 'N', tab.obj_locked_date = null, tab.obj_locked_by = null, tab.obj_locked_lang = null

WHERE tab.name = 'CX_RD_MATRIX'

and tab.row_id = '1NQ-5HY';

commit;

BUSCOMP

UPDATE siebel.s_buscomp bc SET bc.obj_locked_flg = 'N', bc.obj_locked_date = NULL, bc.obj_locked_by = null, bc.obj_locked_lang = null

WHERE bc.row_id = '1-A4LQ-DX3R';

commit;

BUSSERVICE

UPDATE siebel.s_service serv SET serv.obj_locked_flg = 'N', serv.obj_locked_date = NULL, serv.obj_locked_by = null, serv.obj_locked_lang = null

WHERE serv.row_id = '8B-DVG';commit;

APPLET

UPDATE siebel.s_applet appl SET appl.obj_locked_flg = 'N', appl.obj_locked_date = NULL, appl.obj_locked_by = null, appl.obj_locked_lang = null

WHERE appl.row_id = '1-O3D-C3';

commit;

PROJECT

UPDATE siebel.s_project  AS proj SET proj.locked_flg = 'N', proj.locked_date = NULL

WHERE proj.name = 'IOM Order Management';

COMMIT

select ' OR "' || w.name || '"' from siebel.S_VIEW_WTMPL_IT wti,
	   	 	  siebel.S_VIEW_WEB_TMPL wt,
			  siebel.S_VIEW w
	   where
	   		 wti.applet_name in ('IOM Account Entry Applet', 'IOM Account Entry Applet RO', 'IOM Account Entry Applet ZIU')
	   		 and wti.VIEW_WEB_TMPL_ID = wt.row_id
	   		 and wt.view_id = w.row_id
			 and w.repository_id = '1-1N4Y-1'
	   group by w.name
	   order by w.name

SELECT   lov.created, usr_crt.login created_login, lov.last_upd,
         usr_upd.login updated_login, lov.TYPE, lov.NAME, lov.val,
         lov.sub_type, lov.par_row_id, par_lov.TYPE par_lov_type,
         par_lov.NAME par_lov_name
    FROM siebel.s_lst_of_val lov,
         siebel.s_lst_of_val par_lov,
         siebel.s_user usr_crt,
         siebel.s_user usr_upd
   WHERE lov.created_by = usr_crt.row_id
     AND lov.last_upd_by = usr_upd.row_id
     AND par_lov.row_id(+) = lov.par_row_id
ORDER BY lov.last_upd DESC

SELECT   wdd.created, usr_crt.login created_login, wdd.last_upd,
         usr_upd.login updated_login, wdd.NAME, wdd.VERSION,
         wdd.deploy_status_cd
    FROM siebel.s_wfa_dploy_def wdd,
         siebel.s_user usr_crt,
         siebel.s_user usr_upd
   WHERE wdd.created_by = usr_crt.row_id AND wdd.last_upd_by = usr_upd.row_id
ORDER BY wdd.last_upd DESC

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