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, 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) - trunc(t_reporting_plan_tasks.start_date) when ((select trunc(max(wffinishdate)) from (select workflow_type_id,max(case when property_name = 'wfFinishDate' then to_date(property_value, 'dd/mm/yyyy') end) wffinishdate, max(case when property_name = 'wfDuration' then property_value end) wfduration from t_gps_wft_properties group by workflow_type_id) a, t_gps_workflow_types t_gps_workflow_types where a.wfduration <> 0 and a.workflow_type_id = t_gps_workflow_types.workflow_type_id and t_gps_workflow_types.application_id = t_reporting_plan_history.project_id) > t_reporting_plan_tasks.end_date ) then (select trunc(max(wffinishdate)) from (select workflow_type_id,max(case when property_name = 'wfFinishDate' then to_date(property_value, 'dd/mm/yyyy') end) wffinishdate, max(case when property_name = 'wfDuration' then property_value end) wfduration from t_gps_wft_properties group by workflow_type_id) a, t_gps_workflow_types t_gps_workflow_types where a.wfduration <> 0 and a.workflow_type_id = t_gps_workflow_types.workflow_type_id and t_gps_workflow_types.application_id = t_reporting_plan_history.project_id) - trunc(t_reporting_plan_tasks.start_date) else duration 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 and task_est.wffinishdate is not null and task_est.wffinishdate > t_reporting_plan_tasks.end_date then trunc(task_est.wffinishdate) - trunc(t_reporting_plan_tasks.start_date) else duration end duration, 'Task' from stategps.t_reporting_plan_history t_reporting_plan_history, stategps.t_reporting_plan_tasks t_reporting_plan_tasks left outer join (select * from (select workflow_type_id, max(case when property_name = 'wfStartDate' then to_date(property_value, 'dd/mm/yyyy') end) wfstartdate, max(case when property_name = 'wfFinishDate' then to_date(property_value, 'dd/mm/yyyy') end) wffinishdate, max(case when property_name = 'wfDuration' then property_value end) wfduration from t_gps_wft_properties group by workflow_type_id) a where a.wfduration <> 0) task_est on t_reporting_plan_tasks.workflow_id = task_est.workflow_type_id 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 , 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