SELECT T_REPORTING_PLAN_TASKS.TASK_ID, T_REPORTING_PLAN_TASKS.HISTORY_ID, T_REPORTING_PLAN_TASKS.JS_GANTT_CHART_TASK, T_REPORTING_PLAN_TASKS.TASK_ORDER, T_REPORTING_PLAN_TASKS.TASK_TYPE, CASE WHEN T_REPORTING_PLAN_TASKS.WORKFLOW_ID IS NULL AND TASK_ORDER = 0 THEN - 1 ELSE T_REPORTING_PLAN_TASKS.WORKFLOW_ID END, T_REPORTING_PLAN_TASKS.MODULE, T_REPORTING_PLAN_TASKS.TAG, TOTAL_TASKS, TASKS_COMPLETED, TRUNC(T_REPORTING_PLAN_TASKS.START_DATE)START_DATE, CASE WHEN T_REPORTING_PLAN_TASKS.WORKFLOW_ID IS NULL AND TASK_ORDER = 0 THEN CASE WHEN T_REPORTING_PLAN_HISTORY.PROJECT_BUFFER_END_DATE IS NOT NULL AND T_REPORTING_PLAN_HISTORY.PROJECT_BUFFER_END_DATE > T_REPORTING_PLAN_TASKS.END_DATE THEN TRUNC(T_REPORTING_PLAN_HISTORY.PROJECT_BUFFER_END_DATE) ELSE TRUNC(T_REPORTING_PLAN_TASKS.END_DATE) END WHEN T_REPORTING_PLAN_TASKS.WORKFLOW_ID IS NOT NULL AND T_REPORTING_PLAN_TASKS.MODULE IS NULL AND T_REPORTING_PLAN_TASKS.TAG IS NULL THEN CASE WHEN T_REPORTING_PLAN_TASKS.END_DATE <= (SELECT MAX(TD.END_DATE) FROM T_REPORTING_PLAN_TASK_DETAILS TD WHERE TD.EXECUTION_ID = T_REPORTING_PLAN_TASKS.HISTORY_ID AND TD.WORKFLOW_ID = T_REPORTING_PLAN_TASKS.WORKFLOW_ID AND TD.MODULE IS NULL AND TD.TAG IS NULL) THEN (SELECT TRUNC(MAX(TD.END_DATE)) FROM T_REPORTING_PLAN_TASK_DETAILS TD WHERE TD.EXECUTION_ID = T_REPORTING_PLAN_TASKS.HISTORY_ID AND TD.WORKFLOW_ID = T_REPORTING_PLAN_TASKS.WORKFLOW_ID AND TD.MODULE IS NULL AND TD.TAG IS NULL) WHEN T_REPORTING_PLAN_TASKS.END_DATE < ( SELECT MAX(TO_DATE(PROPERTY_VALUE,'DD/MM/YYYY')) FROM T_GPS_WFT_PROPERTIES WHERE PROPERTY_NAME = 'wfFinishDate' AND WORKFLOW_TYPE_ID = T_REPORTING_PLAN_TASKS.WORKFLOW_ID )THEN ( SELECT MAX(TO_DATE(PROPERTY_VALUE,'DD/MM/YYYY')) FROM T_GPS_WFT_PROPERTIES WHERE PROPERTY_NAME = 'wfFinishDate' AND WORKFLOW_TYPE_ID = T_REPORTING_PLAN_TASKS.WORKFLOW_ID ) ELSE TRUNC(T_REPORTING_PLAN_TASKS.END_DATE) END ELSE TRUNC(T_REPORTING_PLAN_TASKS.END_DATE) END FINISH_DATE, 'Task' FROM STATEGPS.T_REPORTING_PLAN_HISTORY T_REPORTING_PLAN_HISTORY, STATEGPS.T_REPORTING_PLAN_TASKS T_REPORTING_PLAN_TASKS WHERE T_REPORTING_PLAN_HISTORY.HISTORY_ID = T_REPORTING_PLAN_TASKS.HISTORY_ID UNION ALL SELECT BUFFER_TASK.TASK_ID, T_REPORTING_PLAN_HISTORY.HISTORY_ID, BUFFER_TASK.JS_CHART_TASK, BUFFER_TASK.TASK_ORDER, NULL TASK_TYPE, BUFFER_TASK.WORKFLOW_ID, NULL, BUFFER_TASK.TAG, NULL, NULL, NULL, NULL, BUFFER_TASK.TAG_TYPE FROM T_REPORTING_PLAN_HISTORY T_REPORTING_PLAN_HISTORY, TABLE(GET_PROJECT_BUFFER_TASK(T_REPORTING_PLAN_HISTORY.HISTORY_ID)) BUFFER_TASK