SELECT T_GPS_APPLICATIONS.APPLICATION_NAME PROJECT, T_MIGRATION_REQUESTS.PROJECT_NAME, T_MIGRATION_REQUESTS.MIGRATION_REQUEST_NUMBER, T_MIGRATION_REQUESTS.MIGRATION_REQUEST_TITLE, T_MIGRATION_REQUESTS_LOG.TARGET_INSTANCE, next_day(trunc(T_MIGRATION_REQUESTS.CREATED_DATE) - 7, 'Monday') TICKET_DATE, count(distinct T_MIGRATION_DETAILS_LOG.DEV_OBJECT_NAME) COUNT, T_MIGRATION_DETAILS_LOG.DEV_OBJECT_NAME, T_MIGRATION_DETAILS_LOG.DEV_OBJECT_VERSION, T_MIGRATION_DETAILS_LOG.OBJECT_TYPE, T_MIGRATION_DETAILS_LOG.DEV_OBJECT_SCHEMA, (CASE WHEN (T_MIGRATION_DETAILS_LOG.OBJECT_TYPE IN ('COPY', 'COPY AND EXTRACT', 'OAF-COPY', 'OAF-WEB_UI')) THEN T_MIGRATION_DETAILS_LOG.APPLICATION_BASE_PATH END) TARGET_PATH, (CASE WHEN (T_MIGRATION_DETAILS_LOG.OBJECT_TYPE NOT IN ('COPY', 'COPY AND EXTRACT', 'OAF-COPY', 'OAF-WEB_UI')) THEN T_MIGRATION_DETAILS_LOG.APPLICATION_BASE_PATH END) ORACLE_APPLICATION_TOP, T_MIGRATION_DETAILS_LOG.APPLICATION_NAME ORACLE_APPLICATION_SHORT_NAME, T_MIGRATION_DETAILS_LOG.MIGRATION_STATUS AS OBJECT_STATUS FROM TRI2GPS_DEV.T_MIGRATION_REQUESTS T_MIGRATION_REQUESTS, TRI2GPS_DEV.T_MIGRATION_REQUESTS_LOG T_MIGRATION_REQUESTS_LOG, STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS, STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES, STATEGPS.T_INSTANCES T_INSTANCES, TRI2GPS_DEV.T_MIGRATION_DETAILS T_MIGRATION_DETAILS, TRI2GPS_DEV.T_MIGRATION_DETAILS_LOG T_MIGRATION_DETAILS_LOG WHERE T_GPS_APPLICATIONS.APPLICATION_ID = T_INSTANCES.APPLICATION_ID AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID = T_INSTANCES.WORKFLOW_TYPE_ID AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_WORKFLOW_TYPES.APPLICATION_ID AND T_MIGRATION_REQUESTS.INSTANCE_ID = T_INSTANCES.INSTANCE_ID AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' AND T_GPS_APPLICATIONS.APPLICATION_NAME = 'Application Object Migrator' AND T_INSTANCES.Active = '1' AND T_MIGRATION_REQUESTS.MIGRATION_REQUEST_ID = T_MIGRATION_REQUESTS_LOG.MIGRATION_REQUESTS_ID AND T_MIGRATION_REQUESTS_LOG.LOG_FILE_NAME IS NOT NULL AND T_MIGRATION_REQUESTS_LOG.MIG_MIGRATION_STATUS IS NOT NULL --Neww AND T_MIGRATION_DETAILS.MIGRATION_REQUEST_ID = T_MIGRATION_REQUESTS.MIGRATION_REQUEST_ID AND (T_MIGRATION_DETAILS.DELETE_FLAG IS NULL OR T_MIGRATION_DETAILS.DELETE_FLAG != 'Y') AND T_MIGRATION_DETAILS.MIGRATION_DETAIL_ID = T_MIGRATION_DETAILS_LOG.MIGRATION_DETAIL_ID AND T_MIGRATION_DETAILS_LOG.MIGRATION_REQUESTS_LOG_ID = T_MIGRATION_REQUESTS_LOG.MIGRATION_REQUESTS_LOG_ID AND T_MIGRATION_DETAILS_LOG.MIGRATION_STATUS IN ('COMPLETED', 'SKIPPED') GROUP BY T_MIGRATION_REQUESTS.INSTANCE_ID, T_GPS_APPLICATIONS.APPLICATION_NAME, T_GPS_WORKFLOW_TYPES.Workflow_Type_Id, T_MIGRATION_REQUESTS.CREATED_DATE, T_MIGRATION_REQUESTS.PROJECT_NAME, T_MIGRATION_REQUESTS.MIGRATION_REQUEST_NUMBER, T_MIGRATION_REQUESTS.MIGRATION_REQUEST_TITLE, T_MIGRATION_REQUESTS_LOG.TARGET_INSTANCE, T_MIGRATION_DETAILS_LOG.DEV_OBJECT_NAME, T_MIGRATION_DETAILS_LOG.DEV_OBJECT_VERSION, T_MIGRATION_DETAILS_LOG.OBJECT_TYPE, T_MIGRATION_DETAILS_LOG.DEV_OBJECT_SCHEMA, T_MIGRATION_DETAILS_LOG.APPLICATION_BASE_PATH, T_MIGRATION_DETAILS_LOG.APPLICATION_NAME, T_MIGRATION_DETAILS_LOG.MIGRATION_STATUS ORDER BY MIGRATION_REQUEST_NUMBER DESC