tobno01

Clarity Tuesday Tip: Queries to find out who has personalized their view

Discussion created by tobno01 Employee on Nov 26, 2014
Latest reply on Sep 22, 2015 by Uruj

It is possible to find out which users have personalized their views using a query

 

This query returns all user-personalized views in the system :

 

SELECT
users.user_name,
grid.dal_code,
grid.portlet_id,
att.id,
views.object_code,
views.code,
views.partition_code,
users.user_name,
views.view_type,
views.principal_type,
att.label,
grid.code,
grid.dal_type,
grid.default_sort_col_code,
grid.sort_col_code_2,
grid.auto_expand,
portlet.portlet_code,
nls.name views_name
FROM cmn_grids grid
LEFT JOIN odf_views views ON grid.id = views.id
LEFT JOIN odf_view_attributes att ON grid.id = att.view_id
LEFT JOIN cmn_portlets portlet ON grid.portlet_id = portlet.id
INNER JOIN cmn_sec_users users ON users.id = grid.principal_id
INNER JOIN cmn_captions_nls nls ON nls.pk_id = grid.id
WHERE grid.principal_type = 'USER'
AND users.id = grid.principal_id
AND nls.language_code IN ('en', null)
AND users.id IN
(SELECT DISTINCT principal_id FROM cmn_grids WHERE principal_type ='USER')
GROUP BY
users.user_name,
grid.dal_code,
grid.portlet_id,
att.id,
views.object_code,
views.code,
views.partition_code,
users.user_name,
views.view_type,
views.principal_type,
att.label,
grid.code,
grid.dal_type,
grid.default_sort_col_code,
grid.sort_col_code_2,
grid.auto_expand,
portlet.portlet_code,
nls.name
ORDER BY users.user_name


Similar queries can be constructed to find the details on different levels. For example this Query returns all users that have personalized their Project List view:

 

SELECT user_name,
   first_name,
   last_name
FROM cmn_sec_users
WHERE id IN (  SELECT DISTINCT principal_id
               FROM cmn_grids
               WHERE principal_type ='USER'
                  AND code='projmgr.projectList'
   )
ORDER BY user_name

 

Happy Thanksgiving to all!

Outcomes