View HLPDTEST.STATEGPS.RECENTLY_ACCESSED_OBJECTS_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Wed May 25 01:05 IST 2022 |
View Definition:
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
Possibly Referenced Tables/Views:
![]() ![]() |