View HLPDTEST.STATEGPS.MYVIEW | 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,
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
Possibly Referenced Tables/Views:
![]() ![]() |