SELECT t_gps_applications.application_name as APPLICATION, T_GPS_CUSTOM_SETS.GROUP_NAME, t_gps_dbv_users.user_name, t_gps_favourites_history.application_id, CASE WHEN t_gps_favourites_history.object_type = 'APPLICATION' AND (SELECT T_GPS_APP_PROPERTIES.PROPERTY_VALUE FROM T_GPS_APP_PROPERTIES T_GPS_APP_PROPERTIES WHERE t_gps_applications.APPLICATION_ID = T_GPS_APP_PROPERTIES.APPLICATION_ID AND T_GPS_APP_PROPERTIES.PROPERTY_NAME = 'PROJECTPLAN_APPLICATION') IS NOT NULL THEN 'PROJECT' ELSE t_gps_favourites_history.object_type END AS object_type, CASE t_gps_favourites_history.object_type WHEN 'BUILD / VIEW' THEN (t_gps_applications.application_name || ' Workbench') WHEN 'CUSTOM SET' THEN COALESCE(T_GPS_CUSTOM_SETS.DISPLAY_NAME,t_gps_favourites_history.object_name) ELSE t_gps_favourites_history.object_name END AS object_name, T_GPS_CUSTOM_SETS.CUSTOM_URL, max(t_gps_favourites_history.accessed_time) AS max, NULL AS FOLDER, NULL AS EWKS_SYS_NAME, NULL AS EWKS_ID FROM t_gps_favourites_history t_gps_favourites_history LEFT OUTER JOIN STATEGPS.T_GPS_CUSTOM_SETS T_GPS_CUSTOM_SETS ON t_gps_favourites_history.application_id = T_GPS_CUSTOM_SETS.application_id AND t_gps_favourites_history.object_name = T_GPS_CUSTOM_SETS.custom_set_name, t_gps_dbv_users t_gps_dbv_users, t_gps_applications t_gps_applications WHERE t_gps_dbv_users.dbv_user_id = t_gps_favourites_history.dbv_user_id AND t_gps_favourites_history.application_id = t_gps_applications.application_id AND trunc(t_gps_favourites_history.accessed_time) >= (CURRENT_DATE - 365) AND t_gps_favourites_history.object_type != 'VIEWER SET / REPORT' AND (t_gps_applications.end_date IS NULL OR (t_gps_applications.end_date+30 >= CURRENT_DATE)) AND t_gps_applications.VISIBLE_FLAG = 'true' GROUP BY t_gps_applications.APPLICATION_ID,t_gps_applications.application_name,T_GPS_CUSTOM_SETS.GROUP_NAME,T_GPS_CUSTOM_SETS.DISPLAY_NAME,t_gps_favourites_history.application_id, t_gps_favourites_history.object_type, t_gps_dbv_users.user_name, t_gps_favourites_history.object_name, T_GPS_CUSTOM_SETS.CUSTOM_URL UNION ALL SELECT t_gps_applications.APPLICATION_NAME AS APPLICATION, CASE COALESCE(T_GPS_VIEWER_INSTANCES.GROUP_NAME,T_GPS_VIEWER_INSTANCES_SET.SET_NAME) WHEN 'Administration' THEN 'Settings' ELSE COALESCE(T_GPS_VIEWER_INSTANCES.GROUP_NAME,T_GPS_VIEWER_INSTANCES_SET.SET_NAME) END AS GROUP_NAME, t_gps_dbv_users.user_name, NULL, 'WORKSHEET' AS object_type, COALESCE(T_GPS_VIEWER_INSTANCES.DISPLAY_NAME,T_GPS_VIEWER_INSTANCES.FORM_NAME) AS object_name, NULL, max(t_gps_favourites_history.accessed_time) AS max, T_GPS_VIEWER_INSTANCES_SET.SET_NAME AS FOLDER, T_GPS_VIEWER_INSTANCES.FORM_NAME AS EWKS_SYS_NAME, T_GPS_VIEWER_INSTANCES.VIEWER_INSTANCES_ID AS EWKS_ID FROM t_gps_favourites_history t_gps_favourites_history, t_gps_dbv_users t_gps_dbv_users, t_gps_applications t_gps_applications, STATEGPS.T_GPS_VIEWER_INSTANCES_SET T_GPS_VIEWER_INSTANCES_SET, STATEGPS.T_GPS_VIEWER_INSTANCES T_GPS_VIEWER_INSTANCES WHERE t_gps_dbv_users.dbv_user_id = t_gps_favourites_history.dbv_user_id AND t_gps_favourites_history.application_id = t_gps_applications.application_id AND trunc(t_gps_favourites_history.accessed_time) >= (CURRENT_DATE - 365) AND t_gps_favourites_history.object_type = 'VIEWER SET / REPORT' AND T_GPS_VIEWER_INSTANCES.post_instance_name IS NOT NULL and t_gps_applications.application_id = T_GPS_VIEWER_INSTANCES_SET.application_id and T_GPS_VIEWER_INSTANCES_SET.SET_ID = T_GPS_VIEWER_INSTANCES.SET_ID AND t_gps_favourites_history.object_name = T_GPS_VIEWER_INSTANCES_SET.SET_NAME || ' / ' || T_GPS_VIEWER_INSTANCES.FORM_NAME AND T_GPS_VIEWER_INSTANCES.VISIBLE_FLAG = 'true' AND T_GPS_VIEWER_INSTANCES_SET.VISIBLE_FLAG = 'true' AND t_gps_applications.VISIBLE_FLAG = 'true' GROUP BY t_gps_applications.APPLICATION_NAME, T_GPS_VIEWER_INSTANCES.GROUP_NAME, T_GPS_VIEWER_INSTANCES_SET.SET_NAME, T_GPS_VIEWER_INSTANCES.FORM_NAME, T_GPS_VIEWER_INSTANCES.VIEWER_INSTANCES_ID, t_gps_dbv_users.user_name,T_GPS_VIEWER_INSTANCES.post_instance_name,T_GPS_VIEWER_INSTANCES.DISPLAY_NAME,T_GPS_VIEWER_INSTANCES.FORM_NAME ORDER BY 8 DESC