select distinct a.state_id, HISTORY_ID,USER_COMMENTS,UPDATE_DATE,DAYSREMAINING,DURATION,USER_NAME, UPDATED_BY,PRIORITY,PROMISED_DATE,MANAGER_COMMENTS,APPLICATION_NAME,STATE_NAME,WORKFLOW_TYPE_NAME,PROJECT_TASK_ID, ISSUE_TITLE,ISSUE_NUMBER,INSTANCE_ID,APPLICATION_MODULE, getDocuments(HISTORY_ID) as DOCUMENT_NAME from ( SELECT t_project_task_history.HISTORY_ID "HISTORY_ID" ,t_project_task_history.COMMENTS "USER_COMMENTS" ,to_char(t_project_task_history.UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') "UPDATE_DATE" ,t_project_task_history.DAYSREMAINING "DAYSREMAINING" ,t_project_task_history.DURATION "DURATION" ,( SELECT(first_name || '' || middle_name || ' ' || last_name) FROM stategps.t_users t WHERE t.user_name = t_users.USER_NAME ) "USER_NAME" ,( SELECT(first_name || '' || middle_name || ' ' || last_name) FROM stategps.t_users t WHERE t.user_name = t_task_manager_comment.UPDATED_BY ) "UPDATED_BY" ,(SELECT T_PROJECT_CUSTOM_PRIORITY.priority FROM stategps.T_PROJECT_CUSTOM_PRIORITY T_PROJECT_CUSTOM_PRIORITY WHERE T_PROJECT_CUSTOM_PRIORITY.start_range = t_task_manager_comment.PRIORITY ) "PRIORITY" ,t_task_manager_comment.PROMISED_DATE "PROMISED_DATE" ,t_task_manager_comment.COMMENTS "MANAGER_COMMENTS" ,t_gps_applications.APPLICATION_NAME "APPLICATION_NAME" ,t_gps_workflow_states.STATE_NAME "STATE_NAME" ,t_gps_workflow_types.WORKFLOW_TYPE_NAME "WORKFLOW_TYPE_NAME" ,t_project_task.PROJECT_TASK_ID "PROJECT_TASK_ID" ,t_project_task.ISSUE_TITLE "ISSUE_TITLE" ,t_project_task.ISSUE_NUMBER "ISSUE_NUMBER" ,t_project_task.INSTANCE_ID "INSTANCE_ID" ,t_project_task.APPLICATION_MODULE "APPLICATION_MODULE" ,t_gps_workflow_states.state_id ,CASE WHEN t_instance_child_docs.DOCUMENT_NAME IS NOT NULL THEN t_instance_child_docs.document || '/' || t_instance_child_docs.DOCUMENT_NAME ELSE t_instance_child_docs.DOCUMENT_NAME END AS "DOCUMENT_NAME" FROM stategps.t_project_task_history t_project_task_history ,tas.t_users t_users ,stategps.t_task_manager_comment t_task_manager_comment ,stategps.t_gps_applications t_gps_applications ,stategps.t_gps_workflow_states t_gps_workflow_states ,stategps.t_gps_workflow_types t_gps_workflow_types ,(select t_project_task1.PROJECT_TASK_ID, t_project_task1.state_id, t_project_task1.instance_id, issues.ISSUE_TITLE ,issues.ISSUE_NUMBER ,issues.APPLICATION_MODULE from tri2gps_dev.issues issues,stategps.t_project_task t_project_task1 where issues.INSTANCE_ID = t_project_task1.INSTANCE_ID) t_project_task LEFT OUTER JOIN (select parent_docs.instance_id,parent_docs.document_id,parent_docs.document,child_docs.document_name,child_docs.state_id from stategps.t_Instance_Documents parent_docs, stategps.t_instance_child_docs child_docs where parent_docs.document_id=child_docs.document_id ) t_instance_child_docs ON t_project_task.instance_id = t_instance_child_docs.Instance_Id and t_project_task.state_id = t_instance_child_docs.state_id WHERE t_gps_workflow_types.APPLICATION_ID = t_gps_applications.APPLICATION_ID AND t_project_task.STATE_ID = t_gps_workflow_states.STATE_ID AND t_project_task_history.PROJECT_TASK_ID = t_project_task.PROJECT_TASK_ID AND t_project_task_history.RESOURCE_ID = t_users.USER_ID AND t_gps_workflow_states.WORKFLOW_TYPE_ID = t_gps_workflow_types.WORKFLOW_TYPE_ID AND t_task_manager_comment.PROJECT_TASK_ID = t_project_task.PROJECT_TASK_ID AND t_task_manager_comment.UPDATED_AT = t_project_task_history.UPDATE_DATE ORDER BY t_project_task_history.HISTORY_ID ASC ) a