SELECT project_task_id, COALESCE(start_date, current_date) start_date, COALESCE(finish_date, current_date) finish_date, COALESCE(description, state_name) 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, workflow_order FROM (SELECT project_task_id, start_date, finish_date, DESCRIPTION, application_module, state_name, workflow_type_name, application_name, DURATION, daysremaining, task_count, tasks_completed, duration_changes, user_id, state_id, workflow_type_id, application_id, workflow_order 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, t_gps_workflow_states.DESCRIPTION, issues.application_module, t_gps_workflow_states.state_name, 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, wt.workflow_order 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, ws.DESCRIPTION, issues.application_module, ws.state_name, 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, wt.workflow_order 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 --WHERE p1.application_id = 8522 UNION ALL --This Union is to get workflow(s) where tasks are not available SELECT null task_id, COALESCE(TO_DATE(tgwp.wfstartdate, 'dd/mm/yyyy'), current_date) start_date, COALESCE(TO_DATE(tgwp.wffinishdate, 'dd/mm/yyyy'), current_date) finish_date, NULL tag, NULL module, t_gps_workflow_types.workflow_type_name, t_gps_applications.application_name, COALESCE(to_number(tgwp.wfduration), 0) DURATION, 0 remainingdays, 0 percent_completed, 0 tasks_count, 0 tasks_completed, 0 duration_changes, null user_id, null state_id, t_gps_workflow_types.workflow_type_id, t_gps_applications.application_id, t_gps_workflow_types.workflow_order 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 FROM t_gps_wft_properties GROUP BY workflow_type_id) A WHERE A.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.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)) --AND t_gps_applications.application_id = 7111