SELECT "STATE_NAME", null "PROJECT_TASK_ID", STATE_NAME "ACTUAL_STATE_NAME", STATE_NAME "ACTION_ITEM_DESCRIPTION", 'COMPLETED' "STATUS", null "RESOURCE_NAME", t_users.FIRST_NAME || ' ' || t_users.MIDDLE_NAME || ' ' || t_users.LAST_NAME USER_NAME, t_instances.creation_date "START_DATE", t_instances.creation_date "FINISH_DATE", null "DURATION", null "DAYSREMAINING", issues.notes "COMMENTS", null "MANAGER_COMMENTS", issues.instance_id "INSTANCE_ID", "STATE_ORDER", "DISPLAY_ORDER", issue_number "PROPERTY_VALUE", "WORKFLOW_TYPE_NAME", '' for_order from t_instances t_instances, tri2gps_dev.issues issues, t_gps_workflow_states t_gps_workflow_states, t_gps_workflow_types t_gps_workflow_types, tas.t_users t_users where t_instances.instance_id = issues.instance_id and t_instances.workflow_type_id = t_gps_workflow_states.workflow_type_id and t_gps_workflow_states.workflow_type_id = t_gps_workflow_types.workflow_type_id and issues.created_by = t_users.user_id and t_gps_workflow_states.startnode = 1 and t_instances.active=1 --and t_gps_workflow_states.state_descriptor <> 'Workflow Move' --AND issues.issue_number in ('CC/ISOE/19103') UNION ALL SELECT nvl((select k.action_item_name from t_task_action_items k where k.project_task_id = T_PROJECT_TASK.Project_Task_Id), T_GPS_WORKFLOW_STATES.STATE_NAME) STATE_NAME, project_task_id, T_GPS_WORKFLOW_STATES.STATE_NAME ACTUAL_STATE_NAME, (select description from t_task_action_items where project_task_id = T_PROJECT_TASK.Project_Task_Id) action_item_description, DECODE(t_gps_instances.instance_present, 1, DECODE(T_PROJECT_TASK.DAYSREMAINING, 0, decode((select count(state_id) count1 from t_project_task a where a.instance_id = T_PROJECT_TASK.Instance_Id and a.state_id = T_PROJECT_TASK.State_Id group by instance_id, state_id), 1, 'STARTED', 'COMPLETED'), DECODE(t_instances.PREDECESSOR, NULL, 'STARTED', DECODE((SELECT DISTINCT gi.instance_present FROM STATEGPS.t_gps_instances gi, STATEGPS.t_project_task pt, STATEGPS.t_gps_workflow_states ws WHERE gi.instance_id = pt.instance_id AND gi.current_state_id = ws.state_id AND gi.instance_id IN (SELECT ip.PRED_INSTANCE_ID FROM STATEGPS.t_instance_predecessor ip, STATEGPS.t_gps_workflow_states ws1 WHERE ip.instance_id = T_PROJECT_TASK.Instance_Id AND NVL(ip.pred_state_id, ws.state_id) = ws1.state_id AND ws.state_order <= ws1.state_order) AND gi.instance_present = 1 AND gi.active = 1), 1, 'PENDING', DECODE(T_PROJECT_TASK.DURATION, 0, 'SKIPPED', 'STARTED')))), 0, DECODE(T_PROJECT_TASK.DAYSREMAINING, 0, DECODE(T_PROJECT_TASK.DURATION, 0, 'SKIPPED', 'COMPLETED'), DECODE(t_gps_instances.instance_present, 0, DECODE(T_PROJECT_TASK.DURATION, 0, 'SKIPPED', 'COMPLETED')))) STATUS, T_PROJECT_TASK.RESOURCE_NAME, decode(T_GPS_APPLICATIONS.Application_Name, 'Application Object Migrator', DECODE(t_gps_instances.instance_present, 0, T_USERS1.FIRST_NAME || ' ' || T_USERS1.MIDDLE_NAME || ' ' || T_USERS1.LAST_NAME, stategps.getApproverName(T_GPS_WORKFLOW_STATES.STATE_ID)), DECODE(T_PROJECT_TASK.DAYSREMAINING,null,T_USERS.FIRST_NAME || ' ' || T_USERS.MIDDLE_NAME || ' ' || T_USERS.LAST_NAME, 0, decode((select count(state_id) count1 from t_project_task a where a.instance_id = T_PROJECT_TASK.Instance_Id and a.state_id = T_PROJECT_TASK.State_Id group by instance_id, state_id), 1,T_USERS1.FIRST_NAME || ' ' || T_USERS1.MIDDLE_NAME || ' ' || T_USERS1.LAST_NAME, T_USERS.FIRST_NAME || ' ' || T_USERS.MIDDLE_NAME || ' ' || T_USERS.LAST_NAME))) USER_NAME, T_GPS_INSTANCES.CREATED_DATE START_DATE, DECODE(T_PROJECT_TASK.DAYSREMAINING, 0, decode((select count(state_id) count1 from t_project_task a where a.instance_id = T_PROJECT_TASK.Instance_Id and a.state_id = T_PROJECT_TASK.State_Id group by instance_id, state_id), 1, T_GPS_INSTANCES.MOVED_OUT_DATE, decode(sign(T_PROJECT_TASK.FINISH_DATE - T_GPS_INSTANCES.CREATED_DATE), -1, T_GPS_INSTANCES.MOVED_OUT_DATE, null, T_GPS_INSTANCES.MOVED_OUT_DATE, T_PROJECT_TASK.FINISH_DATE)), T_GPS_INSTANCES.MOVED_OUT_DATE) FINISH_DATE, T_PROJECT_TASK.DURATION, T_PROJECT_TASK.DAYSREMAINING, --T_GPS_INSTANCES.STATE_COMMENTS COMMENTS, DECODE(T_PROJECT_TASK.DAYSREMAINING, 0, decode((select count(state_id) count1 from t_project_task a where a.instance_id = T_PROJECT_TASK.Instance_Id and a.state_id = T_PROJECT_TASK.State_Id group by instance_id, state_id), 1, T_GPS_INSTANCES.STATE_COMMENTS, decode(sign(T_PROJECT_TASK.FINISH_DATE - T_GPS_INSTANCES.CREATED_DATE), -1, T_GPS_INSTANCES.STATE_COMMENTS, T_PROJECT_TASK.COMMENTS)), T_GPS_INSTANCES.STATE_COMMENTS) COMMENTS, T_PROJECT_TASK.MANAGER_COMMENTS, T_PROJECT_TASK.INSTANCE_ID, T_GPS_WORKFLOW_STATES.STATE_ORDER, T_GPS_WORKFLOW_STATES.Display_Order, T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE, T_GPS_WORKFLOW_TYPES.Workflow_Type_Name, '' for_order FROM STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES LEFT OUTER JOIN TAS.T_USERS T_USERS1 on T_USERS1.USER_NAME = T_GPS_INSTANCES.MOVED_OUT_BY, STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES, STATEGPS.T_PROJECT_TASK T_PROJECT_TASK LEFT OUTER JOIN TAS.T_USERS T_USERS on T_USERS.USER_NAME = T_PROJECT_TASK.USER_NAME, STATEGPS.T_INSTANCES T_INSTANCES, STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS, STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES, STATEGPS.T_WORKFLOW_STATE_TASK_V T_WORKFLOW_STATE_TASK_V, STATEGPS.T_GPS_INSTANCE_PROPERTIES T_GPS_INSTANCE_PROPERTIES WHERE T_PROJECT_TASK.STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID AND T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID = T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID AND T_GPS_WORKFLOW_TYPES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID AND (T_PROJECT_TASK.STATE_ID = T_WORKFLOW_STATE_TASK_V.STATE_ID AND T_PROJECT_TASK.STATE_ID = T_GPS_INSTANCES.CURRENT_STATE_ID) AND (T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL OR T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true') AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID AND T_PROJECT_TASK.INSTANCE_ID = T_GPS_INSTANCES.INSTANCE_ID AND T_GPS_INSTANCES.ACTIVE = 1 AND T_GPS_INSTANCE_PROPERTIES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID AND (T_GPS_INSTANCES.VISIBLE_FLAG IS NULL OR T_GPS_INSTANCES.VISIBLE_FLAG = 'true') --AND T_GPS_WORKFLOW_STATES.STATE_NAME ='Verification on change initiation' --AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE in ('CC/ISOE/19103') AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME IN ('tri2.SG_TRI2_ENTITY.IA_ISSUE_NUMBER', 'tri2.SG_TRI2_ENTITY.MIG_REQUEST_NUMBER') UNION ALL SELECT DISTINCT nvl((select k.action_item_name from t_task_action_items k where k.project_task_id = T_PROJECT_TASK.Project_Task_Id), T_GPS_WORKFLOW_STATES.STATE_NAME) STATE_NAME, project_task_id, T_GPS_WORKFLOW_STATES.STATE_NAME ACTUAL_STATE_NAME, (select description from t_task_action_items where project_task_id = T_PROJECT_TASK.Project_Task_Id) action_item_description, DECODE(T_PROJECT_TASK.DAYSREMAINING, 0, DECODE(T_PROJECT_TASK.DURATION, 0, 'SKIPPED', 'COMPLETED'), DECODE((select instance_present from t_gps_instances a, T_GPS_WORKFLOW_STATES b where a.instance_id = T_PROJECT_TASK.INSTANCE_ID and a.current_state_id = b.state_id and ( /*(b.state_descriptor = 'Workflow End' and a.instance_present = 0)or */ b.state_order in (select max(state_order) from t_gps_workflow_states a, t_instances y where a.workflow_type_id = y.workflow_type_id and y.instance_id = T_PROJECT_TASK.INSTANCE_ID) and a.instance_present = 1)), 0, 'PATH NOT TRAVERSED', 1, 'PATH NOT TRAVERSED', 'NOT STARTED')) STATUS, T_PROJECT_TASK.RESOURCE_NAME, DECODE(T_GPS_APPLICATIONS.Application_Name, 'Application Object Migrator', getApproverName(T_GPS_WORKFLOW_STATES.STATE_ID), T_USERS.FIRST_NAME || ' ' || T_USERS.MIDDLE_NAME || ' ' || T_USERS.LAST_NAME) USER_NAME, NULL START_DATE, NULL FINISH_DATE, T_PROJECT_TASK.DURATION, T_PROJECT_TASK.DAYSREMAINING, NULL COMMENTS, NULL MANAGER_COMMENTS, T_PROJECT_TASK.INSTANCE_ID, T_GPS_WORKFLOW_STATES.STATE_ORDER, T_GPS_WORKFLOW_STATES.Display_Order, T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE, T_GPS_WORKFLOW_TYPES.Workflow_Type_Name, 'NOT STARTED' for_order FROM STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES, STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES, --TAS.T_USERS T_USERS1, STATEGPS.T_PROJECT_TASK T_PROJECT_TASK LEFT OUTER JOIN TAS.T_USERS T_USERS on T_USERS.USER_NAME = T_PROJECT_TASK.USER_NAME, STATEGPS.T_INSTANCES T_INSTANCES, STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS, STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES, STATEGPS.T_WORKFLOW_STATE_TASK_V T_WORKFLOW_STATE_TASK_V, STATEGPS.T_GPS_INSTANCE_PROPERTIES T_GPS_INSTANCE_PROPERTIES WHERE T_PROJECT_TASK.STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID --AND T_USERS1.USER_NAME = T_GPS_INSTANCES.MOVED_OUT_BY AND T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID = T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID AND T_GPS_WORKFLOW_TYPES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID AND T_PROJECT_TASK.STATE_ID = T_WORKFLOW_STATE_TASK_V.STATE_ID AND (T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL OR T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true') AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID AND T_PROJECT_TASK.INSTANCE_ID = T_GPS_INSTANCES.INSTANCE_ID AND T_GPS_INSTANCES.ACTIVE = 1 AND T_GPS_INSTANCE_PROPERTIES.Instance_Id = T_PROJECT_TASK.INSTANCE_ID --AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE in ('CC/ISOE/19103') AND NOT EXISTS (SELECT T_GPS_INSTANCES$1.CURRENT_STATE_ID FROM STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES$1 WHERE T_PROJECT_TASK.STATE_ID = T_GPS_INSTANCES$1.CURRENT_STATE_ID AND T_PROJECT_TASK.INSTANCE_ID = T_GPS_INSTANCES$1.INSTANCE_ID AND (T_GPS_INSTANCES$1.VISIBLE_FLAG IS NULL OR T_GPS_INSTANCES$1.VISIBLE_FLAG = 'true')) --AND T_GPS_WORKFLOW_STATES.STATE_NAME ='Verification on change initiation' --AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE in ('CC/ISOE/19103') AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME IN ('tri2.SG_TRI2_ENTITY.IA_ISSUE_NUMBER', 'tri2.SG_TRI2_ENTITY.MIG_REQUEST_NUMBER') union all SELECT "STATE_NAME", null "PROJECT_TASK_ID", STATE_NAME "ACTUAL_STATE_NAME", null "ACTION_ITEM_DESCRIPTION", decode(moved_out_date, null, 'STARTED', 'COMPLETED') "STATUS", null "RESOURCE_NAME", 'AOM System' USER_NAME, t_instances.created_date "START_DATE", t_instances.moved_out_date "FINISH_DATE", null "DURATION", null "DAYSREMAINING", decode(moved_out_date, null, '', 'Moved by System') "COMMENTS", null "MANAGER_COMMENTS", t_migration_requests.instance_id "INSTANCE_ID", "STATE_ORDER", "DISPLAY_ORDER", migration_request_number "PROPERTY_VALUE", "WORKFLOW_TYPE_NAME", '' for_order from t_gps_instances t_instances, tri2gps_dev.t_migration_requests t_migration_requests, t_gps_workflow_states t_gps_workflow_states, t_gps_workflow_types t_gps_workflow_types where t_instances.instance_id = t_migration_requests.instance_id and t_instances.workflow_type_id = t_gps_workflow_states.workflow_type_id and t_gps_workflow_states.workflow_type_id = t_gps_workflow_types.workflow_type_id and t_instances.current_state_id = t_gps_workflow_states.state_id and t_gps_workflow_states.state_descriptor = 'Self Move State' and t_instances.active=1 --and t_migration_requests.migration_request_number = 'CC/ISOE/19103' --1512 ORDER BY start_date, STATE_ORDER, FINISH_DATE