SELECT T_GPS_APPLICATIONS.APPLICATION_ID, T_GPS_APPLICATIONS.APPLICATION_NAME, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME, T_INSTANCES.INSTANCE_ID, T_INSTANCES.PRIORITY, SUBSTR((SELECT IP.PROPERTY_VALUE FROM T_GPS_INSTANCE_PROPERTIES IP, T_GPS_WORKFLOW_INSTANCES WI, T_GPS_WF_INSTANCE_CLASSES WIC, T_GPS_WF_INST_CLASS_PROPS WICP WHERE T_INSTANCES.INSTANCE_ID = IP.INSTANCE_ID AND (T_GPS_APPLICATIONS.APPLICATION_ID = WI.APPLICATION_ID OR T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID = WI.WORKFLOW_TYPE_ID) AND WI.INSTANCE_ID = WIC.INSTANCE_ID AND WIC.CLASS_ID = WICP.CLASS_ID AND WICP.REGISTER_AS = 'TASK_NUMBER' AND IP.PROPERTY_NAME LIKE '%.' || WICP.PROPERTY_NAME) || ' - ' || (SELECT IP.PROPERTY_VALUE FROM T_GPS_INSTANCE_PROPERTIES IP, T_GPS_WORKFLOW_INSTANCES WI, T_GPS_WF_INSTANCE_CLASSES WIC, T_GPS_WF_INST_CLASS_PROPS WICP WHERE T_INSTANCES.INSTANCE_ID = IP.INSTANCE_ID AND (T_GPS_APPLICATIONS.APPLICATION_ID = WI.APPLICATION_ID OR T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID = WI.WORKFLOW_TYPE_ID) AND WI.INSTANCE_ID = WIC.INSTANCE_ID AND WIC.CLASS_ID = WICP.CLASS_ID AND WICP.REGISTER_AS = 'TASK_NAME' AND IP.PROPERTY_NAME LIKE '%.' || WICP.PROPERTY_NAME), 0, 20) || '...', T_PROJECT_TASK.PROJECT_TASK_ID, SUBSTR(T_GPS_WORKFLOW_STATES.STATE_NAME, 0, 20) || '...', T_PROJECT_TASK.START_DATE, T_PROJECT_TASK.FINISH_DATE, T_PROJECT_TASK.DURATION, T_PROJECT_TASK.DAYSREMAINING, ((T_PROJECT_TASK.DURATION - NVL(T_PROJECT_TASK.DAYSREMAINING, T_PROJECT_TASK.DURATION)) * 100 / T_PROJECT_TASK.DURATION), T_PROJECT_TASK.USER_NAME,DUE_DATE, STATE_ORDER,WORKFLOW_ORDER, (SELECT MAX(PROPERTY_VALUE) FROM T_GPS_WFT_PROPERTIES WFTP WHERE WFTP.WORKFLOW_TYPE_ID= T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID AND PROPERTY_NAME='wfPredecessors') PREDECESSORS FROM STATEGPS.T_INSTANCES T_INSTANCES, STATEGPS.T_PROJECT_TASK T_PROJECT_TASK, STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES, STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES, STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS WHERE T_PROJECT_TASK.STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID AND T_INSTANCES.WORKFLOW_TYPE_ID = T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID AND T_INSTANCES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID AND T_PROJECT_TASK.CRITICAL = 1 AND T_PROJECT_TASK.DURATION > 0 AND (T_PROJECT_TASK.DAYSREMAINING > 0 OR T_PROJECT_TASK.DAYSREMAINING IS NULL) AND T_PROJECT_TASK.START_DATE IS NOT NULL AND T_PROJECT_TASK.FINISH_DATE IS NOT NULL AND T_INSTANCES.ACTIVE = 1 --AND T_GPS_APPLICATIONS.APPLICATION_ID=8722 UNION SELECT T_GPS_APPLICATIONS.APPLICATION_ID, T_GPS_APPLICATIONS.APPLICATION_NAME, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME, NULL INSTANCE_ID, NULL PRIORITY, NULL TASK, NULL PROJECT_TASK_ID, NULL STATE_NAME, NVL(TO_DATE(TGWP.WFSTARTDATE,'DD/MM/YYYY'),SYSDATE) START_DATE, NVL(TO_DATE(TGWP.WFFINISHDATE,'DD/MM/YYYY'),SYSDATE) FINISH_DATE, NVL(TO_NUMBER(TGWP.WFDURATION),0) DURATION, NULL DAYSREMAINING, 0 PERCENT_COMPLETED, '' USER_NAME,NULL DUE_DATE, NULL STATE_ORDER,WORKFLOW_ORDER, WFPREDECESSORS PREDECESSORS FROM T_GPS_APPLICATIONS T_GPS_APPLICATIONS, T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES, ( SELECT * FROM ( SELECT WORKFLOW_TYPE_ID, MAX( CASE WHEN PROPERTY_NAME = 'wfPredecessors' THEN PROPERTY_VALUE END ) WFPREDECESSORS, MAX( CASE WHEN PROPERTY_NAME = 'wfStartDate' THEN PROPERTY_VALUE END ) WFSTARTDATE, MAX( CASE WHEN PROPERTY_NAME = 'wfFinishDate' THEN PROPERTY_VALUE END ) WFFINISHDATE, MAX( CASE WHEN PROPERTY_NAME = 'wfDuration' THEN PROPERTY_VALUE END ) WFDURATION, MAX( CASE WHEN PROPERTY_NAME = 'critical' THEN PROPERTY_VALUE END ) CRITICAL FROM T_GPS_WFT_PROPERTIES GROUP BY WORKFLOW_TYPE_ID ) AK WHERE AK.WFDURATION <> 0 ) TGWP WHERE T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_WORKFLOW_TYPES.APPLICATION_ID AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID = TGWP.WORKFLOW_TYPE_ID AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' AND TGWP.CRITICAL = 1 --AND T_GPS_APPLICATIONS.APPLICATION_ID=8722 AND TGWP.WORKFLOW_TYPE_ID IN ( SELECT WORKFLOW_TYPE_ID FROM ( SELECT TGWT.WORKFLOW_TYPE_ID FROM T_GPS_WORKFLOW_TYPES TGWT MINUS SELECT T_INSTANCES.WORKFLOW_TYPE_ID FROM T_INSTANCES T_INSTANCES WHERE T_INSTANCES.ACTIVE = 1 ) ) ORDER BY DUE_DATE, STATE_ORDER,WORKFLOW_ORDER ASC