SELECT ISSUES.ISSUE_NUMBER IA_ISSUE_NUMBER, T_GPS_INSTANCES.Instance_Primary_Id, ISSUES.ISSUE_TITLE IA_ISSUE_TITLE, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID, T_GPS_APPLICATIONS.APPLICATION_NAME, T_GPS_APPLICATIONS.APPLICATION_ID, T_INSTANCES.OWNER, (SELECT user_name FROM t_project_task WHERE state_id = T_GPS_INSTANCES.current_state_id AND instance_id = T_INSTANCES.instance_id AND rownum = 1) USER_NAME, (SELECT DECODE(ws.state_descriptor, 'Workflow End', 'Issue Closed', 'AND State', 'Issue Closed',decode(ws.State_Order, (select max(state_order) from stategps.t_gps_workflow_states a where a.workflow_type_id = ws.workflow_type_id group by a.workflow_type_id), 'Issue Closed',ws.state_name)) FROM t_gps_workflow_states ws WHERE ws.state_id = T_GPS_INSTANCES.current_state_id AND rownum = 1 ) STATE_NAME, T_INSTANCES.CREATION_DATE START_DATE, NVL((SELECT FINISH_DATE FROM T_PROJECT_TASK WHERE instance_id = T_INSTANCES.INSTANCE_ID AND STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID AND rownum = 1), (SELECT MAX(MOVED_OUT_DATE) FROM t_gps_instances WHERE INSTANCE_ID = T_INSTANCES.INSTANCE_ID)) END_DATE, (SELECT SUM(NVL(daysremaining, duration)) FROM t_project_task WHERE instance_id = T_INSTANCES.instance_id AND duration > 0 AND daysremaining > 0) DAYSREMAING, (SELECT comments FROM t_project_task WHERE state_id = T_GPS_INSTANCES.current_state_id AND instance_id = T_INSTANCES.instance_id AND rownum = 1) COMMENTS, NVL(T_INSTANCES.PRIORITY, 99) PRIORITY, T_INSTANCES.INSTANCE_ID PROPERTY3, T_INSTANCES.CREATION_DATE crtd_date, DECODE(T_GPS_INSTANCES.active, 1, 'Active', 'Inactive') Active, T_INSTANCES.DUE_DATE, NULL DURATION, T_GPS_INSTANCES.current_state_id current_state_id, T_GPS_INSTANCES.Instance_Present, T_GPS_WORKFLOW_STATES.state_type, NULL FINAL_STATE_ID, (SELECT DOCUMENT_ID FROM t_instance_documents WHERE instance_id = T_INSTANCES.Instance_Id AND rownum = 1) DOCUMENT_ID, (SELECT DOCUMENT FROM t_instance_documents WHERE instance_id = T_INSTANCES.Instance_Id AND rownum = 1) DOCUMENT_PATH, x.approver, x.approver_comments, x.approved_on, (select sum(a.non_billable_hours) from stategps.t_project_activity a, stategps.t_project_task b where a.PROJECT_TASK_ID = b.PROJECT_TASK_ID AND instance_id = T_INSTANCES.instance_id group by b.INSTANCE_ID) time_spent_in_hours, (SELECT b.feedback_answer FROM t_gps_wf_state_feedback_form a, t_gps_instance_feedback b, t_gps_instances c, t_instances d where a.question_id = b.question_id AND a.state_id = c.Current_State_Id AND C.INSTANCE_ID = d.instance_id and d.instance_id = T_INSTANCES.INSTANCE_ID and b.feedback_answer IS NOT NULL and UPPER(a.question) like UPPER('%Time%') and c.from_state_id is not null and b.instance_primary_id = c.instance_primary_id) TIME_IT_TOOK, (SELECT b.feedback_answer FROM t_gps_wf_state_feedback_form a, t_gps_instance_feedback b, t_gps_instances c, t_instances d where a.question_id = b.question_id AND a.state_id = c.Current_State_Id AND C.INSTANCE_ID = d.instance_id and d.instance_id = T_INSTANCES.INSTANCE_ID and b.feedback_answer IS NOT NULL and UPPER(a.question) like UPPER('%Solution%') and c.from_state_id is not null and b.instance_primary_id = c.instance_primary_id) OVERALL_QUALITY, (SELECT b.feedback_answer FROM t_gps_wf_state_feedback_form a, t_gps_instance_feedback b, t_gps_instances c, t_instances d where a.question_id = b.question_id AND a.state_id = c.Current_State_Id AND C.INSTANCE_ID = d.instance_id and d.instance_id = T_INSTANCES.INSTANCE_ID and b.feedback_answer IS NOT NULL and UPPER(a.question) like UPPER('%documentation%') and c.from_state_id is not null and b.instance_primary_id = c.instance_primary_id) QUALITY_OF_THE_DOCUMENTATION, (SELECT b.feedback_answer FROM t_gps_wf_state_feedback_form a, t_gps_instance_feedback b, t_gps_instances c, t_instances d where a.question_id = b.question_id AND a.state_id = c.Current_State_Id AND C.INSTANCE_ID = d.instance_id and d.instance_id = T_INSTANCES.INSTANCE_ID and b.feedback_answer IS NOT NULL and UPPER(a.question) like UPPER('%Communication%') and c.from_state_id is not null and b.instance_primary_id = c.instance_primary_id) QUALITY_OF_COMMUNICATION, T_GPS_WORKFLOW_STATES.state_name currentstate, t_gps_instances.moved_out_date moved_out_date, T_GPS_INSTANCES.State_Comments, ISSUES.Parent_Issue_Number PARENT_ISSUE_NUMBER FROM STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS, STATEGPS.T_GPS_APP_PROPERTIES T_GPS_APP_PROPERTIES, STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES, STATEGPS.T_INSTANCES T_INSTANCES, --STATEGPS.V_INSTANCE_PROPS V_INSTANCE_PROPS, STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES, STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES, (SELECT DISTINCT pt.user_name approver, pt.comments approver_comments, gi.moved_out_date approved_on, gi.instance_id instance_id FROM t_gps_workflow_states ws, t_project_task pt, t_gps_instances gi WHERE ws.state_id = pt.state_id AND pt.duration > 0 AND gi.instance_id = pt.instance_id AND gi.current_state_id = pt.state_id AND gi.instance_present = 0 AND UPPER(ws.state_name) LIKE '%APPROV%' AND state_order IN (SELECT MAX(state_order) FROM t_gps_workflow_states GROUP BY workflow_type_id)) x, TRI2GPS_DEV.ISSUES ISSUES WHERE T_INSTANCES.INSTANCE_ID = ISSUES.INSTANCE_ID AND T_GPS_APPLICATIONS.APPLICATION_ID = T_INSTANCES.APPLICATION_ID AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_APP_PROPERTIES.APPLICATION_ID AND T_GPS_APP_PROPERTIES.PROPERTY_NAME = 'PROJECTPLAN_APPLICATION' AND T_GPS_APP_PROPERTIES.PROPERTY_VALUE = 'TRUE' AND T_INSTANCES.INSTANCE_ID = T_GPS_INSTANCES.INSTANCE_ID AND T_GPS_INSTANCES.WORKFLOW_TYPE_ID = T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_WORKFLOW_TYPES.APPLICATION_ID AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID = T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID AND T_GPS_WORKFLOW_STATES.STATE_ID = T_GPS_INSTANCES.CURRENT_STATE_ID AND T_GPS_WORKFLOW_STATES.STATE_DESCRIPTOR NOT IN ('AND State') AND (T_GPS_INSTANCES.INSTANCE_PRESENT = 1 OR T_GPS_WORKFLOW_STATES.STATE_ORDER = (SELECT MAX(state_order) FROM t_gps_workflow_states WHERE workflow_type_id = T_GPS_WORKFLOW_TYPES.workflow_type_id)) AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' AND T_GPS_WORKFLOW_STATES.VISIBLE_FLAG = 'true' AND T_INSTANCES.ACTIVE = 1 AND T_INSTANCES.INSTANCE_ID = x.instance_id(+) AND T_GPS_INSTANCES.From_State_Id IS NOT NULL --AND T_GPS_INSTANCES.ACTIVE = 1 GROUP BY ISSUES.ISSUE_NUMBER, ISSUES.ISSUE_TITLE, T_GPS_APPLICATIONS.APPLICATION_NAME, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME, T_INSTANCES.PRIORITY, T_GPS_INSTANCES.CURRENT_STATE_ID, T_GPS_INSTANCES.ACTIVE, T_INSTANCES.CREATION_DATE, T_GPS_APPLICATIONS.APPLICATION_ID, T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID, T_INSTANCES.INSTANCE_ID, T_GPS_WORKFLOW_STATES.STATE_ID, T_INSTANCES.OWNER, T_INSTANCES.DUE_DATE, x.approver, x.approver_comments, x.approved_on, T_GPS_INSTANCES.Instance_Primary_Id, T_GPS_WORKFLOW_STATES.state_name, t_gps_instances.moved_out_date, T_GPS_INSTANCES.State_Comments, T_GPS_INSTANCES.Instance_Present, T_GPS_WORKFLOW_STATES.state_type, ISSUES.Parent_Issue_Number