SELECT T_GPS_APPLICATIONS.APPLICATION_ID, T_GPS_APPLICATIONS.APPLICATION_NAME AS APPLICATION, T_GPS_USERS.USER_NAME, MAX(COALESCE(CASE T_GPS_FAVOURITES_HISTORY.DBV_USER_ID WHEN T_USERS.USER_ID THEN T_GPS_FAVOURITES_HISTORY.ACCESSED_TIME ELSE NULL END, CURRENT_DATE - 365)) AS SELECTED_ORDER from STATEGPS.T_GPS_USER_APPLICATIONS T_GPS_USER_APPLICATIONS LEFT OUTER JOIN STATEGPS.T_GPS_FAVOURITES_HISTORY T_GPS_FAVOURITES_HISTORY ON T_GPS_FAVOURITES_HISTORY.APPLICATION_ID = T_GPS_USER_APPLICATIONS.APPLICATION_ID AND T_GPS_FAVOURITES_HISTORY.OBJECT_TYPE = 'APPLICATION', STATEGPS.T_GPS_USERS T_GPS_USERS, TAS.T_USERS T_USERS, STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS, STATEGPS.T_GPS_APP_PROPERTIES T_GPS_APP_PROPERTIES where T_GPS_USERS.USER_ID = T_GPS_USER_APPLICATIONS.USER_ID AND T_GPS_USERS.USER_NAME = T_USERS.USER_NAME AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_USER_APPLICATIONS.APPLICATION_ID AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_APP_PROPERTIES.APPLICATION_ID AND T_GPS_APP_PROPERTIES.PROPERTY_NAME = 'PROJECTPLAN_APPLICATION' AND T_GPS_APP_PROPERTIES.PROPERTY_VALUE = 'TRUE' AND (trunc(T_GPS_APPLICATIONS.END_DATE) + 30 > CURRENT_DATE OR T_GPS_APPLICATIONS.END_DATE IS NULL) AND (T_GPS_APPLICATIONS.START_DATE IS NULL OR TRUNC(T_GPS_APPLICATIONS.START_DATE) <= CURRENT_DATE) AND EXISTS (SELECT user_app_id FROM STATEGPS.t_gps_user_menuconfig where user_app_id = T_GPS_USER_APPLICATIONS.user_app_id and mode_id = 0) GROUP BY T_GPS_APPLICATIONS.APPLICATION_ID, T_GPS_APPLICATIONS.APPLICATION_NAME, T_GPS_USERS.USER_NAME ORDER BY 4 DESC