SELECT APPLICATIONS1.APPLICATION_ID APPLICATION_ID, APPLICATIONS1.APPLICATION_NAME, WORKFLOWS.WORKFLOW_TYPE_ID, WORKFLOWS.WORKFLOW_TYPE_NAME, PROJECTTASKS.START_DATE, PROJECTTASKS.FINISH_DATE, PROJECTTASKS.DURATION, PROJECTTASKS.DAYSREMAINING, PROJECTTASKS.RESOURCE_NAME, PROJECTTASKS.USER_NAME, PROJECTTASKS.ACTUALSTART_DATE, PROJECTTASKS.ACTUALFINISH_DATE, STATES.State_Id, instance_props.IA_ISSUE_NUMBER, instance_props.IA_ISSUE_TITLE, NULL PROPERTY1, NULL PROPERTY2, PROJECTTASKS.INSTANCE_ID PROPERTY3, 22.2 PROPERTY4, PROJECTTASKS.PROJECT_TASK_ID, NULL COMMENTS, NULL USER_ID, NULL REASSIGN_STATE, decode(insts.priority, NULL, PROJECTTASKS.PRIORITY, insts.priority) PRIORITY, NULL OWNER, STATES.State_Name, (select DOCUMENT_ID from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_ID, (select DOCUMENT from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_PATH, ISSUES.CREATED_DATE, ISSUES.DUE_DATE, decode(PROJECTTASKS.Duration, 0, 'SKIPPED', decode(gps_insts.instance_present, 1, decode(insts.PREDECESSOR,NULL,'STARTED', --The following decode will show the status as PENDING, if the predecessor ISSUE Tasks are still STARTED -- Otherwise, it will show the status as STARTED DECODE((SELECT distinct gi.instance_present from t_gps_instances gi, t_project_task pt where gi.instance_id = pt.instance_id and gi.current_state_id = pt.state_id and gi.instance_id in (select ip.PRED_INSTANCE_ID from t_instance_predecessor ip where ip.instance_id = PROJECTTASKS.Instance_Id -- If the ip.pred_state_id is null, then use gi.current_state_id and gi.current_state_id = nvl(ip.pred_state_id,gi.current_state_id)) -- The predecessor instance should be active=1 and instance_present=1 and gi.instance_present = 1 and gi.active = 1 ), 1, 'PENDING', 'STARTED')), NULL, 'NOT STARTED', 'COMPLETED')) STATUS, NULL MANAGER_COMMENTS, insts.due_date CURRENT_DUE_DATE, insts.PREDECESSOR, PROJECTTASKS.PROMISED_DUE_DATE, ---added application module from tri2 issues table by kchinta on 27/5/2009 ISSUES.Application_Module, ---added STATE_ORDER from T_GPS_WORKFLOW_STATES table by kchinta on 8/6/2009 STATES.STATE_ORDER ---added Migration Number ,Migration request id,MIG_ISSUE_LINK_ID , NULL MIGRATION_NUMBER, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , ISSUES.ISSUE_ID ISSUE_ID, PROJECTTASKS.Critical FROM T_GPS_APPLICATIONS APPLICATIONS1, T_GPS_WORKFLOW_TYPES WORKFLOWS, T_GPS_WORKFLOW_STATES STATES, T_PROJECT_TASK PROJECTTASKS, t_workflow_state_task_v t_workflow_state_task_v, t_instances insts, t_gps_instances gps_insts, tri2gps_dev.issues ISSUES, v_instance_props instance_props WHERE PROJECTTASKS.STATE_ID = STATES.STATE_ID AND STATES.WORKFLOW_TYPE_ID = WORKFLOWS.WORKFLOW_TYPE_ID AND WORKFLOWS.APPLICATION_ID = APPLICATIONS1.APPLICATION_ID and PROJECTTASKS.state_id = t_workflow_state_task_v.state_id and PROJECTTASKS.STATE_ID = gps_insts.current_state_id and (APPLICATIONS1.visible_flag is null or APPLICATIONS1.visible_flag = 'true') and WORKFLOWS.visible_flag = 'true' and insts.instance_id = PROJECTTASKS.Instance_Id and PROJECTTASKS.Instance_Id = gps_insts.instance_id and gps_insts.active = 1 AND instance_props.IA_ISSUE_NUMBER = ISSUES.ISSUE_NUMBER AND instance_props.Instance_Id = PROJECTTASKS.Instance_Id union -- ISSUE's state is not available t_gps_instances table -- NOT STARTED tasks SELECT distinct APPLICATIONS1.APPLICATION_ID APPLICATION_ID, APPLICATIONS1.APPLICATION_NAME, WORKFLOWS.WORKFLOW_TYPE_ID, WORKFLOWS.WORKFLOW_TYPE_NAME, PROJECTTASKS.START_DATE, PROJECTTASKS.FINISH_DATE, PROJECTTASKS.DURATION, PROJECTTASKS.DAYSREMAINING, PROJECTTASKS.RESOURCE_NAME, PROJECTTASKS.USER_NAME, PROJECTTASKS.ACTUALSTART_DATE, PROJECTTASKS.ACTUALFINISH_DATE, STATES.State_Id, instance_props.IA_ISSUE_NUMBER, instance_props.IA_ISSUE_TITLE, NULL PROPERTY1, NULL PROPERTY2, PROJECTTASKS.INSTANCE_ID PROPERTY3, 22.2 PROPERTY4, PROJECTTASKS.PROJECT_TASK_ID, NULL COMMENTS, NULL USER_ID, NULL REASSIGN_STATE, -- priorty from project tasks if instance doesn't have priority decode(insts.priority, NULL, PROJECTTASKS.PRIORITY, insts.priority) PRIORITY, null owner, STATES.State_Name, (select DOCUMENT_ID from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_ID, (select DOCUMENT from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_PATH, ISSUES.CREATED_DATE, ISSUES.DUE_DATE, decode(PROJECTTASKS.DAYSREMAINING, 0, 'COMPLETED', 'NOT STARTED') STATUS, NULL MANAGER_COMMENTS, insts.due_date CURRENT_DUE_DATE, insts.PREDECESSOR, PROJECTTASKS.PROMISED_DUE_DATE, ---added application module from tri2 issues table by kchinta on 27/5/2009 ISSUES.Application_Module, ---added STATE_ORDER from T_GPS_WORKFLOW_STATES table by kchinta on 8/6/2009 STATES.STATE_ORDER ---added Migration Number and Migration Request id,, MIG_ISSUE_LINK_ID , NULL MIGRATION_NUMBER, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , ISSUES.ISSUE_ID ISSUE_ID, PROJECTTASKS.Critical FROM T_GPS_APPLICATIONS APPLICATIONS1, T_GPS_WORKFLOW_TYPES WORKFLOWS, T_GPS_WORKFLOW_STATES STATES, T_PROJECT_TASK PROJECTTASKS, t_workflow_state_task_v t_workflow_state_task_v, t_instances insts, t_gps_instances gps_insts, tri2gps_dev.issues ISSUES, v_instance_props instance_props WHERE PROJECTTASKS.STATE_ID = STATES.STATE_ID AND STATES.WORKFLOW_TYPE_ID = WORKFLOWS.WORKFLOW_TYPE_ID AND WORKFLOWS.APPLICATION_ID = APPLICATIONS1.APPLICATION_ID and PROJECTTASKS.state_id = t_workflow_state_task_v.state_id and (APPLICATIONS1.visible_flag is null or APPLICATIONS1.visible_flag = 'true') and WORKFLOWS.visible_flag = 'true' and insts.instance_id = PROJECTTASKS.Instance_Id and PROJECTTASKS.Instance_Id = gps_insts.instance_id and gps_insts.active = 1 AND instance_props.IA_ISSUE_NUMBER = ISSUES.ISSUE_NUMBER AND instance_props.Instance_Id = PROJECTTASKS.Instance_Id and not exists (select current_state_id from t_gps_instances where PROJECTTASKS.state_id = current_state_id and PROJECTTASKS.instance_id = instance_id) union -- This section retrieves all the non-workflow native tasks, which are assigned role/user -- No link to t_gps_instanceS is provided here, where these tasks are not workflow instances -- SKIPPED, STARTED, NOT STARTED and COMPLETED tasks SELECT APPLICATIONS.APPLICATION_ID APPLICATION_ID, APPLICATIONS.APPLICATION_NAME, WORKFLOWS.WORKFLOW_TYPE_ID, WORKFLOWS.WORKFLOW_TYPE_NAME, PROJECTTASKS.START_DATE, PROJECTTASKS.FINISH_DATE, PROJECTTASKS.DURATION, PROJECTTASKS.DAYSREMAINING, PROJECTTASKS.RESOURCE_NAME, PROJECTTASKS.USER_NAME, PROJECTTASKS.ACTUALSTART_DATE, PROJECTTASKS.ACTUALFINISH_DATE, STATES.STATE_ID, NULL IA_ISSUE_NUMBER, STATES.STATE_NAME IA_ISSUE_TITLE, NULL PROPERTY1, NULL PROPERTY2, NULL PROPERTY3, 22.2 PROPERTY4, PROJECTTASKS.PROJECT_TASK_ID, NULL COMMENTS, NULL USER_ID, NULL REASSIGN_STATE, PROJECTTASKS.PRIORITY PRIORITY, 'tri2support' owner, NULL STATE_NAME, NULL DOCUMENT_ID, NULL DOCUMENT_PATH, NULL CREATED_DATE, PROJECTTASKS.FINISH_DATE DUE_DATE, decode(daysremaining, 0, 'COMPLETED', --'STARTED' DECODE((SELECT DAYSREMAINING FROM T_PROJECT_TASK WHERE STATE_ID = PROJECTTASKS.STATE_ID - 1 and rownum = 1), 0, 'STARTED', NULL, 'STARTED', 'NOT STARTED')) STATUS, NULL MANAGER_COMMENTS, NULL CURRENT_DUE_DATE, NULL PREDECESSOR, PROJECTTASKS.PROMISED_DUE_DATE, -- added NULL for application module by kchinta on 27/5/2009 NULL, ---added STATE_ORDER from T_GPS_WORKFLOW_STATES table by kchinta on 8/6/2009 STATES.STATE_ORDER ---added Migration Number and Migration Request id,,MIG_ISSUE_LINK_ID , NULL MIGRATION_NUMBER, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , NULL ISSUE_ID, PROJECTTASKS.Critical FROM T_GPS_APPLICATIONS APPLICATIONS, T_GPS_WORKFLOW_TYPES WORKFLOWS, T_GPS_WORKFLOW_STATES STATES, T_PROJECT_TASK PROJECTTASKS WHERE PROJECTTASKS.STATE_ID = STATES.STATE_ID AND STATES.WORKFLOW_TYPE_ID = WORKFLOWS.WORKFLOW_TYPE_ID AND WORKFLOWS.APPLICATION_ID = APPLICATIONS.APPLICATION_ID and (APPLICATIONS.visible_flag is null or APPLICATIONS.visible_flag = 'true') and WORKFLOWS.visible_flag = 'true' and not exists (select * from t_workflow_state_task_v x where x.state_id = PROJECTTASKS.state_id) -- LCHINTA COMMENTS FOR SQL OBJECTS MAPPING TESTING IN MIGRATION TOOL ORDER BY 24, 30, 14, 37