View HLPDTEST.STATEGPS.T_PROJECT_TASK_V Generated by
SchemaSpy
Legend: SourceForge.net
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
APPLICATION_ID number 22  √  null
T_GPS_APPLICATIONS.APPLICATION_ID Implied Constraint R
APPLICATION_NAME varchar2 255  √  null
WORKFLOW_TYPE_ID number 22  √  null
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID Implied Constraint R
WORKFLOW_TYPE_NAME varchar2 255  √  null
START_DATE date 7  √  null
FINISH_DATE date 7  √  null
DURATION number 22  √  null
DAYSREMAINING number 22  √  null
RESOURCE_NAME varchar2 2000  √  null
USER_NAME varchar2 2000  √  null
ACTUALSTART_DATE date 7  √  null
ACTUALFINISH_DATE date 7  √  null
STATE_ID number 22  √  null
T_GPS_WORKFLOW_STATES.STATE_ID Implied Constraint R
IA_ISSUE_NUMBER varchar2 100  √  null
IA_ISSUE_TITLE varchar2 4000  √  null
RAISED_BY number 20  √  null
RAISED_BY_NAME varchar2 255  √  null
ISSUES_ATTRIBUTE1 varchar2 2000  √  null
ISSUES_ATTRIBUTE2 varchar2 2000  √  null
ISSUES_ATTRIBUTE3 date 7  √  null
ISSUES_ATTRIBUTE4 date 7  √  null
PROPERTY1 varchar2 0  √  null
PROPERTY2 varchar2 0  √  null
PROPERTY3 number 22  √  null
PROPERTY4 number 22  √  null
PROJECT_TASK_ID number 22  √  null
T_PROJECT_TASK.PROJECT_TASK_ID Implied Constraint R
COMMENTS varchar2 0  √  null
USER_ID varchar2 0  √  null
REASSIGN_STATE varchar2 0  √  null
PRIORITY number 22  √  null
OWNER varchar2 0  √  null
STATE_NAME varchar2 255  √  null
DOCUMENT_ID number 22  √  null
T_INSTANCE_DOCUMENTS.DOCUMENT_ID Implied Constraint R
DOCUMENT_PATH varchar2 200  √  null
CREATED_DATE date 7  √  null
DUE_DATE date 7  √  null
STATUS varchar2 11  √  null
MANAGER_COMMENTS varchar2 0  √  null
CURRENT_DUE_DATE date 7  √  null
PREDECESSOR varchar2 4000  √  null
PROMISED_DUE_DATE date 7  √  null
APPLICATION_MODULE varchar2 50  √  null
STATE_ORDER number 22  √  null
MIGRATION_NUMBER varchar2 0  √  null
MIGRATION_TITLE varchar2 0  √  null
MIGRATION_REQUEST_ID varchar2 0  √  null
MIG_ISSUE_LINK_ID varchar2 0  √  null
ISSUE_ID number 20  √  null
CRITICAL number 22  √  null
ISSUE_TYPE varchar2 0  √  null
TAG varchar2 1000  √  null
STATE_TYPE varchar2 50  √  null
INSTANCE_PRIMARY_ID number 22  √  null
T_GPS_INSTANCES.INSTANCE_PRIMARY_ID Implied Constraint R
TASK_CREATED_DATE date 7  √  null
TASK_MOVED_OUT_DATE date 7  √  null
AREA varchar2 200  √  null
TAGS varchar2 1500  √  null
ORA_TARGET_INSTANCE_ID varchar2 2000  √  null
ORA_TASK_CODE varchar2 2000  √  null
ORA_DETAIL_IMPL_ID varchar2 2000  √  null
ORA_PACK_TASK_ID varchar2 2000  √  null
ORA_CLOUD_SETUP_TYPE varchar2 2000  √  null

Analyzed at Sun Nov 29 01:06 IST 2020

View Definition:
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
 
Possibly Referenced Tables/Views:


Close relationships: