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,*/ ISSUES.ISSUE_NUMBER IA_ISSUE_NUMBER, ISSUES.ISSUE_TITLE IA_ISSUE_TITLE, ISSUES.RAISED_BY RAISED_BY, (SELECT k.user_name from t_users k where k.user_id=RAISED_BY)RAISED_BY_NAME, ISSUES.ISSUES_ATTRIBUTE1, ISSUES.ISSUES_ATTRIBUTE2, ISSUES.ISSUES_ATTRIBUTE3, ISSUES.ISSUES_ATTRIBUTE4, 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, -- First check if any Task with 0 duration and Predecessor uncomplete DECODE(gps_insts.instance_present, 1, DECODE(PROJECTTASKS.DAYSREMAINING, 0, decode((select count(state_id) count1 from t_project_task a where a.instance_id = PROJECTTASKS.Instance_Id and a.state_id = PROJECTTASKS.State_Id group by instance_id, state_id), 1, 'STARTED', 'COMPLETED'), 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, t_gps_workflow_states ws WHERE gi.instance_id = pt.instance_id /* * The following existing two conditions are removed and added gi.current_state_id = ws.state_id condition * for handling PENDING tasks if Predecessory Issue task is Inter Workflow Integration Task and Issue is available in it * It used to show task status as STARTED for the above criteria before, after changing the conditions, STATUS is showing as PENDING, which is correct */ --AND gi.current_state_id = pt.state_id --AND pt.state_id =ws.state_id AND gi.current_state_id = ws.state_id AND gi.instance_id IN (SELECT ip.PRED_INSTANCE_ID FROM t_instance_predecessor ip, t_gps_workflow_states ws1 WHERE ip.instance_id = PROJECTTASKS.Instance_Id AND NVL(ip.pred_state_id, ws.state_id) = ws1.state_id AND ws.state_order <= ws1.state_order) -- 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 /*DECODE(PROJECTTASKS.DURATION, 0, 'SKIPPED', 'COMPLETED') :- To confirm a task as SKIPPED if its Duration is 0, eventhough Days Remaining is 0 : Added by schigurupati on 19th Feb, 2013*/ ), 1, 'PENDING', DECODE(PROJECTTASKS.DURATION, 0, 'SKIPPED', 'STARTED')))), 0, DECODE(PROJECTTASKS.DAYSREMAINING, 0, DECODE(PROJECTTASKS.DURATION, 0, 'SKIPPED', 'COMPLETED'), null, DECODE(PROJECTTASKS.DURATION, 0, 'SKIPPED', 'COMPLETED'), 'SKIPPED')) 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_TITLE, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , ISSUES.ISSUE_ID ISSUE_ID, PROJECTTASKS.Critical, NULL ISSUE_TYPE, UPPER(NVL(STATES.DESCRIPTION, STATES.STATE_NAME)) TAG, STATES.STATE_TYPE, gps_insts.instance_primary_id, gps_insts.created_date task_created_date, gps_insts.moved_out_date task_moved_out_date, ISSUES.Area, ISSUES.TAGS, --skolla issues.ATTRIBUTE5 ORA_TARGET_INSTANCE_ID, issues.ATTRIBUTE4 ORA_TASK_CODE, issues.ATTRIBUTE3 ORA_DETAIL_IMPL_ID, issues.ATTRIBUTE2 ORA_PACK_TASK_ID, issues.ATTRIBUTE1 ORA_CLOUD_SETUP_TYPE 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 gps_insts.instance_present = 1 /*AND instance_props.IA_ISSUE_NUMBER = ISSUES.ISSUE_NUMBER AND instance_props.Instance_Id = PROJECTTASKS.Instance_Id*/ AND ISSUES.INSTANCE_ID = insts.instance_id AND (gps_insts.visible_flag is null OR gps_insts.visible_flag = 'true') 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,*/ ISSUES.ISSUE_NUMBER IA_ISSUE_NUMBER, ISSUES.ISSUE_TITLE IA_ISSUE_TITLE, ISSUES.RAISED_BY RAISED_BY, (SELECT k.user_name from t_users k where k.user_id=RAISED_BY)RAISED_BY_NAME, ISSUES.ISSUES_ATTRIBUTE1, ISSUES.ISSUES_ATTRIBUTE2, ISSUES.ISSUES_ATTRIBUTE3, ISSUES.ISSUES_ATTRIBUTE4, 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.DURATION, 0, 'SKIPPED', 'COMPLETED') :- To confirm a task as SKIPPED if its Duration is 0, eventhough Days Remaining is 0 : Added by schigurupati on 19th Feb, 2013*/ DECODE(PROJECTTASKS.DAYSREMAINING, 0, DECODE(PROJECTTASKS.DURATION, 0, 'SKIPPED', '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_TITLE, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , ISSUES.ISSUE_ID ISSUE_ID, PROJECTTASKS.Critical, NULL ISSUE_TYPE, UPPER(NVL(STATES.DESCRIPTION, STATES.STATE_NAME)) TAG, STATES.STATE_TYPE, null instance_primary_id, null task_created_date, null task_moved_out_date, ISSUES.AREA, ISSUES.TAGS, --skolla issues.ATTRIBUTE5 ORA_TARGET_INSTANCE_ID, issues.ATTRIBUTE4 ORA_TASK_CODE, issues.ATTRIBUTE3 ORA_DETAIL_IMPL_ID, issues.ATTRIBUTE2 ORA_PACK_TASK_ID, issues.ATTRIBUTE1 ORA_CLOUD_SETUP_TYPE 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 gps_insts.instance_present = 1 /*AND instance_props.IA_ISSUE_NUMBER = ISSUES.ISSUE_NUMBER AND instance_props.Instance_Id = PROJECTTASKS.Instance_Id*/ AND ISSUES.INSTANCE_ID = insts.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 AND (visible_flag IS NULL OR visible_flag = 'true')) /*Added by S.ROY, as we're not deleting the instance track anymore from t_gps_instances table*/ ORDER BY 24, 30, 3, 14, 37