View HLPDTEST.STATEGPS.T_PROJECT_TASK_V | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon Mar 01 01:05 IST 2021 |
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:
![]() ![]() |