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.MIG_MIGRATION_STATUS AS MIGRATION_STATUS, T_MIGRATION_REQUESTS_LOG.TARGET_INSTANCE, next_day(trunc(T_MIGRATION_REQUESTS.CREATED_DATE) - 7, 'Monday') TICKET_DATE, count(distinct T_MIGRATION_REQUESTS.instance_id) COUNT 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 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 --Newww -- AND PROJECT_NAME = 'Triniti Products' -- AND T_MIGRATION_REQUESTS_LOG.TARGET_INSTANCE = 'HLTRI2DV' -- AND MIGRATION_REQUEST_NUMBER = 'MIG008328' 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.MIG_MIGRATION_STATUS, T_MIGRATION_REQUESTS_LOG.TARGET_INSTANCE ORDER BY MIGRATION_REQUEST_NUMBER DESC