SELECT PROJECT_TASK_ID, COALESCE(P1.START_DATE,CURRENT_DATE)START_DATE, COALESCE(P1.FINISH_DATE,CURRENT_DATE)FINISH_DATE, T_REPORTING_PLAN_TAGS.TAG, APPLICATION_MODULE, WORKFLOW_TYPE_NAME, APPLICATION_NAME, DURATION, DAYSREMAINING, COALESCE(DECODE(DAYSREMAINING,0,DURATION,NULL,0)/ DECODE(DAYSREMAINING,0,DURATION,COALESCE(DAYSREMAINING,DURATION))* 100,0)PERCENT_COMPLETED, TASK_COUNT, TASKS_COMPLETED, ROUND(DURATION - DURATION_CHANGES,1)DURATION_CHANGES, USER_ID, STATE_ID, WORKFLOW_TYPE_ID, APPLICATION_ID, T_PROJECT_PLAN_TAGS.TAG_ORDER, T_REPORTING_PLAN_TAGS.TAG_ID FROM ( SELECT PROJECT_TASK_ID, START_DATE, FINISH_DATE, APPLICATION_MODULE, WORKFLOW_TYPE_NAME, APPLICATION_NAME, DURATION, DAYSREMAINING, TASK_COUNT, TASKS_COMPLETED, DURATION_CHANGES, USER_ID, STATE_ID, WORKFLOW_TYPE_ID, APPLICATION_ID, PROJECT_TAG_ID FROM ( SELECT T_PROJECT_TASK.PROJECT_TASK_ID, DECODE(GI.INSTANCE_PRESENT,0,GI.CREATED_DATE,T_PROJECT_TASK.START_DATE)START_DATE, DECODE(GI.INSTANCE_PRESENT,0,GI.MOVED_OUT_DATE,T_PROJECT_TASK.FINISH_DATE)FINISH_DATE, ISSUES.APPLICATION_MODULE, WT.WORKFLOW_TYPE_NAME, A.APPLICATION_NAME, T_PROJECT_TASK.DURATION, T_PROJECT_TASK.DAYSREMAINING, DECODE(( SELECT E.HISTORY_ID FROM T_REPORTING_PLAN_HISTORY E WHERE E.HISTORY_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) AND E.DATE_TIME < ISSUES.CREATED_DATE ),NULL,0,DECODE(( SELECT DISTINCT (PROJECT_TASK_ID) FROM T_PROJECT_TASK_HISTORY H WHERE H.PROJECT_TASK_ID = T_PROJECT_TASK.PROJECT_TASK_ID AND H.EXECUTION_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) ),NULL,1,0))TASK_COUNT, DECODE(DAYSREMAINING,0,DECODE(( SELECT COUNT(*) FROM T_PROJECT_TASK_HISTORY H WHERE H.PROJECT_TASK_ID = T_PROJECT_TASK.PROJECT_TASK_ID AND H.EXECUTION_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) ),0,DECODE(( SELECT E.HISTORY_ID FROM T_REPORTING_PLAN_HISTORY E WHERE E.HISTORY_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) AND E.DATE_TIME < ISSUES.CREATED_DATE ),NULL,0,1),1,1),0)TASKS_COMPLETED, DECODE(DAYSREMAINING,0,T_PROJECT_TASK.DURATION,NVL(( SELECT MAX(DURATION) FROM T_PROJECT_TASK_HISTORY H WHERE H.PROJECT_TASK_ID = T_PROJECT_TASK.PROJECT_TASK_ID AND H.EXECUTION_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) ),0))DURATION_CHANGES, T_PROJECT_TASK.USER_ID, T_GPS_WORKFLOW_STATES.STATE_ID, WT.WORKFLOW_TYPE_ID, A.APPLICATION_ID, T_GPS_WORKFLOW_STATES.PROJECT_TAG_ID FROM STATEGPS.T_PROJECT_TASK T_PROJECT_TASK, STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES, TRI2GPS_DEV.ISSUES ISSUES, T_GPS_WORKFLOW_TYPES WT, T_GPS_APPLICATIONS A, T_GPS_INSTANCES GI WHERE T_GPS_WORKFLOW_STATES.STATE_ID = T_PROJECT_TASK.STATE_ID AND T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID = WT.WORKFLOW_TYPE_ID AND WT.APPLICATION_ID = A.APPLICATION_ID AND T_PROJECT_TASK.INSTANCE_ID = GI.INSTANCE_ID AND T_PROJECT_TASK.STATE_ID = GI.CURRENT_STATE_ID AND ACTIVE = 1 AND A.VISIBLE_FLAG = 'true' AND WT.VISIBLE_FLAG = 'true' --AND A.application_id = 8522 --and gi.instance_id = 888314 AND T_PROJECT_TASK.DURATION > 0 AND ISSUES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID )P --This Union is to get the NOT STARTED Tasks UNION ALL SELECT PT.PROJECT_TASK_ID, PT.START_DATE, PT.FINISH_DATE, ISSUES.APPLICATION_MODULE, WT.WORKFLOW_TYPE_NAME, A.APPLICATION_NAME, PT.DURATION, PT.DAYSREMAINING, DECODE(( SELECT E.HISTORY_ID FROM T_REPORTING_PLAN_HISTORY E WHERE E.HISTORY_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) AND E.DATE_TIME < ISSUES.CREATED_DATE ),NULL,0,DECODE(( SELECT DISTINCT (PROJECT_TASK_ID) FROM T_PROJECT_TASK_HISTORY H WHERE H.PROJECT_TASK_ID = PT.PROJECT_TASK_ID AND H.EXECUTION_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) ),NULL,1,0))TASK_COUNT, 0 TASKS_COMPLETED, DECODE(DAYSREMAINING,0,PT.DURATION,NVL(( SELECT MAX(DURATION) FROM T_PROJECT_TASK_HISTORY H WHERE H.PROJECT_TASK_ID = PT.PROJECT_TASK_ID AND H.EXECUTION_ID IN( GET_PREVIOUS_HISTORY_ID(A.APPLICATION_ID) ) ),0))DURATION_CHANGES, PT.USER_ID, WS.STATE_ID, WT.WORKFLOW_TYPE_ID, A.APPLICATION_ID, WS.PROJECT_TAG_ID FROM T_GPS_WORKFLOW_STATES WS, T_GPS_WORKFLOW_TYPES WT, T_GPS_APPLICATIONS A, T_PROJECT_TASK PT, T_INSTANCES TI, TRI2GPS_DEV.ISSUES ISSUES WHERE A.APPLICATION_ID = WT.APPLICATION_ID --AND A.application_id = 8522 AND WT.WORKFLOW_TYPE_ID = WS.WORKFLOW_TYPE_ID AND A.VISIBLE_FLAG = 'true' AND WT.VISIBLE_FLAG = 'true' AND NOT EXISTS( SELECT CURRENT_STATE_ID FROM T_GPS_INSTANCES WHERE PT.STATE_ID = CURRENT_STATE_ID AND PT.INSTANCE_ID = INSTANCE_ID AND(VISIBLE_FLAG IS NULL OR VISIBLE_FLAG = 'true') ) AND WS.STATE_DESCRIPTOR = 'Default State' AND PT.STATE_ID = WS.STATE_ID AND PT.DURATION > 0 --and ti.instance_id = 888314 AND PT.INSTANCE_ID = TI.INSTANCE_ID AND TI.APPLICATION_ID = A.APPLICATION_ID AND TI.WORKFLOW_TYPE_ID = WS.WORKFLOW_TYPE_ID AND TI.ACTIVE = 1 AND ISSUES.INSTANCE_ID = TI.INSTANCE_ID )P1 LEFT JOIN T_PROJECT_PLAN_TAGS T_PROJECT_PLAN_TAGS ON T_PROJECT_PLAN_TAGS.PROJECT_TAG_ID = P1.PROJECT_TAG_ID LEFT JOIN T_REPORTING_PLAN_TAGS T_REPORTING_PLAN_TAGS ON T_REPORTING_PLAN_TAGS.TAG_ID = T_PROJECT_PLAN_TAGS.TAG_ID --WHERE p1.application_id = 8522 UNION ALL --Get TAG level estimates SELECT DISTINCT NULL TASK_ID, T_PROJECT_PLAN_TAGS.START_DATE, T_PROJECT_PLAN_TAGS.END_DATE, T_REPORTING_PLAN_TAGS.TAG, NULL MODULE, NULL WORKFLOW_TYPE_NAME, T_GPS_APPLICATIONS.APPLICATION_NAME, T_PROJECT_PLAN_TAGS.ESTIMATED_DURATION DURATION, NULL REMAININGDAYS, 0 PERCENT_COMPLETED, 0 TASKS_COUNT, 0 TASKS_COMPLETED, 0 DURATION_CHANGES, NULL USER_ID, NULL STATE_ID, NULL WORKFLOW_TYPE_ID, T_GPS_APPLICATIONS.APPLICATION_ID, T_PROJECT_PLAN_TAGS.TAG_ORDER, T_REPORTING_PLAN_TAGS.TAG_ID FROM T_PROJECT_PLAN_TAGS T_PROJECT_PLAN_TAGS, T_REPORTING_PLAN_TAGS T_REPORTING_PLAN_TAGS, T_GPS_APPLICATIONS T_GPS_APPLICATIONS, T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES WHERE T_PROJECT_PLAN_TAGS.TAG_ID NOT IN( SELECT P_TAGS.TAG_ID FROM T_INSTANCES T_INSTANCES, T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES, T_PROJECT_TASK T_PROJECT_TASK, T_PROJECT_PLAN_TAGS P_TAGS WHERE T_INSTANCES.ACTIVE = 1 AND T_GPS_WORKFLOW_STATES.PROJECT_TAG_ID IS NOT NULL AND T_PROJECT_TASK.INSTANCE_ID IS NOT NULL AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID AND T_GPS_WORKFLOW_STATES.STATE_ID = T_PROJECT_TASK.STATE_ID AND P_TAGS.PROJECT_TAG_ID = T_GPS_WORKFLOW_STATES.PROJECT_TAG_ID AND P_TAGS.PROJECT_ID = T_GPS_APPLICATIONS.APPLICATION_ID ) AND T_PROJECT_PLAN_TAGS.TAG_ID = T_REPORTING_PLAN_TAGS.TAG_ID AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_WORKFLOW_TYPES.APPLICATION_ID AND T_GPS_APPLICATIONS.APPLICATION_ID = T_PROJECT_PLAN_TAGS.PROJECT_ID AND T_PROJECT_PLAN_TAGS.ESTIMATED_DURATION IS NOT NULL AND T_PROJECT_PLAN_TAGS.ESTIMATED_DURATION > 0